How to incorporate SCD2?

[T4FF]

Hi there, I'm after some design assistance here, did the FM course beginning of last year but due to change of priorities in work, I'm only now being able to put training into practice.  As expected, I've lost a lot of the knowledge I soaked up last year and I'm struggling to apply something, hoping someone can help.

I need to incorporate a dimension into the model that lists a set of company codes.  This is an SCD2 and the table includes start and end dates.

The bit that I'm struggling to work out is how I would allow various dates to interact with this dimension.  The Company dimension is linked to the fact by CompanyKey, I'm assuming I would need to include a further join by a date key (for example new business date) in order to obtain the correct company number for an account? 

But what if I want to see different company number based on different dates for an account?  What I mean is, if I want account 1234 to show as new business under company 2 and settled under company 3 (representing a change in company number during the life of the account), how would I incorporate that?  If I was to run a summary by company of new business and settlement, I would want to see:


 
   
   
   

Company	New Business	Settlements
2	1	0
3	0	1

Presumably, the more dates I then need to include, the more complicated it gets?

Thanks in advance....

[MFGF]

Hi,

I'm assuming the CompanyKey column used to join the dimension to the fact is a unique surrogate key defined within the Company dimension (ie a Company with a specific Company Code can have many dimension rows, each identified by a different CompanyKey)?

If this is the case, you do not need anything extra to join the dimension to the fact.  Each fact row will have a CompanyKey that joins it to a specific row in the dimension table.

If an account moves from one company to another, does this impact the Company dimension table? (the company itself hasn't changed, so I'd assume not).  I'd assume this will be catered for with a new fact row incorporating the account's new company key?  If so, your report below will be really simple - just select the fact rows with the desired account number and bring in the company from the Company dimension table.

MF.