1. Database Administrator's Guide for Oracle Essbase
  2. Calculating Time Series Data
  3. Calculating Period-to-Date Values Using Dynamic Time Series Members

Calculating Period-to-Date Values Using Dynamic Time Series Members

In Essbase, you can calculate period-to-date values. To do this, you define Dynamic Time Series members in the outline. For example, to calculate quarter-to-date values, you enable the Q-T-D member and associate it with a particular generation.

A sample use case of dynamic time series is when you want to 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 a detailed example, see Calculating Period-to-Date Values in an Accounts Dimension.

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.

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 below, 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 23-4 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 and Disabling Dynamic Time Series Members

To use a predefined Dynamic Time Series member, you must enable the member and associate it with an appropriate generation number. See Understanding the Cube.Settings Worksheet: Dynamic Time Series.

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)

If required, you can specify aliases for Dynamic Time Series members. See Specifying Alias Names for Dynamic Time Series Members.

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

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 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.

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.

The following table lists the Dynamic Time Series members and their corresponding generation names:

Table 23-3 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.

For example, in Sample.Basic, you can enable Dynamic Time Series for a generation called Quarter. Quarter contains quarterly data in the members Qtr1, Qtr2, and so on. If you enable Dynamic Time Series for Quarter, Essbase creates 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, 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, specify the Select DTS Member option in Smart View.

In the example below, 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 23-5 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 configuration setting TARGETTIMESERIESOPT.