Calculating Time Series Data

In This Section:

Introduction

Calculating First, Last, and Average Values

Calculating Period-to-Date Values

Using Dynamic Time Series Members in Transparent Partitions

The information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases. Also see Comparison of Aggregate and Block Storage.

Introduction

Time series calculations assume that you have Dynamic Time Series members defined in the outline. Calculating time series data is helpful in tracking inventory by calculating the first and last values for a time period, and in calculating period-to-date values.

Calculating First, Last, and Average Values

Using time balance and variance reporting tags on the dimension tagged as accounts, you can tell Essbase how to perform time balance calculations on accounts data.

Essbase usually calculates a dimension tagged as time by consolidating or calculating the formulas on the parent’s children. However, you can use accounts tags, such as time balance and variance reporting tags, to consolidate a different kind of value. For example, if you tag a parent member in the accounts dimension with a time balance property of First, Essbase calculates the member by consolidating the value of the member’s first child. For example, in the Sample.Basic database, the Opening Inventory member in the Measures dimension (the accounts dimension) has a time balance property of First. This member represents the inventory at the beginning of the time period. If the time period is Qtr1, Opening Inventory represents the inventory available at the beginning of Jan (the first member in the Qtr1 branch).

To use accounts tags, you must have a dimension tagged as accounts and a dimension tagged as time. You use the First, Last, and Average tags (time balance properties) and the Expense tag (variance reporting property) only on members of a dimension tagged as accounts. The dimensions you tag as time and accounts can be either dense or sparse dimensions.

For cells of time balance account members, a member in any dimension other than the time dimension that is set with the ^ consolidation property is excluded from the Average calculation; the member is, however, included in First and Last calculations.

Note:

If you are using Intelligent Calculation, changing accounts tags in the database outline does not cause Essbase to restructure the database. You may have to tell Essbase explicitly to recalculate the required data values. See Changing Formulas and Accounts Properties.

Specifying Accounts and Time Dimensions

When you tag a dimension as accounts, Essbase knows that the dimension contains members with accounts tags. When you tag a dimension as time, Essbase knows that this dimension is the one on which to base the time periods for the accounts tags.

As shown in Figure 129, Sample.Basic Outline Showing Accounts and Time Tags, the Measures dimension is tagged as accounts, and the Year dimension is tagged as time.

Figure 129. Sample.Basic Outline Showing Accounts and Time Tags

This image shows an outline in which the Measures dimension is tagged as accounts and the Year dimension is tagged as time.

See Creating a Time Dimension and Creating an Accounts Dimension.

Reporting the Last Value for Each Time Period

For an accounts dimension member, you can tell Essbase to move the last value for each time period up to the next level. To report the last value for each time period, set the member’s time balance property as Last. (The tag displays as TB Last in the database outline.)

As shown in Figure 130, Sample.Basic Outline Showing Last Tag, the accounts member Ending Inventory is tagged as TB Last. Ending Inventory consolidates the value for the last month in each quarter and uses that value for that month’s parent. For example, the value for Qtr1 is the same as the value for Mar.

Figure 130. Sample.Basic Outline Showing Last Tag

This image shows an outline in which the accounts member Ending Inventory's time balance property is set as Last.

For information on tagging an accounts member as Last, see Setting Time Balance Properties.

By default, Essbase does not skip #MISSING or zero (0) values when calculating a parent value. You can choose to skip these values. For a discussion of how and why to skip #MISSING values, see Skipping #MISSING and Zero Values.

Reporting the First Value for Each Time Period

For an accounts dimension member, you can tell Essbase to move the first value for each time period up to the next level. To report the first value for each time period, set the member’s time balance property as First. (The tag displays as TB First in the database outline.)

As shown in Figure 131, Sample.Basic Outline Showing First Tag, the accounts member Opening Inventory is tagged as TB First. Opening Inventory consolidates the value of the first month in each quarter and uses that value for that month’s parent. For example, the value for Qtr1 is the same as the value for Jan.

Figure 131. Sample.Basic Outline Showing First Tag

This image shows an outline in which the accounts member Opening Inventory's time balance property is set as First.

For information on tagging an accounts member as First, see Setting Time Balance Properties.

By default, Essbase does not skip #MISSING or zero (0) values when calculating a parent value. You can choose to skip these values. See Skipping #MISSING and Zero Values.

Reporting the Average Value for Each Time Period

For an accounts dimension member, you can tell Essbase to average values across time periods and consolidate the average up to the next level. For example, you can tell Essbase to average the values for Jan, Feb, and Mar and then use that value for the Qtr1 value. To report the average value for each time period, set the member’s time balance property as Average.

For information on tagging an accounts member as Average, see Setting Time Balance Properties.

By default, Essbase does not skip #MISSING or zero (0) values when it calculates a parent value. Thus, when it calculates the average, Essbase aggregates the child values and divides by the number of children, regardless of whether the children have #MISSING or zero values. You can tell Essbase to skip #MISSING and zero values. See Skipping #MISSING and Zero Values.

