Linking Type 2 SCD
2 posters
Page 1 of 1
Linking Type 2 SCD
I have a DW where we are tracking loans. Top level is Loan Program, which has Dealers and Loan Products. A Loan is created in a Program with a Dealer and a Product type. Not all dealers can use all products within a program, and for all intents a Dealer only exists in one Program.
We currently have a Loan fact table that has a grain of a single loan. Dimensions for Program, Dealer, Product, and Borrower. Borrower is Type 1, however Program, Dealer, and Product are all type 2.
The issue I haven't been able to wrap my head around is that they are going to want the ability to say get a list all of the active dealers within a program. So I need to somehow model this relationship without the type 2 changes messing it up. What's the best way to do that?
I was thinking Factless fact table, however I'm not sure how to handle type 2 changes and still keep the grain to a single Program-Dealer relationship with the updates being visible. Any advise?
We currently have a Loan fact table that has a grain of a single loan. Dimensions for Program, Dealer, Product, and Borrower. Borrower is Type 1, however Program, Dealer, and Product are all type 2.
The issue I haven't been able to wrap my head around is that they are going to want the ability to say get a list all of the active dealers within a program. So I need to somehow model this relationship without the type 2 changes messing it up. What's the best way to do that?
I was thinking Factless fact table, however I'm not sure how to handle type 2 changes and still keep the grain to a single Program-Dealer relationship with the updates being visible. Any advise?
jjhartma- Posts : 4
Join date : 2010-05-20
Re: Linking Type 2 SCD
There is a fairly length discussion on type 2 dimensions in http://forum.kimballgroup.com/dimensional-modeling-and-data-architecture-f6/scd-type-1-and-2-t433.htm.
In summary you can build a type 2 with both a type 2 primary key and a type 1 alternate key and current flag or build two separate dimension tables, one containing type 2 data an the other all data as type 1.
In your factless fact tables, reference the dimension using the type 1 key. OR you can do none of the above, link using the type 2 PK and do a self join of the dimension table, based on natural key, to get the current version of the dimension row.
In summary you can build a type 2 with both a type 2 primary key and a type 1 alternate key and current flag or build two separate dimension tables, one containing type 2 data an the other all data as type 1.
In your factless fact tables, reference the dimension using the type 1 key. OR you can do none of the above, link using the type 2 PK and do a self join of the dimension table, based on natural key, to get the current version of the dimension row.
Similar topics
» Type-2 Dates as Date Data Type ?
» rationale behind dimension with Type 0 and missing Type 5
» Example of a business process with more than 1 fact table
» Why do I need type 3 and 6 SCDs when I can implement type 7?
» Type 2 dimension or type 2 column?
» rationale behind dimension with Type 0 and missing Type 5
» Example of a business process with more than 1 fact table
» Why do I need type 3 and 6 SCDs when I can implement type 7?
» Type 2 dimension or type 2 column?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|