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

Dimension scd 2

4 posters

Go down

Dimension scd 2 Empty Dimension scd 2

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

Back to top Go down

Dimension scd 2 Empty Re: Dimension scd 2

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

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

http://aginity.com

Back to top Go down

Dimension scd 2 Empty Re: Dimension scd 2

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

Back to top Go down

Dimension scd 2 Empty Re: Dimension scd 2

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

Back to top Go down

Dimension scd 2 Empty Re: Dimension scd 2

Post  nick_white 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 : 364
Join date : 2014-01-06
Location : London

Back to top Go down

Dimension scd 2 Empty Re: Dimension scd 2

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