Providing software solutions since 1976

• Log in
• Create profile
• Welcome
  [Log out]
• Edit profile

• Print  |
• Feedback  |
• Share

  • E-mail

  
  
  

  E-mail This Page

  Your Name:*
  Your E-mail:*
  Recipient E-mail:*
  Send a copy to me
  Personal Message:
  
  

  * indicates a required field

Overview

When using NUNIQUE measures in your cube, it is very important to have a well-tuned cube. Because of the typically high cardinalities involved with NUNIQUE, disk I/O becomes a measurable factor. Reading data from aggregations that have more rows than necessary can slow down your queries.

The best way to find out which aggregations your cube needs is to run a set of representative queries, and use the Advanced Aggregation Tuning plug-in in SAS^® OLAP Cube Studio to help you identify and add the required aggregations.

In SAS^® 9.1, for cubes with NUNIQUE measures, an additional step is required. Currently, the ARM log does not report aggregations that are used by the data requests that the SAS OLAP server generates to retrieve the NUNIQUE information. For more information, see Problem Note 016145.

Beginning in SAS 9.2, the ARM log will report the additional aggregations that would benefit the NUNIQUE query, so the steps outlined below will not be necessary.

Example

In order to understand how to optimally tune your cube for use with NUNIQUE measures, consider the following example.

You have two NUNIQUE measures defined in your cube, based on the levels ObjectID and CustomerID, respectively.

The ARM log tells you that you need the following aggregations.

For an optimally tuned cube, you need to create the above aggregations, plus these aggregations.

In other words, for each aggregation in your cube, create an additional aggregation that includes one of the columns that each of your NUNIQUE measures is based on.

For this example, it is not helpful to add an aggregation that adds both NUNIQUE columns.

Adding these additional aggregations to improve NUNIQUE queries will increase build time and the disk footprint of your cube. However, this is the best way to see measurable query-time improvements.

Explanation

All MDX queries internally generate one or more subqueries against the aggregation store. For each NUNIQUE measure, you will have a separate subquery. In the example, which uses two NUNIQUE measures, the server does a COUNT DISTINCT for ObjectID, and then it does a COUNT DISTINCT for CustomerID. Those are two different subqueries that cannot be combined. Therefore, the best aggregation to use for the distinct count for ObjectID is an aggregation that has all the categories for the current context, plus the ObjectID column.

The non-NUNIQUE measures are already optimally retrieved from an aggregation that has neither of the NUNIQUE columns.

Summary

To summarize, for any report that has both non-NUNIQUE and NUNIQUE measures, the SAS OLAP server will make multiple passes through the aggregations in order to build the result set. In the case of the example with two NUNIQUE measures plus an additional non-NUNIQUE measure included in the query, three passes of the data would be required. Therefore, to achieve an optimal query response, make sure the best aggregation for each pass is available.

Operating System and Release Information