Time-Balance and Dynamic-Time-Series Functionality in MDX

Essbase block storage outlines provide pre-defined tags in the form of TBFirst, TBLast and TBAvg for automatic time balance functionality. Built-in time series members, such as YTD and WTD, can be associated with a specific generation of a named time dimension in order to obtain time series functionality.

Such tags are not available directly in aggregate storage outlines. However, it is possible to achieve similar functionality through different means. This topic outlines one such method of achieving both time-balance and time-series functionality using the Sample Basic database when used in aggregate storage mode.

Time Balance Functionality

Recall that before you can use a time balance tag in an outline there must be an Accounts dimension. Normally, the calculation of an upper-level member in the time dimension is based on either the outline hierarchy or any formula (if present) on the member. However, if a member in an accounts dimension is marked as TB First, then any upper level member in the time dimension is computed using the TBFirst tag.

For example, in the Sample Basic block storage outline, data corresponding to member "Opening Inventory" (ignore the formula on opening inventory for the purpose of this discussion and simply focus on the time-balance tag associated with the member) represents the inventory at the beginning of each month. The quarterly value for "Opening Inventory" is equal to the Opening value for the first month in that quarter. Thus, "Opening Inventory" is tagged with TBFirst tag. Similarly, "Ending Inventory" data represents the inventory at the end of each month. The quarterly value for "Ending Inventory" is equal to the ending value for the (last month in the) quarter. Therefore, Ending Inventory is tagged with TBLast.

This functionality can be achieved in aggregate storage through a combination of UDAs and MDX outline formulas. Instead of using pre-defined tags, members of the accounts dimension that require time-balance tags should be marked with UDAs such as TBFirst and TBLast. Thus, "Opening Inventory" would be associated with a UDA called "TBFirst," and "Ending Inventory" with the UDA "TBLast".

Next, consider creating an additional dimension called "Analytics" dimension which houses all formulas required for the outline and hence is tagged as Accounts dimension in the Aggregate Storage outline. Consider the presence of a member called "Actual" which is used to perform all dataloads. Additionally, consider a member "TB" in this dimension that has the following formula attached to it to achieve time-balance functionality.

 WHEN (IsUda([Measures].CurrentMember, "TBLast"))
  THEN (ClosingPeriod (Year.Levels(0), Actual))
 WHEN (IsUda([Measures].CurrentMember, "TBFirst"))
  THEN (OpeningPeriod (Year.Levels(0), Actual))

Thus, when cells at the intersection of the member "TB" from the Analytics dimension and any other measure that is tagged with a "Time Balance" User Defined Attribute are retrieved, the time balance computation is performed dynamically at retrieval time. We have outlined the time balance computation explicitly to aid understanding. However, you may also consider using the functions OpeningPeriod and ClosingPeriod in conjuction with UDAs to accomplish the above.

Dynamic Time Series Functionality

Let us consider support for Quarter-to-Date functionality in Sample Basic outline. The functionality is obtained by enabling the Q-T-D member and associating it with the generation containing quarters, namely, generation number 2, containing members Qtr1, Qtr2, Qtr3, and Qtr4. Retrieving Q-T-D member in a query requires specification of a month (level 0 member) as the latest member and results in the calculation of monthly values up to the current month in the quarter. Thus, Q-T-D(May) returns the quarter-to-date values by adding values for April and May.

Let QTD be a member of the newly added "Analytics" dimension in Sample Basic outline in Aggregate Storage mode.

In keeping with the requirement that only level-0 members of time dimension can be specified as current time period for Q-T-D, the QTD member intersections with level-0 members of Year dimension alone make sense.

The MDX formula required to be associated with the QTD member is given below.

/* If the member from Year dimension is at Level 0, then */
CASE  WHEN IsLevel([Year].CurrentMember, 0)
      /* Add all values from the first sibling of the member to itself */
      THEN Sum( Parent([Year].CurrentMember).FirstChild:[Year].CurrentMember, [Actual])
          #Missing /* Meaningless to ask for QTD for any other [Year].dimension member.

Alternately, the functionality represented by the Dynamic Time Series member can also be expressed as Sum(PeriodToDate (Year.Generations (n)), where n is to be substituted by the generation number to which the Dynamic Time Series member applies. Thus for Q-T-D in the Sample Basic database, the value of n = 2.