| Oracle® OLAP Application Developer's Guide, 10g Release 2 (10.2) Part Number B14349-05 |
|
|
PDF · Mobi · ePub |
| Oracle® OLAP Application Developer's Guide, 10g Release 2 (10.2) Part Number B14349-05 |
|
|
PDF · Mobi · ePub |
Oracle OLAP provides an extensive set of analytic functions for enhancing your database with information-rich 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 run-time 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 cell-by-cell 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 year-to-date 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.
Right-click 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 6-1 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 cell-by-cell 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 6-2:
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
Q1-03 26,946,411 26,946,411
Jan-03 8,400,440 8,400,440
Feb-03 8,953,827 17,354,267
Mar-03 9,592,144 26,946,411
Q2-03 33,247,676 60,194,087
Apr-03 10,457,165 10,457,165
May-03 11,373,236 21,830,401
Jun-03 11,417,275 33,247,676
Q3-03 33,636,358 93,830,445
Jul-03 10,705,642 10,705,642
Aug-03 10,268,927 20,974,569
Sep-03 12,661,790 33,636,358
Q4-03 36,446,070 130,276,515
Oct-03 11,705,602 11,705,602
Nov-03 12,084,512 23,790,114
Dec-03 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 Q1-02 21,499,270 31,846,054 Q2-02 22,586,748 21,499,270 Q3-02 23,845,942 22,586,748 Q4-02 24,583,335 23,845,942 2003 130,276,515 92,515,295 Q1-03 26,946,411 24,583,335 Q2-03 33,247,676 26,946,411 Q3-03 33,636,358 33,247,676 Q4-03 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 Q1-03 26,946,411 24,583,335 Q2-03 33,247,676 26,946,411 Q3-03 33,636,358 33,247,676 Q4-03 36,446,070 33,636,358 Q1-04 32,977,875 32,977,875 Q2-04 35,797,920 32,977,875
Period-to-date calculations generate a running total of the data within a particular time period. Oracle OLAP provides period-to-date 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
Q1-03 26,946,411 26,946,411
Jan-03 8,400,440 8,400,440
Feb-03 8,953,827 17,354,267
Mar-03 9,592,144 26,946,411
Q2-03 33,247,676 60,194,087
Apr-03 10,457,165 37,403,576
May-03 11,373,236 48,776,812
Jun-03 11,417,275 60,194,087
Q3-03 33,636,358 93,830,445
Jul-03 10,705,642 70,899,728
Aug-03 10,268,927 81,168,656
Sep-03 12,661,790 93,830,445
Q4-03 36,446,070 130,276,515
Oct-03 11,705,602 105,536,047
Nov-03 12,084,512 117,620,559
Dec-03 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
Q1-03 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
Q1-04 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 time-series 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.
Right-click 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:
Right-click it on the navigation tree and choose Execute Calculation Plan.
The Maintenance wizard opens.
Follow the steps of the wizard.
Figure 6-3 shows the Create Calculation Plan dialog box with three steps defined.
|
 Copyright © 2003, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
