Modifying and Maintaining Cubes

Tuning Cube Aggregations

SAS OLAP Cube Studio -- Tune Aggregations Function

After you have built a cube in SAS OLAP Cube Studio, you can add new aggregations to the cube. You can also fine-tune the existing aggregations that were built with the cube and delete any unnecessary aggregations. The Aggregation Tuning function enables you to generate new aggregations based on cube data cardinality or ARM log data. You can also manually build an aggregation, selecting the exact hierarchies and levels that you want to generate aggregations from. To modify a cube's aggregations, select the cube from the tree view and select Aggregation Tuning from the Actions menu or from the cube's context menu.

Aggregation Tuning dialog box

The Aggregation Tuning dialog box provides functionality to either automatically generate aggregation recommendations or to manually define cube aggregations. Aggregations that are defined with the Aggregation Tuning function are added to a list of any existing aggregations. This list of aggregations can then be reviewed and edited. You can modify performance options for aggregations and, if needed, you can remove any unnecessary aggregations. Finally, you can build the aggregations for the cube. You can also export the resulting PROC code to a text file.

The Aggregations table displays the aggregations for the cube. This includes both existing aggregations and newly created aggregations that can be built. You can select individual aggregations to edit, or you can select multiple, sequential rows of aggregations. The information displayed on the Aggregations table includes the following columns:

Status	This column indicates the current status of the individual aggregations.
Count	This is a statistic that is generated from the ARM log. It shows the number of queries that match the aggregation definition.
Total Time	This is a statistic that is generated from the ARM log. It shows the total wall time (for internally accessing the cube data) for queries that match the aggregation definition. The time value is displayed in hr : min : sec : millisec format.
Average Time	This is a statistic that is generated from the ARM log. It shows the average wall time (for internally accessing the cube data) for queries that match the aggregation definition. The time value is displayed in hr : min : sec : millisec format.

Note: If an aggregation already exists, it can have its performance values updated from the Arm log. [cautionend]

The Aggregation Tuning Dialog also contains the following options:

Export Code: This option opens the Export Code dialog box. You can preview and save to a text file the PROC OLAP code that is used to update the cube aggregations. The aggregations that are new, modified, or dropped in the Aggregations table are written to the text file. This button is inactive until you add a new aggregation to the Aggregations table or drop an existing aggregation from the table.
Update Aggregations: This option enables you to update the aggregations that are listed as new or modified in the Aggregations table. When Update Aggregations is selected, the cube is updated to include the new aggregations and changes to any existing aggregations. This button is inactive until you add a new aggregation to the Aggregations table, modify the name of an existing aggregation, or drop an existing aggregation from the table. The Aggregations Tuning dialog box closes after each build.

In the Aggregations Tuning dialog box, you can select one of the following methods to define aggregations for a cube:

ARM Log

ARM analysis is used to monitor and diagnose the performance of various SAS applications. It enables you to measure and record application performance and query response times in a designated log file. When using an ARM log to perform ARM analysis of cube aggregations, you can select either of the following options on the ARM :

Create aggregation recommendations and update existing performance values based on the ARM log.: You can add aggregations from the ARM log and update the statistics for the aggregations that have entries in the ARM log. This is the default option.
Update performance values based on the ARM log.: You can update the statistics for aggregations that have entries in the ARM log. This option is used when you want to examine the information in the ARM log and verify that the existing aggregations are being used. This can help determine which aggregations to drop.

The Analyze button opens the Analysis Recommendations dialog box. From here you can select one or more recommended aggregations. The aggregations you select are added to the bottom of the Aggregations table as highlighted rows. The Analyze button is inactive until text is entered in the Enter an ARM log file field.

For ARM log analysis, the generated aggregations list displays all aggregations that can be determined from the cube query data that is provided in the ARM log. If you select all of the generated aggregations and add them to the Aggregations table, you cannot generate further aggregations from that same ARM log. If you reselect the Analyze button on the ARM Log tab, you receive a message stating: "The ARM analysis did not recommend any additional aggregations to add to the cube".

Note: For further information about ARM logging, see "SAS OLAP Server Monitoring and Logging" in the SAS Intelligence Platform: System Administration Guideand the SAS Interface to Application Response Measurement (ARM): Reference. [cautionend]

Cardinality

The Cardinality tab enables you to add aggregations that are recommended based on the relative cardinality (number of members) of the cube levels. This method of adding aggregations is used when a cube is first created and before an ARM log can be generated. For each aggregation build, the cardinality algorithm generates up to 100 aggregation recommendations that are based on the cardinality ratio between the cube levels. The aggregations with the highest cardinality are recommended. All dimensions are included in the analysis, and any dimension of type TIME has up to the first two of its levels included on each aggregation.

Manual

The Manual tab enables you to select the exact hierarchies and levels that you want to generate aggregations from. On the Manual tab, the hierarchies for the cube are listed individually as columns. Levels for the hierarchies are numerically listed in drop-down lists on the columns. The default selection value of each column is None. When you select an individual level from a hierarchy, you are selecting that level and its parent levels.

Using PROC OLAP to Tune Aggregations

To modify an aggregation through PROC OLAP, use the DROP_AGGREGATION statement to delete the aggregation, and then use the AGGREGATION statement to define the new aggregation.

DROP_AGGREGATION level-name1 < level-name2 ...level-nameN > / NAME='aggregation-name' ;
AGGREGATION level-name1 / < NAME='aggregation-name' > ;

For more information about the DROP_AGGREGATION and AGGREGATION statements, see The OLAP Procedure.

Monitoring OLAP Server Performance

SAS OLAP Server performance is monitored and logged with the Application Response Measurement (ARM) interface. The ARM interface provides built-in logging capabilities and generates log records that indicate query content and query start and completion times. From this data, information regarding aggregation usage, individual query response times, or throughput can be determined. Traditionally, ARM enables system administrators to monitor application executions, run times, performance, and completion. SAS OLAP Server uses ARM to monitor the following:

application behavior
user behavior and usage
server loads
cube optimization (query response time)
cube metrics--counts of connections and queries

For more information, see "Monitoring Performance Using Application Response Measurement (ARM)" in SAS Language Reference: Concepts.

Top of Page