Building Data warehouses on Near Real time data

View previous topic View next topic Go down

Building Data warehouses on Near Real time data

Post  nobbydude on Fri Jul 24, 2015 9:44 am

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 ?

nobbydude

Posts : 3
Join date : 2015-07-24

View user profile

Back to top Go down

Re: Building Data warehouses on Near Real time data

Post  ngalemmo on Fri Jul 24, 2015 1:00 pm

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
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Building Data warehouses on Near Real time data

Post  nobbydude on Fri Jul 24, 2015 2:21 pm

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 !!

nobbydude

Posts : 3
Join date : 2015-07-24

View user profile

Back to top Go down

Re: Building Data warehouses on Near Real time data

Post  ngalemmo on Fri Jul 24, 2015 3:45 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Building Data warehouses on Near Real time data

Post  nobbydude on Sat Jul 25, 2015 2:45 pm

Thanks !!

nobbydude

Posts : 3
Join date : 2015-07-24

View user profile

Back to top Go down

Re: Building Data warehouses on Near Real time data

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