Skipping #MISSING and Zero Values

You can tell Essbase how to treat #MISSING and zero (0) values when doing time balance calculations. A #MISSING value is a marker in Essbase that indicates that the data in this location does not exist, does not contain any meaningful value, or was never entered.

By default, Essbase does not skip #MISSING or 0 (zero) values when calculating a parent value.

You can override this default by setting a skip property. See Setting Skip Properties.

For example, if you tag an accounts dimension member as Last and Skip Missing, then Essbase consolidates the last nonmissing child to the parent. Consider the example in Table 77:

Table 77. Example of the Effects of the Skip Missing

Accounts -> Time

Jan

Feb

Mar

Qtr1

Accounts Member (Last, Skip Missing)

60

70

#MI

70

Tagging an account as Average and Skip Missing may produce different results from tagging that account as Average and Skip None. A calculation performed with Average and Skip None produces correct results because no data is skipped. But because grandparents with children are consolidated by summing the averages, results of a calculation on an account with Average and Skip Missing is incorrect unless you use Dynamic Calc or Two-Pass tags.

Considering the Effects of First, Last, and Average Tags

Table 78 shows how Essbase consolidates the time dimension based on the time balance (TB) First, Last, and Average tags on accounts dimension members.

Table 78. Example of the Effects of (TB) First, Last and Average

Accounts -> Time

Jan

Feb

Mar

Qtr1

 

Accounts Member1

11

12

13

36

Value of Jan + Feb + Mar

Accounts Member2 (TB First)

20

25

21

20

Value of Jan

Accounts Member3 (TB Last)

25

21

30

30

Value of Mar

Accounts Member4 (TB Average)

20

30

28

26

Average of Jan, Feb, Mar

Placing Formulas on Time and Accounts Dimensions

If you place a member formula on a time or accounts dimension, it may be overwritten by a time balance calculation.

Table 79 shows an example in which Opening Inventory is tagged as First:

Table 79. Example of the Effects of (TB) First

Measures -> Year

Jan

Feb

Mar

Qtr1

Opening Inventory: First

30000

28000

27000

30000

Because Opening Inventory is tagged as First, Essbase calculates Opening Inventory for Qtr1 by taking the Opening Inventory for Jan value. Any member formula that is placed on Qtr1 in the database outline is overwritten by this time balance calculation.

Calculating Period-to-Date Values

You can calculate period-to-date values for data. For example, you can calculate the sales values for the current quarter up to the current month. If the current month is May, using a standard calendar quarter, the quarter total is the total of the values for April and May.

In Essbase, you can calculate period-to-date values in the following ways:

  • During a batch calculation, using the @PTD function

  • Dynamically, when a user requests the values, using Dynamic Time Series members

  • As part of an MDX query, using the DTS function

This section explains how to use Dynamic Time Series members to dynamically calculate period-to-date values. Using Dynamic Time Series members is almost always the most efficient method. For an example, see Calculating Period-to-Date Values.

Using Dynamic Time Series Members

To calculate period-to-date values dynamically, you must use a Dynamic Time Series member for a period on the dimension tagged as time. See Specifying Accounts and Time Dimensions.

You do not create the Dynamic Time Series member directly in the database outline. Instead, you enable a predefined Dynamic Time Series member and associate it with an appropriate generation number.

For example, to calculate quarter-to-date values, you enable the Q-T-D member and associate it with the generation to which you want to apply the Dynamic Time Series member. In Sample.Basic, the generation containing quarters is generation number 2, which contains the Qtr1, Qtr2, Qtr3, and Qtr4 members. Essbase creates a Dynamic Time Series member called Q-T-D and associates it with generation 2. The Q-T-D member calculates monthly values up to the current month in the quarter. see Enabling Dynamic Time Series Members.

Dynamic Time Series members are not displayed as members in the database outline. Instead, Essbase lists the currently active Dynamic Time Series members in a comment on the time dimension. In the outline in Figure 132, Sample.Basic Outline Showing Dynamic Time Series, H-T-D (history-to-date) and Q-T-D (quarter-to-date) are active. H-T-D is associated with generation 1; Q-T-D is associated with generation 2.

Figure 132. Sample.Basic Outline Showing Dynamic Time Series

This image shows how Dynamic Time Series members are represented in an outline, as described in the text preceding the image.

Essbase provides eight predefined Dynamic Time Series members:

  • HTD (history-to-date)

  • Y-T-D (year-to-date)

  • S-T-D (season-to-date)

  • P-T-D (period-to-date)

  • Q-T-D (quarter-to-date)

  • M-T-D (month-to-date)

  • W-T-D (week-to-date)

  • D-T-D (day-to-date)

These members provide up to eight levels of period-to-date reporting. How many and which members you use depends on the data and the database outline.

For example, if the database contains hourly, daily, weekly, monthly, quarterly, and yearly data, you can report day-to date (D-T-D), week-to-date (W-T-D), month-to-date (M-T-D), quarter-to-date (Q-T-D), and year-to-date (Y-T-D) information.

