Using Calculation Templates

The Data Analysis tool provides templates for all of the calculations typically in demand for business intelligence applications.

The following topics describe the types of calculations available as calculation templates in the tool.

Cumulative Aggregates

Cumulative calculations start with the first time period and calculate up to the current member, or start with the last time period and calculate back to the current member.

The tool provides several aggregation methods for cumulative calculations:

Cumulative Average: Calculates a running average across time periods.
Cumulative Maximum: Calculates the maximum value across time periods.
Cumulative Minimum: Calculates the minimum value across time periods.
Cumulative Total: Calculates a running total across time periods.

You can choose the measure, the time dimension, and the hierarchy. For selecting the time range see “Choosing a Range of Time Periods” in Oracle OLAP User’s Guide.

Cumulative Calculation Example

This template defines a calculated measure using Cumulative Minimum.

Cumulative minimum of SALES in the TIME dimension and TIME.CALENDAR hierarchy within ancestor at level TIME.CALENDAR_YEAR. Total from beginning to current member.

These are the results of a query against the calculated measure, which displays values for the descendants of calendar year 2021. The minimum value for quarters begins with Q1-21 and ends with Q4-21, and for months begins with Jan-21 and ends with Dec-21.

TIME     TIME_LEVEL           SALES      MIN_SALES
-------- -------------------- ---------- ----------
Q1.21    CALENDAR_QUARTER       32977874   32977874
Q2.21    CALENDAR_QUARTER       35797921   32977874
Q3.21    CALENDAR_QUARTER       33526203   32977874
Q4.21    CALENDAR_QUARTER       41988687   32977874
JAN-21   MONTH                  11477898   11477898
FEB-21   MONTH                  10982016   10982016
MAR-21   MONTH                  10517960   10517960
APR-21   MONTH                  11032057   10517960
MAY-21   MONTH                  11432616   10517960
JUN-21   MONTH                  13333248   10517960
JUL-21   MONTH                  12070352   10517960
AUG-21   MONTH                  11108893   10517960
SEP-21   MONTH                  10346958   10346958
OCT-21   MONTH                  14358605   10346958
NOV-21   MONTH                  12757560   10346958
DEC-21   MONTH                  14872522   10346958

Prior and Future Period

The Data Analysis tool provides several calculations for prior or future time periods.

Here are the calculations used for for prior or future time periods:

Prior Period: Returns the value of a measure at an earlier time period.
Difference From Prior Period: Calculates the difference between values for the current time period and an earlier period.
Percent Difference From Prior Period: Calculates the percent difference between the values for the current time period and an earlier period.
Future Period: Returns the value of a measure at a later time period.
Difference From Future Period: Calculates the difference between the values for the current time period and a later period.
Percent Difference From Future Period: Calculates the percent difference between the values for the current time period and a later period.

When creating a calculation for prior or future time periods, you choose the measure, the time dimension, the hierarchy, and the number of periods from the current period.

Prior Period Example

This template defines a calculated measure using Prior Period:

Prior period for measure SALES in TIME dimension and TIME.CALENDAR hierarchy 1 period ago.

These are the results of a query against the calculated measure. The PRIOR_PERIOD column shows the value of Sales for the preceding period at the same level in the Calendar hierarchy.

TIME     TIME_LEVEL           SALES      PRIOR_PERIOD
-------- -------------------- ---------- ------------
2020     CALENDAR_YEAR         136986572    144290686
2021     CALENDAR_YEAR         140138317    136986572
Q1.20    CALENDAR_QUARTER       31381338     41988687
Q2.20    CALENDAR_QUARTER       37642741     31381338
Q3.20    CALENDAR_QUARTER       32617249     37642741
Q4.20    CALENDAR_QUARTER       35345244     32617249
Q1.21    CALENDAR_QUARTER       36154815     35345244
Q2.21    CALENDAR_QUARTER       36815657     36154815
Q3.21    CALENDAR_QUARTER       32318935     36815657
Q4.21    CALENDAR_QUARTER       34848911     32318935

Period to Date

Period-to-date functions perform a calculation over time periods with the same parent up to the current period.

These functions calculate period-to-date:

