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

Initial Approach via MDX

Let’s initialize the MDX Sample Application, the platform from which we perform many practice exercises within the articles of our series. (We choose it because any organization that has installed MSAS has access to the Sample Application). We will create our initial query by taking the following steps:

1. Start the MDX Sample Application.

We are initially greeted by the Connect dialog, shown in Figure 1.

Figure 1: The Connect Dialog for the MDX Sample Application

The figure above depicts the name of my server, MOTHER1, and properly indicates that we will be connecting via the MSOLAP provider (the default).

2. Click OK.

The MDX Sample Application window appears.

3. Click File ---> New.

A blank Query pane appears.

4. Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

5. Select the Sales cube in the Cube drop-down list box.

The MDX Sample Application window should resemble that depicted in Figure 2, complete with the information from the Sales cube displaying in the Metadata tree (left section of the Metadata pane).

Figure 2: The MDX Sample Application Window (Compressed View)

We will begin creating our query with a focus on returning results to meet the expressed business need of the information consumers. We will construct two calculated members / measures, one to contain the distinct count of the Customers, and one to calculate the average sale for each Product Category, per individual Customer. We will then SELECT the two calculated measures, presenting them to the immediate right of the Unit Sales measure for each respective Product Category.

We will retrieve a dataset with the measure / calculated measures forming the column axis, and the Product Category forming the row axis.

1. Create the following new query:

The above represents an attempt to meet the information consumers’ objectives with what appears to be the straightforward use of the DISTINCTCOUNT() function within a calculated member, to contain the count of the distinct Customers; we then create a second calculated member based upon the first, which we divide into the Unit Sales measure to derive the Average Sales per (individual) Customer, as requested by the intended audience. We SELECT all three into the desired matrix to render the desired presentation.