SQL Server: Select Star From Cube

Select * from Cube (SQL? No, its MDX)

           Everybody knows that to query the data from an OLTP database, we use SQL. Similarly, to query the data from an OLAP database, we have to use MDX. MDX stands for “Multi Dimensional Expression” language which queries the data from multidimensional database, also known as “Cube”.

But when/why should we use MDX? Think of some report showing summarised data over a period of time. To fetch the data from an OLTP database requires some complex query with aggregate functions, group by etc. and taking long time to execute due to a good number of page reads, use of tempdb for preparing summary and so on. Instead, if we query the data from an OLAP database, we will get the results within no time as all of us know that the OLAP data is pre-aggregated which is stored using some efficient structure. So we can use MDX within SSRS reports, SSIS packages, PPS (Performance Point Server), custom .Net code to design windows forms and web pages etc. to design and develop reports which are based on aggregated data.

In this blog, we will have a brief understanding of MDX especially for those who have not yet touched the subject due to the “well known” complexity of the language. Many people think that it is hard to learn, complicated, not so popular etc. Believe me, I was also thinking the same and kept it away for a long time till I really made some efforts to digest it. It’s just a matter to kick start and wow, now I can write my own MDX.

Let’s first have a look at opening the query editor window where we can write MDX code. [For the demo, I am using “Adventure Works DW 2008” sample database in SSAS 2008]

1. Open SQL Server Management Studio (SSMS)
2. Click File –> New –> Analysis Services MDX Query.
3. Connect to the SQL Server Analysis Services (SSAS) server.
4. Check the toolbar drop-down and you will find the available SSAS databases. Select Adventure Works DW 2008.
5. Once the database is selected, it filters the cube dropdown with the available cubes inside that database. Select the “Adventure Works” cube and the metadata pane will show the cube metadata like measures, dimensions, KPI etc.

You can now write the queries either by typing the syntax directly in the query editor window or by drag drop from the metadata pane. I am not going to discuss here the “MDX Query Architecture” and the concepts like “Tuples” and “Sets” though they are important to understand the overall functioning. (Some people find it very boring like me :)). I assume that the audience knows SSAS and concepts like cube, dimensions, measures, attributes, hierarchies etc. So let’s start with one sample query.

select
from
[Adventure Works];

The MDX query looks very much similar to SQL (only for eyes) but unlike SQL the functioning is very different. In SQL we select columns; in MDX we have dimensions (attributes and hierarchies) to presents the data across multiple axis up-to 128. In SQL we select data from table; in MDX we select data from cube or perspective. In the above query, the only difference is that we have not specified any columns or * for all columns and still we are getting the result. The result showing is the value for Reseller Sales Amount which is the default measure. (Defined while designing the cube project in BIDS)

The below query has the Internet Sales Amount as a where clause which is called as a slicer in MDX and used to limit the results of the query.

select
from
    [Adventure Works]
where
    [Measures].[Internet Sales Amount];

We can have the similar results using the following query showing the Internet Sales Amount measure on the column axis. Remember there are total 128 axes available to present the data.

select
    [Measures].[Internet Sales Amount] on columns
from
    [Adventure Works];

Now lets have a look at the query looking similar to SQL having select, from and where clauses, but works very differently.

select
    [Date].[Calendar Year].[Calendar Year] on columns,
    [Geography].[Country].[Country] on rows
from
    [Adventure Works]
where
    [Measures].[Reseller Sales Amount];

The above query represents Year on the column axis and Country on the row axis to show the Reseller Sales Amount, a typical cross tab summary report. Just execute it and you will get the results within no time. Think of the SQL query to fetch the similar results and you will realize the power of MDX.

Want to remove nulls, ok; the following query will take care of that.

select
    non empty([Date].[Calendar Year].[Calendar Year]) on columns,
    [Geography].[Country].[Country] on rows
from
    [Adventure Works]
where
    [Measures].[Reseller Sales Amount];

CY 2006 has been removed. Still there are null values showing in some of the cells as the Non Empty operates on the entire column or row and not on the part.

This is just a beginning and there are many more things to learn like how we can filter the data, sort the results, navigating through dimensions and hierarchies. MDX plays an important role in defining calculations, KPI, sub-cubes etc. and if you really want to have interesting results from the cube, there is no option but to learn MDX.

All The Best 🙂

Regards

Amit Karkhanis

Like us on FaceBook  |  Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook