topic Re: Slowly changing dimension problem in QlikView

Slowly changing dimension problem

Sat, 28 May 2011 13:14:11 GMT

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.

Re: Slowly changing dimension problem

nagaiank — Sat, 28 May 2011 16:12:06 GMT

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

Slowly changing dimension problem

Sun, 29 May 2011 12:59:51 GMT

Nice. What does Replace do here?

Slowly changing dimension problem

nagaiank — Sun, 29 May 2011 13:15:06 GMT

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'.

Slowly changing dimension problem

Sun, 29 May 2011 14:38:48 GMT

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

Re: Slowly changing dimension problem

nagaiank — Sun, 29 May 2011 15:11:25 GMT

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.

Re: Slowly changing dimension problem

Sun, 29 May 2011 17:18:42 GMT

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;