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

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

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

Page 4 / 6


22. Click No to skip designing aggregations at present.

The Select the Processing Method dialog appears.

Full Processing is the default, and only option, as the Warehouse cube has not been processed since the structural change we have made to it.

The Select the Processing Method dialog appears, as depicted in Figure 11.

Figure 11: The Select the Processing Method Dialog

23. Leaving settings at default, click OK.

Processing begins, and runs rapidly, as evidenced by the Process viewer’s presentation of processing log events in real time. The Processing cycle ends, and the success of the evolution is indicated by the appearance of the Processing Completed Successfully message (in green letters) at the bottom of the viewer, as shown in Figure 12.

Figure 12: Indication of Successful Processing

24. Click Close.

We are returned to the Cube Editor. We can now browse the data and see our new Distinct Count measure in action.

25. Click the Data tab, if necessary.

On the refreshed Data View, data appears in the default formation, ready for our manipulations and review. A portion of the Data View, depicting the Warehouse Profit and new Product Count measures, appears in Figure 13.

Figure 13: Warehouse Profit and Product Count Measures in the Data View

Now that we have a credible result set with which to compare, let’s take a look at replicating the same results using MDX. We can leave the Data View as it is, for easy referral against our next results dataset, which we will generate independently within the MDX Sample Application.

Rendering Distinct Counts Using MDX

We now have a set of “answers” that we can attempt to replicate in direct MDX. Let’s initialize the MDX Sample Application, as a platform from which to perform our practice exercises, taking the following steps:

1. Start the MDX Sample Application.

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

Figure 14: The Connect Dialog for the MDX Sample Application

The illustration 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 Warehouse cube in the Cube drop-down list box.

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

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