Dimension scd 2

View previous topic View next topic Go down

Dimension scd 2

Post  NewDpr on Fri Jan 09, 2015 4:23 am

Hello,

I am quite new here and just learning Kimball methodology. I have some questions about the management of a SCD 2 Dimension.
Let's say that we have a SCD type 2 dimension with tracking history of some attributes, and facts linked to that DIM.
If in a case of change request, end users would like to track other attributes but no more the ones already tracked. What would happen in this case.
=> Rebuild the DIM, and update the Facts ?
or only the DIM as we are on full load and normally the lookups should do the update job ?

THanks in advance for your info

NewDpr

Posts : 4
Join date : 2014-11-15

View user profile

Back to top Go down

Re: Dimension scd 2

Post  ngalemmo on Fri Jan 09, 2015 4:37 am

The one thing that does happen is the keys do not change. By that I mean you cannot create history when it doesn't exist. If you did not track change for a value that history is lost, but you can being to accumulate history from this point forward. As far as old history they no longer want, you can treat them as type 1 columns and update all the old rows with the current value.

If the users vacillate one which columns should or shouldn't have history, the easiest thing to do is have 2 tables (a type 1 and a full-blown type 2) and two keys on the fact. This would give them current as well as a history of everything.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Dimension scd 2

Post  NewDpr on Fri Jan 09, 2015 4:45 am

THanks for your prompt answer.
But in this case (creating a full-blown type 2), isn't it better/simple to have a layer before the DWH on which we will preserve history of all the tables involved (kinda 3NF/Datavault) ?

NewDpr

Posts : 4
Join date : 2014-11-15

View user profile

Back to top Go down

Re: Dimension scd 2

Post  topcat on Mon Jan 12, 2015 4:27 pm

I like to use a "durable" key in some of my scd2 dimensions. a durable key does not change with each iteration of the scd2 record.

The fact carries both the durable key and the standard surrogate key.

for full blown history, we tend to keep most of the source data in our landing/staging database.

topcat

Posts : 19
Join date : 2012-08-09

View user profile

Back to top Go down

Re: Dimension scd 2

Post  nick_white on Tue Jan 13, 2015 11:56 am

If you want to hold both the history and the current position then the Kimball approach is either SCD6 (limited number of attributes) or SCD7 (large number of attributes).
If you need the data for reporting purposes (as opposed to holding it for e.g. auditing purposes) then you need it in your dimensional model - I'm not sure how you would expect to report on it if half of it was in a dimensional model and half of it wasn't

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Dimension scd 2

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