An integral part of a dimensional model is the need to manage descriptive changes in various dimensions such as Employee. Employees frequently change jobs within a company. Often, a new job is in a different department, so it is inevitable that the company finds a way to handle this slowly changing dimension for point-in-time reporting.
Back to Basics: Type 2 Slowly Changing Dimension(SCD)
Type 2 Slowly Changing Dimension is widely used in most data warehousing implementations. Having said that, let’s understand how SCD type 2 dimension works in general.
When an employee moves from one department to another — due to promotion or internal job transfer — a new record gets created in the employee dimension for the same employee. The new record shows new department and job start date as the effective date. Also, the record with previous department shows day prior to the new effective date as the job end date.
In addition, the job end date of the new record normally has future date such as 12/31/2099 or so. As depicted in the Figure 1 of Ralph Kimball’s article, type 2 SCD contains current flag(Y/N) in addition to begin effective date and end effective date.
With this dimension, it is easy to find which department employee worked for by performing date look up for a given field such as order date.
In SQL, you can use BETWEEN operator..
SELECT Employee_Name, e.Department
FROM Employee e, Order o
WHERE e.emp_id = o.emp_id and orderdate BETWEEN begin_effective_date AND End_effective_date;
“How can I deal with slowly changing dimension in QlikView?,” you may ask as an ardent QlikView advocate.
Enter IntervalMatch function in QlikView — It has the Midas touch to handle type 2 slowly changing dimension.
IntervalMatch(Extended Syntax): IntervalMatch(Matchfield, Keyfield1,…KeyfieldN)
Remember:
The Keyfield name should match between both tables.
End_eff_Date has to have a value.
Let’s explore an example to show how IntervalMatch function works…
Step1: Load Fact table.
Step2: Load Type 2 Slowly changing dimension.
Step 3: Perform Interval match using the extended syntax.
That’s it. Now, let’s create a pivot table to report point-in-time sales..
Elsewhere:
1. IntervalMatch and Slowly Changing Dimensions via Qlikview community
2. IntervalMatch via QlikView Design Blog
3. Slowly Changing Dimensions via Kimball Group
