Dimension scd 2
4 posters
Page 1 of 1
Dimension scd 2
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
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
Re: Dimension scd 2
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.
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.
Re: Dimension scd 2
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) ?
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
Re: Dimension scd 2
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.
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
Re: Dimension scd 2
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
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
Similar topics
» Replacing Aggregate dimension with Conformed base dimension at lowest level.
» How to handle a Type I or II dimension with a snowflaked customer sub dimension (kimball book page 337, 338)
» bridge table and junk dimension on customer dimension (bank/credit union)
» Tip #136 Adding a Mini-Dimension to a Bridge Table: What if you can't split the dimension?
» Using the Date Dimension for Summary Fact Tables or new specialized Month Dimension?
» How to handle a Type I or II dimension with a snowflaked customer sub dimension (kimball book page 337, 338)
» bridge table and junk dimension on customer dimension (bank/credit union)
» Tip #136 Adding a Mini-Dimension to a Bridge Table: What if you can't split the dimension?
» Using the Date Dimension for Summary Fact Tables or new specialized Month Dimension?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|