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. - Prior and Future Period
The Data Analysis tool provides several calculations for prior or future time periods. - Period to Date
Period-to-date functions perform a calculation over time periods with the same parent up to the current period. - 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. - Moving Aggregates
Moving aggregates are performed over the time periods surrounding the current period. - Share
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. - 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.
Parent topic: The Data Analysis 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
Parent topic: Using Calculation Templates
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
Parent topic: Using Calculation Templates
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
Parent topic: Using Calculation Templates
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
Parent topic: Using Calculation Templates
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
Parent topic: Using Calculation Templates
Share
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
Parent topic: Using Calculation Templates
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,4for 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,5for 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,5for 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
Parent topic: Using Calculation Templates