SSAS::::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

Creating Cube with SQL Server 2005 Analysis Services

Definitions::::
Before we dive into building a cube, we need to review some basic definitions. This will help to ensure that at the end you not only have a working cube but an idea of what the cube represents, as well as how to use it. The first two items we will define are Facts and Dimensions.

In the AdventureWorksDW database you will find tables prefixed with the letters ‘Dim’ and ‘Fact’. Ideally, any time you build something to be called a data warehouse you would be building your tables with these two concepts in mind. But what are they? Well, the simplest explanation is to think of them as nouns and verbs, and try not to reflect back to those frustrating hours spent in grammar class. Instead, try to forge ahead with a “Schoolhouse Rock” version of grammar by looking at an example of each.