MDX Query

Transact SQL and MDX are almost the opposite in their scope. Transact SQL is a row level language and MDX is a top down language. You have to thing of them in the way that the data is stored.

Going back to my simple OLTP datamart

The query

SELECT COUNT([impression_id])AS Total ,[date] ,[destination]

FROM [DataMart].[staging].[impression]

GROUP BY [date] ,[destination]

We queried the staging table so we only have a primary key on TrasctionId. The plan looks like the following

The largest cost of the query was the hash match with a 23% percent table scan because we didn’t have indexes in date and destination.

Next run

SELECT COUNT(fi.fact_id)AS Total, d.short_date

FROM fact.impression fi

INNER JOIN dimension.date d ON fi.date_id=d.date_id

GROUP BY d.short_date

The plan is much better because we have indexed date_id and destination_id

 

We cant go much farther because SQL still has to bind the 2 tables together using a hash table and when you get into the millions of records this is a time and memory consuming process.

Here is where OLAP comes in, we need to look at this data from the top down instead of OLTP bottom up.

Connecting to our SSAS services using SQL Management Studio we can run

 

SELECT Measures.MEMBERS ON COLUMNS,

([Date].[Calendar].MEMBERS,

[Destination].[Desinations].MEMBERS) ON ROWS

FROM [Data Mart]

Well this is diffrent. What we see is every possible aggregated result is displayed.

But what I need is a similar result to my SQL Query. What you have to do is be more specific.

SELECT Measures.MEMBERS ON COLUMNS,

([Date].[Calendar].[Year].&[2011],[Destination].[Desinations].MEMBERS) ON ROWS

FROM [Data Mart]

So this is pretty good but I we need to use this data. I will show you how to in SQL Reporting Services