Tuning Aggregations for a Cube

After you have built a cube, you can modify the aggregations for the cube with the Aggregation Tuning function in SAS OLAP Cube Studio. The Aggregation Tuning function enables you to either automatically generate aggregation recommendations or to manually define cube aggregations. In the SAS OLAP Cube Studio tree view, select a cube and select Aggregation Tuning from the Actions menu. In the Aggregation Tuning dialog box, you can select from three different methods of aggregation tuning:

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. On the Cardinality tab, click Analyze.

The Analysis Recommendations dialog box appears. A list of recommended aggregations is displayed. This list can contain up to 100 aggregation recommendations for the cube. Select the aggregation recommendations that you want to add to the Aggregations table. Click Select All to choose all of the aggregation recommendations. Select OK to execute the analysis. The selected aggregation recommendations are added to the list of aggregations in the Aggregations table.

In the Aggregation Tuning dialog box, the Aggregations table displays the newly recommended aggregations. In the Status column, the new aggregation symbol is listed for each new aggregation. The Method column lists the recommended aggregations as Cardinality type aggregations. You can scroll through the list of aggregations to analyze which aggregations to keep, edit properties for aggregations, or remove aggregations.

You can also view the levels that compose the aggregation recommendations. Select the Preview levels check box on the Aggregation Tuning dialog box. The Preview levels panel opens, displaying the levels for the currently selected aggregation recommendation.

In the Aggregation Tuning dialog box, you can select to edit or drop aggregations that are loaded with the cube. When you select an existing aggregation, the Drop button becomes active.

If you click Drop, the drop icon appears in the Status column for that aggregation. In addition, the Clear Drop button becomes active. This button enables you to clear the drop status for an aggregation. Those aggregations that are marked with the drop icon are dropped from the cube the next time you click the Build Aggregations button.

You can also review those aggregations that were recommended by cardinality analysis, and if needed delete them. When you select a newly recommended aggregation in the Aggregations table, the Delete button becomes active.

If you click Delete, a message dialog box appears. Select Yes to delete the selected aggregations. Select No to cancel the Delete function.

You can also modify the performance options for the cube aggregations. In the Aggregation Tuning dialog box, click Options. This opens the Performance Options dialog box. On the Default tab, you can set default performance option settings for all generated aggregations for the cube.

On the Aggregation tab, you can select an individual aggregation and change performance option settings for that specific aggregation. Select OK when you are finished changing the performance options. The Performance Options dialog box closes.

If needed, you can export the SAS code that is used to build the aggregations that are listed in the Aggregations table. Select Export Code on the Aggregation Tuning dialog box. The Export Code dialog box appears. Enter the path of the text file that you are exporting the code to. Click OK to create the file. The code is stored in a text file that you can further review, edit, and use to recreate the cube later.

When you have finished updating and modifying the aggregations listed in the Aggregations table, you can click Build Aggregations. The Aggregation Tuning function builds the recommended aggregations, and drops any existing aggregations that are marked with the drop icon. A build success confirmation message opens and the Aggregation Tuning dialog box closes. Click OK on the message box.

The Manual tab enables you to select the exact hierarchies and levels that you want use to generate aggregation recommendations from. The manual tuning option is used when you have dimension levels that are frequently used with other dimension levels. It is also used when a level has unique member counts (NUNIQUE) set.

Select the Manual tab on the Aggregation Tuning dialog box. The hierarchies for the cube are listed individually as columns. Levels for the hierarchies are numerically listed in drop-down lists on the columns. When you select an individual level from a hierarchy, you are selecting that level and its parent levels.

From the drop-down lists, select the levels that you want to use to create the aggregation recommendation. Both the Add to List and the Reset All buttons are enabled whenever one or more of the drop-down lists are changed to a selection other than the default selection of (None).

For this example, select the levels for Product_Category, Year. Select Add to List.

The aggregation recommendation that you created is listed in the Aggregations table. On the row for that aggregation, the levels that were manually selected for the aggregation are displayed and the new aggregation icon is listed in the Status column. When you are finished adding aggregation recommendations, click Build Aggregation to build the aggregations for the cube.

The ARM Log tab enables you to add aggregations that are based on the query analysis records that are stored in an Application Response Measuring (ARM) log. ARM analysis is recommended for optimal cube tuning. When the ARM API is used, a log file can be created that can be gleaned for query performance details. The ARM log is used to analyze query patterns and determine which aggregations to generate that will most likely have a positive impact on query performance. In order to create an ARM log, ARM logging must be turned on and queries must be performed against the cube. This function is only as effective as the amount of ARM data that is provided. Effectiveness also depends on whether the ARM log data truly reflects the future cube query patterns. For more information about creating the ARM log see “Using ARM to Monitor SAS OLAP Server Performance” in the SAS 9.2 Intelligence Platform: System Administration Guide.

Select the ARM Log tab and select the option Create aggregation recommendations and update existing performance values based on the ARM log. In the Enter an ARM log text field, enter the file path for the ARM log that you will use to generate aggregation recommendations. Use the Browse button to search for an ARM log if needed. After you have selected an ARM log file, click Analyze.

After you have selected Analyze, the Analysis Recommendations dialog box appears. A list of recommended aggregations is displayed. Select the aggregation recommendations that you want to add to the Aggregations table. Click Select All to choose all of the aggregation recommendations. Click OK to execute the analysis. The selected aggregation recommendations are added to the list of aggregations in the Aggregations table.

In the Aggregation Tuning dialog box, the aggregation recommendations are listed. The Method column displays the ARM log status for these aggregation recommendations. Select Build Aggregations to build the aggregations for the cube.

Alternately, you can also choose to update only the statistics for the existing aggregations that have entries in the ARM log. The Update performance values based on the ARM log 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. Select Update to execute the update.

The existing aggregations that are listed in the Aggregations table have been updated in the Count, Total Time, and Average Time columns. The Method column also lists Arm Log as the update method.