Aug 08 2009

Time-Sliced Measures Part I: Defining the Problem

Categories: 2008 GBN - Dallas, Universe Design Dave Rathbun @ 10:05 am

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

9 Responses to “Time-Sliced Measures Part I: Defining the Problem”

  1. Comment by Eileen King

    Dave,

    This is awesome! I was looking at your presentation for my current project so the fact that you blogged about is even better.

    I was hoping you could clarify Slide 23 for me. What information actually goes into “cal_tmslc_mn_val”? You have it labeled as the Time slice name. Would I have one for each of the different time slice periods? So I would have rows with start and end dates for period, quarter, YTD, full period, etc…

    Thanks!

  2. Comment by Eileen King

    I’ve worked with it and to answer my own question, the answer is yes.

  3. Comment by Dave Rathbun

    Eileen, the answer to your question is scheduled to come out shortly. :)

  4. Comment by Eileen King

    I’ve got to have the implementation finished this week… :)

  5. Comment by Eileen King

    Here’s the REAL question…

    How do you think this would work with a 3NF data model with multiple contexts by subject?

  6. Comment by Dave Rathbun

    It should work okay, but could get really messy. The driving factor here is you have an alias / context for every time-slice / fact combination. So if you have pseudo-fact tables (anything with measures, basically, think of the Motors training database if you want) and multiple contexts already, then the number of contexts that you’ll have could become … unwieldy.

  7. Comment by Andreas

    Hmm, a normalized data model is really bad for reporting, it is typically used for OLTP systems or as an atomic layer for a data warehouse. Nevertheless, one should denormalize the 3NF as necessary. Of course the standard (Kimbal) Star-schema is ideal for reporting (I know this is nothing new for you Eileen though).

    If you needed various time-sliced measures and have the luxury of a data mart/data warehouse, then calculating some of the time-sliced measures (such as Current YTD, Previous YTD, etc.) as part of the fact table (=additional columns) would be the easiest IMHO.

  8. Comment by Shiva

    When are you going to publish your second part Dave…..
    This topic is a nightmare for me….. I’m looking forward to it.

    I’ve also posted one of the ways to calculate YTD and MTD etc…. in your 2007 blog… its just i came to know of this link few days back.
    I’m repeating my formula here also …..

    SDOY (start date of year): =To_Date(”1/1″+FormatDate(currentdate();”yyyy”);”MM/dd/yyyy”)
    Then you have the current date with you ….. as currentdate()
    Use these 2 to create YTD
    Similarly for YTM……

    Regards,

  9. Comment by Shiva

    This topic doesn’t mean the one you published………..

    This time sliced measures….. This topic as a whole is a killer… i believe one who does this perfectly he can do anything in time related fields…..

Leave a Reply

If you want to include formulas or code in your comment, please read my Tips for formatting comments first. Tags you can use are listed below.

XHTML: You can use these tags: <a href="" title=""> <acronym title=""> <blockquote cite=""> <code> <em> <strike> <strong> <sup> <sub> <u>

Confirm submission by clicking only the marked checkbox:

             *

Please remember that comments that are not related to this blog post may be ignored or deleted without notice. If you're looking for help on a topic you have already posted on BOB then please do not repost your question here.