| Oracle® OLAP User's Guide 11g Release 1 (11.1) Part Number B28124-01 |
|
|
View PDF |
| Oracle® OLAP User's Guide 11g Release 1 (11.1) Part Number B28124-01 |
|
|
View PDF |
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 create calculated measures using templates and free-form calculations.
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 using the templates in Analytic Workspace Manager.
As soon as you create a calculated measure, it appears as a column in a cube view. 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 by SQL applications.
The library of functions for defining calculated measures contains these basic categories:
Arithmetic Operators: Perform calculations on the values of two measures.
Analytic Functions: Perform calculations on an ordered series or a range of values in a single measure or column.
Single-Row Functions: Perform calculations on a value in a single row.
You can perform the following arithmetic operations using two measures. The calculations in the cube are performed on a cell-by-cell basis at all levels of the dimension hierarchies.
Addition: Adds the values of two measures.
Subtraction: Subtracts the values of one measure from the values of another measure.
Multiplication: Multiplies the values of two measures.
Division or Ratio: Divides the values of one measure by the values of another measure.
Percent Difference: Calculates the percent difference between the values of two measures.
The arithmetic operations are available in Analytic Workspace Manager as templates. as described in "Modifying a Calculation".
The analytic functions provide the most powerful computations and fuel the most useful queries for business intelligence and similar applications. They include a variety of rank, share, time series, and other single-column functions. The analytic functions enable analysts and decision makers to make comparisons and identify trends.
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 cumulative calculations. Because the knowledge is stored with the dimension, you do not need to specify these relationships when creating a calculated measure.
The analytic functions are available in Analytic Workspace Manager as templates. They are described in "Using Calculation Templates".
Oracle OLAP supports most of the SQL single-row functions including:
Numeric functions such as ABS, CEIL, FLOOR, POWER, ROUND, and TRUNC.
Character functions such as CONCAT, LPAD, RPAD, LTRIM, RTRIM, REPLACE, and SUBSTR.
Datetime functions such as CURRENT_DAY, MONTHS_BETWEEN, NEXT_DAY, and SYSTIMESTAMP.
Comparison functions GREATEST and LEAST.
Conversion functions such as TO_CHAR, TO_DATE, TO_NUMBER, and TO_TIMESTAMP.
You can use these functions to manipulate the data values in a measure, typically as part of a more complex calculation. These functions are not available as templates, but you can use them in free-form calculations, as described in "Creating User-Defined Expressions".
Analytic Workspace Manager provides easy-to-use templates for creating calculated measures. You can create them in the same cube with the source measures, or you can create them in a separate cube.
Calculated measures are available for querying as additional columns in a cube view (such as UNITS_CUBE_VIEW). They are not available through cube materialized views (such as CB$UNITS_CUBE).
To create a calculated measure:
In the navigation tree, expand an existing cube or create a new cube.
Right-click Calculated Measures, then choose Create Calculated Measure from the pop-up menu.
The Create Calculated Measure dialog box is displayed.
Enter a descriptive name.
Choose a calculation method.
Your choice of an arithmetic or analytic function dynamically changes the Calculation template.
Modify the calculation template.
Click Create.
The new calculated measure appears in the navigation tree in the Calculated Measures folder.
Figure 5-1 displays the Create Calculated Measure dialog box.
The calculation that you selected is presented as template, which is a description of the calculation with variable parts that enable you to customize it.
Figure 5-2 shows the template for calculating the prior period. You can view the choice lists by clicking the links.
Figure 5-2 Changing the Variable Parts of a Calculation
You can include all values of a measure in a calculation, or, for some types of calculations, you can filter the measure to include only a selection of values. To limit one or more dimensions to a single dimension member, click the ellipses (...) next to the measure. The Qualify Measure dialog box appears, as shown in Figure 5-3.
Figure 5-3 Limiting a Dimension to a Single Member
Many calculations are performed over time periods at the same level in the hierarchy. In some types of calculations, you can control the range of time periods that are used in the same calculation. For example, you might want to calculate a running total of months for each fiscal year, not a running total that begins with the first month stored in the cube.
You can use the following methods for identifying the range of time periods that you want calculated together:
Level: Calculates all time periods at the same level, so that all months in the cube are included in one calculation, all quarters are included in another calculation, and so forth.
Parent: Calculates all time periods with the same parent, so that all months in Q1-07 are included in one calculation, all months in Q2-07 are included in another calculation, and so forth.
Ancestor at level: Calculates all time periods with the same ancestor at a specified level. For example, if the specified level is Year in a Year-Quarter-Month hierarchy, then Q1-06 to Q4-06 are included in one calculation, Q1-07 to Q4-07 are included in another calculation, Jan-06 to Dec-06 are included in a third calculation, and so forth. Any levels higher in the hierarchy are not calculated.
Gregorian periods: The Gregorian periods -- Year, Quarter, Month, and Week -- impose the Gregorian calendar onto the selected hierarchy. This can be useful for analyzing data that uses non-standard calendar hierarchies. For example, if you use Gregorian Year on a fiscal hierarchy that begins July 1 and ends June 30, then the last half of one fiscal year and the first half of the next fiscal year are calculated together. Time periods higher in the hierarchy than the specified Gregorian period are not calculated.
Analytic Workspace Manager provides templates for all of calculations typically in demand for business intelligence applications. The following topics describe the types of calculations available as templates in Analytic Workspace Manager.
Basic mathematical operations enable you to perform cell-by-cell calculations on two measures, as described in "Arithmetic Operators".
Arithmetic Example
This template defines a calculated measure using Percent Difference:
Percent difference between measure PRICE_CUBE.UNIT_PRICE and measure PRICE_CUBE.UNIT_COST.
A query against this calculated measure returns results like these. The PCT_CHG column shows the percent change between PRICE and COST, which is calculated as PRICE-COST/COST.
PRODUCT PRICE COST PCT_DIFF -------------------- ---------- ---------- ---------- Envoy Ambassador 2892 2664 .09 Envoy Executive 2803 2644 .06 Envoy Standard 1662 1737 -.04 Sentinel Financial 1755 1658 .06 Sentinel Multimedia 1770 1813 -.02 Sentinel Standard 1552 1410 .1
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: 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 UNITS_CUBE_SALES. Calculate rank using RANK method with member's parent in order from lowest to highest.
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
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: 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 UNITS_CUBE.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
Oracle OLAP provides several calculations for prior or future time periods:
Prior Period: Returns the value of a measure at an earlier time period.
Future Period: Returns the value of a measure at a later time period.
Difference From Prior Period: Calculates the difference between values for the current time period and an earlier period.
Difference From Future Period: Calculates the difference between the values for the current time period and a later period.
Percent Difference From Prior Period: Calculates the percent different between the values for the current time period and an earlier period.
Percent Difference From Future Period: Calculates the percent different 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 UNITS_CUBE.SALES in TIME dimension and 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 -------- -------------------- ---------- ------------ 2005 CALENDAR_YEAR 136986572 144290686 2006 CALENDAR_YEAR 140138317 136986572 Q1.05 CALENDAR_QUARTER 31381338 41988687 Q2.05 CALENDAR_QUARTER 37642741 31381338 Q3.05 CALENDAR_QUARTER 32617249 37642741 Q4.05 CALENDAR_QUARTER 35345244 32617249 Q1.06 CALENDAR_QUARTER 36154815 35345244 Q2.06 CALENDAR_QUARTER 36815657 36154815 Q3.06 CALENDAR_QUARTER 32318935 36815657 Q4.06 CALENDAR_QUARTER 34848911 32318935
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.
Oracle OLAP 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 UNITS_CUBE.SALES in the TIME dimension and CALENDAR hierarchy 1 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-06 is APR-06, for AUG-06 is MAY-06, and for SEP-06 is JUN-06.
TIME PARENT SALES LAST_QTR -------- ---------- ---------- ---------- APR-06 CY2006.Q2 11356940 13119235 MAY-06 CY2006.Q2 13820218 11441738 JUN-06 CY2006.Q2 11638499 11593842 JUL-06 CY2006.Q3 9417316 11356940 AUG-06 CY2006.Q3 11596052 13820218 SEP-06 CY2006.Q3 11305567 11638499
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.
CALENDAR YEAR to date for UNITS_CUBE.SALES in the TIME dimension and 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.06 CALENDAR_QUARTER 36154815 36154815 Q2.06 CALENDAR_QUARTER 36815657 36154815 Q3.06 CALENDAR_QUARTER 32318935 32318935 Q4.06 CALENDAR_QUARTER 34848911 32318935 JAN-06 MONTH 13119235 13119235 FEB-06 MONTH 11441738 11441738 MAR-06 MONTH 11593842 11441738 APR-06 MONTH 11356940 11356940 MAY-06 MONTH 13820218 11356940 JUN-06 MONTH 11638499 11356940 JUL-06 MONTH 9417316 9417316 AUG-06 MONTH 11596052 9417316 SEP-06 MONTH 11305567 9417316 OCT-06 MONTH 11780401 9417316 NOV-06 MONTH 10653184 9417316 DEC-06 MONTH 12415325 9417316
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. Oracle OLAP 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. You can also select the range, as described in "Choosing a Range of Time Periods", and whether you want to start the calculation with the first period and calculate forward, or start with the last period and calculate back.
Cumulative Calculation Example
This template defines a calculated measure using Cumulative Minimum.
Cumulative minimum of UNITS_CUBE.SALES in the TIME dimension and Calendar hierarchy within ancestor at level CALENDAR_YEAR. Total from beginning member to current member.
These are the results of a query against the calculated measure, which displays values for the descendants of calendar year 2004. The minimum value for quarters begins with Q1-04 and ends with Q4-04, and for months begins with Jan-04 and ends with Dec-04.
TIME TIME_LEVEL SALES MIN_SALES -------- -------------------- ---------- ---------- Q1.04 CALENDAR_QUARTER 32977874 32977874 Q2.04 CALENDAR_QUARTER 35797921 32977874 Q3.04 CALENDAR_QUARTER 33526203 32977874 Q4.04 CALENDAR_QUARTER 41988687 32977874 JAN-04 MONTH 11477898 11477898 FEB-04 MONTH 10982016 10982016 MAR-04 MONTH 10517960 10517960 APR-04 MONTH 11032057 10517960 MAY-04 MONTH 11432616 10517960 JUN-04 MONTH 13333248 10517960 JUL-04 MONTH 12070352 10517960 AUG-04 MONTH 11108893 10517960 SEP-04 MONTH 10346958 10346958 OCT-04 MONTH 14358605 10346958 NOV-04 MONTH 12757560 10346958 DEC-04 MONTH 14872522 10346958
Moving calculations are performed over the time periods surrounding the current period. Oracle OLAP provides several aggregation method 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", and the number of time periods before and after the current period to include in the calculation.
Moving Calculation Example
This template defines a calculated measure using Moving Minimum.
Moving minimum of UNITS_CUBE.SALES in the TIME dimension and 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 2004. 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.04 CALENDAR_QUARTER 32977874 32977874 Q2.04 CALENDAR_QUARTER 35797921 32977874 Q3.04 CALENDAR_QUARTER 33526203 33526203 Q4.04 CALENDAR_QUARTER 41988687 31381338 JAN-04 MONTH 11477898 10982016 FEB-04 MONTH 10982016 10517960 MAR-04 MONTH 10517960 10517960 APR-04 MONTH 11032057 10517960 MAY-04 MONTH 11432616 11032057 JUN-04 MONTH 13333248 11432616 JUL-04 MONTH 12070352 11108893 AUG-04 MONTH 11108893 10346958 SEP-04 MONTH 10346958 10346958 OCT-04 MONTH 14358605 10346958 NOV-04 MONTH 12757560 12757560 DEC-04 MONTH 14872522 12093518
An index calculates the percentage difference between the values of a measure and a selected value that serves as a base number.
Index Example
An index calculation does not use a template. Instead, it provides a list of dimension members, from which you can choose one to use as an index. This example creates an index on the Product dimension using Desktop PCs as the index.
PRODUCT SALES PROD_INDEX -------------------- ---------- ---------- Desktop PCs 76682955 100 Portable PCs 18072328 24 CD/DVD 17302122 23 Modems/Fax 5565552 7 Memory 5347292 7 Monitors 3926632 5
You can extend the variety of functions available through the templates by using a calculated measure as the basis for another calculated measure.
For example, Analytic Workspace Manager has templates for Rank and for Prior Period. You can create a calculated measure that calculates rank, then use it to calculate the rank of the prior period.
Nested Calculations Example
This template creates a Rank calculation for Product Sales named SALES_PROD_RANK:
Rank members of the PRODUCT dimension and PRODUCT_PRIMARY hierarchy based on measure UNITS_CUBE_SALES. Calculate rank using RANK method with member's parent in order from lowest to highest.
The next template creates a Prior Period calculation from SALES_PROD_RANK.
Prior period for UNITS_CUBE.SALES_PROD_RANK in TIME dimension and CALENDAR hierarchy 1 period ago.
These are the results of a query against the calculated measure. The PRIOR_PERIOD column shows the Sales rank for the preceding month.
TIME PRODUCT SALES RANK PRIOR_PERIOD -------- ------------------ ---------- ---------- ------------ JAN-06 Monitors 308329 1 1 JAN-06 Memory 308329 2 1 JAN-06 Modems/Fax 504260 3 3 JAN-06 Portable PCs 1427967 4 5 JAN-06 CD/DVD 308329 5 4 JAN-06 Desktop PCs 7928253 6 6 FEB-06 Monitors 326697 1 1 FEB-06 Memory 398675 2 2 FEB-06 Modems/Fax 426603 3 3 FEB-06 Portable PCs 1566064 4 4 FEB-06 CD/DVD 1569463 5 5 FEB-06 Desktop PCs 6029854 6 6 MAR-06 Monitors 279203 1 1 MAR-06 Modems/Fax 403299 2 3 MAR-06 Memory 279203 3 1 MAR-06 Portable PCs 1462553 4 4 MAR-06 CD/DVD 279203 5 5 MAR-06 Desktop PCs 6064321 6 6
Among the calculation types is a user-defined expression. It enables you to create calculations using the OLAP expression syntax, which includes the analytic functions, arithmetic operators, and single-row functions described in this chapter. The OLAP syntax is an extension of the SQL syntax. If you have used SQL analytic functions or single-row functions, this syntax will be familiar to you.
The easiest way to formulate an expression is to let Analytic Workspace Manager do the work for you. You can use the templates to create a similar calculation, and cut-and-paste the syntax into an expression.
To create an expression:
Open the Create Calculated Measure dialog box.
Select the calculation type that most closely matches the one you want to define.
Modify the template as desired.
Cut-and-paste the calculation from the Calculation box into a text editor.
Repeat these steps if your calculation uses two or more functions.
Modify the calculation as desired in the text editor. You can combine numeric operators, analytic functions, and single-row functions in a single calculation.
From the Calculation Types list, select Expression.
Cut-and-paste the calculation from the text editor into the Calculation box.
Click Create.
See Also:
Analytic Workspace Manager Help for information about the OLAP expression syntaxExpression Example Using an Arithmetic Operator
This template for Multiplication generates a calculation using Units Sold and Unit Cost.
Multiply measure UNITS_CUBE_UNITS by measure PRICE_CUBE.UNIT_COST.
The template generates this calculation using the multiplication operator (*). It is displayed in the Calculation box.
GLOBAL.UNITS_CUBE.UNITS * GLOBAL.PRICE_CUBE.UNIT_COST
The syntax of this calculation is so simple that you only need the template to obtain the fully qualified name of the measure.
Following is a free-form calculation that calculates a 2% increase in units sold:
GLOBAL.UNITS_CUBE.UNITS * 1.02
These are the results of a query against this calculated measure:
PRODUCT UNITS TARGET -------------------- ---------- ---------- Envoy Ambassador 2116 2158 Envoy Executive 2481 2531 Envoy Standard 3300 3366 Sentinel Financial 30513 31123 Sentinel Multimedia 7948 8107 Sentinel Standard 7302 7448
Free-Form Calculation Example Using an Analytic Function
This template for Cumulative Average generates a calculation for the average number of units sold:
Cumulative average of UNITS_CUBE.UNITS in the TIME dimension and Calendar hierarchy within parent. Total from beginning member to following member.
The template generates this calculation using the AVG function.
AVG(GLOBAL.UNITS_CUBE.UNITS) OVER HIERARCHY (GLOBAL.TIME.CALENDAR BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING WITHIN LEVEL)
Following is a free-form calculation that computes the percent difference between current units sold and the cumulative average. It uses the AVG function and the subtraction (-), division (/) and multiplication (*) operators.
((GLOBAL.UNITS_CUBE.UNITS - AVG(GLOBAL.UNITS_CUBE.UNITS) OVER HIERARCHY (GLOBAL.TIME.CALENDAR BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING WITHIN LEVEL)) / AVG(GLOBAL.UNITS_CUBE.UNITS) OVER HIERARCHY (GLOBAL.TIME.CALENDAR BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING WITHIN LEVEL)) * 100
These are the results of a query against this calculated measure.
TIME UNITS CUM_AVG PCT_DIFF -------- ---------- ---------- ---------- Q1.06 146819 107965 36 Q2.06 145233 109062 33 Q3.06 143572 110048 30 Q4.06 149305 111138 34
Note that you could create this calculation using templates:
Calculate the cumulative average of UNITS with the Cumulative Average template.
Calculate the percent difference between current UNITS and the cumulative average with the Percent Difference template.
Analytic Functions
Table 5-1 describes the analytic functions that you can use to create free-form calculations. For the syntax of these functions, refer to Analytic Workspace Manager Help.
Table 5-1 OLAP Analytic Functions
| Function | Description |
|---|---|
|
Orders the members of a dimension based on the values of an expression. The function returns the sequence numbers of the dimension members, and assigns the same average rank to identical values. |
|
|
Returns the average of a selection of values calculated over time. |
|
|
|
Tallies the number of data values identified by a selection of dimension members. |
|
Orders dimension members based on the values of an expression. The function returns the sequence numbers of the dimension members, and assigns the same minimum rank to identical values. |
|
|
Returns an ancestor at a particular level of a hierarchy for either all members in the hierarchy or a particular member. |
|
|
Returns the number of children of either all dimension members in a hierarchy or a particular member. |
|
|
Returns a number representing the level depth of either all members of a hierarchy or a particular member, where 0 is the top level. |
|
|
Returns the level of either all members of a hierarchy or a particular member. |
|
|
Returns the parent of either all dimension members in a hierarchy or a particular member. |
|
|
Returns the topmost ancestor of either all members of a hierarchy or a particular member. |
|
|
Returns the value of an expression at a specified number of time periods before the current period. |
|
|
Returns the difference between values for the current time period and a prior period. |
|
|
Returns the percent different between values for the current time period and a prior period. |
|
|
Returns the value of an expression at a specified number of time periods after the current period. |
|
|
Returns the difference between values for the current time period and a future period. |
|
|
Returns the percent different between values for the current time period and a future period. |
|
|
Returns the largest of a selection of data values calculated over a particular dimension. |
|
|
Returns the smallest of a selection of data values calculated over a particular dimension. |
|
|
Orders the members of a dimension based on the values of an expression. The function returns the sequence numbers of the dimension members, and assigns the same rank to identical values. |
|
|
Orders the members of a dimension based on the values of an expression. The function returns the sequence numbers of the dimension members, and assigns a unique and arbitrary rank to identical values. |
|
|
Calculates the ratio of an expression's value for the current dimension member to the value for a related member of the same dimension. |
|
|
Returns the total of a selection of values calculated over a particular dimension. |
