Oracle® OLAP Application Developer's Guide, 10g Release 2 (10.2) B1434905 


PDF · Mobi · ePub 
Oracle OLAP provides an extensive set of analytic functions for enhancing your database with informationrich content. This chapter explains how you can use Analytic Workspace Manager to enhance your database by defining calculated measures and calculation plans.
This chapter contains the following topics:
Calculated measures return values that are computed at runtime from data stored in one or more measures. Like relational views, calculated measures store queries against data stored in other objects. Because calculated measures do not store data, you can create dozens of them without increasing the size of the database. You can use them as the basis for defining other calculated measures, which adds depth to the types of calculations you can create in Analytic Workspace Manager.
Because calculated measures do not contain data, they are not associated with a build process. You can create a calculated measure at any time, and it is available immediately for querying.
Oracle OLAP offers an extensive range of functions and operators that can be used to define calculated measures. Analytic Workspace Manager provides a Calculation Wizard, which provides both arithmetic and analytic calculations. The calculations in the cube are performed on a cellbycell basis at all levels of the dimension hierarchies. The analytic functions provide the most powerful computations and fuel the most useful queries for business intelligence and similar applications. By enriching your database with an extensive list of calculated measures, you enable analysts and decision makers to make comparisons, identify trends, and make solid decisions based on the best information available.
The Calculation Wizard provides these arithmetic calculations:
Basic Arithmetic: Addition, subtraction, multiplication, and division, using two measures or a measure and a number
Advanced Arithmetic: Cumulative total, index, percent markup, percent variance, rank, share, variance
The Calculation Wizard provides these analytic functions:
Prior/Future Comparison: Prior value, difference from prior period, percent difference from prior period, future value
Time Frame: Moving average, moving maximum, moving minimum, moving total, year to date
Analytic functions provided by Oracle OLAP leverage the knowledge associated with the dimensions about levels and family relationships. Time dimensions have additional information that enables them to support time series methods such as lags, leads, moving and yeartodate calculations. Because the knowledge is stored with the dimension, you do not need to specify these relationships when creating a calculated measure.
Using the Calculation Wizard, you can create calculated measures in the same cube with the source measures or in a separate cube.
To create a calculated measure:
Expand the folder for the cube that contains the base measures that will be used in the calculation.
Rightclick Calculated Measures, then choose Create Calculated Measure from the shortcut menu.
The Calculation Wizard Welcome page is displayed.
Follow the steps of the wizard.
Click Help for specific information about these choices. When you are done, the name of the new calculated measure appears as an item in the Calculated Measures folder.
Figure 61 displays the Name and Type page of the Calculation Wizard.
The Calculation Wizard supports all of the calculations typically in demand for business intelligence applications. The following topics describe the types of calculations available through the Calculation Wizard.
Basic arithmetic operations enable you to perform cellbycell calculations on two measures or a measure and a number, using addition, subtractions, multiplication, or division.
The Multiplication page defines a calculated measure using these parameters, as shown in Figure 62:
These are the results of a query against this calculated measure, which generates sales targets based on the results for the current year:
Sales Sales Budget Memory 5,272,678 5,589,038 CD/DVD 15,083,555 15,988,568 Portable PCs 19,155,814 20,305,162 Desktop PCs 67,900,544 71,974,577 Monitors 4,346,492 4,607,281 Modems/Fax 5,977,011 6,335,632
Percent Variance calculates the percent difference between two measures.
The Percent Variance page defines a calculated measure using these parameters:
These are the results of a query against this calculated measure:
Unit Price Unit Cost Pct Variance Memory 404 366 10.51 CD/DVD 191 156 22.55 Portable PCs 2,425 2,408 0.72 Desktop PCs 1,652 1,663 (0.66) Monitors 388 324 19.79 Modems/Fax 111 98 14.05
Index calculates the percent difference between the values of a measure and a selected value that serves as a base number.
The Index page defines a calculation using these parameters:
These are the results of a query against this calculated measure, which uses Desktop PCs as the index for hardware products.
Sales Sales Index Desktop PCs 67,900,544 100% Portable PCs 19,155,814 28% CD/DVD 15,083,555 22% Modems/Fax 5,977,011 9% Memory 5,272,678 8% Monitors 4,346,492 6%
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 also choose the group in which the dimension members are ranked:
Total: Ranks all members of the hierarchy.
Parent: Ranks members with the same parent.
Level: Ranks members at the same level.
The Rank page defines a calculated measure using these parameters:
These are the results of a query against this calculated measure in which the products are ranked from lowest to highest based on units sold.
Units Sold Rank Portable PCs 8,259 1 Monitors 14,520 2 Memory 15,093 3 Desktop PCs 40,729 4 Modems/Fax 48,743 5 CD/DVD 64,160 6
Share calculates the ratio of a measure's value for the current dimension member to a baseline, which is one of the following:
Total: The total of all values for members on the same level as the current member.
Parent: The total of all values for members on the same level as parent of the current member.
Level: The total of all values for members on a specified level.
Member: The value of a specified dimension member.
When creating a share calculation, you also select the measure, dimension, and hierarchy.
The Share page defines a calculated measure using these parameters:
These are the results of a query against this calculated measure. The Total Share column displays the percent share of the total for the selected products.
Sales Total Share Total Product 130,276,515 1.00 Hardware 117,736,092 0.90 Software/Other 12,540,422 0.10
Cumulative totals start with the first time period within a particular rank and calculate a running total up to the current member. The range can be all members of the level or just members with the same parent.
The Cumulative Total page defines a calculated measure using these parameters:
These are the results of a query against this calculated measure.
Sales Cumulative Sales 2003 130,276,515 698,876,935 Q103 26,946,411 26,946,411 Jan03 8,400,440 8,400,440 Feb03 8,953,827 17,354,267 Mar03 9,592,144 26,946,411 Q203 33,247,676 60,194,087 Apr03 10,457,165 10,457,165 May03 11,373,236 21,830,401 Jun03 11,417,275 33,247,676 Q303 33,636,358 93,830,445 Jul03 10,705,642 10,705,642 Aug03 10,268,927 20,974,569 Sep03 12,661,790 33,636,358 Q403 36,446,070 130,276,515 Oct03 11,705,602 11,705,602 Nov03 12,084,512 23,790,114 Dec03 12,655,955 36,446,070
Prior and future time period calculations are an important gauge for detecting and analyzing trends. Oracle OLAP provides several calculations under Prior/Future Comparison:
Prior Value: Returns the value of a measure from an earlier time period.
Difference From Prior Period: Calculates the difference between values for the current time period and an earlier time period.
Percent Difference From Prior Period: Calculates the percent difference between values for the current time period and an earlier time period.
Future Value: Returns the value of a measure from a later time period.
When creating a calculation for prior or future time periods, you choose the measure, the time dimension, the hierarchy, and the distance from the current period. The distance can be calculated as any of the following:
Year Ago
Period Ago
Number of Years, Quarters, Months, Weeks, or Days
The Prior Value page defines a calculated measure using these parameters:
These are the results of a query against this calculated measure. The Prior Period column shows the value of Sales for the preceding period at the same level in the Calendar Year hierarchy.
Sales Prior Period 2002 92,515,295 116,931,479 Q102 21,499,270 31,846,054 Q202 22,586,748 21,499,270 Q302 23,845,942 22,586,748 Q402 24,583,335 23,845,942 2003 130,276,515 92,515,295 Q103 26,946,411 24,583,335 Q203 33,247,676 26,946,411 Q303 33,636,358 33,247,676 Q403 36,446,070 33,636,358
Moving calculations are performed over the time periods surrounding the current period. They smooth the fluctuations in the data, so that you can more easily detect trends. Oracle OLAP provides several aggregation methods under Time Frame 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 choose the measure, the time dimension, and the hierarchy. You can also select the number of previous time periods to include in the calculations.
The Prior Value page defines a calculated measure using these parameters:
These are the results of a query against this calculated measure for quarterly data. Each value of Minimum Sales is the smaller of the current and the previous values.
Sales Minimum Sales Q103 26,946,411 24,583,335 Q203 33,247,676 26,946,411 Q303 33,636,358 33,247,676 Q403 36,446,070 33,636,358 Q104 32,977,875 32,977,875 Q204 35,797,920 32,977,875
Periodtodate calculations generate a running total of the data within a particular time period. Oracle OLAP provides periodtodate under Time Frame. You select the measure, the time dimension, the hierarchy, and the level.
The Period to Date page defines a calculated measure using these parameters:
These are the results of a query against this calculated measure. The Year to Date column shows a running total of sales for the months within the year.
Sales Year to Date 2003 130,276,515 130,276,515 Q103 26,946,411 26,946,411 Jan03 8,400,440 8,400,440 Feb03 8,953,827 17,354,267 Mar03 9,592,144 26,946,411 Q203 33,247,676 60,194,087 Apr03 10,457,165 37,403,576 May03 11,373,236 48,776,812 Jun03 11,417,275 60,194,087 Q303 33,636,358 93,830,445 Jul03 10,705,642 70,899,728 Aug03 10,268,927 81,168,656 Sep03 12,661,790 93,830,445 Q403 36,446,070 130,276,515 Oct03 11,705,602 105,536,047 Nov03 12,084,512 117,620,559 Dec03 12,655,955 130,276,515
You can extend the variety of functions available through the Calculation Wizard by using a calculated measure as the basis for another calculated measure.
For example, the Calculation Wizard can create rank and prior period calculations. You can create a calculated measure that calculates rank, then use it to calculate the rank of the prior period.
The Rank page creates a Rank calculation named Units Rank using these parameters:
The Prior Value page creates a Prior Year calculation from Units Rank:
These are the results of a query against the calculated measures.
Units Sold Units Rank Prior Year Q103 Portable PCs 2,051 1 1 Monitors 3,153 2 2 Memory 3,468 3 3 Desktop PCs 7,721 4 4 Modems/Fax 11,349 5 5 CD/DVD 13,225 6 6 Q104 Portable PCs 2,082 1 1 Monitors 3,685 2 2 Memory 3,846 3 3 Desktop PCs 9,429 4 4 Modems/Fax 13,106 5 5 CD/DVD 18,320 6 6
Analytic Workspace Manager provides the tools for generating advanced analytic content:
Forecast: Uses statistical timeseries forecasting methods to predict future performance based on past results
See Also:
Chapter 7, "Generating Forecasts"Allocation: Distributes data down the dimension hierarchies using a selected allocation method
See Also:
Chapter 9, "Allocations"Aggregation: Consolidates data up the dimension hierarchies using a selected aggregation method
See Also:
Chapter 8, "Advanced Aggregations"You create these types of calculations by developing a calculation plan. Calculation plans are composed of an ordered list of steps that generate additional analytical data. Each step performs a specific type of calculation. Unlike calculated measures, these steps generate data that is stored in the cube. By specifying the order in which these steps are performed, you can allow for interdependencies.
You execute calculation plans using the Maintenance Wizard, typically after loading and aggregating new data.
Expand the folder for the analytic workspace.
Rightclick Calculation Plans, then choose Create Calculation Plan from the shortcut menu.
The Create Calculation Plan dialog box is displayed.
Complete the General tab.
Click Help for specific information about these choices.
To create a new step, click New Step.
Choose the type of step: Forecast, allocation, or aggregation.
The New Step dialog box is displayed for that type of calculation.
Complete all tabs, then click Create.
The new step is listed on the Calculation Plan General tab.
Click Create.
The new calculation plan appears as an item in the Calculation Plans folder.
To run the calculation plan:
Rightclick it on the navigation tree and choose Execute Calculation Plan.
The Maintenance wizard opens.
Follow the steps of the wizard.
Figure 63 shows the Create Calculation Plan dialog box with three steps defined.