This article is intended to get you started on Multi-Dimensional eXpressions,

the Microsoft OLAP query language. MDX allows you to do complex reports quickly

on Very Large Databases. We have reports that run on many millions of records

with over 20 fields in under a second! OLAP is a powerful technology.

OLAP is a way of looking at data that has been totaled up, but allowing any

report on that data you require. 

Suppose you were logging sales in SQL server, you could have a table [sales] which had [value], [customer], [product], [timeofsale], [salesrep], [store], [county] etc.  If you wanted to know how many sales each sales rep had made you might use:
select s.[salesrep], count (*) as 'total' from [sales] s group by s.[salesrep] 

If you had a lot of sales this report would take a long time.  Suppose you

wanted to report on something else at the same time?  You might want to

see a cross-tab report of [salesrep] by [product].  This is messy to

write in SQL as you have to use case statements for each value of whichever

field you want on the columns.

What we may need to do is build all these totals at a time of low load and then

make them easily accessible for reporting all the time.  This is what

analysis services does. In OLAP we would build a cube of this [sales] table.  We call them cubes

because they can be visualized as such for simpler reports.  As a cube can

have up to 128 dimensions however this metaphor quickly breaks down. 

Supposing that we only want to report on 3 fields ([timeofsale], [salesrep] and

[product]); we could think of all the reports you could want on those fields as

a cube. Each edge of the cube would have all the values of a field along it,

and each face would be a cross tab of two of the fields.

An introduction to the rest of the terminology may be useful at this point:

Dimension

A dimension is one of the fields that you want to report on. Dimensions have a

tree structure, allowing complicated data to be reported on at different

levels. For instance [timeofsale] could be a dimension if you wanted to report

on it

Measure

What we are actually reporting on, be it sum, count or average.

Level

A level is a step along a dimension. In [timeofsale] we could have [Year],

[Month], [Day] and [Hour] allowing us to report on sales per hour or products

year on year.

Member

A member is a value of a level. In [timeofsale].[Year] we might have [2001],

[2002], [2003], or in [timeofsale].[Month] we have [March], [April], [May] etc

Axis

This is what you set to put [product] across the columns, [timeofsale] down the

rows or report on a [salesrep] to a page. An MDX statement can have up to 128

axis, although it is rare to use more than 2. The first 5 have names:

0 Columns

1 Rows

2 Pages

3 Chapters

4 Sections

Dimensionality

This is an attribute of a collection of members or levels which describes what

dimensions they are from and what order those dimensions are in.

Tuple

A tuple is a collection of members which all have different dimensionality

(so each is from a different dimension). Tuples have () brackets around them,

but don't need them when there is only one member. For instance

( [timeofsale].[Year].[2001], [product].[all products] )

Set

A set is a collection of tuples which all have the same dimensionality

(so all have the same dimensions in the same order). Sets have {} brackets

around them, and always need them. For instance

{ ( [timeofsale].[Year].[2001], [product].[all products]

) , ( [timeofsale].[Year].[2002], [product].[all products] ) }

Function

Functions can return Sets, Tupels, Members, Levels, Dimensions or values. We'll

come across some of the more useful ones later.

Slice

We may want to cross tab by two fields for some specific value of a third, for

instance [timeofsale] by [product] for a particular [salesrep].

When we picture the report as a cube we think of this filter as a slice into

the cube, to show the values on a new face.

So that's the lexicon done, now how do we use it? Well here is the structure of a

statement

select 
{set 0} on axis(0) , /* this would be a block comment */ 
{set 1} on axis(1) , // this is a line comment 
...
{set n} on axis(n) 
from [cube]
where (tupel) 

So if we wanted a report of [product] on columns by [salesrep] on rows we would

execute:

select 
{ ( [product].[productname].[product1] ) , 
  ( [product].[productname].[product2] ) } on columns ,
{ [salesrep].[repname]. members } on rows 
from [sales] 

On the columns I have a set with two tupels from the same dimension. The ()

brackets are not required in this case because each tupel contains just one

member. The {} are required.

The rows has a function . members , which returns a set

with all the member of that level it. As . members returns

a set we don't need the {} brackets but again I've put them in.

Here is another one:

select 
{ [product].[productname].[product1] : [product].[productname].[product20] } on columns ,
{ [timeofsale].[Year].[2002]. children } on rows 
from [sales] 

In this example I've used a range to give me a set of all the products

inclusive between [product1] and [product20] on columns. On rows I've used

another function called . children to give me all the

months in [timeofsale].[Year].[2002]

. members works on a level to give all the members at

that level.

. children works on a member to give all the members

below it (assuming there are any).

Two more useful features before we look at slices:

select 
non empty { [product].[productname]. members } on columns ,
{ { [timeofsale].[Year].[2002]. children } 
     * 
  { [salesrep].[repname]. members } } on rows 
from [sales] 

First of all the keyword non empty excludes any values

from that axis where no values are returned.

The * operator does a cross join between the two sets,

and works in a similar way to a cross join in sql. The final set will be made

up of every possible combination of the tuples in the two sets.

Now we will add a slice:

select 
{ [product].[productname]. members } on columns ,
{ [timeofsale].[Year].[2002]. children } on rows 
from [sales]
where ( [salesrep].[repname].[Mr Sales Rep1] ) 

Note that the where criteria requires a tuple rather than a slice and that tuple

cannot contain any of the same dimensions as the sets on the axis

And to finish off this introduction a list of all the MDX functions along with a

brief summary of what they do. For more detail look them up in SQL Books Online

or MDSN:

A good place to go start trying the ideas in this article is the Foodmart

2000 database that comes as a sample when you install Analysis services.