Calculating Time Series Data

This chapter explains how to calculate time series data. For example, you can do inventory tracking by calculating the first and last values for a specific time period. You can also calculate period-to-date values.

This chapter includes the following sections:

• Calculating First, Last, or Average Values
• Calculating Period-to-Date Values
• Using Dynamic Time Series Members in Partitions

Calculating First, Last, or Average Values

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

Hyperion Essbase usually calculates a parent in the 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, Hyperion 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.

Specifying Accounts and Time Dimensions

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

In the Sample Basic database, the Measures dimension is tagged as accounts, and the Year dimension is tagged as time.

Figure 30-1: Sample Basic Outline Showing Accounts and Time Tags

For information on tagging accounts and time dimensions, see Setting Dimension and Member Properties.

Reporting the Last Value for Each Time Period

For an accounts dimension member, you can tell Hyperion 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. (In the database outline, the TB Last tag is displayed.)

For example, in the Sample Basic database, the accounts member 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 30-2: Sample Basic Outline Showing Last Tag

For information on tagging an accounts member as Last, see Setting Dimension and Member Properties.

By default, Hyperion 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 First Value for Each Time Period

For an accounts dimension member, you can tell Hyperion 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.)

For example, in the Sample Basic database, the accounts member 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 30-3: Sample Basic Outline Showing First Tag

For information on tagging an accounts member as First, see Setting Dimension and Member Properties.

By default, Hyperion 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 Hyperion Essbase to average values across time periods and consolidate the average up to the next level. For example, you can tell Hyperion 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 Dimension and Member Properties.

By default, Hyperion Essbase does not skip #MISSING or zero (0) values when calculating a parent value. When calculating the average, Hyperion 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 Hyperion Essbase to skip #MISSING and zero values. See Skipping #MISSING and Zero Values.

Skipping #MISSING and Zero Values

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

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

You can override this default using the following tags:

To...	Use this Tag...
Skip #MISSING values when calculating a parent value	Skip Missing In Outline Editor, select the parent member from the accounts dimension and click the button.
Skip 0 values when calculating a parent value	Skip Zeros In Outline Editor, select the parent member from the accounts dimension and click the button.
Skip #MISSING values and 0 values when calculating a parent value	Skip Missing and Skip Zeros In Outline Editor, select the parent member from the accounts dimension and click the button.

For example, if you tag an accounts dimension member as Last and Skip Missing, then Hyperion Essbase consolidates the last non-missing child to the parent. Consider the following example:

Considering the Effects of First, Last, and Average Tags

The following table shows how Hyperion Essbase consolidates the time dimension based on the time balance (TB) First, Last, and Average tags on accounts dimension members.

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.

Consider the following example from Sample Basic, in which Opening Inventory is tagged as First:

Because Opening Inventory is tagged as First, Hyperion 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 your 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 Hyperion Essbase, you can calculate period-to-date values in two ways:

• During a batch calculation, using the @PTD function
• Dynamically, when a user requests the values, using Dynamic Time Series members

This section explains how to use Dynamic Time Series members to dynamically calculate period-to-date values. Using Dynamic Time Series members is the most efficient method in almost all cases. For an example using the @PTD function to calculate period-to-date values, see Examples of Formulas.

Using Dynamic Time Series Members

In order to calculate period-to-date values dynamically, you need to 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. This procedure creates a Dynamic Time Series member for you.

For example, if you want 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, this is generation number 2, which contains the Qtr1, Qtr2, Qtr3, and Qtr4 members. Hyperion 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. For more information, see Enabling Dynamic Time Series Members.

Figure 30-4: Sample Basic Outline Showing Time Dimension

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

Figure 30-5: Sample Basic Outline Showing Dynamic Time Series

Hyperion Essbase provides eight predefined Dynamic Time Series members:

• H-T-D 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 eight members provide up to eight levels of period-to-date reporting. How many members you use and which members you use depends on your data and your database outline.

For example, if your database contains hourly, daily, weekly, monthly, quarterly, and yearly data, you might want to 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 your database contains monthly data for the past 5 years, you might want to report year-to-date (Y-T-D) and history-to-date (H-T-D) information, up to a specific year.

If your database tracks data for seasonal time periods, you might want to 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, irrespective of the data. For example, if you choose, 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.

Enabling Dynamic Time Series Members

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

1. In Outline Editor, open the database.
2. Select Outline > Dynamic Time Series.

   Hyperion Essbase displays the Dynamic Time Series Member Information dialog box.

   Figure 30-6: Dynamic Time Series Member Information Dialog Box for Sample Basic

   

