Slowly changing dimension problem

[#1]

Here is an example of a problem I want to solve with a slowly changing dimension.

I have 2 tables - Department (Department, Manager, StartDate, EndDate) and Budget (Quarter, Department, BudgetAmount).  Managers always change department at the end a quarter in this example.

I want to create a new table that includes the Budget columns and the Department.Manager.

In SQL it would look like this:

select Budget.Quarter, Budget.Department, Department.Manager, Budget.BudgetAmount

from Budget, Department

where Budget.Department = Department.Department

and Budget.Quarter between Department.StartDate and Department.EndDate

order by Department, Quarter asc

I have a attached and Excel with sample data.

I've tried to use intervalmatch without success.

Message was edited by: dlaplant Added qvw with interval match.

[nagaiank]

A solution to your problem using IntervalMatch is attached. Hope this helps.

[#3]

Nice.  What does Replace do here?

[nagaiank]

The 'Replace' prefix just drops the table and generates a new table. This is not necessary. I got into the habit of using it to be sure that I am not appending the data to previous data in a table with the same name in the application. The use of Replace is not relevant for implementation of 'IntervalMatch'.

[#5]

Thanks.  How would you retain the Key table and eliminate the $Syn 1 Table?

[nagaiank]

If you rename the field Manager in one of the tables, you can avoid the synthetic key and retain the Key table, The revised qvw file is attached.

[#7]

Thanks for your help. I'm a newbie, but I'm learning a lot.  

If I want to use a composite key in the Key table and embed the composite key in the Transaction table, is the attached correct?  I believe I'm substituting the composite key for the combination of the Department and TDate based on the interval match and then dropping Department from Transaction.  If this is correct, is this the most concise way to do this? 

The results seem to be correct and the model seems easier to read than than allowing a synthetic key table.

I don't get why I need to include all the fields from the Key table in the left join then drop them.  Can you tell why this is necessary?

For instance, why is this:

Left Join (Transact) LOAD DimKey Resident Key;

not equivalent to:

Left Join (Transact) LOAD DimKey, Department, Manager, FDate, LDate Resident Key;

DROP Fields Department, Manager, FDate, LDate From Transact;