Dave's Adventures in Business Intelligence

During the 2008 GBN Conference in Dallas I delivered a presentation related to universe design. The last third of the presentation demonstrated a solution for time-sliced measures that I have used on a couple of different projects now. After the presentation I had two different people make suggestions that were proposed as being easier to implement than what I showed.

In each case I was able to tell the person that we had considered and perhaps even tested their suggestion and found it lacking in some way. I didn’t have time to present all of the different options during the one hour slot that I had at the conference, but I have unlimited time to explain options here on my blog. 🙂 But before I go back and detail things that we tried that did not work out so well, I am going to have a few posts (it’s too long for just one) about the solution we did implement.

This blog post will cover slides 19 through 22 from the 2008 GBN Conference presentation.

What is a Time-Sliced Measure?

Measures are analytical values defined in a universe. Time can be “sliced” into intervals like period (or month), quarter, and year. The combination of phrases as “time-sliced measures” is generally used to describe special measure objects built in a universe that automatically get associated with a specific time interval. So the measure “Period to Date Revenue” would provide a total of revenue for the period to date. In most cases the “to date” is the last load date for the database, but it does not have to be so.

Why are Time-Sliced Measures a Challenge?

Once time-sliced measures are created, users often want to combine them. A simple query might include Period to Date (PTD) and Year to Date (YTD) for both the Current Year (CY) and the Prior Year (PY) all at the same time. If the time-sliced measures are created with hard-coded date ranges, then each would be incompatible with the rest. A YTD measure has a different date range than a PTD measure. Using both together without some sort of mechanism to split them out would result in the YTD value being impacted by the PTD restriction.

Of course with Web Intelligence users can create multiple data providers. A user could be knowledgable enough to split the various measures into different queries. There are several problems with this approach.

First, it requires the user to know and understand and implement the solution. This requires training and still doesn’t prevent a user from making a mistake. It’s also a very repetitive process. Every time a user wants to retrieve more than one time-slice they would be doing the same process over and over. What if there was a way to force Web Intelligence to split the queries into multiple passes for each time-slice instead?

Contexts Create Multipass SQL Statements

In the conference presentation I spent a few minutes prior to this topic talking about contexts. Contexts are used to split SQL into multiple passes based on loops, fan, or chasm traps in a universe. At this point I have a problem that requires me to generate multiple SQL statements, and a universe design technique that does just that. It’s time to put the two together in a solution.

Business Requirements

Here are the requirements that I was presented with:

• 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

The typical time-slices used include CY PTD, CY YTD, PY PTD, and PY YTD. However, the “P” in PTD could be Month (MTD) instead of Period based on the result of a prompt. The users on this project needed to be able to see the daily data rolled up my month or period, by calendar or fiscal quarter, and so on. They wanted this to happen automatically so casual adhoc users would be able to use the universe, and of course they wanted the answers in five seconds or less. 🙂 This from a fact table that was expected to grow into the billions of rows.

In the next post for this series I will cover slides 23 through 25 and provide details about the special calendar table we created in our database to support our solution.

Related Links

• Universe Designer Essentials and Beyond conference presentation