3. In the DTS Member list, select the required Dynamic Time Series member. For example, select Q-T-D (quarter-to-date).
4. In the Generation list, select the appropriate generation number. For example, in the Sample Basic database, select generation 2 to associate the Q-T-D member with that generation.

   Note:

   How many generations the Generation list displays depends on how many generations are in the time dimension. You cannot associate Dynamic Time Series members with the highest generation (level 0 members).

5. Check Enable This DTS Member.

   In Sample Basic, the DTS member may already be enabled by default.

   Figure 30-7: Dynamic Time Series Member Information Dialog Box
   Showing Q-T-D on Sample Basic

   

6. If desired, you can create an alias name for the DTS member. See Specifying Alias Names for Dynamic Time Series Members.
7. To enable the Dynamic Time Series member, click OK.

   In the database outline, Hyperion Essbase adds a comment to the dimension tagged as time. The following figure shows the Sample Basic database with H-T-D, Q-T-D, and M-T-D defined.

   Figure 30-8: Sample Basic Outline Showing Dynamic Time Series Members

   

Disabling Dynamic Time Series Members

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

1. In Outline Editor, open the database.
2. Select Outline > Dynamic Time Series.

   Hyperion Essbase displays the Dynamic Time Series Member Information dialog box.

3. In the DTS Member list, select the Dynamic Time Series member you want to disable.

   Figure 30-9: Dynamic Time Series Member Information Dialog Box
   Showing Enabled Q-T-D

   

4. Clear Enable This DTS Member.

   Figure 30-10: Dynamic Time Series Member Information Dialog Box
   Showing Q-T-D Disabled

   

5. Click OK.

Specifying Alias Names for Dynamic Time Series Members

You can specify alias names for predefined Dynamic Time Series members. You can then use the alias names to retrieve the Dynamic Time Series members in Hyperion Essbase Spreadsheet Add-in or in a report.

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

1. In the Dynamic Time Series Member Information dialog box, enable the DTS member. See Enabling Dynamic Time Series Members.
2. In the Alias Table list, select the alias table in which you want to save the alias name.
3. In the Alias text box, type the alias name for the DTS member.

   For example, type QtrToDate.

   Figure 30-11: Dynamic Time Series Member Information Dialog Box
   Showing Q-T-D Alias

   

4. Click Set.
5. To save the alias name to the chosen alias table, click OK.

For more information on specifying and displaying alias names, see Creating and Changing Database Outlines.

Applying Predefined Generation Names to Dynamic Time Series Members

When you enable a Dynamic Time Series member and associate it with a generation number, Hyperion Essbase creates a predefined generation name for that generation number. These generation names display in the Generation and Level Names dialog box. To open this dialog box, select Outline > Gen/Level Names in Hyperion Essbase Application Manager Outline Editor. For more information on creating generation names, see Creating and Changing Database Outlines.

This table shows the Dynamic Time Series members and their corresponding generation names:

These member and generation names are reserved for use by Hyperion Essbase. If you use one of these generation names to create a generation name on the time dimension, Hyperion 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, Hyperion 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 need to tell Hyperion Essbase the time period up to which you want to calculate the period-to-date value. This time period is known as the latest time period and must be a level 0 member on the time dimension.

• For a specific member, in Hyperion Essbase 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, do one of the following:
  • Use the <LATEST command in Report Writer. For more information, see Reporting on Data.
  • Specify the Latest Time Series option in the Spreadsheet Add-in Essbase Options dialog box. For more information, see the Hyperion Essbase Spreadsheet Add-in User's Guide.

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

The following example shows Sample Basic data. 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 30-12: Hyperion Essbase Spreadsheet Add-in Showing Period-To-Date Value for May

Using Dynamic Time Series Members in Partitions

If Dynamic Time Series members are part of the shared area between databases, you need to define the Dynamic Time Series members in both databases, just as you would for regular members. For example, if your partition definition includes Qtr1, Qtr2, Qtr3, Qtr4, and the Dynamic Time Series member Q-T-D, then you need to define the Q-T-D member in both the source database and the target database.

If a Dynamic Time Series member is not part of the shared area between databases, Hyperion Essbase gets the data for that member from the source database. You do not need to define the Dynamic Time Series member in both databases. However, this configuration is generally less efficient than including the Dynamic Time Series member in the partition definition.

For more information on partitioning, see Designing Partitioned Applications.

Copyright © 1991-2000 Hyperion Solutions Corporation. All rights reserved.