The AGGMAP command identifies an aggmap object as a specification for aggregation and adds an aggregation specification to the definition of the current aggmap object. To use AGGMAP to assign an aggregation specification to n aggmap object, the definition must be the one most recently defined or considered during the current session. When it is not, you must first use a CONSIDER statement to make it the current definition.
An alternative to the AGGMAP command is the EDIT AGGMAP statement, which is available only in OLAP Worksheet. The EDIT AGGMAP statement opens an Edit window in which you can add, delete, or change the aggregation specification for an aggmap object.
See also:
"OLAP DML Aggregation Objects"(Note that there are two other OLAP DML statements that are also sometimes referred to as "AGGMAP statements": AGGMAP ADD or REMOVE model statement that you can use to add or remove a model from an aggmap object of type AGGMAP, and AGGMAP SET that you can use to specify the default aggmap for a variable.)
A multiline text expression that is the aggregation specification for the current aggmap object. Each statement is a line of the multiline text expression. When coding an AGGMAP command at the command line level, separate statements with newline delimiters (\n), or use JOINLINES.
An aggregation specification begins with AGGMAP and ends with an END. Between these statements, you code one or more the following statements depending on the calculation that you want to specify. Minimally, you must code one RELATION (for aggregation) statement.
Note:
You cannot specify a conjoint dimension in the specification for the aggmap; use composites instead.Creating Temporary or Custom Aggregates
Most aggmap objects are defined to calculate variable values that are dimensioned by permanent dimension members (that is, dimension members that persist from one session to another). However, users might want to create their own aggregates at run time for forecasting or what-if analysis, or just because they want to view the data in an unforeseen way. Adding temporary members to dimensions and aggregating data for those members is sometimes called creating temporary or custom aggregates. For example, you can use a MAINTAIN ADD SESSION statement like the one below to temporarily add a model to an aggmap object.
MAINTAIN dimension ADD SESSION member = model APPLY TO AGGMAP aggmap
Aggregating Variables Dimensioned by Compressed Composites
Keep the following points in mind when designing an aggregation specification for a variable dimensioned by a compressed composite:
RELATION statements in the aggregation specification must be coded following the guidelines given in "RELATION Statements for Compressed Composites".
There is no support for parallel aggregation. Instead, use multiple sessions to compute variables or partitions that have their own compressed composites.
If possible, Oracle OLAP automatically performs incremental aggregation when you reaggregate a variable dimensioned by the compressed composite. In other words, Oracle OLAP determines what changes have occurred since the last aggregation, determines the smallest region of the variable that needs to be recomputed, and recomputes only that region.
Consequently, there is no support for explicit incremental aggregation. You cannot aggregate a variable dimensioned by a compressed composite if the dimension status of the variable is limited. The status of the variable's dimensions must be ALLSTAT for the aggregation to succeed. You can, however, partition using a dense dimension with local compressed composites. In this way you can aggregate only those partitions that contain new data.
Aggregation Options and System Properties
Several options can impact aggregation as outlined in "Aggregation Options".
See "System Properties by Category" for a list of system properties that relate to aggregation or allocation.
AGGREGATE automatically checks relations for circularity in and among multiple hierarchies. When you first define hierarchies, check for circularity by setting PRECOMPUTE statements to NA and AGGINDEX to NO. A XSHIERCK01 error during aggregation indicates that a circular hierarchy may have been detected. However, when the message includes a reference to UNDIRECTED, then multiple paths to an ancestor from a detail data cell have been detected. Some calculations require that a detail data cell use multiple paths to the same ancestor cell. When this is the case, then you must set the MULTIPATHHIER option to YES before you execute the AGGREGATE command. Otherwise, you must correct the error in the hierarchy structure. For more details about this error message and how to interpret it, see the MULTIPATHHIER option.
Example 9-6 Combining Pre-calculation and Calculation on the Fly
This example describes the steps you can take to pre-calculate some data in your analytic workspace and specify that the rest should be calculated when users request it.
Suppose you define an analytic workspace named mydtb that has a units variable with the following definition.
DEFINE units INTEGER <time, SPARSE <product, geography>>
You now must create and add a specification to the aggmap, which specifies the data that should be aggregated. This example shows you how to use an input file, which contains OLAP DML statements that define the aggmap and add a specification to it:
Identify the name of each dimension's hierarchy. When you have defined the hierarchies as self-relations, you use the names of the self-relations.
Decide which data to aggregate.
Suppose you want to calculate data for all levels of the time and product dimensions, but not for geography. The geography dimension's lowest level of data is at the city level. The second level of the hierarchy has three dimension values that represent regions: East, Central, and West. The third level of the hierarchy has one dimension value: Total.
Suppose that you want to pre-calculate the data for East and store it in the analytic workspace. You want the data for Central, West, and Total to be calculated only when users request that data — that data is not stored in the analytic workspace. Therefore, you must specify this information in the specification that you add to your aggmap object.
Create an ASCII text file named units.txt. Add the following OLAP DML statements to your text file.
DEFINE units.agg AGGMAP <time, SPARSE <product, geography>>
AGGMAP
RELATION myti.parent
RELATION mypr.parent
RELATION myge.parent PRECOMPUTE ('East')
END
The preceding statements define an aggmap named units.agg, then add the three RELATION statements to the aggregation specification when you read the units.txt file into your analytic workspace.
To read the units.txt file into your analytic workspace, execute the following statement.
INFILE 'inf/units.txt'
The units.agg aggmap should now exist in your analytic workspace. You can aggregate the units variable with the following statement.
AGGREGATE units USING units.agg
Now the data for East for all times and products has been calculated and stored in the analytic workspace.
Set up the analytic workspace so that when a user requests data for Central, West, or Total, that data is calculated and displayed. It is generally a good idea to compile the aggmap object before using it with the AGGREGATE function, as shown by the following statement.
COMPILE units.agg
This is not an issue when you are just using the AGGREGATE command, because this statement compiles the aggmap object before it uses it. However, when you do not use the FUNCDATA keyword with the AGGREGATE command, the metadata that is needed to perform calculation on the fly has not been compiled yet. If you have performed all other necessary calculations (such as calculating models), then it is a good practice to compile the aggmap when you load data. When you fail to do so, that means that every time a user opens the analytic workspace, that user has to wait for the aggregation to be compiled automatically. In other words, when any data is calculated on the fly, you can improve query performance for all of your users by compiling the aggmap before making the analytic workspace available to your users.
Add a property to the units variable.
CONSIDER units PROPERTY '$NATRIGGER' 'AGGREGATE(units USING units.agg)'
This property indicates that when a data cell contains an NA value, Oracle OLAP calls the AGGREGATE function to aggregate the data for that cell. Therefore, any units data that is requested by a user displayed. However, only the data for the East dimension value of the geography dimension has actually been aggregated and stored in the analytic workspace. All other data (for Central, West, and Total) is calculated only when users request it.
Example 9-7 Performing Non-additive Aggregation
This example shows how to use operators and arguments to combine additive and non-additive aggregation.
Suppose that you have defined four variables: sales, debt, interest_rate, and inventory. The variables have been defined with the same dimensionality where cp is a composite that has been defined with the product and geography dimensions.
<time cp<product geography>>
Suppose you want to use one AGGREGATE command to aggregate all four variables. The debt variable requires additive aggregation. The sales variable requires a weighted sum aggregation, and interest_rate requires a hierarchical weighted average. Therefore, both sales and interest_rate require a weight object, which you must define and populate with weight values. inventory requires a result that represents the total inventory, which is the last value in the hierarchy.
You specify the aggregation operation for debt and inventory with the OPERATOR keyword. However, because sales and interest_rate have aggregation operations that require weight objects, you must use the ARGS keyword to specify their operations. You define an operator variable to use the OPERATOR keyword. Typically, the operator variable is dimensioned by a measure dimension or a line item dimension.
Here are the steps to define the aggregation you want to occur:
Because you are also using a measure dimension to define an argument variable to use with the ARGS keyword, define that measure dimension, as illustrated by the following statements.
DEFINE measure DIMENSION TEXT MAINTAIN measure 'sales', 'debt', 'interest_rate', 'inventory'
Note:
Whenever you use ameasure dimension in a RELATION statement, you must include a MEASUREDIM statement in the same aggregation specificationDefine an operator variable named opvar and populate it.The statements specify that the aggregation for debt should use the SUM operator, and the aggregation for inventory should use the HLAST operator.
DEFINE opvar TEXT <measure> opvar (measure 'sales') = 'WSUM' opvar (measure 'debt') = 'SUM' opvar (measure 'interest_rate') = 'HWAVERAGE' opvar (measure 'inventory') = 'HLAST'
Because sales and interest_rate require weight objects, define and populate those weight objects. The following statement defines a weight object named currency (to be used by sales).
DEFINE currency DECIMAL <time geography>
Notice that the currency variable is dimensioned only by time and geography. The purpose of this variable is to provide weights that act as currency conversion information for foreign countries; therefore, it is unnecessary to include the product dimension.
Populate currency with the weight values that you want to use.
The interest_rate variable's nonaddictive aggregation (hierarchical weighted average) requires the sum of the variable debt. In other words, interest_rate cannot be aggregated without the results of the aggregation of debt.
You can now define an argument variable, which you must specify the aggregation results of debt as a weight object for interest_rate. You use the same argument variable to specify currency as the weight object for the sales variable. The following statement defines an argument variable named argvar.
DEFINE argvar TEXT <measure>
The next few statements populate the argument variable.
argvar (measure 'sales') = 'weightby currency' argvar (measure 'debt') = NA argvar (measure 'interest_rate') = 'weightby debt' argvar (measure 'inventory') = NA
For the aggregation of product and geography, the data for the sales, debt, and interest_rate variables can simply be added. But the inventory variable requires a hierarchical weighted average. Consequently, it is necessary to define a second operator variable and a second argument variable, both of which are used in the RELATION statement for product and geography.
The following statements define the second operator variable and populate it.
DEFINE opvar2 TEXT <measure> opvar (measure 'sales') = 'Sum' opvar (measure 'debt') = 'Sum' opvar (measure 'interest_rate') = 'Sum' opvar (measure 'inventory') = 'HWAverage'
The following statements define the second argument variable and populate it.
DEFINE argvar2 TEXT <measure> argvar (measure 'sales') = NA argvar (measure 'debt') = NA argvar (measure 'interest_rate') = NA argvar (measure 'inventory') = 'weightby debt'
Now create the aggmap, by issuing the following statements.
DEFINE sales.agg AGGMAP <time, CP<product geography>> AGGMAP RELATION time.r OPERATOR opvar ARGS argvar RELATION product.r OPERATOR opvar2 ARGS argvar2 RELATION geography.r OPERATOR opvar2 ARGS argvar2 MEASUREDIM measure END
Finally, use the following statement to aggregate all four variables.
AGGREGATE sales debt interest_rate inventory USING sales.agg
Example 9-8 Programmatically Defining an Aggmap
The following program uses the EXISTS function to test whether an AGGMAP exists, and defines the AGGMAP when it does not. It then uses an AGGMAP statement to define the specification for the aggmap.
DEFINE MAKEAGGMAP PROGRAM
LD Create dynamic aggmap
PROGRAM
IF NOT EXISTS ('test.agg')
THEN DEFINE test.agg AGGMAP <geography product channel time>
ELSE CONSIDER test.agg
AGGMAP JOINLINES(-
'RELATION geography.parentrel PRECOMPUTE (geography.lvldim 2 4)' -
'RELATION product.parentrel' -
'RELATION channel.parentrel' -
'RELATION time.parentrel' -
'END')
END
Example 9-9 Creating an Aggmap Using an Input File
Suppose that you have created a disk file called salesagg.txt, which contains the following aggmap definition and specification.
DEFINE sales.agg AGGMAP <time, product, geography> AGGMAP RELATION time.r PRECOMPUTE (time NE 'Year99') RELATION product.r PRECOMPUTE (product NE 'ALL') RELATION geography.r CACHE STORE END
To include the sales.agg aggmap in your analytic workspace, execute the following statement, where inf is the alias for the directory where the file is stored.
INFILE 'inf/salesagg.txt'
The sales.agg aggmap has now been defined and contains the three RELATION statements and the CACHE statement. In this example, you are specifying that all of the data for the hierarchy for the time dimension, time.r, should be aggregated, except for any data that has a time dimension value of Year99. All of the data for the hierarchy for the product dimension, product.r, should be aggregated, except for any data that has a product dimension value of All. All geography dimension values are aggregated. The CACHE STORE statement specifies that any data that are rolled up on the fly should be calculated just once and stored in the cache for other access requests during the same session.
You can now use the sales.agg aggmap with an AGGREGATE command, such as.
AGGREGATE sales USING sales.agg
In this example, any data value that dimensioned by a Year99 value of the time dimension or an All value of the product dimension is calculated on the fly. All other data is aggregated and stored in the analytic workspace.
Example 9-10 Using Multiple Aggmaps
When you use a forecast, you must ensure that all of the input data that is required by that forecast has been pre-calculated. Otherwise, the forecast uses incorrect or nonexistent data. For example, suppose your forecast requires that all line items are aggregated. Using a budget variable that is dimensioned by time, line, and division, one approach would be to perform a complete aggregation of the line dimension, forecast the dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, and then aggregate the remaining dimension, division.
You can support this processing by defining three aggmap objects:
Define the first aggmap, named forecast.agg1, which aggregates the data needed by the forecast. It contains the following statement.
RELATION line.parentrel
Define the second aggmap, named forecast.agg2, which aggregates the data generated using the first aggmap and the forecast. It contains the following statement.
RELATION division.parentrel PRECOMPUTE ('L3')
Define the third aggmap, named forecast.agg3, which contains the RELATION statements in the specifications of the first two aggmaps.
RELATION line.parentrel
RELATION division.parentrel PRECOMPUTE ('L3')
When your forecast is in a program named fore.prg, then you would use the following statements to aggregate the data.
AGGREGATE budget USING forecast.agg1 "Aggregate over LINE CALL fore.prg "Forecast over TIME AGGREGATE budget USING forecast.agg2 "Aggregate over DIVISION "Compile the limit map for LINE and DIVISION COMPILE forecast.agg3 "Use the combined aggmap for the AGGREGATE function CONSIDER budget PROPERTY 'NATRIGGER' 'AGGREGATE(budget USING forecast.agg3)'
Example 9-11 Using an AGGINDEX Statement in an Aggregation Specification
Suppose you have two variables, sales1 and sales2, with the following definitions.
DEFINE sales1 DECIMAL <time, SPARSE<product, channel, customer>> DEFINE sales2 DECIMAL <time, SPARSE<product, channel, customer>>
You do not want to precompute and commit all of the sales data to the database, because disk space is limited and you must improve performance. Therefore, you must create an aggmap, in which you specify which data should be pre-computed and which data should be calculated on the fly.
You define the aggmap, named sales.agg, with the following statement.
DEFINE sales.agg AGGMAP <time, SPARSE<product, channel, customer>>
Next, you use an AGGMAP statement to enter the following specification for sales.agg.
RELATION time.r PRECOMPUTE (time NE 'Year99') RELATION product.r PRECOMPUTE (product NE 'All') RELATION channel.r RELATION customer.r AGGINDEX NO
This aggregation specification tells Oracle OLAP that all sales data should be rolled up and committed to the database except for any data that has a time dimension value of Year99 or a product dimension value of All—the data for those cells is calculated the first time a user accesses them. The AGGINDEX value of NO tells Oracle OLAP not to create the indexes for data that should be calculated on the fly.
Now you execute the following statement.
sales2 = AGGREGATE(sales1 USING sales.agg) ACROSS SPARSE - <product, channel, customer>
sales2 now contains all of the data in sales1, plus any data that is aggregated for Year99—this is because time is not included in a composite.
On the other hand, the data that is aggregated for the product value of All is not computed and stored in sales2. This data is not computed or stored because the product dimension is included in a composite—the indexes that are required for dimensions that are included in composites were not created because the aggregation specification contains an AGGINDEX NO statement. Because the indexes did not exist, Oracle OLAP never called the AGGREGATE function to compute the data to be calculated on the fly.
Example 9-12 Aggregating By Dimension Attributes
Assume that when your business makes a sales it keeps records of the customer's name, sex, age, and the amount of the sale. To hold this data, your analytic workspace contains a dimension named customer and three variables (named customer_sex, customer_age, and sales) that are dimensioned by customer.
REPORT W 14 <customer_sex customer_age sales> CUSTOMER CUSTOMER_SEX CUSTOMER_AGE SALES -------------- -------------- -------------- -------------- Clarke M 26 26,000.00 Smith M 47 15,000.00 Ilsa F 24 33,000.00 Rick M 33 22,000.00
You want to aggregate the detail sales data over sex and age to calculate the amount of sales you have made to males and females, and the amount of sales for different age ranges. To hold this data you need an INTEGER variable that is dimensioned by hierarchical dimensions for sex and age. You also need an aggmap object that specifies the calculations that Oracle OLAP performs to populate this variable from the data in the sales variable.
To create and populate the necessary objects, you take the following steps:
Create and populate dimensions and self-relations for hierarchical dimensions named sex and age.
DEFINE sex DIMENSION TEXT DEFINE sex.parentrel RELATION sex <sex> DEFINE age DIMENSION TEXT DEFINE age.parentrel RELATION age <age> AGE AGE.PARENTREL -------------- -------------------- 0-20 All 21-30 All 31-50 All 51-100 All No Response All All NA SEX SEX.PARENTREL -------------- -------------------- M All F All No Reponse All All NA
Create and populate relations that map the age and sex dimensions to the customer dimension.
DEFINE customer.age.rel RELATION age <customer> DEFINE customer.sex.rel RELATION sex <customer> CUSTOMER CUSTOMER.AGE.REL CUSTOMER.SEX.REL -------------- -------------------- -------------------- Clarke 21-30 M Smith 31-50 M Ilsa 21-30 F Rick 31-50 M
Create a variable named sales_by_sex_age to hold the aggregated data. Like the sales variable this variable is of type DECIMAL, but it is dimensioned by sex and age rather than by customer.
DEFINE sales_by_sex_age VARIABLE DECIMAL <sex age>
Define an AGGMAP type aggmap object named ssa_aggmap to calculate the values of the sales_by_sex_age variable.
DEFINE SSA_AGGMAP AGGMAP AGGMAP RELATION sex.parentrel OPERATOR SUM RELATION age.parentrel OPERATOR SUM BREAKOUT DIMENSION customer - BY customer.sex.rel, customer.age.rel OPERATOR SUM END
Notice that the specification for the ssa_aggmap includes the following statements:
A BREAKOUT DIMENSION statement that specifies how to map the customer dimension of the sales variable to the lowest-level values of the sales_by_sex_age variable. This statement specifies the name of the dimension of the variable that contains the detail values (that is, customer) and the names of the relations (customer.sex.rel and customer.age.rel) that define the relations between customer dimension and the sex and age dimensions.
Two RELATION statements that specify how to aggregate up the sex and age dimensions of the sales_by_sex_age variable. Each of these statements includes the name of the child-parent relation (sex.parentrel or age.parentrel) that define the self-relation for the hierarchal dimension (sex or age).
Populate the sales_by_sex_age variable by issuing an AGGREGATE command that specifies that the detail data for the aggregation comes from the sales variable.
AGGREGATE sales_by_sex_age USING ssa_aggmap FROM sales
After performing the aggregation, a report of sales_by_sex_age shows the calculated values.
---------------------SALES_BY_SEX_AGE----------------------
----------------------------SEX----------------------------
AGE M F No Reponse All
-------------- -------------- -------------- -------------- --------------
0-20 NA NA NA NA
21-30 26,000.00 33,000.00 NA 59,000.00
31-50 37,000.00 NA NA 37,000.00
51-100 NA NA NA NA
No Response NA NA NA NA
All 63,000.00 33,000.00 NA 96,000.00
Example 9-13 Using a CACHE Statement in an Aggregation Specification
Suppose you have a sales variable with the following definition.
DEFINE sales DECIMAL <time, SPARSE<product, channel, customer>>
You do not want to pre-compute and commit all of the sales data, because space is limited and you must improve performance. Therefore, you must create an aggmap, in which you specify which data should be pre-computed and which data should be calculated on the fly.
You define the aggmap, named sales.agg, with the following statement.
DEFINE sales.agg AGGMAP <time, SPARSE<product, channel, - customer>>
Next, you use the AGGMAP statement to enter the following aggregation specification forsales.agg.
AGGMAP RELATION time.r PRECOMPUTE (time NE 'YEAR99') RELATION product.r PRECOMPUTE (product NE 'ALL') RELATION channel.r RELATION customer.r CACHE SESSION END
This aggregation specification tells Oracle OLAP that all sales data should be rolled up and committed, except for any cells that have a time dimension value of Year99 or a product dimension value of ALL; the data for those cells is calculated the first time a user accesses them. Because the CACHE statement uses the SESSION keyword, that means that when those cells are calculated on the fly, the data is stored in the cache for the remainder of the Oracle OLAP session. That way, the next time a user accesses the same cell, the data does not have to be calculated again. Instead, the data is retrieved from the session cache.
Example 9-14 Populating All Levels of a Hierarchy Except the Detail Level
Assume that your analytic workspace contains the relations and dimensions with the following definitions.
DEFINE geog.d TEXT DIMENSION DEFINE geog.r RELATION geog.d <geog.d> DEFINE sales_by_units INTEGER VARIABLE <geog.d> DEFINE sales_by_revenue DECIMAL VARIABLE <geog.d> DEFINE price_per_unit DECIMAL VARIABLE <geog.d>
Assume that you create two aggmap objects. One aggmap object, named units_aggmap, is the specification to aggregate data in the sales_by_units variable. The other aggmap object, revenue_aggmap, is the specification to calculate all of the data except the detail data in the sales_by_revenue variable.
DEFINE units_aggmap AGGMAP AGGMAP RELATION geog.r OPERATOR SUM END DEFINE revenue_aggmap AGGMAP AGGMAP RELATION geog.r OPERATOR WSUM ARGS WEIGHTBY price_per_unit CACHE NOLEAF END
The following steps outline the aggregation process:
Before either the sales_by_unit or sales_by_revenue variables are aggregated, they have the following values.
GEOG.D SALES_BY_UNIT SALES_BY_REVENUE --------- ------------- ---------------- Boston 1 NA Medford 2 NA San Diego 3 NA Sunnydale 4 NA MA NA NA CA NA NA USA NA NA
After the data for the sales_by_unit variable is aggregated, the sales_by_unit and sales_by_revenue variables have the following values.
AGGREGATE sales_by_unit USING units_aggmap GEOG.D SALES_BY_UNIT SALES_BY_REVENUE --------- ------------- ---------------- Boston 1 NA Medford 2 NA San Diego 3 NA Sunnydale 4 NA MA 3 NA CA 7 NA USA 10 NA
After the data for the sales_by_revue variable is aggregated, the sales_by_unit and sales_by_revenue variables have the following values.
AGGREGATE sales_by_revenue USING revenue_aggmap FROM units_aggmap GEOG.D SALES_BY_UNIT SALES_BY_REVENUE --------- ------------- ---------------- Boston 1 NA Medford 2 NA San Diego 3 NA Sunnydale 4 NA MA 3 13.5 CA 7 31.5 USA 10 45.0
Example 9-15 Aggregating into a Different Variable
Assume that there is a variable named sales that is dimensioned by time, a hierarchical dimension, and district, a non-hierarchical dimension.
DEFINE time DIMENSION TEXT
DEFINE time.parentrel RELATION time <time>
DEFINE district DIMENSION TEXT
DEFINE sales VARIABLE DECIMAL <time district>
-----------------------SALES-----------------------
---------------------DISTRICT----------------------
TIME North South West East
------------ ------------ ------------ ------------ ------------
1976Q1 168,776.81 362,367.87 219,667.47 149,815.65
1976Q2 330,062.49 293,392.29 237,128.26 167,808.03
1976Q3 304,953.04 354,240.51 170,892.80 298,737.70
1976Q4 252,757.33 206,189.01 139,954.56 175,063.51
1976 NA NA NA NA
Assume also that you want to calculate the total sales for each quarter and year for all districts except the North district. To perform this calculation using an aggmap object, you take the following steps:
Create a valueset named not_north that represents the values of district for which you want to aggregate data.
DEFINE not_north VALUESET district LIMIT not_north TO ALL LIMIT not_north REMOVE 'North'
Define a variable named total_sales_exclud_north to hold the results of the calculation.
DEFINE total_sales_exclud_north VARIABLE DECIMAL <time>
Notice that, like sales, the total_sales_exclud_north variable is dimensioned by time. However, unlike sales, the total_sales_exclud_north variable is not dimensioned by district because it holds detail data for each district, but only the total (aggregated) values for the South, West, and East districts (that is, all districts except North).
Define an aggmap object that specifies the calculation that you want performed.
DEFINE agg_sales_exclud_north AGGMAP AGGMAP RELATION time.parentrel OPERATOR SUM DROP DIMENSION district OPERATOR SUM VALUES not_north END
Notice that the aggregation specification consists of two statements that specify how to perform the aggregation:
A RELATION statement that specifies how to aggregate up the hierarchical time dimension
A DROP DIMENSION statement that specifies how to aggregate across the non-hierarchical district dimension. In this case, the DROP DIMENSION also uses the not_north valueset to specify that values for the North district are excluded when performing the aggregation
Aggregate the data.
AGGREGATE total_sales_exclud_north USING agg_sales_exclud_north FROM sales
The report of the total_sales_exclud_north variable shows the aggregated values.
TIME ALL_SALES_EXCEPT_NORTH ------------ ------------------------------ 1976Q1 731,850.99 1976Q2 698,328.58 1976Q3 823,871.02 1976Q4 521,207.09 1976 2,775,257.69
Example 9-16 Using a MEASUREDIM Statement in an Aggregation Specification
Suppose you have defined a measure dimension named measure. You then define an operation variable named myopvar, which is dimensioned by measure. When you use myopvar in an aggregation specification, you must also include a MEASUREDIM statement that identifies measure as the dimension is included in the definition of myopvar.
The MEASUREDIM statement should follow the last RELATION statement in the aggregation specification, as shown in the following example.
DEFINE sales.agg AGGMAP <time, product, geography> AGGMAP RELATION time.r OPERATOR myopvar RELATION product.r RELATION geography.r MEASUREDIM measure END
Example 9-17 Solving a Model in an Aggregation
This example uses the budget variable.
DEFINE budget VARIABLE DECIMAL <line time> LD Budgeted $ Financial
The time dimension has two hierarchies (Standard and YTD) and a parent relation named time.parentrel as follows.
-----TIME.PARENTREL------
----TIME.HIERARCHIES-----
TIME Standard YTD
-------------- ------------ ------------
Last.YTD NA NA
Current.YTD NA NA
Jan01 Q1.01 Last.YTD
...
Dec01 Q4.01 Last.YTD
Jan02 Q1.02 Current.YTD
Feb02 Q1.02 Current.YTD
Mar02 Q1.02 Current.YTD
Apr02 Q2.02 Current.YTD
May02 Q2.02 Current.YTD
Q1.01 2001 NA
...
Q4.01 2001 NA
Q1.02 2002 NA
Q2.02 2002 NA
2001 NA NA
2002 NA NA
The relationships among line items are defined in the following model.
DEFINE income.budget MODEL MODEL DIMENSION line time opr.income = gross.margin - marketing gross.margin = revenue - cogs revenue = LAG(revenue, 12, time) * 1.02 cogs = LAG(cogs, 1, time) * 1.01 marketing = LAG(opr.income, 1, time) * 0.20 END
The following aggregation specification pre-aggregates all of the data. Notice that all of the data must be pre-aggregated because the model includes both LAG functions and a simultaneous equation.
DEFINE budget.aggmap1 AGGMAP AGGMAP MODEL income.budget RELATION time.parentrel END
Example 9-18 Aggregating Up a Hierarchy
Suppose you define a sales variable with the following statement.
DEFINE sales VARIABLE <time, SPARSE <product, geography>>
The aggregation specification for sales might include RELATION statements like the following.
AGGMAP
RELATION time.r PRECOMPUTE ('Yr98', 'Yr99')
RELATION product.r
RELATION geography.r PRECOMPUTE (geography NE 'Atlanta')
END
The AGGREGATE command aggregates values for Yr98 and Yr99, over all of products, and over all geographic areas except for Atlanta. All other aggregates are calculated on the fly.
Suppose you have a hierarchy dimension named time.type, whose dimension values are Fiscal and Calendar, in that order. These hierarchies are in conflict, and you want to precompute some time data but calculate the rest on the fly. Because the Calendar hierarchy is the last dimension value in the hierarchy dimension, consequently, you must define a valueset to get the correct results for the Fiscal hierarchy.
First, use the following statements to define and populate a valueset.
DEFINE time.vs VALUESET time LIMIT time.vs TO 'Calendar' 'Fiscal'
You can then use the valueset in the following RELATION statement. Because the Fiscal hierarchy is the last hierarchy in the valueset, the data that is aggregated is accurate for the Fiscal hierarchy.
RELATION time.r(time.vs) PRECOMPUTE ('Yr99', 'Yr00')
Example 9-20 Aggregating with a RELATION Statement That Uses an ARGS Keyword
You can list the arguments in a RELATION statement directly in the statement or as the value of a text variable. For example, the following statement specifies WEIGHTBY wobj as an argument.
RELATION time.r OPERATOR wsum ARGS WEIGHTBY wobj
Alternatively, you can define an variable for the argument whose value is the text of the WEIGHTBY clause.
DEFINE argvar TEXT argvar = 'WEIGHTBY wobj'
Then the RELATION statement can specify the text variable that contains the WEIGHTBY clause.
RELATION time.r OPERATOR WSUM ARGS argvar
Example 9-21 Aggregating Using a Measure Dimension
Suppose you want to use a single AGGREGATE command to aggregate the sales, units, price, and inventory variables. When you want to use the same operator for each variable, then you do not have to use a measure dimension. However, when you want to specify different aggregation operations, then you must use a measure dimension.
The following statement defines a dimension named measure.
DEFINE measure DIMENSION TEXT
You can then use a MAINTAIN statement to add dimension values to the measure dimension.
MAINTAIN measure ADD 'sales', 'units', 'quota', 'inventory'
Use the measure dimension to dimension a text variable named meas.opvar that you use as the operator variable.
DEFINE meas.opvar TEXT WIDTH 2 <measure>
The following statements add values to OPVAR
meas.opvar (measure 'sales') = 'SU' meas.opvar (measure 'units') = 'SU' meas.opvar (measure 'price') = 'HA' meas.opvar (measure 'inventory') = 'HL'
The aggregation specification might look like the following. Note that when you specify an operator variable in a RELATION statement, you must include a MEASUREDIM statement that specifies the name of the measure dimension (measure in the following example) in the aggregation specification.
DEFINE opvar.aggmap AGGMAP AGGMAP RELATION geography.parentrel PRECOMPUTE (geography.lvldim 2 4) RELATION product.parentrel OPERATOR opvar RELATION channel.parentrel OPERATOR opvar RELATION time.parentrel OPERATOR opvar MEASUREDIM measure END
Example 9-22 Aggregating Using a Line Item Dimension
Suppose you have two variables, actual and budget, that have these dimensions.
<time line division>
You want to use different methods to calculate different line items. You create a text variable that you use as the operator variable.
DEFINE line.opvar TEXT WIDTH 2 <line>
You then populate line.opvar with the appropriate operator for each line item, for example.
line.opvar (line 'Net.Income') = 'SU' line.opvar (line 'Tax.Rate') = 'AV'
The aggregation specification might look like this.
DEFINE LINE.AGGMAP AGGMAP AGGMAP RELATION time.parentrel OPERATOR line.opvar RELATION division.parentrel END
Example 9-23 Skip-Level Aggregation
Suppose you want to aggregate sales data. The sales variable is dimensioned by geography, product, channel, and time.
First, consider the hierarchy for each dimension. How many levels does each hierarchy have? What levels of data do users typically query? When you are designing a new workspace, what levels of data do your users plan to query?
Suppose you learn the information described in the following table about how users tend to query sales data for the time hierarchy.
| Time Level Names | Descriptive Level Name | Examples of Dimension Values | Do users query this level often? |
|---|---|---|---|
| L1 | Year | Year99, Year00 |
yes |
| L2 | Quarter | Q3.99, Q3.99, Q1.00 |
yes |
| L3 | Month | Jan99, Dec00 |
yes |
While the next table shows how your users tend to query sales data for the geography hierarchy.
| Geography Level Names | Descriptive Level Name | Examples of Dimension Values | Do users query this level often? |
|---|---|---|---|
| L1 | World | World |
yes |
| L2 | Continent | Europe, Americas |
no |
| L3 | Country | Hungary, Spain |
yes |
| L4 | City | Budapest, Madrid |
yes |
Finally, the next table shows how your users tend to query sales data for the product dimension hierarchy.
| Product Level Names | Descriptive Level Name | Examples of Dimension Values | Do users query this level often? |
|---|---|---|---|
| L1 | All Products | Totalprod |
yes |
| L2 | Division | Audiodiv, Videodiv |
yes |
| L3 | Category | TV, VCR |
yes |
| L4 | Product | Tuner, CDplayer |
yes |
Using this information about how users query data, use the following strategy for aggregation:
Fully aggregate time and product because all levels are queried frequently.
For the geography dimension, aggregate data for L1 (World) and L3 (Country) because they are queried frequently. However, L2 is queried less often and so can be calculated on the fly.
The lowest level of data was loaded into the analytic workspace. The aggregate data is calculated from this source data.
Therefore, the aggregation specification might look like the following.
RELATION time.parentrel RELATION geography.parentrel PRECOMPUTE (geog.leveldim 'L3' 'L1') RELATION product.parentrel
Example 9-24 Aggregation Specification with RELATION Statements That Include PRECOMPUTE Clauses
This aggregation specification uses PRECOMPUTE clauses in the RELATION statements to limit the data that is aggregated by the AGGREGATE command.
DEFINE gpct.aggmap AGGMAP LD Aggmap for sales, units, quota, costs AGGMAP RELATION geography.parentrel PRECOMPUTE (geography.levelrel 'L3') RELATION product.parentrel PRECOMPUTE (LIMIT(product complement 'TotalProd')) RELATION channel.parentrel RELATION time.parentrel PRECOMPUTE (time NE '2001') END
Within an aggregation specification, an AGGINDEX statement tells Oracle OLAP whether the compilation of that aggmap should create indexes (meaning, composite tuples) for data cells that are calculated on the fly by the AGGREGATE function. Therefore, the AGGINDEX statement has an effect on a dimension that is included in a composite but it has no effect on a dimension that is not included in a composite.
These indexes are used by the MODEL statement in an AGGMAP and by statements that use the ACROSS phrase to help Oracle OLAP loop over variables that are dimensioned by composites. These statements expect all data to be calculated. When you specify calculating some data on the fly, that data appears to be missing. When you set AGGINDEX to YES, then the statements try to access the missing data whether or not you are using the AGGREGATE function to perform calculation on the fly (meaning, you have added to the variable whose data is being aggregated an NA trigger property that calls the AGGREGATE function).
When the indexes have been created and you use AGGREGATION with the AGGREGATE function, then when MODEL (or a statement that uses the ACROSS phrase) requests the missing data, that data is calculated on the fly. That means that the results of the MODEL (or other statement) are correct, because the statement has all of the data that it needs.
When these indexes have not been created, the missing data cannot be calculated. Consequently, the statements that need the indexes interpret the missing data as NA data, even when you use the AGGREGATE function.
(Default) Tells the AGGMAP compiler to ensure that all possible indexes are created whenever an aggmap is recompiled. In other words, indexes are created both for the data that is being pre-calculated and the data that is calculated on the fly. Data is recalculates on the fly happens when a COMPILE statement compiles the aggmap and when the AGGREGATE command executes an aggmap whose specification has changed since the last time it was compiled. The creation of all possible indexes results in a longer compilation time but faster execution of the AGGREGATE function. For a discussion of when AGGINDEX should be set to YES, see "When To Use an AGGINDEX Value of YES".
Does not create the indexes for data that is calculated on the fly. Omitting the creation of these index values accelerates the compilation time, but causes Oracle OLAP to treat the uncomputed data as NA data whenever the MODEL statement in an AGGMAP or an ACROSS phrase is executed. For a discussion of when AGGINDEX should be set to NO, see "When To Use an AGGINDEX Value of NO".
When To Use an AGGINDEX Value of YES
The primary advantage to using an AGGINDEX value of YES is that then Oracle OLAP always try to access data that you have specified to be calculated on the fly. When you have created an $NATRIGGER property for a variable that calls the AGGREGATE function, the variable appears to have been fully precomputed. That means that when any NA value is encountered, the NA trigger is called during the execution of an ACROSS phrase or the MODEL statement in an AGGMAP. When the NA trigger is called, the AGGREGATE function is executed, and the data is calculated on the fly.
When AGGINDEX has a value of NO, then the NA trigger is called only to aggregate data for dimensions that are not included in a composite. Data for dimensions that are included in composites is interpreted as NA values.
For example, suppose you have two variables called sales1 and sales2, which are defined with the following definitions.
DEFINE sales1 DECIMAL <time, SPARSE <product, geography>> DEFINE sales2 DECIMAL <time, SPARSE <product, geography>>
Now suppose you have an aggmap object named sales.agg, which has the following definition.
DEFINE sales.agg AGGMAP <time, SPARSE <product, geography>>
When you add a specification to the sales.agg aggmap, you enter RELATION statements for time, product and geography with PRECOMPUTE clauses that specify NA which specifies that no data is aggregated—instead, all of the data for any variable that uses this aggmap is calculated on the fly.
RELATION time.r PRECOMPUTE (NA) RELATION product.r PRECOMPUTE (NA) RELATION geography.r PRECOMPUTE (NA)
Now attach the following $NATRIGGER property to the sales1 variable.
CONSIDER sales1 PROPERTY '$NATRIGGER' 'AGGREGATE(sales1 USING sales.agg)'
Consider the effect of AGGINDEX in the following statement. Because you did not enter an AGGINDEX statement in the sales.agg aggregation specification, the default of AGGINDEX YES is assumed.
sales2 = sales1 ACROSS SPARSE <product, geography>
This statement loops over the data in sales1 and copies the values into sales2. This statement causes the NA trigger to call the AGGREGATE function for all of the data that you have specified to be calculated on the fly in sales1. Consequently, after the aggregation that sales2 contains a copy of sales1 plus all the aggregate data cells (the cells that would have been calculated if the sales1 data had been completely precomputed, meaning, fully rolled up).
However, when you put an AGGINDEX NO statement in the sales.agg aggregation specification, then sales2 contains a copy of the data in sales1 and the aggregate data cells for the time dimension.
Note that in both cases, $NATRIGGER is called to aggregate time data, because the time dimension is not included in the composite, so the value of AGGINDEX has no effect on it.
When To Use an AGGINDEX Value of NO
You can use an AGGINDEX value of NO when you know that either of the following is true:
Your application does not contain an ACROSS phrase or a MODEL statement in an AGGMAP command.
The results of your MODEL statements or ACROSS phrases are additive, and data that needs to be aggregated can be calculated safely on the fly.
Each of the preceding cases ensures that the data that you have specified to be calculated on the fly is available at the appropriate time.
By setting AGGINDEX to NO, the size of the indexes is reduced, and overall application performance improves.
When Using an AGGINDEX Value Of NO Causes Problems
When you run a MODEL that assumes all data that should be aggregated has been aggregated, then you may get NA data where real data should occur. For instance, suppose you have a variable that has a composite that includes the time dimension. You perform a calculation that subtracts the fourth quarter from the total for the year. When the value of Year is to be calculated dynamically, and the AGGINDEX statement is set to NO, then the result of the calculation is NA. When the value of Year was precomputed or when AGGINDEX is set to YES, then the MODEL correctly calculates a result equal to the sum of the first three quarters.
Index Creation Is Based on Existing Data
Only the indexes that are needed to aggregate existing data are created when AGGINDEX has a value of YES. For example, suppose one dimension in your composite is a dimension named time. The lowest-level data for the time dimension is at the monthly level. Therefore, the dimension values that are associated with the lowest-level data are Jan99, Feb99, and so on. The monthly data aggregates to quarters and to years. Suppose you have data for the first six months of the year. When AGGINDEX has a value of YES, indexes are created for the Q1, Q2, and Yr99 dimension values, but not for Q3 and Q4.
Reducing Compilation Time When AGGINDEX is YES
One disadvantage of using the default of AGGINDEX YES is that the compilation of the aggmap takes a longer time to complete. You can eliminate the cost of this extra time by using the FUNCDATA keyword with the AGGREGATE command. When you use the FUNCDATA keyword, all possible indexes (regardless of how you have limited your data) are created. However, do not use the FUNCDATA keyword when you use a different aggmap to execute the AGGREGATE command and the AGGREGATE function.
For an example of using an AGGINDEX statement, see Example 9-11, "Using an AGGINDEX Statement in an Aggregation Specification".
Within an aggregation specification, a BREAKOUT DIMENSION statement specifies how a dimension of the target variable maps to one or more dimensions of the source variable. You use this statement in an aggregation specification when you are aggregating the detail data from one variable (the source variable) into another variable (the target variable) that has a different dimension (that is, a "breakout" dimension) than the variable that contains the detail data.
BREAKOUT DIMENSION dimname BY relation [, relation...] -
OPERATOR operation [ARGS argument]
where:
relation has the following syntax:
relationname [IGNORE ignore_dim_value [DEFAULT default_dim_value]]
argument specifies the settings of various options and is one or more of the following phrases:
The name of a dimension in the variable that contains the detail data (that is, the source variable).
The name of a relation whose values relate a dimension of the target variable to dimname.
Specifies that if the target dimension is QDRd to the value specified by ignore_dim-value then AGGREGATE does not use the relation specified by relationname to limit the source dimension.
Specifies that if all relations have an IGNORE phrase, then AGGREGATE uses the value specified by default_dim-value value to create a QDR rather than using a relation. If all relations have an IGNORE phrase and you do not include a DEFAULT phrase, the AGGREGATE arbitrarily chooses a relationship to limit by.
when dimname is QDRd to the dimension value specified by ignore_dim-value then AGGREGATE does not use the relation specified by relationname to limit the source dimension
Identifies the calculation method used to aggregate the data.
A keyword that describes the type of aggregation to perform. The keywords are listed in Table 9-1, "Aggregation Methods".
Indicates optional handling of the aggregation.
Specifies whether to allow division by zero.
YES allows division by zero; a statement involving division by zero executes without error but produces NA results.
NO disallows division by zero; a statement involving division by zero stops executing and produces an error message.
The default value is the current value of the DIVIDEBYZERO option.
Specifies whether to allow decimal overflow, which occurs when the result of a calculation is very large and can no longer be represented by the exponent portion of the numeric representation. Specify YES to allow overflow, which means that a calculation that generates overflow executes without error and produces NA results. Specify NO to disallow overflow, which means that; a calculation involving overflow stops executing and generates an error message. The default value is the current value of the DECIMALOVERFLOW option.
Specifies whether NA values are input. Specify YES when you want Oracle OLAP to ignore NA values when aggregating which means that only actual values are used in calculations. Specify NO when you want Oracle OLAP to consider NA values are considered which means that when any of the values being considered are NA, the calculation returns NA.The default value is the current value of the NASKIP option.
The value that you specify for the NASKIP phrase does not effect calculation performed when you specify HAVERAGE, HFIRST, HLAST, HWAVERAGE, HWFIRST, HWLAST for operation.
Indicates that weighted aggregation is to be performed. You must include a WEIGHTBY clause when you specify HWAVERAGE, HWFIRST, HWLAST, SSUM, WAVERAGE, WFIRST, WLAST, or WSUM for operation. The WEIGHTBY phrase always includes a wobj argument and can optionally include the WNAFILL keyword. For more information about the use of the WEIGHTBY phrase, see RELATION (for aggregation) statement of the AGGMAP command.
Indicates handling for NA values. The default values for WNAFILL vary depending on the value of operation.
Substitutes a number for every NA value. That number replaces every NA value in the weight object, weight formula, or weight relation. The default for HWAVERAGE and SSUM is The default for HWFIRST, HWLAST, WAVERAGE, WFIRST, WLAST, and WSUM is 1.0 .
Specifies that NA values are to be specified as NA. NA is the default for OR.
For more information about using the WNAFILL phrase, see RELATION (for aggregation) statement of the AGGMAP command.
A variable, formula, or relation that provides the weighted values. It can be numeric or BOOLEAN. When wobj is BOOLEAN, then TRUE has a weight of 1.0 and FALSE has a weight of 0.0. A formula is queried only when needed, depending on the dimensionality of the formula and the variable being aggregated. When wobj is a relation, it should be a one-dimensional self-relation. For more information about specifying values for wobj, see RELATION (for aggregation) statement of the AGGMAP command.
For an example of using the BREAKOUT DIMENSION statement, see Example 9-12, "Aggregating By Dimension Attributes".
Within an aggregation specification, a CACHE statement tells Oracle OLAP whether to cache or store the calculated data, whether to populate leaf or detail data when the variable data is aggregated using detail data from another variable, and whether to cache NA values when a summary values calculates to NA.
Note:
The CACHE statement is only one factor that determines whether variable data that has been aggregated on-the-fly using the AGGREGATE function is stored or cached. See "How Oracle OLAP Determines Whether to Store or Cache Aggregated Data".For data that is calculated using the AGGREGATE function, specifies that Oracle OLAP calculates the data each time the AGGREGATE function executes. When you specify either of these keywords, Oracle OLAP does not store or cache the data calculated by the AGGREGATE function.
For data that is calculated using the AGGREGATE function, specifies that Oracle OLAP stores data calculated by the AGGREGATE function in the variable in the database. When you specify this option, the results of the aggregation are permanently stored in the variable when the analytic workspace is updated and committed.
For data that is calculated using the AGGREGATE function, specifies that Oracle OLAP caches data calculated by the AGGREGATE function in the session cache (see "What is an Oracle OLAP Session Cache?"). When you specify this option, the results of the aggregation are ignored during updates and commits and are discarded after the session.
Note:
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.(Default) For data that is calculated using the AGGREGATE function, specifies that Oracle OLAP uses the value of the VARCACHE option to determine what to do with data that is calculated by the AGGREGATE function. See "How Oracle OLAP Determines Whether to Store or Cache Aggregated Data".
When the variable data is aggregated using detail data from another variable, specifies that Oracle OLAP calculates the leaf data for the variable.
(Default) When the variable data is aggregated using detail data from another variable, specifies that Oracle OLAP does not calculate the leaf data for the variable.
For data that is calculated using the AGGREGATE function, specifies that Oracle OLAP places any NA values that are the results of the execution of the AGGREGATE function in the Oracle OLAP session cache. In this case, when there is a variable has an $NATRIGGER property with an AGGREGATE function as its expression, Oracle OLAP does not recalculate the values for the variable. (For more information on the caching NA values, see "How Oracle OLAP Determines Whether to Store or Cache Results of $NATRIGGER".)
For data that is calculated using the AGGREGATE function, specifies that Oracle OLAP does not cache any NA values that are the results of the execution of the AGGREGATE function. In this case, when a variable has an $NATRIGGER property with an AGGREGATE function as its expression, Oracle OLAP recalculates the values for the variable.
Use NOSTORE when you know that your users are likely to modify pre-computed data, and you want any data that calculated by the AGGREGATE function to consistent with any of those users' changes.
In other words, suppose a user makes a change to detail-level data, such as sales figures for three stores, which are in a geography dimension. The geography dimension rolls up data from stores to cities to states to regions to countries. In other words, there are five levels in the geography dimension's hierarchy. Now suppose that users tend to access data only at the store level (your detail data), the regions level, and the countries level. Those are the levels for which you roll up sales data and commit it to the database. Because users do not access data at the city and state level, you specify that the data cells in those two levels are calculated on the fly. When users modify the store-level data and then access city data, the city data are calculated every time that a user requests it. Therefore, any changes that a user makes to the store-level details accurately rollup to the city and state level every time that user accesses a data cell in the city or state level. (However, this is not true of the data in the region and country levels, because those cells store pre-computed data.)
The advantage to using STORE or SESSION is that it improves query performance. For example, suppose your users use a Table tool to look at a variable's data and an individual user requests the same data cells several times in the same session. When you use the default of NOSTORE, then any data that is not aggregated using the AGGREGATE command has to be calculated every time the user requests that data even if you do not use the FORECALC keyword in the AGGREGATE function. On the other hand, when you use STORE or SESSION, then any given cell of data is calculated only once because it is available in either the variable or the cache for the entire session. Therefore, the next time a user requests that data cell, the data is returned from the variable or the cache instead of being calculated on the fly, which results in faster query time for the user.
Frequently you do not want the data that is calculated using the AGGREGATE function to be stored permanently in the database because that would defeat the purpose of calculating data on the fly.
To ensure that the aggregated values cannot be permanently committed to the database, use SESSION.
Use STORE when you know either of the following is true which also ensures that the data that is calculated on the fly using the AGGREGATE function is not committed to the database:
The users of the analytic workspace can only open it as read-only
You know that the users of the analytic workspace will not or cannot issue UPDATE and COMMIT statements.
Note:
Use STORE with caution when it is likely that your users modify pre-computed data, and they access data that you have specified to be calculated on the fly using the AGGREGATE function. The problem is that any data that is calculated using the AGGREGATE function before the user's modification does not reflect the user's change unless the user made the change using an AGGREGATE function with the FORCECALC keyword or unless there is an $AGGREGATE_FORCECALC property on the variable being aggregatedFor examples of using a CACHE statement in an aggregation specification, see Example 9-13, "Using a CACHE Statement in an Aggregation Specification" and Example 9-14, "Populating All Levels of a Hierarchy Except the Detail Level".
Within an aggregation specification, a DIMENSION statement sets the status to a single value of a dimension. When an aggregation specification does not specify such single values with DIMENSION statements, Oracle OLAP uses the current status values of the dimensions when performing the aggregation.
You use a DIMENSION statement to ensure that the status of a dimension is set to the value that you want it to have for the aggregation. You must use a separate DIMENSION statement for each dimension that is not shared by the source, basis, and target objects.
Within an aggregation specification, a DROP DIMENSION statement specifies how non-hierarchical aggregation across variables is performed. You use this statement in aggregation specification when you are aggregating the detail data from one variable (the source variable) into another variable (the target variable) and you want to aggregate across a non-hierarchical dimension of the source variable. In this case, the target variable has one less dimension (the "dropped" dimension) than the source variable because the values of the source variable associated with this dimension are aggregated to populate the target variable.
DROP DIMENSION dimname [VALUES {valsetname|ALL} OPERATOR operation [ARGS argument]
where argument is one or more of the following phrases:
DIVIDEBYZERO {YES|NO}
DECIMALOVERFLOW {YES|NO}
NASKIP {YES|NO}
WEIGHTBY [WNAFILL {number|NA}] wobj
The name of a dimension in the source variable that contains the detail data.
Sets the status of dimname during the aggregation.
The name of a valueset object that determines the status of the dimension specified by dimname.
Specifies that all of the values of dimname are in status.
Identifies the calculation method used to aggregate the data.
A keyword that describes the type of aggregation to perform. The keywords are listed in Table 9-1, "Aggregation Methods".
Indicates optional handling of the aggregation.
Specifies whether to allow division by zero. Specify YES to allow division by zero which means that a statement involving division by zero executes without error but produces NA results. Specify NO to disallow division by zero which means that a statement involving division by zero stops executing and produces an error message. The default value is the current value of the DIVIDEBYZERO option.
Specifies whether to allow decimal overflow, which occurs when the result of a calculation is very large and can no longer be represented by the exponent portion of the numeric representation. Specify YES to allow overflow, which means that a calculation that generates overflow executes without error and produces NA results. Specify NO to disallow overflow which means that a calculation involving overflow stops executing and generates an error message. The default value is the current value of the DECIMALOVERFLOW option.
Specifies whether NA values are input. Specify YES when you want Oracle OLAP to ignore NA values when aggregating which means that only actual values are used in calculations. Specify NO when you want Oracle OLAP to consider NA values when aggregating which means that when any of the values being considered are NA, the calculation returns NA. The default value is the current value of the NASKIP option.
The value that you specify for the NASKIP phrase does not effect calculation performed when you specify HAVERAGE, HFIRST, HLAST, HWAVERAGE, HWFIRST, HWLAST for operation.
Indicates that weighted aggregation is to be performed. You must include a WEIGHTBY clause when you specify HWAVERAGE, HWFIRST, HWLAST, SSUM, WAVERAGE, WFIRST, WLAST, or WSUM for operation. The WEIGHTBY phrase always includes a wobj argument and can optionally include the WNAFILL keyword. For more information about the use of the WEIGHTBY phrase, see the RELATION (for aggregation) statement of the AGGMAP command.
Indicates handling for NA values. The default values for WNAFILL vary depending on the value of operation. For more information about using the WNAFILL phrase, see the RELATION (for aggregation) statement of the AGGMAP command.
Substitutes a number for every NA value. That number replaces every NA value in the weight object, weight formula, or weight relation.
0.0 is the default for HWAVERAGE and SSUM.
1.0 is the default for HWFIRST, HWLAST, WAVERAGE, WFIRST, WLAST, and WSUM.
Specifies that NA values are to be specified as NA. NA is the default for OR.
A variable, formula, or relation that provides the weighted values. It can be numeric or BOOLEAN. When wobj is BOOLEAN, then TRUE has a weight of 1.0 and FALSE has a weight of 0.0. A formula is queried only when needed, depending on the dimensionality of the formula and the variable being aggregated. When wobj is a relation, it should be a one-dimensional self-relation. For more information about specifying values for wobj, see the RELATION (for aggregation) statement of the AGGMAP command.
For an example of using a DROP DIMENSION statement in an aggregation specification, see Example 9-15, "Aggregating into a Different Variable".
Within an aggregation specification, a MEASUREDIM statement identifies the name of a measure dimension that is specified in the definition of an operator variable or an argument variable.
The name of the measure dimension. A measure dimension is a dimension that you define. The dimension values are names of existing variables.
Note:
You cannot specify a measure dimension when it is included in the definition of the aggmap object.The following statement defines a dimension named MEASURE.
DEFINE measure DIMENSION TEXT
Populating a Measure Dimension
Once you have defined a measure dimension, you can then use a MAINTAIN statement to add dimension values to the MEASURE dimension.
The following statement adds the names of the sales, units, price, and inventory variables to measure as its dimension values.
MAINTAIN measure ADD 'sales', 'units', 'price', 'inventory'
Using a Measure Dimension with an Operator Variable
The purpose of using measure dimensions is to take advantage of the flexibility of using non-additive aggregation operators. You can use measure dimensions in the definition of operation variables or argument variables.
The following statements show how to define an operator variable named opvar and populate it.
DEFINE opvar TEXT <measure> opvar (measure 'sales') = 'SUM' opvar (measure 'inventory') = 'HLAST'
For an example of an aggregation specification that includes a MEASUREDIM statement, see Example 9-16, "Using a MEASUREDIM Statement in an Aggregation Specification".
Within an aggregation specification, a MODEL statement executes a predefined model.
A text expression that contains the name of a predefined MODEL object.
Specifies whether the model is a static (precomputed) model or a dynamic model.
PRECOMPUTE ALL is the default and specifies a static model. The following conditions must be met:
Any RELATION or MODEL statements that precede it in the aggregation specification must also be specified as PRECOMPUTE ALL.
Any RELATION or MODEL statements that follow it in the aggregation specification can either be specified as PRECOMPUTE ALL or PRECOMPUTE NA.
PRECOMPUTE NA specifies a dynamic model. The following conditions must be met for run-time execution of the model:
All RELATION statements in the aggregation specification must appear before the MODEL statements specified as PRECOMPUTE NA.
Any additional MODEL statements that follow it in the aggregation specification must also be specified as PRECOMPUTE NA.
Dynamic Models and Non-Additive Operators
Model statements are executed in the order that they are coded within the aggregation specification. Typically, when the order of execution matters to the result, MODEL statements follow the corresponding RELATION statement.
Because the order of RELATION statements that use non-additive operators (for example, MAX) effects the result of the calculation and because dynamic models (that is, MODEL statements that include a PRECOMPUTE NA phrase) must follow all RELATION statements, the use of dynamic models with non-additive operators is somewhat constrained.
For an example of using a model in an aggregation specification, see Example 9-17, "Solving a Model in an Aggregation".
Within an aggregation specification, a PRECOMPUTE statement specifies which of the variable's aggregate values are calculated only with the AGGREGATE command.
Note:
An aggregation specification that has a PRECOMPUTE statement cannot have any PRECOMPUTE clauses in its RELATION statements.PRECOMPUTE precompute-phrase
where precompute-phrase is one of the following:
Specifies an explicit percentage of the aggregate variable values that are aggregated as a database maintenance procedure using an AGGREGATE command. Oracle OLAP uses special functionality called the Aggregate Advisor to determine exactly which values are in the percentage.
Specifies that Oracle OLAP uses the Aggregate Advisor to determine how many and which aggregate variable values to aggregate as a database maintenance procedure using an AGGREGATE command.
Specifies that all aggregated data is precomputed using an AGGREGATE command.
Specifies that all values should be calculated on the fly using the AGGREGATE function (that is, that no data should be precalculated with the AGGREGATE command).
Within an aggregation specification, a RELATION statement specifies how data is aggregated across a hierarchical dimension. Frequently, an aggregation specification contains one RELATION statement for each of the hierarchical dimensions of a variable.
Note:
Do not confuse this RELATION statement which can only be used as part of an AGGMAP command with either the RELATION command that defines a default relation for a dimension or the RELATION statement that is used as part of an ALLOCMAP command.RELATION rel-name [(valueset...)] -
[PRECOMPUTE (precompute-phrase)] -
[OPERATOR {operation|opvar}] -
[PARENTALIAS dimension-alias-name] -
[ARGS {argument|argsvar}] -
[LOAD_STATUS(status-valueset-name)]
where:
precompute-phrase is one or more of the following:
argument is one or more of the following:
argsvar is a text variable that contains argument phrases for some or all dimension values.
A relation that defines a hierarchy by identifying the parent of every dimension value in a hierarchy.
Sets the status of one or more dimensions for the duration of the aggregation. It overrides the current status.
Indicates that some dimension values are populated only with the AGGREGATE command. The PRECOMPUTE clause of the RELATION statement limits the data that is aggregated by the AGGREGATE command. In its simplest form, you can think of the PRECOMPUTE clause as working like a LIMIT dimension TO statement. Notice that the default limit is on the dimension, which is not explicitly named in the RELATION statement.
Note:
An aggregation specification has PRECOMPUTE clauses in any of its RELATION statements cannot also have a PRECOMPUTE statement. Additionally, you cannot specify a PRECOMPUTE phrase for a RELATION statement for a compressed composite.Specifies an explicit percentage of the aggregate variable values that are aggregated as a database maintenance procedure using an AGGREGATE command. Oracle OLAP uses special functionality called the Aggregate Advisor to determine exactly which values are in the percentage.
Specifies that Oracle OLAP uses the Aggregate Advisor to determine how many and which aggregate variable values to aggregate as a database maintenance procedure using an AGGREGATE command.
A list of one or more values of dimension.
For all dimensions except those with INTEGER or NUMBER values, the positions of the dimension values that you want precomputed. Specify the positions using INTEGER values, separated by commas.
The name of a valueset. When you include this argument, only data that is dimensioned by the dimension values in the valueset should be precalculated with the AGGREGATE command. The rest of the values can be calculated on the fly.
Note that the current status of a dimension can also limit the data that is precalculated. See the AGGREGATE command for details.
Specifies that data should be precalculated for all dimension values.
Specifies that all values should be calculated on the fly using the AGGREGATE function (that is, that no data should be precalculated with the AGGREGATE command).
Specifies the levels of the dimension to be precomputed. For level-relation-name, specify, as a TEXT value, the name of the relation object that relates the values of the dimension to the names of the levels of the dimension. For level-name, specify, as TEXT values, the name of one or more levels using the same level names used in level-relation-name.
Identifies the calculation method used to aggregate the data.
A keyword that describes the type of calculation to perform. The keywords are listed in Table 9-1, "Aggregation Methods" and can be retrieved by issuing an AGGROPS statement. You can specify a fixed-length three-character abbreviation for the keywords by specifying only the first three characters.
| Keyword | Description |
|---|---|
|
AND |
When any child data value is |
|
AVERAGE |
Adds data values, then divides the sum by the number of data values that were added. When you use AVERAGE, there are special considerations described in "Average Operators". |
|
FIRST |
The first non- |
|
HAVERAGE |
(Hierarchical Average) Adds data values, then divides the sum by the number of the children in the dimension hierarchy. Unlike AVERAGE, which counts only non- This keyword is not affected by the setting of the NASKIP option for argument. |
|
HFIRST |
(Hierarchical First) The first data value that is specified by the hierarchy, even when that value is This keyword is not affected by the setting of the NASKIP option for argument. |
|
HLAST |
(Hierarchical Last) The last data value that is specified by the hierarchy, even when that value is This keyword is not affected by the setting of the NASKIP option for argument. |
|
HWAVERAGE |
(Hierarchical Weighted Average) Multiplies non- When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object. This keyword is not affected by the setting of the NASKIP option for argument. |
|
HWFIRST |
(Hierarchical Weighted First) The first data value that is specified by the hierarchy multiplied by its corresponding weight value, even when that value is When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object. This keyword is not affected by the setting of the NASKIP option for argument. |
|
HWLAST |
(Hierarchical Weighted Last) The last data value that is specified by the hierarchy multiplied by its corresponding weight value, even when that value is When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object. This keyword is not affected by the setting of the NASKIP option for argument. |
|
LAST |
The last non- |
|
MAX |
The largest data value among the children of any parent data value. |
|
MIN |
The smallest data value among the children of any parent data value. |
|
NOAGG |
Do not aggregate any data for this dimension. |
|
OR |
When any child data value is |
|
SSUM |
(Scaled Sum) Adds the value of a weight object to each data value, then adds the data values. When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object. |
|
SUM |
(Default) Adds data values. |
|
WAVERAGE |
(Weighted Average) Multiplies each data value by a weight factor, adds the data values, and then divides that result by the sum of the weight factors. When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object. |
|
WFIRST |
(Weighted First) The first non- When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object. |
|
WLAST |
(Weighted Last) The last non- When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object. |
|
WMAX |
(Weighted Maximum) The largest data value among the children of any parent data value multiplied by its corresponding weight value. When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object. |
|
WMIN |
(Weighted Minimum) The smallest data value among the children of any parent data value multiplied by its corresponding weight value. When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object. |
|
WSUM |
(Weighted Sum) Multiplies each data value by a weight factor, then adds the data values. When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object. |
A TEXT variable that you define that specifies a different the operation for each of its dimension values.
Note:
Not valid for variables dimensioned by compressed composites.The opvar argument is used in two ways:
Measure dimension -- Changes the aggregation method depending upon the variable being aggregated. Changing the aggregation method based on the variable being aggregated is useful when a single aggmap is used to aggregate several variables that must be aggregated with different methods. Whether you pre-aggregate all of the measures in a single AGGREGATE command or in separate statements, AGGREGATE uses the operation variable to identify the calculation method. The values of the measure dimension are the names of the variables to be aggregated. It dimensions a text variable whose values identify the operation to be used to aggregate each measure. The aggregation specification must include a MEASUREDIM statement that identifies the measure dimension. See Example 9-21, "Aggregating Using a Measure Dimension".
Line item dimension -- Changes the aggregation method depending upon the line item being aggregated. The line item dimension is typically non-hierarchical and identifies financial allocations. The line item dimension is used both to dimension the data variable and to dimension a text variable that identifies the operation to be used to aggregate each item. The operation variable is typically used to aggregate line items over time. You do not use the MEASUREDIM statement in the aggmap. See Example 9-22, "Aggregating Using a Line Item Dimension".
The opvar argument cannot be dimensioned by the dimension it is used to aggregate. For example, when you want to specify different operations for the geography dimension, then opvar cannot be dimensioned by geography.
To minimize the amount of paging for the operator variable, define the operation variable as type of TEXT with a fixed width of 8.
Specifies that an alias dimension for the dimension being aggregated is QDRd to the parent value currently being aggregated.
The name of the alias dimension for the dimension of rel-name.
Indicates optional handling of the aggregation.
Specifies whether to allow division by zero.
YES allows division by zero; a statement involving division by zero executes without error but produces NA results.
NO disallows division by zero; a statement involving division by zero stops executing and produces an error message.
The default value is the current value of the DIVIDEBYZERO option.
Specifies whether to allow decimal overflow, which occurs when the result of a calculation is very large and can no longer be represented by the exponent portion of the numeric representation.
YES allows overflow; a calculation that generates overflow executes without error and produces NA results.
NO disallows overflow; a calculation involving overflow stops executing and generates an error message.
The default value is the current value of the DECIMALOVERFLOW option.
Specifies whether NA values are input.
YES specifies that NA values are ignored when aggregating. Only actual values are used in calculations.
NO specifies that NA values are considered when aggregating. When any of the values being considered are NA, the calculation returns NA.
The default value is the current value of the NASKIP option.
The value that you specify for the NASKIP phrase does not effect calculation performed when you specify HAVERAGE, HFIRST, HLAST, HWAVERAGE, HWFIRST, HWLAST for operation.
Indicates that weighted aggregation is to be performed. You must include a WEIGHTBY clause when you specify HWAVERAGE, HWFIRST, HWLAST, SSUM, WAVERAGE, WFIRST, WLAST, or WSUM for operation. The WEIGHTBY phrase always includes a wobj argument and, optionally, can include the WNAFILL keyword.
Indicates handling for NA values. The default values for WNAFILL vary depending on the value of operation. The default value for HWAVERAGE and SSUM is 0.0. The default value for OR is NA. The default value for the other operators is 1.0. WNAFILL defaults for each operator in an aggregation specification. In other words, when one RELATION statement includes a WSUM OPERATOR, then WNAFILL defaults to 1.0. When the next RELATION statement includes an SSUM OPERATOR, then WNAFILL defaults to 0.0, and so on. See "Using WNAFILL".
A variable, formula, or relation that provides the weighted values. It can be numeric or BOOLEAN. When wobj is BOOLEAN, then TRUE has a weight of 1.0 and FALSE has a weight of 0.0. A formula is queried only when needed, depending on the dimensionality of the formula and the variable being aggregated. When wobj is a relation, it should be a one-dimensional self-relation. See Using Weighted Aggregation Methods for more information about specifying values for wobj.
YES specifies that when Oracle OLAP aggregates a variable using this relation that it also populates the Aggcount variable associated with that variable. For more information on Aggcount variables, see "Aggcount Variables".
NO specifies that when Oracle OLAP aggregates a variable using this relation that it does not populate the Aggcount variable associated with that variable. For more information on Aggcount variables, see "Aggcount Variables".
A TEXT variable that contains the argument options for some or all dimension values.
Specifies that, for the aggregation, Oracle OLAP consider the values specified by status-valueset-name as the detail or lowest level of the hierarchy.
A previously-defined valueset that specifies the lowest-level values to have in status when performing the aggregation. When performing any aggregation using an aggmap with a RELATION statement with this clause, Oracle OLAP temporarily sets the status of the dimension to the values specified by status-valueset-name and their ancestors. The valueset specified by status-valueset-name must be a single dimensional valueset for the relation dimension (not the hierarchy dimension). Additionally, the valueset specified by status-valueset-name cannot contain both a value and an ancestor of that value.
Ordering RELATION Statements with Non-Additive Operators
The order of RELATION statements that use non-additive operators effects the result of the calculation. For example the max of sum is not generally equal to the sum of max. Consequently, the order of RELATION statements within an aggregation specification must follow the logical requirements of the calculation. This logical necessity limits the use of dynamic models within an aggregation as discussed in "Dynamic Models and Non-Additive Operators".
RELATION Statements for Compressed Composites
When designing the aggregation specification, follow these guidelines when coding RELATION statements for compressed composites:
The HAVERAGE, HWAVERAGE, HWFIRST, HWLAST, SSUM, WAVERAGE, WFIRST, WLAST, WMAX, WMIN, and WSUM operators cause data values to change with each level of aggregation, regardless of sparsity. When possible, to insure the largest amount of overall compression, place RELATION statements with these operators at the beginning of your aggregation specification before RELATION statements that use an AND, AVERAGE, FIRST, HFIRST, HLAST, LAST, MAX, MIN, NOAGG, OR, or SUM operator.
To optimize the compression of a compressed composite, list similar operators contiguously if the calculation logic allows. For example, specifying MAX for the first dimension and then SUM for all the other dimensions results in better compression, and thus provides better calculation performance, than specifying SUM, MAX, and then SUM over the remaining dimensions.
SUM is the fastest and most compressible operator. Changing the aggregation operator for one or more dimensions from SUM to some other operator results in less compression, and therefore a larger variable, and the AGGREGATE command for that variable takes longer to complete.
When an AGGMAP contains a RELATION statement that specifies the AVERAGE operator, any variable using that aggregation specification must be defined using a DEFINE VARIABLE statement with a WITH AGGCOUNT phrase.
You can only specify a single aggregation operation. You cannot specify aggregation operations using an opvar variable.
You can use valuesets to:
Limit hierarchy dimensions. You can limit which hierarchies are used by the AGGREGATE command and AGGREGATE function and the order in which these hierarchies should be used. The valueset that you use specifies the names of a dimension's hierarchies. To use a valueset in this way, use the following syntax.
RELATION rel-name (valueset)
In this case, using valuesets provides a way to manage hierarchies that are in conflict with each other, meaning, when the same dimension value stores data for different children in different hierarchies (such as, Q1 stores data for Jan, Feb, and Mar in the Calendar hierarchy, but Q1 stores data for May, Jun, and Jul in the Fiscal hierarchy).
Specify which values should be calculated on the fly by the AGGREGATE function and which values should be pre-calculated by the AGGREGATE command. The valueset that you use specifies the names of dimension values. To use a valueset in this way, use the following syntax.
RELATION rel-name PRECOMPUTE (valueset)
In this case, you use the valueset that follows the PRECOMPUTE keyword.
When you use valuesets to limit hierarchy dimensions and when using multiple aggmaps and the hierarchies are inconsistent, you must also use the FORCECALC keyword in the AGGREGATE function or have set an $AGGREGATE_FORCECALC property on the variable to be aggregated.
When You Change a PRECOMPUTE or an OPERATOR Clause
Any time you make changes to a PRECOMPUTE or an OPERATOR clause, aggregate the variable data again and recompile the aggmap to produce accurate data.
Aggregating Data Loaded into Different Hierarchy Levels
When data is loaded into dimension values that are at different levels of a hierarchy, then you must be careful in how you set status in the PRECOMPUTE clause in a RELATION statement in your aggregation specification.
Suppose that a time dimension has a hierarchy with three levels: months aggregate into quarters, and quarters aggregate into years. Some data is loaded into month dimension values, while other data is loaded into quarter dimension values. For example, Q1 is the parent of January, February, and March. Data for March is loaded into the March dimension value. But the sum of data for January and February is loaded directly into the Q1 dimension value. In fact, the January and February dimension values contain NA values instead of data. Your goal is to add the data in March to the data in Q1.
When you attempt to aggregate January, February, and March into Q1, the data in March simply replaces the data in Q1. When this happens, Q1 contains only the March data instead of the sum of January, February, and March.
To aggregate data that is loaded into different levels of a hierarchy, create a valueset for only those dimension values that contain data.
DEFINE all_but_q4 VALUESET time LIMIT all_but_q4 TO ALL LIMIT all_but_q4 REMOVE 'Q4'
Within the aggregation specification, use that valueset to specify that the detail-level data should be added to the data that exists in its parent, Q1, as shown in the following statement.
RELATION time.r PRECOMPUTE (all_but_q4)
There are several issues involved in using the AVERAGE, HAVERAGE, WAVERAGE, and HWAVERAGE operators:
Oracle OLAP needs a separate INTEGER variable in which it stores the non-NA counts of the number of leaf nodes that contributed to aggregate values to calculate average values. When you want to aggregate a variable using one the average operators, include the WITH AGGCOUNT phrase in the DEFINE VARIABLE statement for the variable.
Accuracy when averaging—All decimal data is converted to floating point format, both for storing and for calculations, consequently, in some cases, an average aggregation computed on a DECIMAL or SHORTDECIMAL variable can differ in the least significant digits from a result you compute by hand. For this reason, you might want to use the NUMBER data type when accuracy is more important than computational speed, such as variables that contain currency amounts. See "Numeric Expressions" for more information.
Using Average operators when aggregating using an AGGREGATE command—When you use an average operator with the PRECOMPUTE keyword, the best practice is to use variables that have a decimal or NUMBER data type to ensure the accuracy of the results.
Using Average operators for partial aggregations—When you use an average operator in a partial aggregation, then you must always aggregate using the same INTEGER variable (that is, Aggcount or Countvar variable). Do not change the values that are stored in this INTEGER variable between aggregations. Finally, the number of INTEGER variables must match the number of variables that are being aggregated.
HAVERAGE, HFIRST, HLAST, AND HWAVERAGE Operators
The "hierarchical" operators (HAVERAGE, HFIRST, HLAST, AND HWAVERAGE) are intended to provide an alternative form of NA handling.
FIRST, HFIRST, LAST, AND HLAST Operators
These operators rely on the existing order of the dimension values, which are assumed to be the default logical order of that dimension. For example, in a month dimension, it is assumed that February follows January, March follows February, and so on.
When you must change the default order, use the MAINTAIN statement to do so. For example, suppose Q1 includes January, February, and March, but you must make February the last month in the Q1 instead of March. Use the following statement to do so.
MAINTAIN time MOVE 'Feb01' AFTER 'Mar01'
Now, the LAST operator assumes that FEB01 is the last month in Q1.
When you change the read permission to rel-name in a RELATION statement, then you must recompile the aggmap before using it with the AGGREGATE function. Compilation is not an issue when you use the AGGREGATE command, because the aggmap is recompiled automatically. However, when you do not have read access to every rel-name in the aggmap, then attempting to use that aggmap results in an error message.
Using Weighted Aggregation Methods
When you use a weighted method of aggregation, you must define and populate an object that contains the weights. You identify the aggregation method in the OPERATOR clause and the weight object in the ARGS clause.
The weight object can be a variable, a formula, or a relation. Special considerations apply depending on the type of object. the data type of the weight object, and whether or not you are performing a partial aggregation.
Weight Object Considerations Based on Type of Object The following considerations apply depending on the type of object that you use for the weight object:
When the weight object is a variable, you can define it with a numeric or BOOLEAN data type. Use a variable as your weight object when you want to pre-calculate weight values and commit them to the database. You can use a variable weight object with any weight option.
When the weight object is a relation, define it as a one-dimensional self-relation. You can use the weight object to specify that the weight for a specific cell is contained in the current variable at a different location. Use a relation as your weight object when you use a line item or a measure dimension. In this case, one line item is used as the weight to calculate the aggregate value of another line item. Using a relation enables you to specify another set of cells in the variable being aggregated as the weight values for a weighted operation.
When the weight object is a formula, that formula is queried only as often as needed, depending on the dimensionality of the formula and the dimensionality of the variable whose data is being aggregated. You can define the formula with a numeric or BOOLEAN data type. Use a formula as your weight object when you want to calculate weight values on the fly. A formula weight object is similar to a variable weight object, except that it cannot be aggregated. The value of a formula weight object is executed dynamically. Therefore, you cannot use a formula weight object with many of the weight options.
Considerations Based on Data Type of the Weight Object The following considerations apply when the weight object is numeric or BOOLEAN:
When the weight object has a numeric data type, It is good practice for the weight object variable to have the same dimensionality (or a subset thereof) as the variable to which it corresponds, but it is not required. When you use Oracle numbers or decimals to define your data variable, then always use the same data type to define the corresponding weight object. Otherwise, use the same data type for the weight object and the data variable unless you use WAVERAGE or HWAVERAGE; in this case, use a decimal or NUMBER data type to define the weight object.
When the weight object variable, formula, or relation that you define has a BOOLEAN data type, then TRUE represents a weight of 1.0 and FALSE represents a weight of 0.0. Furthermore, when an NA value is multiplied by any value, the result is NA.
Weight Object Considerations When Performing Partial Aggregations When you use any operators that require the WEIGHTBY phrase, and you are performing a partial aggregation, then do not change the values that are stored in the weight object between AGGREGATE commands.
For example, suppose you use the WSUM operator to perform currency conversion. The currency conversation rates are applied at the detail data level. Only the detail data needs to be converted, because the variable data is aggregated after the conversion. To get the correct results, all of the non-detail level weight values in the weight object would have to be 1. Although this strategy produces correct results, it is inefficient. The best practice is to use the default WNAFILL value of 1 which specifies that all NA values in the weight object should be treated as if they have a weight of 1. In this case, because the operator is WSUM, you do not have to include WNAFILL in the AGGREGATE command, because the default values are correct.
For example, the following statement causes the value 0.7 to be substituted for every NA value in the salesw weight object.
AGGREGATE sales USING sales.agg WEIGHTBY WNAFILL 0.7 salesw
When you do not want to specify a number to replace NA values, then you can use NA instead of a number, as shown in the following statement.
AGGREGATE sales USING sales.agg WEIGHTBY WNAFILL NA salesw
Specifying NA after WNAFILL has the following effect:
When the aggregation specification contains a WAVERAGE or a WSUM OPERATOR, then any child cell in the weight object that has an NA value is treated as an NA cell.
When the aggregation specification contains an SSUM OPERATOR, then the results depend on how the Oracle OLAP option NASKIP is set. When NASKIP is set to YES, then any NA value is treated as 0.0. However, when NASKIP is set to NO, then any NA value is treated as an NA cell.
Effects of Dimension Status on Aggregation
A RELATION statement only aggregates those source data values that are in status—whether you set the status using LIMI T statements or a LOAD STATUS clause on the RELATION statement. The parent values are calculated regardless of whether they are in status or not. For example, when only Jan01, Feb01, and Mar01 are in status for the time dimension, then Q1.01 is calculated (but no other quarters), and 2001 is calculated (but no other years) using only Q1.01 as input because the other quarters are NA. This functionality is useful when you want to aggregate just the new data in your analytic workspace.
Assume that there is a variable named sales that is dimensioned by time, a hierarchical dimension, and district, a non-hierarchical dimension.
DEFINE time DIMENSION TEXT
DEFINE time.parentrel RELATION time <time>
DEFINE district DIMENSION TEXT
DEFINE sales VARIABLE DECIMAL <time district>
REPORT DOWN time sales
-----------------------SALES-----------------------
---------------------DISTRICT----------------------
TIME North South West East
------------ ------------ ------------ ------------ ------------
1976Q1 168,776.81 362,367.87 219,667.47 149,815.65
1976Q2 330,062.49 293,392.29 237,128.26 167,808.03
1976Q3 304,953.04 354,240.51 170,892.80 298,737.70
1976Q4 252,757.33 206,189.01 139,954.56 175,063.51
1976 NA NA NA NA
For examples of aggregation specifications that include RELATION statements, see the examples in the AGGMAP command.