We recently had scenario where our users wanted to be able to view some sales data by ‘ID Number’ attribute. This attribute, which existed in our fact table as a degenerate dimension, with no related dimension table we originally included as a logical column in the FACT – Sales table in the RPD BMM.
This worked for basic reporting, but our users had a requirement to be able to see the sum of sales grouped by ‘ID Number’ as a percent of total sales (of all ID Numbers). This seemed fairly straight forward, since we already had level based measures for total sales and already had a sales mix % measure. However, because the ‘ID Number’ column didn’t have an associated hierarchy (as it existed only in the fact table), we couldn’t set any levels.
At first, we tried to solution in Answers by creating a pseudo level based measure using the expression ‘SUM(SALES) / SUM(SALES) BY ()’. This allowed for the correct sales mix %, but if users tried to drill down the fiscal dates hierarchy or added other dimensional attributes, the SUM(SALES) BY () expression always caused the sales to be a % of total sales for the entire report.
Reading through this Rittman Mead article as well as this post, I was able to create the following RPD design, which called for creating a new logical dimension and an associated hierarchy for ‘ID Number’ as well as having this column available in four logical tables sources (both ‘this year’ and a ‘last year’ alias, and different levels of aggregation)
In this scenario, we had two phyiscal fact tables (each with an LY alias against a dates dimension not shown). Physical model shows the following, where all four alias tables joined to a regular dimension:
The business model diagram shows two dimensions (one for the regular dimension and the newly created logical table for the degenerate ID column):
I added all four fact aliases to the new degenerate logical table, and only created the single logical column ‘ID Number’. I then created a hierarchy on this logical table, with a grand total level, and an ID Number Detail level. Levels were set on all applicable logical table sources for both the fact table and the degenerate dimension.
The two ‘total’ level based measures were set to the Grand Total level of the new hierarchy.
Lastly, we replace the presentation ‘ID Number’ column in the presentation layer (which was originally mapped to the ‘ID Number’ column in the logical fact table) with the ‘ID Number’ column from the new Degenerate Dimension logical table.
This allowed for reports to be written in Answers using the sales mix % measures, as well as ensuring the that drilling down or adding additional columns in Answers doesn’t cause incorrect results.