If the database contains monthly data for the last five years, you can report year-to-date (Y-T-D) and history-to-date (H-T-D) information, up to a specific year.

If the database tracks data for seasonal time periods, you can report period-to-date (P-T-D) or season-to-date (S-T-D) information.

You can associate a Dynamic Time Series member with any generation in the time dimension except the highest generation number, regardless of the data. For example, you can use the P-T-D member to report quarter-to-date information. You cannot associate Dynamic Time Series members with level 0 members of the time dimension.

Note:

Oracle recommends that you avoid assigning time balance properties (First, Last, Average, Skip Missing) to members set for dynamic calculations if you plan to use these members in Dynamic Time Series calculations. Doing so may retrieve incorrect values for the parent members in your accounts dimension.

Enabling Dynamic Time Series Members

To use Dynamic Time Series members, you must enable them. If required, you can specify aliases for Dynamic Time Series members. See Specifying Alias Names for Dynamic Time Series Members.

  To enable Dynamic Time Series members, see “Enabling Dynamic Time Series Members” in the Oracle Essbase Administration Services Online Help.

Note:

The number of generations displayed depends on the number of generations in the time dimension. You cannot associate Dynamic Time Series members with the highest generation (level 0 members).

After you enable Dynamic Time Series members in the database outline, Essbase adds a comment to the dimension tagged as time; for example, the Year dimension from Sample.Basic showing H-T-D and Q-T-D defined:

Year Time (Active Dynamic Time Series Members: H-T-D, Q-T-D) (Dynamic Calc)

Disabling Dynamic Time Series Members

To disable a Dynamic Time Series member, tell Essbase not to use the predefined member.

  To disable Dynamic Time Series members, see “Disabling Dynamic Time Series Members” in the Oracle Essbase Administration Services Online Help.

Specifying Alias Names for Dynamic Time Series Members

You can specify alias names for predefined Dynamic Time Series members, such as QtrToDate, for the Q-T-D Dynamic Time Series member. You can then use the alias names to retrieve the Dynamic Time Series members in Smart View, Spreadsheet Add-in, or in a report.

You can create up to eight alias names for each Dynamic Time Series member. Essbase saves each alias name in the Dynamic Time Series alias table that you specify.

  To create aliases for Dynamic Time Series members, see “Creating Aliases for Dynamic Time Series Members” in the Oracle Essbase Administration Services Online Help.

For information on specifying and displaying alias names, see Setting Aliases.

Applying Predefined Generation Names to Dynamic Time Series Members

When you enable a Dynamic Time Series member and associate it with a generation number, Essbase creates a predefined generation name for that generation number. See Naming Generations and Levels.

  To display generation and level names, see “Naming Generations and Levels” in the Oracle Essbase Administration Services Online Help.

Table 80 lists the Dynamic Time Series members and their corresponding generation names:

Table 80. Dynamic Time Series Members and Corresponding Generation Names

Member

Generation Name

D-T-D

Day

H-T-D

History

M-T-D

Month

P-T-D

Period

Q-T-D

Quarter

S-T-D

Season

W-T-D

Week

Y-T-D

Year

These member and generation names are reserved for use by Essbase. If you use one of these generation names to create a generation name on the time dimension, Essbase automatically creates and enables the corresponding Dynamic Time Series member for you.

For example, in Sample.Basic, you can create a generation name called Quarter for generation number 2. Quarter contains quarterly data in the members Qtr1, Qtr2, and so on. When you create the generation name Quarter, Essbase creates and enables a Dynamic Time Series member called Q-T-D.

Retrieving Period-to-Date Values

When you retrieve a Dynamic Time Series member, you must tell Essbase the time period up to which you want to calculate the period-to-date value. This time period, known as the latest time period, must be a level 0 member on the time dimension.

  Use the following methods to specify the latest time period:

  • For a specific member, in Smart View or Spreadsheet Add-in, specify the latest period member name. Place that name after the Dynamic Time Series member or alias name. For example, Q-T-D(May) returns the quarter-to-date value by adding values for April and May.

  • For a retrieval, use one of the following methods to specify the latest time period:

    • Use the <LATEST command in Report Writer.

    • Specify the Latest Time Period option in the Essbase Options dialog box in Spreadsheet Add-in.

      The member-specific setting—for example, Q-T-D(May)—takes precedence over the <LATEST or Latest Time Series option setting.

In the example in Figure 133, Spreadsheet Showing Period-To-Date Value for May, Q-T-D(May) displays the period-to-date value for May that is obtained by adding the values for Apr and May (8644 + 8929 = 17573).

Figure 133. Spreadsheet Showing Period-To-Date Value for May

This image shows a spreadsheet in which the value for May is the P-T-D value.

Using Dynamic Time Series Members in Transparent Partitions

To optimize query time across transparent partitions for outlines containing Dynamic Time Series members, use the essbase.cfg setting TARGETTIMESERIESOPT.

See the Oracle Essbase Technical Reference and Creating and Maintaining Partitions.