Linking Type 2 SCD

View previous topic View next topic Go down

Linking Type 2 SCD

Post  jjhartma on Fri May 21, 2010 12:17 pm

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?

jjhartma

Posts : 4
Join date : 2010-05-20

View user profile

Back to top Go down

Re: Linking Type 2 SCD

Post  ngalemmo on Fri May 21, 2010 12:24 pm

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

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

View user profile http://aginity.com

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