articles >> business intelligence >> Optimizing Microsoft SQL Server Analysis Services: MDX ...

Optimizing Microsoft SQL Server Analysis Services: MDX Optimization Techniques: Segregating DISTINCT COUNT

By : William E. Pearson, III Apr 22, 2004

Page 3 / 12


The calculated member Distinct Customers embodies the heavy lifting in the query. We used the following definition (within the AS clause string for calculated member Distinct Customers):

 

 

'COUNT(CrossJoin({[Unit Sales]},    Descendants ([Customers].CurrentMember,    [Customers].[Name])), ExcludeEmpty)'

 

to count the non-null Sales / Customer member tuples that it found, thereby deriving the number of customers. Because we wish to avoid counting all customer names (the lowest level of the Customer hierarchy), regardless of our level position in the hierarchy, we inserted the Descendants() function shown; this forces a limitation upon the count to solely the customers under the current member of the Customers dimension.

2. Execute the query using the Run Query button.

The results dataset appears as partially shown in Figure 3.

Figure 3: The Results Dataset (Partial View)

The first thing that we notice, after clicking Run Query, is that this query takes a little longer to run than many of the “sample” queries we have created in past articles. As a matter of fact, this is exactly the observation that I am hoping even those new to MDX in general will make. They query provides the data to meet the requirements of the information consumers, but performance could become a problem.

 

The overhead generated in the query is due to the requirement for MSAS to perform a runtime assessment of each customer member, and there are many members. While this overhead may not be unduly troublesome from the perspective of our sample data, performance will be degraded far more within the context of the sizes of member populations that exist in many production environments: the performance degradation we have witnessed in our tiny sample cube is extrapolated to those larger populations, to an extent that becomes very real to analysts and other information consumers that rely upon the system to provide data in a reasonable response time.

3. Save the query as SSP11-1 in a convenient location.

4. Close the Sample Application.

Our next step will be to examine an option for mitigating the performance hit suffered within the "straightforward" application of DISTINCTCOUNT() within our query.