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