Dave's Adventures in Business Intelligence

In the first post in this series I defined what time-sliced measures are and why they can be useful in a universe. I also shared the design requirements for a particular project I was on. The requirements included:

• Each report is expected to have multiple time-sliced measures
• Users must be able to select the type of calendar during the refresh process; calendar types include Monthly and Fiscal
• Users can provide any possible date as the “to date” for the time slices
• The process of splitting each time-slice time period into its own SQL statement should be completely transparent
• The resulting SQL should be as efficient as possible

In this post I am going to cover the design of a special table that we built in order to support our solution for these requirements. After I talk about the table design I will cover how I use it in the universe, as well as provide a few pros and cons about this solution as I have outlined it so far. Just to prepare you, this post is a bit longer than most that I write, and gets fairly detailed. This post will cover slides 23 through 25 from my 2008 GBN Conference presentation. There is a link to download the file at the end of this post. Continue reading “Time-Sliced Measures Part II: Time Slice Calendar Table”