| Oracle® OLAP DML Reference 11g Release 2 (11.2) Part Number E12197-02 |
|
|
View PDF |
| Oracle® OLAP DML Reference 11g Release 2 (11.2) Part Number E12197-02 |
|
|
View PDF |
This chapter contains the following topics:
One topic for each of the OLAP DML system properties, arranged alphabetically beginning with $AGGMAP.
For other reference topics for the OLAP DML, see Chapter 6, "OLAP DML Options", Chapter 7, "OLAP DML Functions: A - K", Chapter 7, "OLAP DML Functions: A - K", Chapter 9, "OLAP DML Commands: A-G", and Chapter 10, "OLAP DML Commands: H-Z".
A property is a named value that is associated with a definition of an analytic workspace object. You can name, create, and assign properties to an object using an OLAP DML PROPERTY command.
Properties that begin with a $ (dollar sign) are recognized by Oracle OLAP as system properties. You cannot create system properties; however, in some cases you can assign system properties to objects. In particular, you can assign system properties that interact with the OLAP DML.
The OLAP DML provides system properties that set or retrieve values that influence how the OLAP DML performs the following:
Aggregation Properties
Allocation Property
Grouping Id Properties
Formula Properties
Language Property
NA Value Properties
The $AGGMAP property specifies that Oracle OLAP use the identified aggmap to automatically aggregate non-precomputed data to substitute for NA values that are in the dimensioned variable, but not in the session cache for the variable (if any). Consequently, you do not need to explicitly use the AGGREGATE function to aggregate non-precomputed data in a variable that has an $AGGMAP property.
Additionally, the aggmap specified in a variable's $AGGMAP property, is the aggmap that Oracle OLAP uses when the variable is the target of an AGGREGATE command that does not include a USING phrase.
Syntax
You add or delete an $AGGMAP property to the most recently defined or considered object (see DEFINE and CONSIDER commands) using a PROPERTY statement:
To add the property, issue the following statement.
PROPERTY '$AGGMAP' agggmap-name
To delete the property, issue the following statement.
PROPERTY DELETE '$AGGMAP'
Arguments
A TEXT expression that is the name of a previously defined aggmap object.
Examples
Example 5-1 Using $AGGMAP To Dynamically Aggregate Data
Assume that you have a hierarchical dimension named geog,a simple dimension named year, and the following variable named sales which is dimensioned by both and which has data only at the detail level.
Assume that you want to explicitly specify the value of 8000 for the sales cell for Connecticut in 2005. To do this you issue the following assignment statement and a report of sales shows the value.
sales (geog 'Connecticut' year '2005') = 8000
REPORT sales;
-----------SALES-----------
-----------YEAR------------
GEOG 2004 2005 2006 2007
-------------- ------ ------ ------ ------
Toronto 1,000 1,333 1,954 1,260
Norfolk 1,131 1,867 1,843 1,767
Montreal 1,571 1,754 1,316 1,905
Quebec City 1,914 1,728 1,386 1,847
Hartford 1,870 1,943 1,085 1,335
New Haven 1,684 1,330 1,458 1,402
Springfield 1,630 1,116 1,897 1,690
Boston 1,780 1,310 1,368 1,581
Ontario NA NA NA NA
Quebec NA NA NA NA
Connecticut NA 8,000 NA NA
Massachusetts NA NA NA NA
Canada NA NA NA NA
USA NA NA NA NA
All Geog NA NA NA NA
Now assume that you define an aggmap for sales. The aggmap has the following definition which specifies that only the upper-level data for Canada and the top level (All Geog) be aggregated by the AGGREGATE command.
DEFINE MYAGGMAP AGGMAP
AGGMAP
RELATION geogParentrel PRECOMPUTE ('Quebec' 'Ontario' 'Canada' 'All Geog')
END
Now assume you issue the following statements:
CONSIDER sales PROPERTY '$AGGMAP' 'Myaggmap'
As a result of using the $AGGMAP property to make myaggmap as the default aggmap for sales, a simple REPORT statement for sales causes Oracle OLAP to aggregate all of the data for the USA. (Note that only those values that were not specified as PRECOMPUTE and that previously had NA values are calculated. The 8,000 value for Connecticut in 2005 that was specifically assigned is not recalculated.)
REPORT sales
-----------SALES-----------
-----------YEAR------------
GEOG 2004 2005 2006 2007
-------------- ------ ------ ------ ------
Toronto 1,000 1,333 1,954 1,260
Norfolk 1,131 1,867 1,843 1,767
Montreal 1,571 1,754 1,316 1,905
Quebec City 1,914 1,728 1,386 1,847
Hartford 1,870 1,943 1,085 1,335
New Haven 1,684 1,330 1,458 1,402
Springfield 1,630 1,116 1,897 1,690
Boston 1,780 1,310 1,368 1,581
Ontario NA NA NA NA
Quebec NA NA NA NA
Connecticut 3,554 8,000 2,543 2,737
Massachusetts 3,410 2,426 3,265 3,271
Canada NA NA NA NA
USA 6,964 5,699 5,808 6,008
All Geog NA NA NA NA
Once you aggregate sales using the AGGREGATE command, Oracle OLAP aggregates values for all of the PRECOMPUTE cells in sales.
REPORT sales
-----------SALES-----------
-----------YEAR------------
GEOG 2004 2005 2006 2007
-------------- ------ ------ ------ ------
Toronto 1,000 1,333 1,954 1,260
Norfolk 1,131 1,867 1,843 1,767
Montreal 1,571 1,754 1,316 1,905
Quebec City 1,914 1,728 1,386 1,847
Hartford 1,870 1,943 1,085 1,335
New Haven 1,684 1,330 1,458 1,402
Springfield 1,630 1,116 1,897 1,690
Boston 1,780 1,310 1,368 1,581
Ontario 2,131 3,200 3,797 3,027
Quebec 3,485 3,482 2,702 3,752
Connecticut 3,554 8,000 2,543 2,737
Massachusetts 3,410 2,426 3,265 3,271
Canada 5,616 6,682 6,499 6,779
USA 6,964 5,699 5,808 6,008
All Geog 12,580 12,381 12,307 12,787
Example 5-2 The $AGGMAP Property Effect on an AGGREGATE Command
Example 5-3, "Using the $AGGREGATE_FROM Property" illustrates how the AGGREGATE command shown in Example 9-12, "Using a CACHE Statement in an Aggregation Specification" can be simplified to the following statement.
AGGREGATE sales_by_revenue USING revenue_aggmap
You can further simplify the AGGREGATE command if you place an $AGGMAP property on the sales_by_revenue variable. To define an $AGGMAP property on the sales_by_revenue variable, issue the following statements.
CONSIDER sales_by_revenue PROPERTY '$AGGMAP' 'revenue_aggmap'
Now you can aggregate the data by issuing the following AGGREGATE command that does not include a USING clause.
AGGREGATE sales_by_revenue
The $AGGREGATE_FORCECALC property specifies the same behavior as that specified by the FORCECALC keyword in an AGGREGATE function. By adding an $AGGREGATE_FORCECALC property to a variable you can ensure this behavior when the variable is aggregated using an AGGREGATE function — even when it is aggregated by an AGGREGATE function does not include the FORCECALC keyword.
The behavior specified by both the $AGGREGATE_FORCECALC property and the FORCECALC keyword is that when an AGGREGATE function aggregates the variable, Oracle OLAP recalculates any value that is not specified in a PRECOMPUTE clause of a RELATION (for aggregation) statement in the aggmap of a variable — even when there is a value stored in the desired cell. Recalculating values that are not specified in a PRECOMPUTE clause is the desired behavior when you want users to be able to change detail data cells and see the changed values reflected in dynamically-computed aggregate cells.
Syntax
You add or delete an $AGGREGATE_FORCECALC property to the most recently defined or considered object (see DEFINE and CONSIDER commands) by issuing a PROPERTY statement:
To add the property, issue the following statement.
PROPERTY '$AGGREGATE_FORCECALC'
To delete the property, issue the following statement.
PROPERTY DELETE '$AGGREGATE_FORCECALC'
The $AGGREGATE_FORCEORDER property specifies the same behavior as that specified by the FORCEORDER keyword in an AGGREGATE command or an AGGREGATE function. By adding an $AGGREGATE_FORCEORDER property to a variable you can ensure this behavior when the variable is aggregated — even when it is aggregated by an AGGREGATE statement does not include the FORCEORDER keyword.
The behavior specified by both the $AGGREGATE_ORDER property and the FORCEORDER keyword is that the calculations must be performed in the order in which the RELATION (for aggregation) statements are listed in the aggmap used for the aggregation. Typically, you want this behavior when some values calculated through aggregation have changed because, otherwise, the optimization methods used by AGGREGATE may cause the modified values to be ignored. (Note, however, that forcing the order of execution can slow performance.)
Syntax
You add or delete an $AGGREGATE_FORCEORDER property to the most recently defined or considered object (see DEFINE and CONSIDER commands) by issuing a PROPERTY statement:
To add the property, issue the following statement.
PROPERTY '$AGGREGATE_FORCEORDER'
To delete the property, issue the following statement.
PROPERTY DELETE '$AGGREGATE_FORCEORDER'
The $AGGREGATE_FROM property specifies the same behavior as that specified by a FROM clause in an AGGREGATE command or an AGGREGATE function. By adding an $AGGREGATE_FROM property to a variable you can ensure this behavior when the variable is aggregated — even when it is aggregated by an AGGREGATE statement does not include the FROM clause.
Both the $AGGREGATE_FROM property and the FROM clause specify an object from which Oracle OLAP obtains the detail data for the aggregation.
Syntax
You add or delete an $AGGREGATE_FROM property to the most recently defined or considered object (see DEFINE and CONSIDER commands) by issuing a PROPERTY statement:
To add the property, issue the following statement.
PROPERTY '$AGGREGATE_FROM' fromspec
To delete the property, issue the following statement.
PROPERTY DELETE '$AGGREGATE_FROM'
Arguments
An arbitrarily dimensioned variable, formula, or relation from which the detail data for the aggregation is obtained.
Examples
Example 5-3 Using the $AGGREGATE_FROM Property
Example 9-14, "Aggregating into a Different Variable" uses the following AGGREGATE command to populate the total_sales_exclud_north variable with aggregate values computed from the sales variable.
AGGREGATE total_sales_exclud_north USING agg_sales_exclud_north FROM sales
You can place a $AGGREGATE_FROM property on the total_sales_exclud_north variable by issuing the following statements.
CONSIDER total_sales_exclud_north PROPERTY '$AGGREGATE_FROM' 'sales'
Now you can aggregate the data by issuing the following AGGREGATE command that does not include a FROM clause.
AGGREGATE total_sales_exclud_north USING agg_sales_exclud_north
The $AGGREGATE_FROMVAR property specifies the same behavior as that specified by a FROMVAR clause in an AGGREGATE command or an AGGREGATE function. By adding an $AGGREGATE_FROMVAR property to a variable you can ensure this behavior when the variable is aggregated — even when it is aggregated by an AGGREGATE statement does not include the FROMVAR clause.
Both the $AGGREGATE_FROMVAR property and the FROMVAR clause specify two or more objects from which Oracle OLAP obtains the detail data for the aggregation.
Syntax
You add or delete an $AGGREGATE_FROMVAR property to the most recently defined or considered object (see DEFINE and CONSIDER commands) by issuing a PROPERTY statement:
To add the property, issue the following statement.
PROPERTY '$AGGREGATE_FROMVAR' textvar ACROSS dimname
To delete the property, issue the following statement.
PROPERTY DELETE '$AGGREGATE_FROMVAR'
Arguments
A TEXT expression that specifies an arbitrarily dimensioned variable or formula that specifies the names of the objects from which to obtain detail data when performing a capstone aggregation. Specify NA to indicate that a node does not need detail data to calculate the value.
Specifies the dimension or a named composite that the aggregation loops over to discover the cells in the objects specified by textvar. Because the objects specified by textvar can be formulas, you can realize a significant performance advantage by supplying a looping dimension that eliminates the sparsity.
Examples
Example 5-4 Capstone Aggregation Using the $AGGREGATE_FROMVAR Property
Example 9-31, "Capstone Aggregation" uses the following AGGREGATE command to perform the final capstone aggregation.
AGGREGATE sales_capstone76 USING capstone_aggmap FROMVAR capstone_source
As the following statements illustrate, you can omit the FROMVAR clause if you create the appropriate FROMVAR property on sales-capstone76.
CONSIDER sales_capstone76 PROPERTY '$AGGREGATE_FROMVAR' 'capstone_source' AGGREGATE sales_capstone76 USING capstone_aggmap
The $ALLOCMAP property specifies the default aggmap for allocation for a variable which is the aggmap that Oracle OLAP uses when the variable is the target variable of an ALLOCATE statement that does not include a USING phrase.
Syntax
You add or delete an $ALLOCMAP property to the most recently defined or considered object (see DEFINE and CONSIDER commands) using a PROPERTY statement:
To add the property, issue the following statement.
PROPERTY '$ALLOCMAP' aggmap-name
To delete the property, issue the following statement.
PROPERTY DELETE '$AALLOCMAP'
Arguments
A TEXT expression that specifies the name of a previously defined ALLOCMAP type aggmap object.
Examples
Example 5-5 Using $ALLOCMAP to Specify a Default Allocation Specification
Example 9-34, "Recursive Even Allocation with a Lock" uses the following statement to allocated data in the projbudget variable using the projbudgmap allocation specification.
ALLOCATE projbudget USING projbudgmap
You can specify that projbudgmap is the default allocation specification for the projbudget variable by issuing the following statements.
CONSIDER projbudget PROPERTY '$ALLOCMAP' "projbugmap'
Now, by issuing the following statement, you can allocate data in the projbudget variable using the projbudgmap allocation specification.
ALLOCATE projbudget
The $COUNTVAR property specifies the same behavior as that specified by a COUNTVAR clause in an AGGREGATE command or an AGGREGATE function. By adding an $COUNTVAR property to a variable you can ensure this behavior when the variable is aggregated — even when it is aggregated by an AGGREGATE statement does not include the COUNTVAR clause.
The behavior specified by both the $COUNTVAR property and the COUNTVAR clause is that Oracle OLAP uses a variable that you have previously-defined (sometimes called a Countvar variable) to store the non-NA counts of the number of leaf nodes that contributed to aggregate values calculated for RELATION (for aggregation) statements that have an AVERAGE, HAVERAGE, HWAVERAGE, or WAVERAGE operator.
Note:
Typically, you do not use a user-defined Countvar variable to store the counts for average aggregations; instead, you use an Oracle OLAP-created Aggcount variable. You cannot use a Countvar variable when the aggregation specification includes a RELATION (for aggregation) statement with an average operator is for a compressed composite. See "Aggcount Variables" for more information.Syntax
You add or delete a $COUNTVAR property to the most recently defined or considered object (see DEFINE and CONSIDER commands) using a PROPERTY statement:
To add the property, issue the following statement.
PROPERTY '$COUNTVAR' countvar
To delete the property, issue the following statement.
PROPERTY DELETE '$COUNTVAR'
Arguments
A TEXT expression that specifies the name of a previously defined Countvar variable. The Countvar variable must be an INTEGER variable with the same dimensions in the same order as the dimensions as the variable on which you add the $COUNTVAR property.
Examples
Example 5-6 Using $COUNTVAR
For a variable named v1, the following statements cause Oracle OLAP to count the number of leaf nodes that contributed to an aggregate value that is the result of the execution of the myaggmap aggmap object by a AGGREGATE function.
CONSIDER v1 PROPERTY '$COUNTVAR' 'mycountvar'
The $DEFAULT_LANGUAGE property identifies a dimension as the language dimension for the analytic workspace in which it is defined and specifies the default language for that language dimension.
Note:
There can be only one language dimension in an analytic workspace and only that dimension can have a $DEFAULT_LANGUAGE property.Syntax
Before you add or delete a $DEFAULT_LANGUAGE property to your language dimension, you must make that dimension the most recently defined or considered object (see DEFINE and CONSIDER commands). You add $DEFAULT_LANGUAGE property using a PROPERTY statement:
To add the property, issue the following statement.
PROPERTY '$DEFAULT_LANGUAGE' language
To delete the property, issue the following statement.
PROPERTY DELETE '$DEFAULT_LANGUAGE'
Arguments
A TEXT expression that is a value in your language dimension, or an empty string.
Notes
Working with Language Dimensions
A language dimension is a dimension that has a $DEFAULT_LANGUAGE property defined for it. There can only be one language dimension in an analytic workspace. Working with language dimensions involves:
Creating a Language Dimension To create a language dimension, take the following steps:
Define a TEXT dimension using DEFINE DIMENSION.
Populate the language dimension with the names of the languages you want to support. As language names, use valid values for NLS_LANGUAGE.
Add the $DEFAULT_LANGUAGE property to the dimension thereby identifying the dimension to Oracle OLAP as the language dimension in the analytic workspace.
Defining Multi-language Variables that are Dimensioned by the Language Dimension To create multi-language variables, you include the language dimension as a dimension of the variable as illustrated in Example 5-8, "Attaching a Language Dimension".
Working with Language Dimension Status When an analytic workspace with a language dimension is attached, Oracle OLAP initializes the status of the language dimension, as follows:
Oracle OLAP limits the language dimension to the value of the SESSION_NLS_LANGUAGE option when the language dimension contains that value.
If the language dimension does not contain value to which the SESSION_NLS_LANGUAGE option is set, then Oracle OLAP limits the language dimension to the language specified in the dimension's $DEFAULT_LANGUAGE property when the $DEFAULT_LANGUAGE property contains a value and when that value is a value of the language dimension.
If the language dimension does not contain value to which the SESSION_NLS_LANGUAGE option is set and if the language dimension's $DEFAULT_LANGUAGE property is empty or names a nonexistent value, Oracle OLAP limits the language dimension to the value of the language dimension to the first value in the dimension's default order.
By default, after initialization, the status of a language dimension cannot be changed. However, you can change this behavior by changing the value of the LOCK_LANGUAGE_DIMS option from TRUE to FALSE which changes the status of the language dimension to ALL and enables issuing LIMIT statements against the dimension.
Exporting Language Dimensions
When exporting an analytic workspace using EXPORT (EIF), Oracle OLAP takes the following steps to determine what values of the language dimension to export:
If the value of the LOCK_LANGUAGE_DIMS option is FALSE when an EXPORT statement executes, Oracle OLAP honors the current status of the language dimension and performs the export accordingly.
If the value of the LOCK_LANGUAGE_DIMS option is TRUE when an EXPORT statement executes, Oracle OLAP:
Changes the value of the LOCK_LANGUAGE_DIMS option to FALSE (thereby setting the status to ALL) before executing the EXPORT statement.
Executes the EXPORT statement. Oracle OLAP exports all of the values of the language dimension.
Changes the value of the LOCK_LANGUAGE_DIMS option to TRUE and resets the status of the language dimension according to the value of the SESSION_NLS_LANGUAGE option.
Examples
Example 5-7 Creating a Language Dimension
This example illustrates creating a language dimension named mylangs that supports the use of both French and American and that specifies that the default language is American.
NLS_LANGUAGE = 'AMERICAN' DEFINE mylangs DIMENSION TEXT MAINTAIN mylangs ADD 'FRENCH' 'AMERICAN' CONSIDER mylangs PROPERTY '$DEFAULT_LANGUAGE' 'AMERICAN' SHOW OBJ(PROPERTY '$DEFAULT_LANGUAGE' 'mylangs') AMERICAN REPORT mylangs MYLANGS -------------- FRENCH AMERICAN
Example 5-8 Attaching a Language Dimension
Assume that in an analytic workspace named myaw that you have created a language dimension named mylangs as described in Example 5-7, "Creating a Language Dimension". Assume also that you have created a products dimension and a prod-desc variable with the following definitions and values.
DEFINE MYLANGS DIMENSION TEXT
SHOW OBJ(PROPERTY '$DEFAULT_LANGUAGE' 'mylangs')
AMERICAN
DEFINE PRODUCTS DIMENSION TEXT
DEFINE PROD_DESC VARIABLE TEXT <PRODUCTS MYLANGS>
MYLANGS
--------------
FRENCH
AMERICAN
PRODUCTS
--------------
PROD01
PROD02
------PROD_DESC------
------PRODUCTS-------
MYLANGS PROD01 PROD02
-------------- ---------- ----------
FRENCH Pantalons JupesAMERICAN Trousers Skirts
Assume that you attach the analytic workspace. By displaying the options for the analytic workspace and requesting a A report of mylangs and prod_desc, shows that Oracle OLAP has limited the mylangs dimension to American which is the value of the SESSION_NLS_LANGUAGE option.
SHOW NLS_LANGUAGE
AMERICAN
AW ATTACH myaw RW
" Get the default language in our language dimension
SHOW OBJ(PROPERTY '$DEFAULT_LANGUAGE' 'mylangs')
AMERICAN
SHOW SESSION_NLS_LANGUAGE
AMERICAN
SHOW LOCK_LANGUAGE_DIMS
yes
SHOW STATIC_SESSION_LANGUAGE
no
REPORT mylangs
MYLANGS
--------------
AMERICAN
REPORT prod_desc
------PROD_DESC------
------PRODUCTS-------
MYLANGS PROD01 PROD02
-------------- ---------- ----------
AMERICAN Trousers Skirts
Example 5-9 Changing NLS_LANGUAGE
Assume that you have attached the analytic workspace myaw as described in Example 5-8, "Attaching a Language Dimension". Now you change the value of NLS_LANGUAGE to French. Because the value of STATIC_SESSION_LANGUAGE is set to NO, making this change effectively changes the value of the SESSION_NLS_LANGUAGE option to French. When the value of SESSION_NLS_LANGUAGE option is French, as a report of mylangs and prod_desc illustrates, Oracle OLAP limits the mylangs dimension to French.
SET NLS_LANGUAGE= 'FRENCH'
SHOW OBJ(PROPERTY '$DEFAULT_LANGUAGE' 'mylangs')
AMERICAN
SHOW NLS_LANGUAGE
FRENCH
SHOW SESSION_NLS_LANGUAGE
FRENCH
SHOW LOCK_LANGUAGE_DIMS
oui
SHOW STATIC_SESSION_LANGUAGE
non
REPORT mylangs
MYLANGS
--------------
FRENCH
REPORT prod_desc
------PROD_DESC------
------PRODUCTS-------
MYLANGS PROD01 PROD02
-------------- ---------- ----------
FRENCH Pantalons Jupes
Example 5-10 Setting NLS_LANGUAGE to a Value that is Not in a Language Dimension
Assume that in the analytic workspace named myaw (described in Example 5-9, "Changing NLS_LANGUAGE" ) the value of NLS_LANGUAGE is set first to American and then set to Spanish. As illustrated in the following code, since the language dimension, mylangs, does not include Spanish as one of its values, Oracle OLAP limits the mylangs dimension using the value of the $DEFAULT_LANGUAGE property which is American.
"Change the value of NLS_LANGUAGE to AMERICAN
SET NLS_LANGUAGE= 'AMERICAN'
"Change the value of NLS_LANGUAGE to SPANISH
SET NLS_LANGUAGE= 'SPANISH'
SHOW OBJ(PROPERTY '$DEFAULT_LANGUAGE' 'mylangs')
AMERICAN
SHOW NLS_LANGUAGE
SPANISH
SHOW SESSION_NLS_LANGUAGE
SPANISH
SHOW LOCK_LANGUAGE_DIMS
sí
SHOW STATIC_SESSION_LANGUAGE
no
REPORT mylangs
MYLANGS
--------------
AMERICAN
REPORT prod_desc
------PROD_DESC------
------PRODUCTS-------
MYLANGS PROD01 PROD02
-------------- ---------- ----------
AMERICAN Trousers Skirts
Assume that you had defined the mylangs language dimension without specifying a default language using the following code.
DEFINE mylangs DIMENSION TEXT MAINTAIN mylangs ADD 'FRENCH' 'AMERICAN' CONSIDER mylangs PROPERTY '$DEFAULT_LANGUAGE' ''
In this case, when you set the value of NLS_LANGUAGE to Spanish, since the language dimension, mylangs does not have a value specified for its $DEFAULT_LANGUAGE property, Oracle OLAP limits the mylangs dimension using the first value in the mylangs dimension which is French.
NLS_LANGUAGE = 'SPANISH'
SHOW OBJ(PROPERTY '$DEFAULT_LANGUAGE' 'mylangs')
SHOW NLS_LANGUAGE
SPANISH
SHOW SESSION_NLS_LANGUAGE
SPANISH
SHOW LOCK_LANGUAGE_DIMS
sí
SHOW STATIC_SESSION_LANGUAGE
no
REPORT mylangs
MYLANGS
--------------
FRENCH
REPORT prod_desc
------PROD_DESC------
------PRODUCTS-------
MYLANGS PROD01 PROD02
-------------- ---------- ----------
FRENCH Pantalons Jupes
The $GID_DEPTH property, which is automatically created and set when a GROUPINGID command populates a grouping id relation, specifies the number of levels of grouping ids in the grouping id relation to which it is added.
Syntax
You cannot explicitly define a $GID_DEPTH property. Oracle OLAP automatically creates a $GID_DEPTH property on a grouping id relation when the execution of a GROUPIONGID command creates the relation.
$GID_DEPTH = intlevels
Arguments
An INTEGER value that specifies the number of levels of grouping ids.
Examples
See Example 9-144, "Using GROUPINGID Command to Populate a Relation with Grouping Ids".
The $GID_LIST property contains the names of the levels used to create the grouping ids in a relation created when the GROUPINGID command with either the ROLLUP or GROUPSET keyword executes.
Syntax
You cannot explicitly define a $GID_LIST property. Oracle OLAP automatically creates a $GID_LIST property on a grouping id relation when the execution of a GROUPIONGID command with either the ROLLUP or GROUPSET keyword creates the relation.
$GID_LIST = levels
Arguments
A TEXT expression which is the levels, separated by hyphens (-), of the hierarchies of the dimension for which grouping ids were created.
The $GID_TYPE property, which is automatically created and set when a GROUPINGID command with either the ROLLUP or GROUPSET keyword populates a grouping id relation, specifies whether the grouping type of the grouping ids.
Syntax
You cannot explicitly define a $GID_TYPE property. Oracle OLAP automatically creates a $GID_TYPE property on a grouping id relation when the execution of a GROUPIONGID command with either the ROLLUP or GROUPSET keyword creates the relation.
$GID_TYPE = ROLLUP | GROUPSET
Arguments
Specifies that the grouping ids are of the rollup type. For more information on this type of grouping type, see the discussion of ROLLUP in the rollup cube clause of a SQL SELECT statement in Oracle Database SQL Language Reference.
Specifies that the grouping ids are of the grouping set type. For more information on this type of grouping type, see the discussion of grouping sets in the grouping sets clause of a SQL SELECT statement in Oracle Database SQL Language Reference.
The $LOOP_AGGMAP property is used to determine how to loop the formula on which it is assigned when a SQL OLAP_TABLE function with the LOOP OPTIMIZED clause is executed. It specifies the name of an aggmap object to use when Oracle OLAP generates a UNION subclause that includes the formula. The value that you specify for this property overrides all other aggmaps associated with a variable (for example, aggmaps for which the variable has an $AGGMAP property) and can be used to clarify which aggmap Oracle OLAP should use when the underlying variables of a formula are associated with different aggmaps. For more information on looping in OLAP_TABLE, see the discussion of the LOOP Clause.
Syntax
You add or delete a $LOOP_AGGMAP property to the most recently defined or considered formula (see DEFINE and CONSIDER commands) using a PROPERTY statement:
To add the property, issue the following statement.
PROPERTY '$LOOP_AGGMAP' agggmap-name
To delete the property, issue the following statement.
PROPERTY DELETE '$LOOP_AGGMAP'
Arguments
The name of an aggmap object.
The $LOOP_DENSE property is used to determine how to loop the formula on which it is assigned when a OLAP_TABLE SQL function with the LOOP OPTIMIZED clause is executed. It specifies that Oracle OLAP loops densely over the formula (that is, that it loop over every tuple of the formula—even those member cells that do not have values). For more information, see the discussion of the LOOP Clause.
Syntax
You add or delete a $LOOP_DENSE property to the most recently defined or considered formula (see DEFINE and CONSIDER commands) using a PROPERTY statement:
To add the property, issue the following statement.
PROPERTY '$LOOP_DENSE' dimension_list
To delete the property, issue the following statement.
PROPERTY DELETE '$LOOP_DENSE'
Arguments
One or more names of the dimensions of the formula separated by commas.
The $LOOP_VAR property specifies that when a OLAP_TABLE SQL function with the LOOP OPTIMIZED clause is executed, the formula on which it is assigned is looped in the same manner as the variable or QDR specified in the property. For more information on looping in OLAP_TABLE, see the discussion of the LOOP Clause.
Syntax
You add or delete a $LOOP_VAR property to the most recently defined or considered formula (see DEFINE and CONSIDER commands) using a PROPERTY statement:
To add the property, issue the following statement.
PROPERTY '$LOOP_VAR' qdr | variable
To delete the property, issue the following statement.
PROPERTY DELETE '$LOOP_VAR'
Arguments
A QDR for a dimension of the formula.
A variable with the same dimensions as the formula.
The $NATRIGGER property specifies values for Oracle OLAP to substitute for NA values that are in a dimensioned variable, but not in the session cache for the variable (if any). To calculate the values, Oracle OLAP takes the steps described in "How Oracle OLAP Calculates Data for a Variable with NA Values". The results of the calculation are either stored in the variable or cached in the session cache for the variable as described in "How Oracle OLAP Determines Whether to Store or Cache Results of $NATRIGGER".
Note:
When you want to trigger the aggregation of a variable, you can use the $AGGMAP property rather than the $NATRIGGER property.Syntax
You add or delete a $NATRIGGER property to the most recently defined or considered object (see DEFINE and CONSIDER commands) using a PROPERTY statement:
To add the property, issue the following statement.
PROPERTY '$NATRIGGER' value
To delete the property, issue the following statement.
PROPERTY DELETE '$NATRIGGER'
Arguments
A TEXT expression that is the value of the property. The text can be any expression that is valid for defining a formula
Notes
How Oracle OLAP Calculates Data for a Variable with NA Values
When calculating the data for a dimensioned variable, Oracle OLAP takes the following steps for each cell in the variable:
Is there is a session cache for the variable.
Yes. Go to step 2.
No. Go to step 3.
Does that cell in the session cache for the variable have an NA value.
Yes. Go to step 3.
No. Go to step 7.
Does that cell in variable storage have an NA value.
Yes. Go to step 4.
No. Go to step 7.
Does the variable have an $AGGMAP property?
Yes. Aggregate the variable using the aggmap specified for the $AGGMAP property and, then, go to step 5.
No. Go to step 6.
What is the value of the cell after aggregating the variable?
NA, go to step 6.
Non-NA, go to step 7.
Does the variable have a $NATRIGGER property?
Yes. Execute the expression specified for the $NATRIGGER property and, then, go to step 7.
No. Go to step 7.
Calculate the data.
Apply the NAFILL function or the NASKIP, NASKIP2, or NASPELL options, as appropriate.
Making NA Triggers Recursive or Mutually Recursive
You can make NA triggers recursive or mutually recursive by including triggered objects within the value expression. You must set the RECURSIVE option to YES before a formula, program, or other $NATRIGGER expression can invoke a trigger expression again while it is executing. For limiting the number of triggers that can execute simultaneously, see the TRIGGERMAXDEPTH option.
Using $NATRIGGER with Composites
You can set an $NATRIGGER expression on a variable that is dimensioned by a composite, but Oracle OLAP evaluates the $NATRIGGER expression only for the dimension-value combinations that exist in the composite.
$NATRIGGER Ignored by EXPORT and AGGREGATE
The AGGREGATE command and the AGGREGATE function ignore the $NATRIGGER property setting for a variable during an aggregation operation. The statements fetch the stored value only, and do not invoke the $NATRIGGER expression. The $NATRIGGER property remains in effect for other operations.
In executing an EXPORT (EIF) statement, Oracle OLAP does not evaluate the $NATRIGGER property expression on a variable when it simply exports the variable. However, Oracle OLAP does evaluate the $NATRIGGER property expression when the variable is part of an expression that Oracle OLAP calculates during the export operation.
Examples
Example 5-11 Adding an $NATRIGGER Property to a Variable
The following statements define a dimension with three values and define a variable that is dimensioned by the dimension. They add the $NATRIGGER property to the variable, then put a value in one cell of the variable and leave the other cells empty so their values are NA. Finally, they report the values in the cells of the variable.
DEFINE d1 INTEGER DIMENSION MAINTAIN d1 ADD 3 DEFINE v1 DECIMAL <d1> PROPERTY '$NATRIGGER' '500.0' v1(d1 1) = 333.3 REPORT v1
The preceding statements produce the following output.
D1 V1
--------- ----------
1 333.3
2 500.0
3 500.0
The $STORETRIGGERVAL property specifies if, when a $NATRIGGER expression executes, Oracle OLAP replaces the NA values in the variable with the results of the expression.
Note:
Applications typically use the $VARCACHE property rather than the $STORETRIGGERVAL property since the functionality of the $STORETRIGGERVAL property is subsumed within the $VARCACHE property.Syntax
You add or delete a $STORETRIGGERVAL property to the most recently defined or considered object (see DEFINE and CONSIDER commands) using a PROPERTY statement:
To add the property, issue the following statement.
PROPERTY '$STORETRIGGERVAL' value
To delete the property, issue the following statement.
PROPERTY DELETE '$ASTORETRIGGERVAL'
Arguments
A BOOLEAN expression that contains the value of the property.
Examples
The $VARCACHE property specifies whether Oracle OLAP stores or caches variable data that is the result of the execution of an AGGREGATE function or a $NATRIGGER expression.
See also:
"How Oracle OLAP Determines Whether to Store or Cache Aggregated Data", "How Oracle OLAP Determines Whether to Store or Cache Results of $NATRIGGER", "What is an Oracle OLAP Session Cache?", and the description of the NA keyword of the CACHE statement for information on caching NA values calculated by the AGGREGATE function.Syntax
You add or delete a $VARCACHE property to the most recently defined or considered object (see DEFINE and CONSIDER commands) using a PROPERTY statement:
To add the property, issue the following statement.
PROPERTY '$VARCACHE' value
To delete the property, issue the following statement.
PROPERTY DELETE '$VARCACHE'
Arguments
One of the following TEXT expressions that indicate where Oracle OLAP should place variable data that is the result of calculations performed when the AGGREGATE function or $NATRIGGER value executes:
VARIABLE specifies that Oracle OLAP populates the variable with data that is the result of the execution of the AGGREGATE function or $NATRIGGER property. When you specify this option, the data that is the result of the aggregation is permanently stored in the variable when the analytic workspace is updated and committed.
SESSION specifies that Oracle OLAP caches data that is the result of the execution of the AGGREGATE function or $NATRIGGER property in the session cache (See "What is an Oracle OLAP Session Cache?"). When you specify this option, the data that is the result of the execution of the AGGREGATE function or $NATRIGGER property is ignored during updates and commits and is discarded after the session.
Important:
When SESSCACHE is set toNO, Oracle OLAP does not cache the data even when you specify SESSION. In this case, specifying SESSION is the same as specifying NONE.NONE specifies that Oracle OLAP calculates new variable data each time the AGGREGATE function or $NATRIGGER value executes; Oracle OLAP does not store or cache the data.
DEFAULT specifies that you do not want Oracle OLAP to use the $VARCACHE property when determining what to do with data that is calculated by the AGGREGATE function. (See "How Oracle OLAP Determines Whether to Store or Cache Aggregated Data".)
Notes
How Oracle OLAP Determines Whether to Store or Cache Results of $NATRIGGER
When a $NATRIGGER expression executes, what Oracle OLAP does with variable data that results from the execution of the expression is determined based on whether or not the variable that has the $NATRIGGER property also has a $STORETRIGGERVAL property and, if not, if the value of the $NATRIGGER property is an AGGREGATE function.
When a a $NATRIGGER expression executes, Oracle OLAP goes through the following process:
Does the variable with the $NATRIGGER property also have a $STORETRIGGERVAL property? If it does, then Oracle OLAP goes to step 1a. If it does not, then Oracle OLAP goes to step 2.
Is the value of the TRIGGERSTOREOK option, YES or NO? If it is YES, then Oracle OLAP goes to step 1b. If it is NO, then Oracle OLAP goes to step 2.
Is the value of the $STORETRIGGERVAL property, YES or NO? If it is YES, then Oracle OLAP stores the results of the $NATRIGGER expression and end decision-making process. If it is NO, then Oracle OLAP does not store the results of the $NATRIGGER expression and end decision-making process.
Is the $NATRIGGER expression an AGGREGATE function? If it is, then Oracle OLAP follows the steps described in "How Oracle OLAP Determines Whether to Store or Cache Aggregated Data" to determine what to do with the result of $NATRIGGER expression execution. If it is not, then Oracle OLAP goes to step 3.
Does the variable with the $NATRIGGER property also have a $VARCACHE property? If it does, then Oracle OLAP goes to step 4. If it does not, then Oracle OLAP goes to step 5.
Does the $VARCACHE property have a value of DEFAULT? If it does, then go to step 5. If it does not, then Oracle OLAP uses the value of the $VARCACHE property (that is, STORE, CACHE, or NONE) to determine what happens to the variable data values that are the result of $NATRIGGER expression execution and end decision-making process.
Use the current setting of the VARCACHE option to determine what happens to the variable data values that are the result of $NATRIGGER expression execution and end decision-making process.
How Oracle OLAP Determines Whether to Store or Cache Aggregated Data
When an AGGREGATE command executes, Oracle OLAP always stores the results of the calculation directly in the variable in the same way it stores the results of an assignment statement. However, when an AGGREGATE function executes, Oracle OLAP sometimes stores the results of the calculation directly in the variable and sometimes caches it in the session cache. (See "What is an Oracle OLAP Session Cache?" for more information about the session cache.)
To determine where to place the data that is the result of AGGREGATE function execution, Oracle OLAP goes through the following process to determine whether to store or cache aggregated variable data:
Is there a CACHE statement in the specification for the aggmap that is being used by the current AGGREGATE function? If there is, then Oracle OLAP goes to step 2. If there is not, then Oracle OLAP goes to step 3.
Is the CACHE statement a CACHE DEFAULT statement? If it is, then Oracle OLAP goes to step 3. If it is not, then Oracle OLAP uses the CACHE statement in the aggregation specification to determine what to do with variable data that is the result of the calculation and ends the decision-making process.
Does the variable being aggregated have a $VARCACHE property? If it does, then Oracle OLAP goes to Step 4. If it does not, then Oracle OLAP goes to step 5.
Does the $VARCACHE property have a value of DEFAULT? If it does, then Oracle OLAP goes to step 5. If it does not, then Oracle OLAP uses the value of the $VARCACHE property determines what happens to the variable data calculated using the AGGREGATE function, and ends the decision-making process.
Use the current setting of the VARCACHE option to determine what happens to the variable data calculated using the AGGREGATE function. End decision-making process.
Examples
|
 Copyright © 1994, 2009, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|