| MDX Tip of the Month |
| Many multidimensional cubes have empty cells, which occur because a user didn't load data into the cube for these members. For example, if you inspect the Sales cube in the FoodMart sample, its creators didn't load any data for 1998. You must use the NON EMPTY modifier to write an MDX query that includes 1998: |
SELECT NON EMPTY {[Time].[1997], [Time].[1998]}
ON COLUMNS,[Promotion Media].[Media
Type].Members ON ROWS
FROM Sales |
|
An MDX Query
Let's look at an MDX query. Microsoft designed the following query to run against the data in the FoodMart sample database, which installs by default when you install OLAP Services. You can use the MDX sample application included with SQL Server 7.0 OLAP Services to view and edit the sample MDX query. (We used the sample data and cubes in FoodMart to create a common ground for us and you to work from. If you're new to OLAP, you'll get more out of this column if you run the queries against the live FoodMart cubes and work through the results as we discuss them.) Also, you can experiment using other OLAP client applications such as Knosys' ProClarity (http://www.knosysinc.com), which includes an MDX editor for creating complete MDX statements. Check out http://www.microsoft.com/industry/ bi/solutions/olap/olap.stm for a full list of OLAP-compatible front ends.
The following query is the first sample MDX query included with OLAP Services:
SELECT {[Measures].[Unit Sales]}
ON COLUMNS,
ORDER( EXCEPT( [Promotion Media].[Media
Type].members, { [Promotion
Media].[Media Type].[No Media]}),
[Measures].[Unit Sales], DESC) ON ROWS
FROM Sales
To uniquely identify a measure value in the cube, you must specify a member from every dimension in the cube. This combination of members, one from each dimension, is a tuple. This sample query references two of 13 dimensions in the Sales cube. This query references the [Unit Sales] member of the Measures dimension and also references the [Promotion Media] dimension.
If we reference only two dimensions, and we must specify a member from each dimension, how does OLAP Services fill the 11 missing members to complete the tuple? Each dimension has a default member. So if you specify {[Measures].[Unit Sales]} to be on a column and {[Promotion Media].[All Media].[Daily Paper]} to be on a row, these two members and 11 default members define the value of the cell in the cross section. Usually the default member is the All Member. The All Member is the single member at the root of the dimension hierarchy. The value of this member is the fully aggregated dimension. Some dimensions, such as the measures dimension, don't have an All Member, so the cube builder must specify the default member.
This query returns a two-dimensional result in rows and a column. It returns one column, Unit Sales, and several rows. These rows are all the members at the Media Type level in the Promotion Media dimension except No Media. The query returns these rows in descending order, sorted by Unit Sales. The Sales cube has a time dimension with no All Member. Two years, 1997 and 1998, are at the top of the hierarchy. The query uses 1997, because it is the default member of the time dimension.
In future columns, we'll discuss many OLAP and MDX functions. For now, we've given you background information about MDX and OLAP.
End of Article
Prev. page
1
[2]
next page -->
