Summarizing over Outrigger Dimensions Directly (e.g. Total Customers by Demographic by Signup Date)

All we need is an easy explanation of the problem, so here it is.

According to Kimball:

https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/outrigger-dimension/

A dimension can contain a reference to another dimension table. For instance, a bank account dimension can reference a separate dimension representing the date the account was opened.

This suggests to me you’d be able to just query the Account dimension directly if you wanted to track number of accounts opened over time, for example. Is this common?

In my case, I’m modeling Customer as a dimension, which also references Date (for Signed Up Date) and Demographic dimensions.

A common kind of query the business ask is how many signups we’ve had over time by demographic, for example. This doesn’t involve a fact table at all. All this can be worked out by summarizing the dimension itself. Does this indicate some kind of a smell in the schema design, or is this just a trait of certain kinds of dimensions where the dimension itself has some significance to the business?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Conceptually, CustomerSignup is a fact. But if every Customer has exactly one Signup, you only care about the date, and there no measures associated with it, it might as well just be stored on the Customer dimension.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply