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.
» 