Shrunken Dimension for Drilling across Fact Tables

View previous topic View next topic Go down

Shrunken Dimension for Drilling across Fact Tables

Post  KimballFan on Tue Jan 13, 2015 5:43 pm

Our Customer consists of attributes from 3 separate dimensions (PERSON, ACCOUNT, SERVICE AGREEMENT). We have Fact tables at each level of granularity and need to drill across. My first thought was to keep the 3 dimensions separate with a foreign key for each on fact tables that were at the lowest level. However, I've ended up with about 24 Dimension Keys on some of my fact tables (Mostly Dates). In addition to that, I found out that, in order to do rollups in OBIEE, I need the data to be in the same physical table or, they will need to join the dimensions through a fact table to create logical dimensions. This would not be great for performance. So, my new strategy is to have Customer Attributes from all 3 dimensions loaded into a SERVICE AGREEMENT/Customer Dimension) and join fact records at the Service Agreement Level to that dimension. My understanding is that, in order to drill across to fact tables at a higher level of granularity, I'm supposed to create a Shrunken Dimension from my base dimension. Is this supposed to be a logical dimension? If it's physical, I'm back to what I have. Adding a bridge table for this purpose would not be good for performance and I believe, would over complicate things. I will still need the PERSON and Account Dimensions as there are records from those tables that won't have Service Agreement records. Right now, the SA Dimension has about 8,000,000 records and over 80 attributes. I've moved the rapidly changing and seldom used attributes to an SA Attribute Dimension. Below is what I've currently designed to handle this data, please provide feedback if I should be doing something differently:
Dimension Tables:
1. PERSON (SCD2) 3,500,000 rows, 19 Attributes- Highest level, contains all persons regardless of relationship to accounts (or lack thereof). Linked to all Fact tables that are at the PERSON, ACCOUNT or SA Level
2. ACCOUNT (SCD2) 3,000,000 rows, 35 Attributes plus 15 Person Attributes- 2nd Level for Customer, contains all accounts with attributes from PERSON DIM related to the Main Customer that are used for rollups (Later implementation will probably add attributes from other persons). Linked to all Fact tables that are at the ACCOUNT or SA Level
3. SERVICE AGREEMENT (SCD2) 8,000,000 rows, 38 attributes plus 26 Account and Person Attributes - Lowest level of customer, contains attributes from Person and Account Dimensions used for rollups and only those related to Main Customer. It's pretty rare that anyone cares about other persons tied to an account but, I see a need to add them in the future. Linked to all Fact tables that are at the SA Level.
4. SA Attribute (Junk Dimension) 20,000 rows and 32 attributes - Rapidly Changing and Seldom Used attributes related to the Service Agreement. Records from this table are linked to all fact tables that link to the SA Dimension

KimballFan

Posts : 11
Join date : 2014-01-15
Location : Tucson

View user profile

Back to top Go down

Re: Shrunken Dimension for Drilling across Fact Tables

Post  nick_white on Wed Jan 14, 2015 12:39 pm

I am not entirely sure I have understood fully what you are doing so apologies if I have misunderstood...

Person, Account and Service Agreement are (normally) completely independent Dimensions so I'm not clear why you have the same attributes spread across these Dims. Your Dims should only have attributes relevant to that Dim and your Fact tables you should have FKs to each of these Dims where the Fact table grain includes that Dim. Given what you've described, I would expect every fact that has an FK to Service Agreement to also have FKs to Account and Person; every Fact that has an FK to Account would also have an FK to Person.

You can then drill across using the Dims that exist against both facts - so if one fact has the SA Dim and the other doesn't you can only drill across using the attributes on the Person and Account Dims. There is no need for shrunken dimensions.

When you do rollups (aggregations) you just roll up to use the Dims you have available e.g. if you want to roll-up a fact that has SA, Account and Person to the Account and Person level you just take the SA key out of the new fact and aggregate by the Account and Person keys.

What you are doing sounds about similar to aggregating up a time hierarchy - where you start with a date dim that has month and year attributes and then you create a month dim and year dim when creating facts aggregated at the month/year levels. However this pattern is only applicable when you are rolling different grains of the same entity (in this case "Time") - Person, Account and SA are not the same entity so this pattern is not the way to go.

Hope this helps?

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Reply with quote Re: Shrunken Dimension for Drilling across Fact Tables

Post  KimballFan on Wed Jan 14, 2015 3:12 pm

Thanks. That was my original design. I started second guessing when I saw the centipede Fact table that resulted. I forgot to mention that my design also includes Type 7 dimensions to tie fact records to the current versions of those dimensions. That adds another set of dimensions keys. I'm limited on my knowledge of OBIEE so, when the developer told me that data used in a roll up needed to be on the same table or joined through a Fact table, I went down the path of adding the data to the dimensions to avoid performance issues. After reading your post, It appears I should have stuck with my instincts. That's the easier solution for me since I already did most of the ETL and Data Model for that route so, good news! Thanks for the sanity check

KimballFan

Posts : 11
Join date : 2014-01-15
Location : Tucson

View user profile

Back to top Go down

Re: Shrunken Dimension for Drilling across Fact Tables

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum