Dec 17 2009

Time Sliced Measures Part III: Making Measures

Categories: 2008 GBN - Dallas, Universe Contexts, Universe Design Dave Rathbun @ 10:52 pm

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! :lol:

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. 8-)

Related Links

16 Responses to “Time Sliced Measures Part III: Making Measures”

  1. Comment by Avinash Wagh

    Its realy good solution. But what if I dont have any refrence date. I mean if i want to see YTD for current year on all the dates available in FACT table. In this case I would need RunningSum(Revenue) on date level.
    To elaborate more If I want to see YTD values on time dimension(Date, Month..),will this solution work??

  2. Comment by Dave Rathbun

    Hi, Avinash, and thanks for your comment. I hope I am understanding your question. You still need a reference date of some kind, as “YTD” stops on a particular date, even if it’s today’s date. Our solution was complicated by the fact that our users wanted YTD to be defined at runtime. Many (most) solutions define the “to date” in YTD as the most recent warehouse load date.

    In either case, this sort of solution still works.

    I am in the process of implementing this in an older universe, and it will dramatically reduce the amount of time required to do future maintenance. That’s a nice benefit as well.

  3. Comment by Nishant

    Great post, Dave. Is there a link for the completed universe/database — sorry if I missed it? Thanks!

  4. Comment by Dave Rathbun

    Hi, I didn’t post a link to download the database or the universe. The database that I am working with is Oracle, but the same tables could be created in Access like the original Island Resorts universe.

  5. Comment by edpypf

    Thanks, learned “bit” solution,
    I actually designed a date mapping table, which is similar as your customized table, but lists all the mapped date instead of Starting and Ending date column. the columns in my table are “Selected Date”, “Index Number”, “Mapped Date”(which link to fact table-Trans_dt), so with one table, I use Case When Index_Num=999 then 1 else 0 end statement to aggregate the fact in proper time slices. this reduced number of contexts. after index created and collect stats, the performance is close to the one using time partition, the performance is even better if report level filter to limit use of index number is applied. Please let me know what you thought. Thank you

  6. Comment by edpypf

    SELECT
    A_Sales_Org_Curr.Sales_Org_ID,
    A_SITE_CURR_New.Site_ID,
    sum(Case when Yr_Day_Trend_Range.SORT_NUM=100 then A_Daily_Site_Sales.Daily_Dept_Sales_Amt else 0 end),
    sum(Case when Yr_Day_Trend_Range.SORT_NUM=200 then A_Daily_Site_Sales.Daily_Dept_Sales_Amt else 0 end),
    sum(Case when Yr_Day_Trend_Range.SORT_NUM=-100 then A_Daily_Site_Sales.Daily_Dept_Sales_Amt else 0 end),
    sum(Case when Yr_Day_Trend_Range.SORT_NUM=-200 then A_Daily_Site_Sales.Daily_Dept_Sales_Amt else 0 end)
    FROM
    A_Sales_Org_Curr INNER JOIN A_Dist_Channel_Curr ON (A_Dist_Channel_Curr.Sales_Org_ID=A_Sales_Org_Curr.Sales_Org_ID)
    INNER JOIN A_SITE_CURR_New ON (A_SITE_CURR_New.Sales_Org_ID=A_Dist_Channel_Curr.Sales_Org_ID and A_SITE_CURR_New.Dist_Channel_ID=A_Dist_Channel_Curr.Dist_Channel_ID)
    INNER JOIN A_Daily_Site_Sales ON (A_SITE_CURR_New.Site_ID=A_Daily_Site_Sales.Site_ID)
    INNER JOIN Yr_Day_Trend_Range ON A_Daily_Site_Sales.trans_date BETWEEN Yr_Day_Trend_Range.Start_Date and Yr_Day_Trend_Range.end_Date
    WHERE A_Sales_Org_Curr.Sales_Org_ID IN (’0001′)
    AND Yr_Day_Trend_Range.SORT_NUM IN (100,200,-100,-200)
    and ( ( Yr_Day_Trend_Range.select_date ) IN ({d ‘2011-12-28′}) )
    GROUP BY
    1,
    2;

  7. Comment by Sravan

    Hi Dave,

    Thanks for the detailed post. We have a similar requirement that our clients want to time slice the measures by CY, PY – MTD, YTD, QTD (based on fiscal and calendar year). With this post’s help, I was able to implement it in development for one fact table so far (within 4 hrs, database and universe) in semi-complex universe (70 tables, 287 joins, 12 contexts). I am testing the data and it looks great so far.

    I have a question. When I pull CY MTD, CY YTD, PY MTD and PY YTD measures into my report as one query, the SQL viewer shows 4 sqls as ‘Synchronization’ instead of ‘Join’ like you mentioned above. What’s the different between ‘Synchronization’ and ‘Join’ here?

    I have both ‘Multiple sqls for each context’ and ‘Multiple sqls for each measure’ checked in the universe.

    Thanks much,
    Sravan

  8. Comment by Dave Rathbun

    Hi, if you see a synchronization operation it means you have some dimensions that work for some contexts but not others. If you carefully examine the SQL you should see which dimensions those are, which fact table is being used, and which time-slice route. That may help you understand where the issue is coming from.

  9. Comment by Sravan

    Dave, thanks for pointing me in the right direction. I think I understand now what’s causing the synchronization operation in my case. If I select just the timeslice measures (without any dimensions) in my query then the queries are synchronized which I think is expected as there are no shared dimensions across the timeslice contexts. Please correct me if I am wrong.

    (also your comments in old posts from BOB forum helped me understand the ‘Sychronization’ and ‘Join’ concepts, http://www.forumtopics.com/busobj/viewtopic.php?p=264898 and http://www.forumtopics.com/busobj/viewtopic.php?p=622469
    )

    Thanks again.

  10. Comment by Dave Rathbun

    Yes, queries with only measures will be synchronized because there’s nothing to link with. :)

  11. Comment by Guru

    Dave,

    Thank you for all what you do to the Business Objects user community.

    I am currently implementing this on a summary/detail universe. There are summary measures from the summary table and some detail measures from the detail table and the measures are independent of each other. We have created an alias table for the summary measures.

    My question is that we are going to create contexts for each of the time slice measures on both the summary and detail table. Would it be better to use the summary table (instead of the alias) for creating the time-slice measures or use the alias summary table?

    Thank you again for all your help!

  12. Comment by Dave Rathbun

    I’m not sure I follow the question. :-? The core requirement of this technique is that you will have a context per each fact per each time slice, so if you have two fact tables and six time slices you will have 12 contexts. How you pick your facts, and which facts include measures, doesn’t really play into that discussion. There are probably other factors that will influence which table you use to make your summary measures.

  13. Comment by PhillyGirl89

    Hi Dave,

    Thanks for these great posts. I was wondering if you could please explain how to create that special table for beginners?

    Or would you be able to email me the .unv or .unx file in which you did this example, so I can follow along? I have just loaded the Island Resorts universe up and am trying to re-create these steps, but not sure how to start out and create that table.

    Thanks!!

  14. Comment by Dave Rathbun

    I think in my example I created the data in Excel and then loaded it into Oracle for use in my example. Sending you the .UNV file would not help. I will see if I can create a load script for the sample data and attach it to this post.

  15. Comment by bp

    Dave, I am not quite getting how the final SQL is going to look — can you provide an example, maybe a screen shot of what objects are in the data provider, as well as the SQL that is generated?? I was following until you got to the Time Slice Bit objects…

    Also, wondering why you would chose this approach vs time dependent case statements for MTD, YTD, CYTD etc?

    Greate post topic !!! This is a constant thorn in our sides and we have reports with so many time dependent data providers it would make your head spin.

  16. Comment by khalzy

    Hi Dave, how to create Measure Bits?

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.