How to model article dimension for unpredictable changes with multiple versions and multiple article categories

Hello forum,

I'd like your help in solving a modelling problem I am facing. The simplified situation is as follows.

A simplified fact table:
date_id
article_id
quantity
item_price

A simplified article dimension:
id
article_nbr
category_1
category_2
category_3
category_4
category_5
attr1
attr2
attr3
attr4
valid_until_date


In the article dimension, the article_nbr attribute is the unique primary key from the source system. All other attribute values can be changed on every data load. History of all attributes needs to be preserved. Changes are unpredictable and can occur monthly or more frequently.

Sample data for fact table:
date_id article_id quantity
1 100 5
2 101 4
2 102 9

Sample data for article dimension:
id article_nbr cat1 cat2 cat3 cat4 cat5 attr1 ... valid_until
100 1 A X Y Z X1 2 ... 20090101
101 1 B X Z Y X ... ... NULL
102 2 B X X X ... ... ... NULL
103 2 C O P Q ... ... ... NULL
104 ..


The reporting question is to present the total quantity sold of articles currently (after 20090101 in this example) in category_1 = B. The answer to this question should be 18, the sum of facts concerning article_id's 100, 101 and 102. The same type of question could be posed any of the other categories.

How should the article dimension be modelled. If the answer can be found in "The data warehouse toolkit", please point in out to me so I can reread the book.

Another modelling problem in this same setup is that a product can exist in two or more categories at the same time with the same natural key. For instance article 2 in cat1 B as well as in cat1 C at the same time.

You need to add another column to your dimension. This column will be constant across all history for a given article ID. Also, add this column to your fact table. When you want the current view join on the new column to your article dimension where until_dt is null (or I would add a current_row_ind).

Sounds like a perfect candidate for the TYPE 2 Slowly Changing Dimension. Create a new SK with the same BK everytime there is a change on those two fields. Also add fields like, Date_Effective_From and Date_Effective_to the article dimension.

Thanks for your replies DilMustafa and BoxesAndLines.

To solve the problem I will have to add another surrogate key field to the article dimension table and also add this field to the fact table. Although it does complicate things for end users, because they will have to be taught which field to join on, this is indeed a solution to the problem.

That's what the BI layer is for. You really don't want the business writing queries against your fact tables. Even with the improved simplicity of the dimensional model there are still opportunities to get different answers to the same question.