Good Morning Everyone,
Hopefully there is someone that is available to answer my question.
I have an OLAP Cube currently residing in or Analysis Services Environment that is Horizontal, my boss an Essbase guy would like me to manipulate the data in such a way so that it is Vertical with a measures dimension that has all of the actual measures and calculations in the measures dimension. I am not having trouble with the measures dimension from the standpoint of actual columns, I have filtered out all of the data with 0's and trimmed down the number of items shown if there is no data for the column or attribute. The problem I am having is with the calculated measures, some of which are percentage based. I tried to use custom rollups but am not good enough to implement them because I always get back invalid data.
Anyone out there that can help, it would be greatly appreciated. Let me know what else I need to post and I will get that information posted as well.
a 'Vertical' fact table is normally used for accounting/finance cube. It's unusual to 'dimensionalize' the measures into 1 column, especially if they are not additive to each other.
Having said that, if you decided to do it, create a Dim_Measure table (in accounting/finance we have account dimension), containing these 5 columns:
On the DSV, create a relationship from Parent_Measure to Measure_Key (Measure_Key as PK).
Create a dim with 2 attributes:
The 'vertical' fact table contains a few dimensional key column as usual (one of them is Measure_Key), plus 1 column called Amount. It is the value of Measure_Key column that determines what measure it is.
On the Measure attribute, set the KeyColumn property to Measure_Key column, NameColumn property to Measure_Name column, Usage = Key.
On the Parent attribute, set usage = parent, UnaryOperatorColumn = Unary_Operator column, CustomRollupColumn = Custom_Rollup column
Populate the Dim_Measure table:
Measure_Key: surrogate (1,2,3...)
Measure_Name: as per your project
Parent_Account: set to 1 (all rows)
Unary_Operator: set to ~ (all rows)
Custom_Rollup: ([Dim_Measure].[Measure].&, [Measures].[Amount]) + ([Dim_Measure].[Measure].&, [Measures].[Amount])
8 and 9 are examples only, replace them with the corresponding row.
+ is an example only, replace it with the formula in your project
Deploy, process, browse.
Hope this helps and good luck with your project.
PS. There is a property called CustomRollupPropertiesColumn that we can use to set the decimal format etc.
I have done something similar to this before. I was wondering, how could I email you directly if I needed to for possible assistance. The custom Rollups I tried to use before never gave me any data only #xxx.
Thanks for the assistance!
OK I am going to need a little more help on this with the measures dimension. Can you assist again.
Here is the data as displayed.
7 ForecastLbs 1 ~ NULL 8 CBPLbs 1 ~ NULL 9 FirmOrderLbs 1 ~ NULL 10 PlannedOrderLbs 1 ~ NULL 11 GoalLbs 1 ~ NULL 12 HistLbs 1 ~ NULL 13 ActualLbs 1 ~ NULL 15 FirmOrderAdjustedQty 1 ~ NULL 16 FirmOrderShortedQty 1 ~ NULL 30 CBP vs. Actuals 1 ~ (([Dim Measures].[Measures].[Measure Name].&[CBPLbs], [Measures].[Current]) - ([Dim Measures].[Measures].[Measure Name].&[ActualLbs],[Measures].[Current])) 1 Measures NULL ~ NULL NULL NULL NULL NULL NULL
Hi Scott, the MDX you posted above indicates that you have a hierarchy called 'Measures] in Dim Measure. If you don't have this hirarchy then the MDX should be:
([Dim Measures].[Measure Name].&[CBPLbs], [Measures].[Current]) - ([Dim Measures].[Measure Name].&[ActualLbs],[Measures].[Current])
Try it on MDX query window on SSMS first, i.e.
select ([Dim Measures].[Measure Name].&[CBPLbs], [Measures].[Current]) on 0 from cube1
Thanks for your reply. I had not checked this in a few days and I had figured this out accidentally but had to use a number instead.
Now I have a different problem which I will post to you as well as to the group.
I have a dimension (Product) that uses has a hierarchy Brandtype, brand, subbrand, upc and productdimid
Some times the dimension processes fine other times the brandType does not aggregate the data properly and I get skewed numbers.
about the BrandType product dimension that sometimes does not aggregate properly resulting in skewed numbers, a possilbe cause to this is: the 'attribute relationship' of the attributes used in the hierarchy is not correctly set.
For example, in your case, say that from top level to bottom the hierarchy is:
So 'under' the brand attribute you have an attribute relationship to brandtype. And 'under' subbrand you have an attribute relationship to brand. Then we remove the brandtype and brand attribute from 'under' the dimension key attribute, because brand 'relates' to the key attribute via subbrand, and brandtype 'relates' to the key attribute via brand and subbrand.
If these attribute relationship is not set properly, we would have a skewed result.
I normally 'debug' this problem by removing all attribute relationships in all attributes used in the hierarchy, leaving all attributes directly 'connected' to the key attribute. Process full the database and browse the cube to check that all attributes (all levels) produce the correct numbers (compared to the transact-SQL query).
Then I add 1 attribute relationship in 1 attribute, process the database (deploy the database with process = default is quicker than processing the database), browse the cube and check the numbers again.
Then add the other attribute relationships and check again. Then finally I'd create the hierarchy and check again.
Other possible cause is: in the 'dimension usage' the product dimension is not correctly related to the measure group. Double click the elipsis (...) button on the intersection 'cell' between product dimension and the measure group and check which column on the dimension is connected to which column on the fact table. If this is correct, check all other 'cells' in the same column (i.e. the same measure group but different dimension). And finally, check the many-to-many relationship. I usually debug this by setting all cells (except 1 that I'm investigating) to 'no relationship'. If this gives the correct numbers, I'd slowly rebuild the dimension relationships.
Other possible cause is the unknown rows. A null in the fact table dimension key column is not mapped to any row in the product dimension, causing those fact rows to be excluded in the measure totals. To debug/proof this, we can set the UnknownMember of the product dimemension to 'visible'.
Other possilbe cause of a skewed result is many-to-many relationship where the 'bridge' fact table (the one containing the 2 dimension key columns) does not have the full set of both dimensions. For example: say that the sum of measure1 in the Fact1 table is $5million. We have dim1_key in this Fact1 table connecting it to Dim1 table. Dim2 'connects' to the Fact1 table via Fact2 table containing 2 columns: dim1_key and dim2_key. Fact2 is the bridge table to support many-to-many relationship between Dim2 and the main fact table. In the case where Fact2 does not contains some of dim1_key that exist in Fact1, we have a skewed sum(measure1) against Dim2. So if your product dimension connects to the fact table via a bridge table (i.e. many-to-many relationship), this paragraph is a possible cause. But if your product dimension connects to the fact table directly (regular relationship) then ignore this paragraph, it is not a possible cause.
As part of this effort, would there be anyway to take those data elements for the "measures" dimension and display them as attributes-- allowing users to select which element they want to see from an attribute perspective?