Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Slowly Changing Dimensions

As one creates QlikView applications one sometimes encounters a data modeling problem where a dimensional attribute varies over time. It could be that a salesperson changes department or a product is reclassified to belong to another class of products.

 

This problem is called Slowly Changing Dimensions and is a challenge for any Business Intelligence tool. Creating an application with static dimensions is simple enough, but when a salesperson is transferred from one department to another, you will have to ask yourself how you want this change to be reflected in your application. Should you use the current department for all transactions? Or should you try to attribute each transaction to the proper department?

 

First of all, a changed attribute must be recorded in a way that the historical information is preserved. If the old value is overwritten by the new attribute value, there is of course nothing QlikView can do to save the situation:

 

No history.png

 

In such a case, the new attribute value will be used also for the old transactions and sales numbers will in some cases be attributed to the wrong department.

 

However, if the changes have been recorded in a way so that historical data persists, then QlikView can show the changes very well. Normally, historical data are stored by adding a new record in the database for each new situation, with a change date that defines the beginning of the validity period.

 

In the salesperson example, you may in such a case have four tables that need to be linked correctly: A transaction table, a dynamic salesperson dimension with the intervals and the corresponding departments, a static salesperson dimension and a department dimension. To link these tables, you need to match the transaction date against the intervals defined in the dynamic salesperson dimension.

 

Model0.png

 

This is an intervalmatch. The solution is to create a bridge table between the transaction table and the dimension tables. And it should be the only link between them. This means that the link from the transaction table to the bridge table should be a composite key consisting of the salesperson ID (in the picture called SPID) and the transaction date.

 

It also means that the next link, the one from the bridge table to the dimension tables, should be a key that points to a specific salesperson interval, e.g. a composite key consisting of the salesperson ID and the beginning and end of the interval. Finally, the salesperson ID should only exist in the dimension tables and must hence be removed from the transaction table.

 

Model2.png

 

In most cases of slowly changing dimensions, a salesperson (or product, customer, etc.) can only belong to one department (or product group, region, etc.) at a time. In other words, the relationship between salesperson and interval is a many-to-one relationship. If so, you can store the interval key directly in the transaction table to simplify the data model, e.g. by joining the bridge table onto the transaction table.

 

Model3.png

 

A word of caution: If a salesperson belongs to several departments at the same time, such a join may cause QlikView to make incorrect calculations. Bottom line: Double-check before you join.

 

For a more elaborate description of Slowly Changing Dimensions and some script examples, see the technical brief IntervalMatch and Slowly Changing Dimensions.

 

HIC

20 Comments
Not applicable

Double-check before you join

... and "Save before changing Section Access"!

The two golden rules for a QlikView developer!

Thanks HIC, you are the best!

5,498 Views
Not applicable

Thanks for sharing HIC,

We are using same style of solution. But we are struggling when "Slowly changing dimension table"  have multiple dimension like Product wise, Segment wise, Region wise, Brand wise SalesRep with periodic changes.. While providing above solution, bridge table itself will grow as transaction table..

Is there any other solution while having large data on "Slowly changing dimension table"?.

0 Likes
5,498 Views

Can you not use several bridge tables, one per dimension? With several dynamic dimensions, you could have several dynamic keys in the transaction table, e.g. one "SalesRep+Interval" and a second "Product+Interval".

HIC

5,498 Views
Not applicable

I will try and get back to you..

Thanks..

0 Likes
5,498 Views
paulyeo11
Valued Contributor III

Hi HIC ,

I notice your old photo look smarter.

5,498 Views
Not applicable

actually i felt bit odd to say this earlier but since u've said this so i can say now that i agree wid paulyeo11

5,498 Views
MVP & Luminary
MVP & Luminary

As a result of user feedback this change has been rolled back and the avatar rendering of the previous release has been restored.

5,498 Views
Partner
Partner

Dear HIC,

will you please take event(session) about this topic through the Qliktech webex?

thanks,

kalyan

0 Likes
5,498 Views
darrin_pilkingt
Contributor II

Question in regards to IntervalMatch and dates. 

The problem I have when dates are loaded in both the Events table and Invterval they are coming in formatted as a date.  1/1/2013.  

After the IntervalMatch(CustomerID, InvoiceDate) the bridge table shows the dates as integer  41641.

It wasn't till I converted the Bridge date values going in to date(StartDate) and date(EndDate) did it actually start working.

Is this normal?  I have not read anywhere that this is necessary. 

Thank you.

0 Likes
5,498 Views

The number and the date are the same thing. It is just that you lose the formatting for some reason. The intervalmatch is made using the numeric value.

The solution to the problem is - just as you mention - to use the Date() function. See more on http://community.qlik.com/blogs/qlikviewdesignblog/2012/06/07/get-the-dates-right

HIC

0 Likes
5,498 Views
Not applicable

Thanks for your document

can you provide type2 SCD with some example

5,498 Views
_frelev_
New Contributor

Yes, an example would be great!

0 Likes
5,498 Views

Examples can be found along with the more comprehensive white paper: IntervalMatch and Slowly Changing Dimensions

HIC

0 Likes
5,498 Views
rajkumarb
Contributor II

Very  Helpful, Thank you

0 Likes
5,498 Views
Not applicable

good explanation

0 Likes
5,498 Views
Not applicable

If we are talking about SCD in dimensional model context , fact table surrogate keys will be refer to point in time instance of Sales Person. We won't need bridge table if fact table in dimensional model is designed to use SCD. This allows to store massive transaction data and refer to appropriate attributes of Sales Person based on transaction date.

0 Likes
5,498 Views
jerifortune
Contributor III

Excellent post!


Please, how do we implement a scenario where we don't have interval dates? For example, I have a  Sales transaction table and Sales dimension table that tracts  changes on Dept field. How could this be implemented or do you have a different scenario for this?


Thank you.

0 Likes
5,498 Views

If you have a table that tracks changes, then you hopefully have a change date that you can use. To convert a single date into an interval, see Creating a Date Interval from a Single Date.

HIC

5,498 Views
paulyeo11
Valued Contributor III

Hi Henric

Nice to hear from you. I am think of you . As I was about to ask what happen to you ?

Get Outlook for iOS<https://aka.ms/o0ukef>

0 Likes
5,498 Views
jerifortune
Contributor III

Thank you

0 Likes
5,498 Views