![]() Previous |
![]() Next |
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.
(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.)
Syntax
AGGMAP [specification]
Parameters
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. |
Usage Notes
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.
Checking for Circularity
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.
Examples
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.
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 specification |
Define 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
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
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.
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)'
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 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. Since the indexes did not exist, Oracle OLAP never called the AGGREGATE function to compute the data to be calculated on the fly.
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
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.
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
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
since 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
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
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
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.
Using Valuesets
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')
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
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
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
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
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