Dec 17 2009
Time Sliced Measures Part III: Making Measures
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.
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??
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.
Great post, Dave. Is there a link for the completed universe/database — sorry if I missed it? Thanks!
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.
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
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;
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
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.
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.
Yes, queries with only measures will be synchronized because there’s nothing to link with. 🙂
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!
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.
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!!
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.
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.
Hi Dave, how to create Measure Bits?