Conformed Dimensions: the Key to Cross Process Analysis

Over the past few months, I’ve been spending a lot of time reading about, thinking about, and working on conformed dimensions for a client.  The reason is simple, conformed dimensions enable businesses to track richer (and typically more complicated) metrics spanning multiple business processes.

Note: there are many additional benefits of conformed dimensions…for a more thorough explanation, start here.

There are many examples of where one might want to link data across multiple business processes, the textbook example being P&L reporting which can require linking facts across nearly all business processes to provide a bottom-up reporting solution that allows end-users to report top level numbers and drilldown into the base transactions.  Another example, and the one we’re going to cover in this blog post, is tracking a metric called Gross Margin Return on Inventory Invested.

Gross Margin Return on Inventory Investment (GMROII) is a ratio in microeconomics that describes a seller’s income on every unit of currency spent on inventory. It is one way to determine how valuable the seller’s inventory is, and describes the relationship between total sales, total profit from total sales, and the amount of resources invested in the inventory sold. A seller will aim for a high GMROII.

Fact and Dimension tables are the building blocks of a Star Schema

Below is an example of a star schema based on a fact table surrounded by several dimension tables.

As you can see, the fact table represents the center of the star while the dimension tables represent the individual points.  There’s theoretically no limit on the number of points (or dimensions) a star schema can have, but a rule of thumb is to limit it to no more than 20-26 dimensions to avoid cluttering up the UX.  If you find yourself approaching that number of dimensions, you might want to re-evaluate the dimensions and see if it’s not possible to consolidate.

Star Schemas are the building blocks of a Dimensional Model

A dimensional model typically consists of multiple business processes.  And each business process is usually represented by multiple fact tables.  And each fact table forms the center of a star schema (as we learned in the last section). Thus, the logical conclusion is that a dimensional model typically consists of multiple star schemas.

Below is an example of a very simple dimensional model consisting of 2 fact table star schemas:

The observant reader will notice these 2 fact tables (Sales/Inventory) share several dimensions in common:

Now, in order to link facts from the sales fact table (sales process) and inventory fact table, we must use the same dimensions:

And that is the idea of conformed dimensions in a nutshell 🙂

Cross Process Analysis

Now that we understand how to build a dimensional model based on star schemas built upon fact tables and conformed dimensions, let’s walk through an example of how to create a cross-process metric. The exact measure we are going to build is the Average Monthly GMROII based on a trailing 6 month window and is calculated by taking the sum of gross profit for the total period (last 6 months) divided by the sum of each month-ending inventory cost value.

Note: GMROII is an interesting metric with a lot of criticism due mainly to it’s oversimplification of inventory performance and lack of control over sales/inventory expenses. This paper does a nice job of explaining the problems associated with GMROII and factors to consider in order to use it effectively. It also goes on to suggest and define an alternative measure called DPP or Direct Product Profit which appears to overcome many of the issues w/ GMROII at the cost of a bit more complexity.

For this task, we’ll be using the Adventure Works 2012 DW sample database to create the GMROII measure which spans the sales and inventory processes.

Here’s a look at the fact tables (back in the relational DW) that we’ll link together to create this calculated measure in the SSAS cube:

 

The FactProductInventory table is a periodic snapshot fact table containing a snapshot of each product in inventory at the end of each day.  I don’t believe the earlier versions of the AdventureWorks DW sample database contain the FactProductInventory, so be sure you’re using the 2012 version.  Also, because this fact table contains snapshots for products that haven’t been assigned to a product subcategory and product category, I’ve created the following view which filters out these items.  Another, and possibly better (but more time consuming) solution would be to create unknown (or unassigned) records in the product subcategory/category tables and update these products to point to those unknown records.  Either way, you must address this issue before proceeding else you’ll run into the dreaded missing attribute key during fact processing.

