Tuning Cube Aggregations

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.

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:

The Aggregation Tuning dialog box also contains the following options:

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

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 :

The Analyze button opens the Analysis Recommendations dialog box. From here you can select one or more recommended aggregations. The aggregations that 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”.

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.

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.