Period to Date: Calculates the values up to the current time period.
Period to Date Period Ago: Calculates the data values up to a prior time period.
Difference From Period to Date Period Ago: Calculates the difference in data values up to the current time period compared to the same calculation up to a prior period.
Percent Difference From Period To Date Period Ago: Calculates the percent difference in data values up to the current time period compared to the same calculation up to a prior period.

When creating a period-to-date calculation, you can choose from these aggregation methods:

Sum
Average
Maximum
Minimum

You also choose the measure, the time dimension, and the hierarchy.

Period to Date Example

This template defines a calculated measure using Period to Date.

Gregorian Year to date for SALES in the TIME dimension and TIME.CALENDAR hierarchy. Aggregate using MINIMUM from the beginning of the period.

These are the results of a query against the calculated measure. The MIN_TO_DATE column displays the current minimum SALES value within the current level and year.

TIME     TIME_LEVEL                SALES MIN_TO_DATE
-------- -------------------- ---------- -----------
Q1.21    CALENDAR_QUARTER       36154815    36154815
Q2.21    CALENDAR_QUARTER       36815657    36154815
Q3.21    CALENDAR_QUARTER       32318935    32318935
Q4.21    CALENDAR_QUARTER       34848911    32318935
JAN-21   MONTH                  13119235    13119235
FEB-21   MONTH                  11441738    11441738
MAR-21   MONTH                  11593842    11441738
APR-21   MONTH                  11356940    11356940
MAY-21   MONTH                  13820218    11356940
JUN-21   MONTH                  11638499    11356940
JUL-21   MONTH                   9417316     9417316
AUG-21   MONTH                  11596052     9417316
SEP-21   MONTH                  11305567     9417316
OCT-21   MONTH                  11780401     9417316
NOV-21   MONTH                  10653184     9417316
DEC-21   MONTH                  12415325     9417316

Parallel Period

Parallel periods are at the same level as the current time period, but have different parents in an earlier period. For example, you may want to compare current sales with sales for the prior year at the quarter and month levels.

The Data Analysis tool provides several functions for parallel periods:

Parallel Period: Calculates the value of the parallel period.
Difference From Parallel Period: Calculates the difference in values between the current period and the parallel period.
Percent Difference From Parallel Period: Calculates the percent difference in values between the current period and the parallel period.

To identify the parallel period, you specify a level and the number of periods before the current period. You can also decide what happens when two periods do not exactly match, such as comparing daily sales for February (28 days) with January (31 days).

You also choose the measure, the time dimension, and the hierarchy.

Parallel Period Example

This template defines a calculated measure using Parallel Period.

Parallel period for SALES in the TIME dimension and TIME.CALENDAR hierarchy 1 TIME.CALENDAR.QUARTER ago based on position from beginning to ending of period.

These are the results of a query against the calculated measure, which lists the months for two calendar quarters. The parallel month has the same position within the previous quarter. The prior period for JUL-21 is APR-21, for AUG-21 is MAY-21, and for SEP-21 is JUN-21.

TIME     PARENT     SALES      LAST_QTR
-------- ---------- ---------- ----------
APR-21   CY2006.Q2    11356940   13119235
MAY-21   CY2006.Q2    13820218   11441738
JUN-21   CY2006.Q2    11638499   11593842
JUL-21   CY2006.Q3     9417316   11356940
AUG-21   CY2006.Q3    11596052   13820218
SEP-21   CY2006.Q3    11305567   11638499

Moving Aggregates

Moving aggregates are performed over the time periods surrounding the current period.

The Data Analysis tool provides several aggregation methods for moving calculations:

Moving Average: Calculates the average value for a measure over a fixed number of time periods.
Moving Maximum: Calculates the maximum value for a measure over a fixed number of time periods.
Moving Minimum: Calculates the minimum value for a measure over a fixed number of time periods.
Moving Total: Returns the total value for a measure over a fixed number of time periods.

You can choose the measure, the time dimension, and the hierarchy. You can also select the range, as described in “Choosing a range of time periods” in Oracle OLAP User’s Guide , and the number of time periods before and after the current period to include in the calculation.

Moving Aggregates Example

This template defines a calculated measure using Moving Minimum.