[sql]
CREATE VIEW dbo.vFactProductInventory AS
SELECT f.ProductKey
,f.DateKey
,f.MovementDate
,f.UnitCost
,f.UnitsIn
,f.UnitsOut
,f.UnitsBalance
,TotalCostOfBalance = (f.UnitCost * f.UnitsBalance)
FROM AdventureWorksDW2012.dbo.FactProductInventory f
INNER JOIN AdventureWorksDW2012.dbo.DimProduct p
ON p.ProductKey = f.ProductKey
INNER JOIN AdventureWorksDW2012.dbo.DimProductSubcategory psc
ON psc.ProductSubcategoryKey = p.ProductSubcategoryKey
INNER JOIN AdventureWorksDW2012.dbo.DimProductCategory pc
ON pc.ProductCategoryKey = psc.ProductCategoryKey
[/sql]

Next we need to add this table/view to the DSV in our SSAS project:

Then we can build a new measure group in the AdventureWorks cube:

 

[Last Inventory Cost Value] uses the LastNonEmpty aggregation function and is based on a calculated column in the DSV that multiplies Units Balance and Unit Cost at the leaf level. A better idea is to push this calculated column down into the view…I’m not a big fan of creating calculated columns in the DSV but will often do so in labs/demos for the sake of time when it is an after thought.

And here’s a look at the dimension usage tab which we need to take into consideration anytime want to create calculated measures spanning multiple measure groups.

In the screenshot above, I’ve highlighted the (conformed) dimensions in common between the 3 tables we are interested in linking together.  This defines the dimensionality which we need to take into consideration when building and using the calculated measure.

Total Gross Profit

[sql]
Create Member CurrentCube.[Measures].[Total Gross Profit]
As
[Measures].[Internet Gross Profit] +
[Measures].[Reseller Gross Profit]

,Format_String = "Currency"
,Associated_Measure_Group = ‘Product Inventory’
;
[/sql]

Total Gross Profit – Trailing 6 Months

[sql]
Create Member CurrentCube.[Measures].[Total Gross Profit – Trailing 6 Months]
As
SUM (
{
PARALLELPERIOD(
[Date].[Calendar].[Month]
,5
,[Date].[Calendar].CurrentMember
)
:
[Date].[Calendar].CurrentMember
}
,[Measures].[Total Gross Profit]
)
,Format_String = "Currency"
,Associated_Measure_Group = ‘Product Inventory’
;
[/sql]

Total Inventory Cost Value – Trailing 6 Months

[sql]
Create Member CurrentCube.[Measures].[Inventory Cost Value – Trailing 6 Months]
As
SUM (
{
ANCESTOR(
PARALLELPERIOD(
[Date].[Calendar].[Month]
,5
,TAIL(
DESCENDANTS(
[Date].[Calendar].CurrentMember
,[Date].[Calendar].[Date]
)
,1
).Item(0)
)
,[Date].[Calendar].[Month]
)
:
ANCESTOR(
TAIL(
DESCENDANTS(
[Date].[Calendar].CurrentMember
,[Date].[Calendar].[Date]
)
,1
).Item(0)
,[Date].[Calendar].[Month]
)
}
,[Measures].[Last Inventory Cost Value]
)
,Format_String = "Currency"
,Associated_Measure_Group = ‘Product Inventory’
;
[/sql]

GMROII

[sql]
Create Member CurrentCube.[Measures].[GMROII]
As
[Measures].[Total Gross Profit – Trailing 6 Months]
/
[Measures].[Inventory Cost Value – Trailing 6 Months]
,Format_String = "Percent"
,Associated_Measure_Group = ‘Product Inventory’
;
[/sql]

Now, after deploying and publishing the project, our users now have the ability to track the Average Monthly GMROII for the trailing 6 months by product over time:

Furthermore, we can easily extend the capabilities to include Prior-Year and YoY versions of the GMROII:

[sql]
Create Member CurrentCube.[Measures].[GMROII – Prior Year]
As
(
ParallelPeriod(
[Date].[Calendar].[Calendar Year]
,1
,[Date].[Calendar].CurrentMember
)
,[Measures].[GMROII]
)
,Format_String = "Percent"
,Associated_Measure_Group = ‘Product Inventory’
;
[/sql]
[sql]
Create Member CurrentCube.[Measures].[GMROII – YoY]
As
[Measures].[GMROII] – [Measures].[GMROII – Prior Year]
,Format_String = "Percent"
,Associated_Measure_Group = ‘Product Inventory’
;
[/sql]