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

[georgi.georgiev]

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 !

[LAndrews]

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.