Maybe this is a bit out there… but what if there was a “Dimension Builder” tool within Database Designer?
My use case is that I wanted to create a sub-dimension called “Market” on my Sales database to be able to group my price branches. I think that’s a pretty common thing for people to do. What I ended up doing is making a quick CSV containing 2 columns - Branch and Market. I uploaded the CSV to my files on Designer and dragged it onto the Price Branch dimension as a data item. I tied the Branch to the Price Branch, then used the Market column to create the sub-dimension of Market under Price Branch.
The issues I foresee with this are mainly maintenance related. When a new branch is added in the future, I will have to update my CSV file then re-upload and Replace the data file. Otherwise, I could make it a Sync item but I would still need to manage the spreadsheet off of Phocas. This also limits the maintenance to be done by me unless somebody else has access to the spreadsheet.
If there was some type of Dimension Builder that lived within Phocas, then I (or anybody with proper permission in the future) could theoretically manage this from right within the tool. I would envision it to be similar to the Drivers on the BF&P tool, where it’s like a spreadsheet that can be referenced and continually updated. I know there are ways to use Transform Columns currently, but a more intuitive way for the most technological-illiterate administrator (like I can be ) to create new columns/dimensions would be nice.
The “Driver” tab could theoretically live right in the data items area where I currently dropped my market breakout. Perhaps the data itself would show just as the existing data items, but the columns/data itself are managed in a pop-up screen:
Hi Tim, If I understand you correctly, you want to essentially use an existing dataset to create either a group summary or a comparison summary (variance)?
If so, I frequently use similar methods by creating a dataset view in EDA Sync. If you do not have access to this, your rep may be able to create the view and dataset for you to use.
Hi @dvic , Not quite, unless we’re saying the same thing in different ways. This came about because we’re going to start looking at data on a “market” level for the first time. Whereas, in the past, we’ve looked at things by Price Branch. Each “market” would comprise multiple price branches. I wanted to just create a new Dimension to allow Market Managers (or others) to quickly select an entire Market at once. For example, if the North Market was branches 1, 2, and 3, I wanted the user to be able to go to the Market dimension, select “North”, hit Focus, then away they go; instead of going to Price Branch, selecting 1, 2, and 3, then hitting Focus.
I suppose you could say I wanted a new way to view/slice an existing dataset, though this could be used as a group summary by Market and/or to see variances between entire markets too.
I’m an Eclipse user but we’re not on EDA. We’ve been on Phocas since before that deal was struck.
Got it! Thanks for the clarity. A really simple way to create rollups of a dimension is to add a static subset of data with all the information (most likely an csv upload of branch/region lists, which I use in example below).
When you select the 'Parent Dimension, you’ll see an option to “Drag Here”, which you would drag and add the branch list & regions dataset:
This data set Absolutely must have distinct values of the Parent Dimension (In this case Branch ID) and must not have duplicates!!! This is very important to avoid duplicating values. Drag your key (branch id) to the “Key” column on the far right.
You can then drag any of the columns from your Branch List and add them as sub dimensions to your Parent dimension. You can go as far as creating a column on your Branch list with a value of ‘Total’ to use that as a sub dimension rollup of your parent dimension…super useful for various odd reasons.
In my screenshot from the original post, the data file called “Market Breakout by Branch PHOCAS UPLOAD” is exactly what you were describing - a static CSV file that I created and uploaded to achieve my desired result. The product suggestion was meant to describe practically a creation of a CSV-like file within the Phocas platform itself that could be easily updated in the future, instead of having to upload a static file from outside the software each time.
) to create new columns/dimensions would be nice.
