Time Balance and Flow Metrics in ASO Accounts Dimensions

Time Balance properties on Essbase aggregate storage (ASO) cubes provide built-in calculations along the Time dimension. Flow tags help you optimize calculation of time-related accounts. A flow metric is an unsigned Accounts dimension member that stores periodic and year-to-date values.

Use Time Balance Tags in ASO Accounts Dimensions

You can set Time Balance properties on aggregate storage Accounts dimensions to provide built-in calculations along the Time dimension. This saves time and performance overhead of using member formulas to achieve time-balance functionality.

The following time-balance properties are supported on stored or formula-bearing Accounts dimension members:

• TB First, TB Last, TB Average

• SKIP NONE, SKIP MISSING

Consider a stored measure such as Headcount in a human-resources application. Within a Year-Quarter-Months hierarchy, Headcount data is loaded at the month level.

The desired yearly or quarterly Headcount value is not the sum of its months; rather, it should be the last recorded value within a time period.

Tagging Headcount as TB Last with SKIPMISSING means that, for Year 2005, its value is the last nonempty value of the headcount for its months. If Dec has a nonmissing Headcount value, then that value will be returned; otherwise, the Nov value will be checked and returned if nonmissing.

If a formula-bearing member has a time balance tag, the formula is executed only for level 0 Time members, and the Time dimension is aggregated according to the time balance tag.

The time balance tags provide a built-in calculation along the Time dimension. To perform other time-based calculations using formulas, such as period-to-date and rolling averages, you can create a dimension called TimeView and write all time-based formulas on that dimension. Doing so enables you to use Time Balance calculation functionality without losing the ability to perform other time-based calculations.

Use Flow Tags in ASO Accounts Dimensions

You can use flow metrics to optimize calculation of time-based accounts in an aggregate storage cube. A flow metric is an unsigned Accounts dimension member that stores periodic and year-to-date values in the outline.

The following example describes the problem to be solved with flow metrics. Assume you have Sales and Additions figures for all 12 months. You want to perform an aggregation to populate each month’s beginning inventory.

Table 38-6 Inventory Calculation

	Sales	Additions	Inventory
Jan	5	1	50
Feb	6	3	46
Mar	4	2	43
Apr	7	0	41
...	...	...	...

You would use an MDX formula on the Beginning Inventory member in order to calculate its value. Without flow metrics, to obtain each month’s beginning inventory, the calculator engine would have to reiterate the MDX formula exponentially.

Inventory = SUM(MemberRange(Jan:Time.CurrentMember), (Additions - Sales)) + Beg_Inventory

To optimize the illustrated example, assign the Inventory member the formula (Addition – Sales), and tag the member as Flow, using the outline editor in the Essbase web interface.

Before you tag a member as Flow, ensure that it:

• is a member of the Accounts dimension

• has a formula

• has a Time Balance tag

Restrictions on Alternate Hierarchies for Time Balance and Flow Metrics

If alternate hierarchies are used in the aggregate storage time dimension, the following restrictions apply when using Flow and TB tags on the Accounts dimension:

1. The shared level among alternate time hierarchies must be level 0.

2. The order of members at shared level among alternate time hierarchies must be the same in all alternate hierarchies.

3. The number of shares for a stored member must not exceed that of its previous sibling. Previous sibling means the prior stored member in the outline, found at the same generation.

   For example, consider the following Year dimension. The alternate hierarchies are not valid, because the number of shares for April (3) is greater than the number of shares for March (1).

   
   
   

Aggregate Time-Balance Tagged Measures

Use the MDX Aggregate function to aggregate measures tagged with time balance tags.

Effect of Attribute Calculations on Time Balance Measures in ASO Cubes

The following calculation logic applies if

1. The aggregate storage outline contains a time dimension or date-time dimension with one or more attribute or linked-attribute dimensions.

2. You perform queries on time balance tagged measures.

If the above cases are both true, MDX Aggregate() semantics are used to evaluate the cells.

For example, consider a scenario in which:

• Year is a time-date dimension with a day level hierarchy.

• Holiday is an attribute dimension on Year, with each date tagged with Holiday_TRUE or Holiday_FALSE.

• Opening Inventory is tagged as TBFirst.

The value of (Year, Holiday_TRUE, [Opening Inventory]) is evaluated according to the following MDX logic:

Aggregate( {Set of dates that are holidays in Year}, [Opening Inventory])