Building Data warehouses on Near Real time data

[nobbydude]

I have an accumulating snapshot fact table as the business goes through different stages for a particular grain/event. This data will be brought using Power Exchange CDC so essesntially everytime the transaction system hits commit , we bring data over.
Analysis of the source data ensures I can build Type 2 SCD that gets assigned to the fact.
My question is as we are bringing this data real time ( once a day) , this dimension data could possibly change which then leads to a 2 fold problem

1) Dimension table will grow due to new values in the dimension attributes
2) Do I update the foreign key to the dimension in my fact table ? I have read in some past posts it is both good and bad.


Any ideas ?

[ngalemmo]

The purpose of a Type 2 dimension is to allow the fact to reference the dimension version that existed at the time the fact took place.  If you update the foreign keys in the fact table to reference the most current version of a Type 2 dimension row, you are essentially implementing a Type 1 dimension. So, why implement a Type 2?

See: http://forum.kimballgroup.com/t3313-scd-type-2-dimensions-and-facts

[nobbydude]

You are absolutely correct , I would be implementing a Type 1 dimension.

So then my question is while modelling real time data and building a warehouse would Type 1 dimension be recommended ?
If the reporting client do not want to see the changes that occur multiple times in a day , would I do a type 1 dimension or create a factless fact and update the attributes ?
I am trying to understand the best practice .

Thanks so much for your response !!

[ngalemmo]

If the user has no interest in historical dimension values, there is no need to implement a Type 2.

[nobbydude]

Thanks !!

[Sponsored content]