I am faced with an interesting choice and I am wondering what the best/common practice is:
I've got a fact table with 1 billion rows with a weekly snapshot. There is a calendar date and a week number available. The table is partitioned on date, each Saturday, for unknown historical reasons. The PI is on a common business selection with a low skew.
Current reports are accessing the table based on the date via the calendar dimension and translate the week number into a date. In those current reports are some business critical reports that cannot be changed easily.
New reports are going to access the table based on the weeknumber and thus missing the partition.
I've got two choices:
With regards to performance: I do not think that there will be different performance between the approaches, assuming selections are selective instead of full table scans.
However... what is best/common practice? Personally I would choose the repartition option.
If you repartition it's unknown to the optimizer that weeknumber and date are related.
What about using a function to calculate the saturday from the week number or vive versa?
WHERE date_col = week_to_date(201612)
The reporting guys are complaining that it is "difficult" for them to present a year-week number in the report, and in the SQL select on the date... So changing the SQL from their side is not really an option (normally they are very accomodating). Beside that, even if if it is fixed, there will always be one developer with one critical report that ofcourse will select on the year-week instead of the date. Murphy's law ;-)
I think that the missing relation between date and week won't hurt, as long as via the soft RI understands the relation to the day calendar and the week calendar.
But even considering that, I could include it in the semantic view. Although I strongly prefere no logic in semantic views in fact tables... It is time for some DBQL comparisons.