Moving minimum of SALES in the TIME dimension and TIME.CALENDAR hierarchy. Include 1 preceding and 1 following members within level.

These are the results of a query against the calculated measure, which displays values for the descendants of calendar year 2021. Each value of Minimum Sales is the smallest among the current value and the values immediately before and after it. The calculation is performed over all members of a level in the cube.

TIME     TIME_LEVEL           SALES      MIN_SALES
-------- -------------------- ---------- ----------
Q1.21    CALENDAR_QUARTER       32977874   32977874
Q2.21    CALENDAR_QUARTER       35797921   32977874
Q3.21    CALENDAR_QUARTER       33526203   33526203
Q4.21    CALENDAR_QUARTER       41988687   31381338
JAN-21   MONTH                  11477898   10982016
FEB-21   MONTH                  10982016   10517960
MAR-21   MONTH                  10517960   10517960
APR-21   MONTH                  11032057   10517960
MAY-21   MONTH                  11432616   11032057
JUN-21   MONTH                  13333248   11432616
JUL-21   MONTH                  12070352   11108893
AUG-21   MONTH                  11108893   10346958
SEP-21   MONTH                  10346958   10346958
OCT-21   MONTH                  14358605   10346958
NOV-21   MONTH                  12757560   12757560
DEC-21   MONTH                  14872522   12093518

Share calculates the ratio of a measure’s value for the current dimension member to the value for a related member of the same dimension.

You can choose whether the related member is:

Top of hierarchy: Calculates the ratio of each member to the total.
Member’s parent: Calculates the ratio of each member to its parent.
Member’s ancestor at level: Calculates the ratio of each member to its ancestor, that is, a member at a specified level higher in the hierarchy.

When creating a share calculation, you can choose the measure, dimension, and hierarchy. You also have the option of multiplying the results by 100 to get percentages instead of fractions.

Share Example

This template defines a calculated measure using SHARE:

Share of measure SALES in PRODUCT.PRIMARY hierarchy of the PRODUCT dimension as a ratio of top of hierarchy.

These are the results of a query against the calculated measure. The TOTAL_SHARE column displays the percent share of the total for the selected products.

PRODUCT              PROD_LEVEL      SALES      TOTAL_SHARE
-------------------- --------------- ---------- -----------
Total Product        TOTAL            144290686         100
Hardware             CLASS            130145388          90
Desktop PCs          FAMILY            78770152          55
Portable PCs         FAMILY            19066575          13
CD/DVD               FAMILY            16559860          11
Software/Other       CLASS             14145298          10
Accessories          FAMILY             6475353           4
Operating Systems    FAMILY             5738775           4
Memory               FAMILY             5430466           4
Modems/Fax           FAMILY             5844185           4
Monitors             FAMILY             4474150           3
Documentation        FAMILY             1931170           1

Rank

Rank orders the values of a dimension based on the values of the selected measure. When defining a rank calculation, you choose the dimension, a hierarchy, and the measure.

You can choose a method for handling identical values:

Rank: Assigns the same rank to identical values, so there may be fewer ranks than there are members. For example, it may return 1, 2, 3, 3, 4 for a series of five dimension members.
Dense Rank: Assigns the same minimum rank to identical values. For example, it may return 1, 2, 3, 3, 5 for a series of five dimension members.
Average Rank: Assigns the same average rank to identical values. For example, it may return 1, 2, 3.5, 3.5, 5 for a series of five dimension members.

You can also choose the group in which the dimension members are ranked:

Member’s level: Ranks members at the same level.
Member’s parent: Ranks members with the same parent.
Member’s ancestor at level: Ranks members with the same ancestor at a specified level higher in the hierarchy.

Rank Example

This template defines a calculated measure using Rank:

Rank members of the PRODUCT dimension and PRODUCT.PRIMARY hierarchy based on measure SALES. Calculate rank using RANK method with member's parent in order lowest to highest. Rank NA (null) values nulls last.

These are the results of a query against the calculated measure in which the products are ordered by RANK:

PRODUCT              SALES      RANK
-------------------- ---------- ----------
Monitors                4474150          1
Memory                  5430466          2
Modems/Fax              5844185          3
CD/DVD                 16559860          4
Portable PCs           19066575          5
Desktop PCs            78770152          6