relating 2 existing dimensions combined to a fact table
3 posters
Page 1 of 1
relating 2 existing dimensions combined to a fact table
given:
* 1 existing sales fact table; > 1.500.000.000 records
* 1 existing product dimension; >= 300.000 records
* 1 existing store dimensions; >= 1.000 records
required:
* 3 new attributes, defined by the combination of a certain store and a certain product --> most likely a new table 'x'
* not all combinations of store & product in the sales fact table will be present in the new table 'x'
* if possible, no modification of the sales fact table
* result should show the 3 new attributes in relation to the sales fact table showing attributes values for all store / product combination present in the fact table.
thanks for your appreciated feedback.
brgds,
nodderwaak
* 1 existing sales fact table; > 1.500.000.000 records
* 1 existing product dimension; >= 300.000 records
* 1 existing store dimensions; >= 1.000 records
required:
* 3 new attributes, defined by the combination of a certain store and a certain product --> most likely a new table 'x'
* not all combinations of store & product in the sales fact table will be present in the new table 'x'
* if possible, no modification of the sales fact table
* result should show the 3 new attributes in relation to the sales fact table showing attributes values for all store / product combination present in the fact table.
thanks for your appreciated feedback.
brgds,
nodderwaak
nodderwaak- Posts : 2
Join date : 2010-04-12
Re: relating 2 existing dimensions combined to a fact table
A 'junk' dimension may suffice. A junk dimension is a dimension whose natural key is the attributes in the table. You would create rows when a new combination of attributes is encountered. They work well if there is a resonable correlation between the attribute values.
It's difficult to say, not knowing the nature of the attributes.
It's difficult to say, not knowing the nature of the attributes.
Re: relating 2 existing dimensions combined to a fact table
thanks for your feedback.
A junk dimension will not do the trick; Because as said before, this would mean that we need to update or reload the 1.500.000.000 record table, which is not the best option, because may be in the future
1) the huge number of records / downtime
2) the combined store / product dimension might be related to another fact table
3) the combined store / product dimension might change over time (I mean: not SCD type xyz, but just another definition
ideally the solution does not impact a fact table and offers a combined store / product dimension which can be plugged in, into an fact table containing store & product + the requirement that not every store / fact combination is covered in the combined dimension table.
I welcome any other suggestion.
Thanks again for your appreciated feedback.
Nodderwaak
A junk dimension will not do the trick; Because as said before, this would mean that we need to update or reload the 1.500.000.000 record table, which is not the best option, because may be in the future
1) the huge number of records / downtime
2) the combined store / product dimension might be related to another fact table
3) the combined store / product dimension might change over time (I mean: not SCD type xyz, but just another definition
ideally the solution does not impact a fact table and offers a combined store / product dimension which can be plugged in, into an fact table containing store & product + the requirement that not every store / fact combination is covered in the combined dimension table.
I welcome any other suggestion.
Thanks again for your appreciated feedback.
Nodderwaak
nodderwaak- Posts : 2
Join date : 2010-04-12
Re: relating 2 existing dimensions combined to a fact table
Not knowing the nature of the attributes, it is difficult to comment on what is the appropriate solution.
Certainly you can build a table with the store and product keys. Technically, it is not a dimension, but rather a factless fact table with 3 degenerate dimensions (the three attributes). But, that's splitting hairs...
Certainly you can build a table with the store and product keys. Technically, it is not a dimension, but rather a factless fact table with 3 degenerate dimensions (the three attributes). But, that's splitting hairs...
Re: relating 2 existing dimensions combined to a fact table
It seems like you are not modifying the grain of your fact table by adding these new attributes, right?
In other words,these attributes can be safely added to the schema without "reloading" the fact.
So, you need to evaluate whether they belong to a bridge table to product or store, or as an independent entity to this schema based on the nature of these attributes as ngalemmo mentioned.
Does this makes sense?
In other words,these attributes can be safely added to the schema without "reloading" the fact.
So, you need to evaluate whether they belong to a bridge table to product or store, or as an independent entity to this schema based on the nature of these attributes as ngalemmo mentioned.
Does this makes sense?
sanjayvyas- Posts : 3
Join date : 2010-04-13
Similar topics
» Relating dimensions together and fact table design
» Integrating new fact table which has one to many relationship with the main fact table in existing star schema
» Create a new Fact table from an existing Fact table
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» Dimensions in fact table
» Integrating new fact table which has one to many relationship with the main fact table in existing star schema
» Create a new Fact table from an existing Fact table
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» Dimensions in fact table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum