Different Dimension in Different Time

[dennysetiawan]

Dear Expert,

For example, i have a Fact Table like this (the data from 2017 to 2018):

Fact:
LOAD * Inline
[
Material, Year
A001, 2017
A001, 2018
];

also, i have a dimension table called Product like this:

ProductOld:
LOAD * Inline
[
Material, Level1, Level2, Level3
A001, 10, 15, 20
];

Then, in 2019, my dimension table has been changed, into:

ProductNew:
LOAD * Inline
[
Material, LevelA, LevelB, LevelC, LevelD, LevelE
A001, 10, 20, 30, 40, 50
];

also, when my Fact table increased in 2019, it became like this:

Fact:
LOAD * Inline
[
Material, Year
A001, 2017
A001, 2018
A001, 2019
];

My question is, how to associate or combine the tables above, so if I select Year before 2019, the data will refer to ProductOld dimension. And if I select Year 2019 and above, the data will refer to ProductNew dimension?

The data that I explain in the above is just a dummy data. My concern is, i have a large number of data in Product dimension (10 million rows) and Fact table (25 million rows). How the best practice to solve this case, by producing the most optimal load time?

Appreciate to any of your solutions.

Thank you.

[MindaugasBacius]

In my way of thinking, there is no easy or straight forward way to do this:
1. You need to add Year dimension in the tables ProductOld, ProductNew. But that makes you to dublicate all the values in those table. Also, there appears the synthetic key that needs to be take care of.
2. Concatenate both tables ProductOld, ProductNew with the new Date dimension that will allow you to make IntervalMatch. Then you can Left Join the date or leave it as it is.

[avinashelite]

As per your table details and requirement ..you no need to do anythings here simple load the  data into Qlikview ...since you have only one key in common between all the tables it will automatically build the model and it will give you the desired results 

[MindaugasBacius]

Avinashelite is right but in this case you have to change all the expressions as you'll have Level1, Level2, Level3 and LevelA, LevelB, LevelC, LevelD, LevelE columns in your data set.
For example, if you had expression like Sum(Level1) then you have to remake to something like this Sum(Level1) + Sum(LevelA).
It a all depends on your business logic.

[marcohadiyanto]

Hi Denny,

you need to create flag on fact and product.

1. add flag on product old and product new

ProductOld

Material, Level1, Level2, Level3, Flag

ProductNew:

Material, LevelA, LevelB, LevelC, LevelD, LevelE, Flag
2. do cross table product old and product new

3. concatenate product old and product new

4. create concatenate key Material & Flag on product table

5. add flag on Fact table (ex. <2019 as old and >=2019 as new) and create concatenate key Material & Flag

6. load fact and product table

Hope it helps

Marco

[dennysetiawan]

Hi @MindaugasBacius, @avinashelite, @marcohadiyanto

Thank you for the solutions. After I learned the suggestions from all of you, here's my final script:

ProductOld:
LOAD * Inline
[
Material, Level1, Level2, Level3
A001, 10, 15, 20
];

ProductNew:
LOAD * Inline
[
Material, LevelA, LevelB, LevelC, LevelD, LevelE
A001, 10, 20, 30, 40, 50
];

//********** here i create a concatenate key (material & flag) then i concatenate ProductOld & ProductNew to get a DIM_PRODUCT **********//

DIM_PRODUCT:
//ProductOld:
LOAD
*,
Material & '|' & 'Old' as KeyFlag
Resident ProductOld;
DROP Table ProductOld;

Concatenate

//ProductNew:
LOAD
*,
Material & '|' & 'New' as KeyFlag
Resident INLINE_PRODUCT_HIER_NEW;
DROP Table INLINE_PRODUCT_HIER_NEW;

//********** then i create a flag to my fact table using intervalmatch **********//

FACT:
LOAD * Inline
[
Material, Year
A001, 2017
A001, 2018
A001, 2019
];

INLINE_INTERVAL_YEAR:
LOAD * Inline
[
StartYear, EndYear, Flag
2013, 2018, Old
2019, 2099, New
];

Left Join (FACT)
IntervalMatch (Year)
LOAD
StartYear,
EndYear
Resident INLINE_INTERVAL_YEAR;

Left Join (FACT)
LOAD
*
Resident INLINE_INTERVAL_YEAR;
DROP Table INLINE_INTERVAL_YEAR;

//********** finally i create a concatenate key in my fact table (material & flag) **********//

FACTNEW:
LOAD
Material & '|' & Flag as KeyFlag,
Year
Resident FACT;
DROP Table FACT;

Do you have any other workaround, especially to get the result with most optimal load time?

Thank you.