Dimensional update but no new facts
3 posters
Page 1 of 1
Dimensional update but no new facts
Folks,
In a typical Star schema using Type 2 SCD's,
My question is:
When a change happens to the dimension but no new facts are created.
Should a new fact record be created? (Essentially a duplicate of the previous record but with the new time FK)
The reason being that if you want to view the dimensional data with the time dimension (e.g. model status for 21 of Jan 2009) using no facts there would need to be a join between the time dimension and the model dimension using the fact table.
Thoughts suggestions?
Is this correct?
Thanks,
In a typical Star schema using Type 2 SCD's,
My question is:
When a change happens to the dimension but no new facts are created.
Should a new fact record be created? (Essentially a duplicate of the previous record but with the new time FK)
The reason being that if you want to view the dimensional data with the time dimension (e.g. model status for 21 of Jan 2009) using no facts there would need to be a join between the time dimension and the model dimension using the fact table.
Thoughts suggestions?
Is this correct?
Thanks,
omurchuc- Posts : 4
Join date : 2009-03-05
Re: Dimensional update but no new facts
Type 2 SCDs are used so that you can historically trace the dimension in use at specific time periods. From a design perspective, I would think if there is a new record in the Type 2 SCD, that has not yet been used in the Fact table, it only means that all fact table data retrieved (for the period prior to the new Type 2 SCD record getting effective), will be mapped on to the previous version of the dimension attribute.
However, if you need to see the historical fact records mapped on with the latest dimension attribute, then a hybrid solution of handling the SCD might help.
With regards to designing Type 2 SCDs, there is an article by Ralph Kimball on the following link -
http://www.intelligententerprise.com/020812/513warehouse1_1.jhtml
Hope this helps!
However, if you need to see the historical fact records mapped on with the latest dimension attribute, then a hybrid solution of handling the SCD might help.
With regards to designing Type 2 SCDs, there is an article by Ralph Kimball on the following link -
http://www.intelligententerprise.com/020812/513warehouse1_1.jhtml
Hope this helps!
dwbi_rb- Posts : 17
Join date : 2009-02-19
Re: Dimensional update but no new facts
No you should not create a new fact row. This would alter you metrics. Here's the solution I have used to provide historical and current view of dimension.
1 - Create a new column on your dimension table. This column will become a FK (of sorts) on your fact table. The value of column will remain constant across all iterations of history. So if I have a product dimension, and the product "widget" has 5 rows in the dimension table, the new column value will be constant across all 5 versions of history.
2 - Add the new column to your fact table
If you want historical view, use the existing FK from the dimension table. If you want the current view, use the new column where current_row_ind = 'Y', or whatever column you use to identify the current dimension row.
1 - Create a new column on your dimension table. This column will become a FK (of sorts) on your fact table. The value of column will remain constant across all iterations of history. So if I have a product dimension, and the product "widget" has 5 rows in the dimension table, the new column value will be constant across all 5 versions of history.
2 - Add the new column to your fact table
If you want historical view, use the existing FK from the dimension table. If you want the current view, use the new column where current_row_ind = 'Y', or whatever column you use to identify the current dimension row.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» What to do when facts change? Update the fact table with update and deletes? or use SCD type 2?
» update facts?
» Inventory facts and production facts
» Accumulating Snapshots: Can we update the non-milestone dimensional data associated with the shapshot?
» How best to model Timesheet facts against Sales Order facts
» update facts?
» Inventory facts and production facts
» Accumulating Snapshots: Can we update the non-milestone dimensional data associated with the shapshot?
» How best to model Timesheet facts against Sales Order facts
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum