Building Data warehouses on Near Real time data
2 posters
Page 1 of 1
Building Data warehouses on Near Real time data
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 ?
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 ?
nobbydude- Posts : 3
Join date : 2015-07-24
Re: Building Data warehouses on Near Real time data
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
See: http://forum.kimballgroup.com/t3313-scd-type-2-dimensions-and-facts
Re: Building Data warehouses on Near Real time data
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 !!
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 !!
nobbydude- Posts : 3
Join date : 2015-07-24
Re: Building Data warehouses on Near Real time data
If the user has no interest in historical dimension values, there is no need to implement a Type 2.
nobbydude- Posts : 3
Join date : 2015-07-24
Similar topics
» Real time datawarehousing
» Building data warehouse for Bespoke ERP
» Building data warehouse from scratch.. Need some advice.
» Medical Data - Building a reporting Data Warehouse
» Katie and Emil "10 Steps" guide to building a data warehouse
» Building data warehouse for Bespoke ERP
» Building data warehouse from scratch.. Need some advice.
» Medical Data - Building a reporting Data Warehouse
» Katie and Emil "10 Steps" guide to building a data warehouse
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum