If you would like to compute month-to-date or week-to-date information from your daily stored data…
If you would like to automatically run a program following dimension maintenance for a given dimension…
This post is for you!
The Situation:
How does Oracle OLAP compute YTD amounts? To use a simple example, let’s assume that you’re looking at a report for May07, and you want to see the YTD Sales as of May07. This YTD amount is computed by adding up the Sales for a specific list of time periods (Jan07, Feb07, Mar07, Apr07, May07). That list (called a valueset) is stored in an object called TIME_YEAR_TO_DATE_VSET, and is populated automatically by Oracle’s dimension maintenance routines. When a dimension is defined as a ‘Time Dimension’ in AWM, the dimension maintenance routines populate several time-series related metadata objects in the AW.
Each value of the Time dimension has its own list stored in TIME_YEAR_TO_DATE_VSET, a list that the YTD function looks at to determine which values to add to compute a YTD amount. It’s a pretty simple, very straightforward concept.
The Problem:
By default, that valueset is populated with dimension values from the lowest (leaf) level of the Time dimension. This is fine for a Time dimension with a lowest granularity of Month, because the system never needs to add more than 12 numbers together to return a YTD amount. But what if the Time dimension goes down to Daily data? In that case, the YTD formulas for days falling later in the year must aggregate many dimension values. For example, December 2nd must sum 335 days of data, because Oracle OLAP’s default population of the valueset includes all 335 leaf-level days from January 1 through December 2. Performance is usually affected adversely and noticeably.
The Vlamis Solution:
The Vlamis enhanced method changes Oracle’s default valueset, so that it references (in the December 2nd example) Q1, Q2, Q3, Oct, Nov, Dec 1, and Dec 2, thus reducing the number of data points that must be aggregated and thereby speeding up the display of YTD calculated measures. This method can be customized to accommodate a week-level in the time hierarchy. It can also be extended to compute Month-To-Date and Week-To-Date valuesets when data is stored at the Day level, computations which are not offered by the AWM calculated measure wizard.
But wait a minute – each time the Time dimension is maintained in AWM, wouldn’t Oracle’s maintenance routines repopulate the valuesets with the default values? Yes! And that requires that the Vlamis method be reapplied. Since we don’t want to have to remember to do that every time the Time dimension is maintained, we have also created a method by which we automatically repopulate that valueset with the “smarter” values each time the Time dimension is maintained. And this method can be applied not just to the Time dimension and the population of the YTD valueset, but to ANY dimension for which an otherwise manual procedure needs to be run after that dimension is maintained. That method is dimension specific. For example, if you need to run a procedure to sort your Product dimension values, that procedure would run automatically after you maintain the Production dimension.
So…
If your Time dimension uses daily data, and your YTD measures run slowly…
If you would like to compute month-to-date or week-to-date information from your daily stored data…
If you would like to automatically run a program following dimension maintenance for a given dimension…
Contact Mark Thompson (mthompson@vlamis.com), or call Vlamis Software Solutions at 816-781-2880.
