We have a situation where we need to create (in a view) a snapshot from Type 2 dimension behaviour
Lets say we have a record that has is effective from 2016-05-01 to 2016-05-31. I need to reflect the picture of that record for the intervening 31 days. Currently I do this with a product join to the calendar, and filtering on the effective date. It works well, and gives the desired results when filtered on a particular date, and even a month. However, when you do this on a table that has 44 million "instances" of the record at any point in time, per day for several months, it starts performing like a dog.
Are there any physical design patterns that would aid this kind of query to make it more performant? Is anyone doing the same kind of thing with good results? We want to avoid materialising the overlying views as much as possible. The underlying date fields are all partitioned by day.
Thanks for the input
I remember that the INTERVAL function could be usefull. This prevents a product join to your calendar dimension. We ended up with making the tables physical for maximum performance and to make it easy for the reporting tool. To counter the storage the MVC compression reached a 50% compression due to the repeating figures.