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.

YEAR COUNTRY

YEAR

COUNTRY

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

YEAR COUNTRY OBJECTID

YEAR COUNTRY CUSTOMERID

YEAR OBJECTID

YEAR CUSTOMERID

COUNTRY OBJECTID

COUNTRY CUSTOMERID

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.

YEAR COUNTRY OBJECTID CUSTOMERID

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

Product Family	Product	System	SAS Release
			Reported	Fixed*
SAS System	SAS OLAP Server	z/OS	9.1 TS1M0
		Microsoft® Windows® for 64-Bit Itanium-based Systems	9.1 TS1M0
		Microsoft Windows Server 2003 Datacenter 64-bit Edition	9.1 TS1M0
		Microsoft Windows Server 2003 Enterprise 64-bit Edition	9.1 TS1M0
		Microsoft Windows 2000 Advanced Server	9.1 TS1M0
		Microsoft Windows 2000 Datacenter Server	9.1 TS1M0
		Microsoft Windows 2000 Server	9.1 TS1M0
		Microsoft Windows 2000 Professional	9.1 TS1M0
		Microsoft Windows NT Workstation	9.1 TS1M0
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M0
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M0
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M0
		Microsoft Windows XP Professional	9.1 TS1M0
		64-bit Enabled AIX	9.1 TS1M0
		64-bit Enabled HP-UX	9.1 TS1M0
		64-bit Enabled Solaris	9.1 TS1M0
		HP-UX IPF	9.1 TS1M0
		Linux	9.1 TS1M0
		OpenVMS Alpha	9.1 TS1M0
		Tru64 UNIX	9.1 TS1M0

* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.

---

Type:	Usage Note
Priority:
Topic:	Data Management ==> Data Sources ==> Cubes System Administration ==> Performance Query and Reporting ==> OLAP (Online Analytical Processing) System Administration ==> Servers ==> OLAP

Date Modified:	2009-05-22 15:44:54
Date Created:	2009-05-13 09:51:54