Using Sets in MDX Queries

About the Series ...

This is the sixth tutorial article of the series, MDX in Analysis Services. The series is designed to provide hands-on application of the fundamentals of MDX from the perspective of MS SQL Server 2000 Analysis Services ("Analysis Services,"); our primary focus is the manipulation of multidimensional data sources, using MDX expressions in a variety of scenarios designed to meet real-world business intelligence needs.

For more information on the series, as well as the hardware / software requirements to prepare for the tutorials we will undertake, please see Tutorial 1: MDX Concepts and Navigation.

Note: At the time of writing, Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples.

Introduction

In our last tutorial, we expanded upon the topics we introduced in our previous lesson, Retrieve Data from Multiple Cubes. Introducing the concept of time within the context of expression design, we worked through examples of providing support for time-based analysis, such as the quantification of change over time in values, with MDX functions designed for that purpose.

In this lesson, we will begin an exploration of MDX Sets. We will need to have a sound basis in MDX query fundamentals to do so, so we will examine the makeup and operation of MDX queries as a preparatory step. We will then move into our examination of MDX Sets. We will:

• Discuss the differences between MDX expressions and MDX query statements;
• Create rudimentary MDX queries to gain an understanding of their components and uses;
• Explore MDX sets, their functional uses, and the steps involved in their creation;
• Practice the use of set functions as a part of working with MDX sets.

Introducing MDX Queries

The first order of business in this tutorial will be to gain a good understanding of MDX query statements, and to understand how they differ from the multidimensional expressions with which we have worked through our last lesson. As we have noted in past tutorials, an expression calculates a single value. An expression can be used within various applications, among them Excel PivotTable Reports or an Office PivotTable List, to retrieve values from an OLAP data source. In contrast, a query is used to extract multiple values from a cube. Queries underlie the reporting that we might do in the PivotTable components, and are often largely invisible to us when we develop our reports visually with these and other tools.

We often juxtapose numerous expressions, and the values they retrieve, to obtain a multidimensional value for analysis. We will learn to create increasingly sophisticated MDX queries in this and prospective tutorials, and will be able to understand the underlying query components that are generated in the background for us when we create MDX expressions with visual tools. Knowing how to create queries will equip us to retrieve values to support the immediate reporting requirements of our organizations' information consumers, as well as to maintain those queries to meet changing demands, through the precise specification of dimensional criteria.

Key Concepts and Terminology

We will begin our exploration of MDX queries by emphasizing the concepts involved in controlling the values that we extract from our cubes within precisely defined levels of specific dimensions. Recall from previous lessons that a cube is a conceptually multidimensional structure; the intersect points / intersections of the dimensions are where data reside, in single or multiple elements called measures.

As we also discussed in previous lessons, MDX uses a reference system involving the tuples concept to identify and extract data, whether it be data in a single cell or a block of cells. Tuples list dimensions and their members (which include levels) to "address" individual cells, as well as sections of cells, within the cube, and, because any given cell is an intersection of all the dimensions in the cube, tuples can be used to uniquely identify every cell in the cube. As a means of reference, measures themselves are treated as a special sort of dimension, named Measures within Analysis Services.

Because tuples uniquely identify sections of the cube, based upon the dimensional intersections that define the section's "address," they have no need to refer to any specific cell or cells in doing so. They represent subsets of the multiple dimensions of the cube, and provide slices that encompass more than one cell.

We will work with ordered groups of tuples, referred to as sets, later in the lesson. Common examples of set usage include axis dimensions (the dimensions and members to be returned, specified in the SELECT statement in the query, as we will see) and slicer dimensions (the specific dimension and member criteria to which the returned data is restricted, used by the WHERE statement, as we will discover). The axis dimension exists to return data for multiple members, while the slicer dimension is used to return data for a single member. The axis and slicer dimensions work in conjunction to define, in terms of the source cube (identified in the FROM clause of the query), the subset of dimensions that make up the result cube, which itself can be composed of multiple dimensions. More than one dimension can reside on either of the two axes, as we shall soon see, and any given dimension can exist on either axis (but never both). This fact, coupled with the capabilities to move the dimensional members about at will within our reports, as well as with the capability to drill down and zoom on dimensional member levels, are what make multidimensional analysis possible in our reporting efforts.

Understanding the Metadata

As many of us are aware, MSSQL Server 2000 comes equipped with a tool called the Query Analyzer, which allows us to input a SQL statement, execute it, and see the results from a couple of different views. Similarly (in a simple sense), Analysis Services provides this capability via a sample application that is installed with the typical installation of the package. Also similar to its MSSQL Server counterpart, the MDX Sample Application provides a Metadata pane that outfits us with a means of interacting with cube objects visually. We can thus easily work with MDX expressions and build MDX queries. As we progress in our exploration of MDX queries, we will rely upon the MDX Sample Application to make writing queries easier, as well as to help us understand the structure of the information that our cube contains. This can be especially useful as a means of grasping the structure of a cube with which we are not familiar, and / or with which we had no design involvement.

In the Books Online, which are bundled with any typical installation of MSSQL Server 2000 / Analysis Services, or which can be accessed on the CD from which the installation is performed, Microsoft defines metadata as information about data, (or data about data, hence the term "metadata." ). Metadata concerns itself with data and its properties, such as data type (text, numeric, etc.), or column sizes. Metadata also comprises information surrounding data structures, or information concerned with the design or makeup of objects, such as cubes, dimensions, levels, and so forth. Analysis Services provides the MDX Sample Application to provide a vehicle from which new users can issue MDX queries against an Analysis server, as well as to illustrate (along with the underlying source code that is freely available) a working example from which developers can understand how to create custom applications for querying with MDX.