DistinctCount() vs NUNIQUE in OLAP 9.2


I was struggling with a calculated member that completes a distinctcount of members in a dimension, the performance was simply unacceptable. After attempting several other measures such as count(), processing changes on the source data, etc, I sent out a msg on Twitter asking for advice. Once again, why aren't we all in the twitterverse? In 2 hours, @wooly85 came back with a response pointing me to try the NUNIQUE function.

I hadn't thought about trying this in 9.2 (see additional note on 9.1.3 below). The 'Select Measures' GUI in the OLAP Cube Studio has a square for 'Unique Member Count Measures'.

Select 'Define' and choose the levels with unique member counts.

Reference: http://support.sas.com/documentation/cdl/en/olapug/59574/HTML/default/a003212364.htm

Additional Notes on 9.1.3 NUNIQUE: There were some known issues with this function in 9.1.3. Included are some of the references. “MDX queries containing NUNIQUE and no members from other dimensions may cause OLAP Server to crash” http://support.sas.com/kb/17/142.html and "“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”.

(Thanks again to @wooly85 and Zencos Consulting's Brian Miles for contributing to this.)


About Author

Angela Hall

Senior Technical Architect

Angela offers tips on using the SAS Business Intelligence solutions. She manages a team of SAS Fraud Framework implementers within the SAS Solutions On-Demand organization. Angela also has co-written two books, 'Building BI using SAS, Content Development Examples' & 'The 50 Keys to Learning SAS Stored Processes'.

Related Posts

Leave A Reply

Back to Top