Dave's Adventures in Business Intelligence

Putting It All Together

Here’s what this entire series of posts has been leading up to; I am now going to build the time-slice measures in my universe using the following pattern. First, here’s the CY MTD Sales Revenue object.

sum(@Select(Measure Bits\Revenue)) * @Select(Time Slice Bits\CY MTD Bit)

That’s it. Two calls to the @Select() function and I’m done. What does the SQL look like after it’s generated? Here’s what I would see in the object itself:

sum(INVOICE_LINE.DAYS * INVOICE_LINE.NB_GUESTS * SERVICE.PRICE) * 1

What’s missing is the table reference. The 1 hanging off of the end of that code is used to mark which query path I want to take. Remember that when I built that bit object I specifically selected the cy_mtd alias of the calendar table. That means that this table will be included in the full SQL from the query panel. What does the join to that table look like?

SALES.INVOICE_DATE between cy_mtd.CURR_YR_TMSLC_STRT_DT and cy_mtd.CURR_YR_TMSLC_END_DT
AND cy_mtd.CAL_TMSLC_MN_VAL = 'MTD'
AND cy_mtd.CAL_NM = @Prompt('Please select Calendar Type','A','Time Slice Calendar\Calendar Name',mono,free)
AND cy_mtd.REF_CAL_DT = @Prompt('Please select Calendar Date','D',,mono,free)

I talked about the joins earlier, but I will review briefly. I start with the join from the invoice table to the range defined by the start and end date of my time slice. Next, I restrict the rows so I only get values for the MTD date ranges for this specific alias. After that I include a prompt to select the calendar type (I offer monthly and fiscal calendars). Finally I prompt the user for a date that will be used to identify the specific period (or month) they are interested in.

I still really haven’t explained the 1 bit though. It’s just used as a path marker. When I use the CY MTD Bit in an object definition, it restricts that object to a specific date range that provides the current year month-to-date values. When I sum up a number of invoices and multiply that value by 1, I don’t change the value at all. So just think of the time-slice bit as a context or path selector.

Table Drops

Why not just include the table on the object definition itself? I don’t do that because of “table drops” … a rather irritating feature of the Designer application. I am not going to provide screenshots for this but try this experiment on any universe that you might have. Open an object. Click on the Tables button and using the CTRL key select an additional table. Click OK. Now edit the object definition of your object; just take one letter or character out and put it right back in. When you “touch” an object definition Designer tries to be helpful and reset the list of implicated tables for you. When you check, the extra table that you selected earlier is now gone.

This is problem.

Since the tables are not visible on the object definition, it’s incredibly easy for a new designer to come along and really mess things up because this special technique – while accurate – is extremely fragile. By using the bit to reference the table, and by including the bit as part of the selection definition, the issue of dropped tables is reduced if not eliminated altogether.

Does it work?

Seeing Is Believing

Here’s a quick query that I built using this universe. Notice that I don’t have any date ranges defined in my query panel. In fact, I don’t have any conditions defined at all.

Here is what the SQL window looks like.

And to make it easier to read, here’s the SQL code from one of the three “join” passes above.

SELECT
max( RESORT.resort ),
sum(INVOICE_LINE.DAYS * INVOICE_LINE.NB_GUESTS * SERVICE.PRICE) * 1
FROM
INVOICE_LINE,
SALES,
SERVICE,
SERVICE_LINE,
RESORT,
CAL_TIMESLICE cy_mtd
WHERE
( RESORT.RESORT_ID=SERVICE_LINE.RESORT_ID )
AND ( SERVICE_LINE.SL_ID=SERVICE.SL_ID )
AND ( SERVICE.SERVICE_ID=INVOICE_LINE.SERVICE_ID )
AND ( SALES.INV_ID=INVOICE_LINE.INV_ID )
AND ( SALES.INVOICE_DATE between cy_mtd.CURR_YR_TMSLC_STRT_DT and cy_mtd.CURR_YR_TMSLC_END_DT AND cy_mtd.CAL_TMSLC_MN_VAL = 'MTD' AND cy_mtd.CAL_NM = 'Month Calendar' and cy_mtd.REF_CAL_DT = '14-08-2008 00:00:00' )
GROUP BY
RESORT.RESORT_ID

You can ignore the max() on the Resort and the Group By… those are artifacts of the Index Awareness configuration in my universe and have nothing to do with the time-slice process. The important parts are:

• Reference to the cy_mtd alias
• Because of the table reference I inherit all of the logic in the join
• Because each time slice is in a separate context Web Intelligence automatically splits the queries apart
• … which means that every time slice can be dropped onto the same query panel, and I will get the correct results!

Yay, it works! 😆

The Solution, In A Nutshell

This was a long series of posts. Ultimately it comes down to this.

• I designed, created, and populated a time slice table
• I created aliases of this table for each required time slice and built logic into the join to make the date ranges work
• I created measure and time-slice bit objects in a hidden designer class in my universe
• I combined these two types of bits and made all of the time-sliced measures

A user can now pick any / all of these measures in a single query but even more importantly because of the structure the numbers should be correct.

What About Universe Maintenance?

It’s actually quite simple. The items I might be asked to change include updating a measure definition, adding a new measure, or adding a new time slice. All three of these are fairly simple.

If I ever have to change the definition of “revenue” I only have to change one measure bit and every occurrence of that value in a time-slice is updated.

If I ever have to create a new time slice the process is also fairly simple. I would create a new alias and then a new context. I create a new time-slice bit to point to the new alias table. Then I can copy an existing object (for example CY MTD Revenue) and update the time-slice bit reference and I’m done.

If I ever have to create a new measure it’s not much more difficult. First I create the new measure bit. Next, I will copy an existing measure and update the measure bit reference. So CY MTD Revenue becomes CY MTD Quantity Sold with just a few mouse clicks.

Conclusion

This post completes my annotations for my GBN Designer presentation from 2008. At least I finished before 2010. 😉 There was a lot of detail in these posts, so it may take a while to sink in. Is this a perfect solution? I certainly would not call it that. The universe does get complex; any universe with a large number of contexts is going to be complex. My most recent project included a dozen time slices and five fact tables. That’s sixty contexts right there without any other structural issues. I can say that I have used this model in two fairly large projects at this point and it continues to work very well.

Users are happy because they can pick and choose any objects they want and the query engine takes care of splitting everything into different SQL passes. Because everything is simple for the users it reduces the chances of user error generating incorrect report data.

After some tuning and tweaking the DBAs were happy too. We set up a primary partition on the date field in our fact table. Since every time-sliced object is going to reference a date range that helps quite a bit.

I was happy too. 🙂 At one point I was asked to create a brand new time-sliced measure in my universe. We had already created a measure for dollars and a couple of different quantity units. I needed to create a brand new set of objects for a new unit of measure that was coming into the fact table. All I did was go up to the “root class” for one of my existing measures and copy the entire tree. I pasted the copied class back into the same universe. All I had to do at that point was create a new measure bit and then update each of the copied objects so that they referenced that new bit. It took less than twenty minutes to add an entirely new set of time-sliced measures to my universe.

Now that I have completed this series of posts, I think I will copy them and call them the “technical documentation” that I have promised my manager that I would deliver. 😎

Related Links

• Conference presentation download: Universe Designer Essentials and Beyond
• First post in this series: Time-Sliced Measures Part I: Defining the Problem
• Second post in this series: Time-Sliced Measures Part II: Time Slice Calendar Table