Dimensional update but no new facts

View previous topic View next topic Go down

Dimensional update but no new facts

Post  omurchuc on Fri Mar 06, 2009 6:22 am

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,

omurchuc

Posts : 4
Join date : 2009-03-05

View user profile

Back to top Go down

Re: Dimensional update but no new facts

Post  dwbi_rb on Fri Mar 06, 2009 8:28 am

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!

dwbi_rb

Posts : 17
Join date : 2009-02-19

View user profile

Back to top Go down

Re: Dimensional update but no new facts

Post  BoxesAndLines on Fri Mar 06, 2009 9:45 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Dimensional update but no new facts

Post  Sponsored content


Sponsored content


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