Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Building Data warehouses on Near Real time data

2 posters

Go down

Building Data warehouses on Near Real time data  Empty Building Data warehouses on Near Real time data

Post  nobbydude 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

Back to top Go down

Building Data warehouses on Near Real time data  Empty Re: Building Data warehouses on Near Real time data

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

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

http://aginity.com

Back to top Go down

Building Data warehouses on Near Real time data  Empty Re: Building Data warehouses on Near Real time data

Post  nobbydude 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

Back to top Go down

Building Data warehouses on Near Real time data  Empty Re: Building Data warehouses on Near Real time data

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Building Data warehouses on Near Real time data  Empty Re: Building Data warehouses on Near Real time data

Post  nobbydude Sat Jul 25, 2015 2:45 pm

Thanks !!

nobbydude

Posts : 3
Join date : 2015-07-24

Back to top Go down

Building Data warehouses on Near Real time data  Empty Re: Building Data warehouses on Near Real time data

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum