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. In the second post I described a special calendar table that was designed and built to support the requirements for this solution. I also showed how the join logic worked in conjunction with the table design. This post completes the implementation. I am finally going to work on the measure objects that a user will see.

In any universe design project I strive for the following goals:

• Deliver the correct result
  In my opinion, this is always the number one goal in any universe design.
• User friendly
  This is quite important but secondary to correctness
• Easy to maintain
  Universe maintenance is always allowed to suffer in order to provide the first two attributes on this list, but it is a worthwhile goal to strive for nonetheless

In this post I will show how all three of these goals are ultimately met by this implementation. When I am done I will have a completed universe. This post will cover slides 26 through 30 from my 2008 GBN Conference presentation. There is a link to download the file at the end of this post.

A Brief Recap

The overall goals of this project were listed in the prior posts in this series. The most important ones for this post are:

• Each report is expected to have multiple time-sliced measures
• The process of splitting each time-slice time period into its own SQL statement should be completely transparent

Current Year and Prior Year are different time slice attributes. Every report is expected to have month-to-date and year-to-date measures that cover both the current and the prior year. That means I expect to see four different measures used in each document. The month and year date ranges are obviously different, as are the current and prior year ranges. In the last post on this topic I described a special time-slice calendar table and set up different aliases for each required time-slice. I set up contexts to keep queries from hitting more than one time-slice at the same time.

That’s how we got here. Where to next? It’s time to build some measures.

Bits and Pieces

I subscribe to the thought that anything that makes the universe designer’s job easier while making the user’s life harder is the wrong approach. However, if I can do something to make my life easier without impacting the user, then whatever I come up with is fair game. That’s how I arrived at the strategy that I am going to outline next.

Normally a “bit” is either a one or a zero. Many of the bit objects used in this solution will be just that. However, I am also going to create some special measure bits that make use of the @Select() function. As a general rule I am not in favor of using this function, but in this case I will and I will justify why in a bit. (heh, pun )

I am going to focus on creating time-sliced objects for the revenue object. In order to do this, I am going to create a measure bit called “Revenue” that contains only the table / columns required to calculate this value. I am not going to include an aggregation function for this measure which violates another one of my universe design rules. Again, I will justify why. The other bit objects will be used to select (or mark) which context is used for each time slice.

Using Measure Bits

In my time-sliced solution everything is made up of combinations. Imagine a cartesian product between measures and time periods, something like this:

Each time-slice on the left is combined with each measure on the right. Rather than create six different references to the revenue formula shown here…

INVOICE_LINE.DAYS * INVOICE_LINE.NB_GUESTS * SERVICE.PRICE

…I am going to create just one. Notice that the code shown above does not have an aggregate function? This is by design… I am going to use the @Select() function to build my visible measure objects. Here’s the initial part of the object definition for the CY MTD Revenue object.

sum(@Select(Measure Bits\Revenue))

By leaving the aggregate function out of the source measure I can also create a minimum, maximum, or other aggregate version of the sales revenue. This is because the code referenced by the @Select() doesn’t have an aggregate… and why is this important? Because I can’t nest aggregate functions. If my source measure included the sum() function then that’s all I can do with it. By keeping it generic (no aggregate) I can reuse it with any aggregate function as needed. So that’s why I am breaking the rule that says every measure must have an aggregate function.

Ultimately the measure bits are going to be hidden from view so they will never be viewable / usable by report writers. They are only used as building blocks in the Designer application.

Time Slice Bits

Now I need to build some time-sliced bits. These are really simple. Here’s what the object definition looks like for the CY MTD bit object.

The select clause contains the number 1 and that’s it. Notice what the help text says? In big CAPITAL letters it says “DON’T FORGET THE TABLE!!!” I wonder what that means?

Here’s what it means. The 1 is just a placeholder to put something in the Select clause. The Where clause is empty. The From clause comes from the table list, and here’s what that looks like.

Stick with me just a little bit longer and this will all make sense. Remember that in the earlier posts I set up aliases for my time-slice calendar table. Each alias became part of its own context. A context is a series of joins that make up a particular path through a universe. What I am working towards is a set of time-sliced objects that a user can drag onto their query panel without having to worry about where they come from or how they’re put together. Here’s what my schema looks like as a reminder. All of the aliases are on the right side.

This structure gives me a way to automatically split out each time-sliced measure into its own path so that a user can combine CY MTD and PY MTD and any of the other options onto one query and run it.