'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'

View previous topic View next topic Go down

'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'

Post  georgi.georgiev on Sat Sep 29, 2012 10:08 am

We have a fact table which as described in the topic can point to multiple members of a dimension: let me clarify with some example: if sell a TV (unique id) that can show 5 colors and show 10 different programs we will end with 50 entries in the fact table in order to be able to answer the question how many units can show 10 programs (on rows) and 5 colors (on columns) since the default aggregation is count distinct. Furthermore if we want to answer the business question from above for a given day or time period, we will have to perform entries for every day. So from one unit (if it is in stock for a month) we will end up with 1*5*10*30 = 1500 entries and if we have 40000 TVs after a year we won't be able to scale just by using the star schema. What I'm thinking is to have some sort of 'bridge' tables that contains of all possible combinations on colors and one for all combinations of programs as follows:

id | dim_color_id | lookup_id
1 | 3 | 3,4,5,6,7
1 | 4 | 3,4,5,6,7
1 | 5 | 3,4,5,6,7
1 | 6 | 3,4,5,6,7
1 | 7 | 3,4,5,6,7

So that by looking up all the values we can retrieve 1 as id that will point to the bridge table that will connect to dim_color_table so that we can have only one entry but not five in the fact table.

I think this is very interesting case though it makes my head explode for I can't imagine the design.

Has anyone had the same issue or sees a pitfall in the design please do comment !

georgi.georgiev

Posts : 1
Join date : 2012-09-29

View user profile

Back to top Go down

Re: 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'

Post  LAndrews on Sun Sep 30, 2012 12:24 pm


You are heading in the right direction by thinking about bridge tables.

The grain of your fact should be 1 record for each TV sale.

Bridge tables can then be used to provide the M:M relationship to your Color Dimension and your Program Dimension.

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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