Help understanding OLAP MDX Query

Question

I've been trying to learn SSAS Cubes and MDX. So far I have managed to create a cube that returns the correct data/calculations when viewed through the cube browser.

The Query Builder was used to build a query in reporting services to query the cube, I've looked at the MDX this generated and pulled out the stuff that doesn't seem to make a difference to the actual results. The query looks like this:

SELECT NON EMPTY { [Measures].[AverageConnectedSeconds] } ON COLUMNS,    
NON EMPTY { ([Operator].[ACCESS DEF].[ACCESS DEF].ALLMEMBERS * [Calls].[Notification Time Bands].[Notification Time Bands].ALLMEMBERS) } ON ROWS    
FROM ( SELECT ( -{ [Calls].[Notification Time Bands].&[0] } ) ON COLUMNS    
FROM ( SELECT ( { [Calls].[Incoming YN].[N] } ) ON COLUMNS    
FROM ( SELECT ( -{ [Calls].[Entity Type].&[6] } ) ON COLUMNS   
FROM ( SELECT ( -{ [Calls].[Reason Text].&[Background Call] } ) ON COLUMNS    
FROM ( SELECT ( { STRTOMEMBER(@OperatorId) } ) ON COLUMNS   
FROM [PNC5data] )))))

I've read up on the Syntax and I THINK I understand why the query is doing nested selects. Am I right in understanding that the nested SELECT FROM's are acting as a WHERE clause would in SQL?

If so: What is the WHERE clause used for in MDX and when/why would I use one?

If anyone is able to give me a breakdown of what is actually happening in this query I'd really appreciate it! Or if you're able to direct me to a resource I could further my understanding of MDX, I'd be greatful!

Thanks,

James

Nylon Smile Nylon Smile · Accepted Answer · 2011-01-07T13:10:08

Understanding WHERE clause is straightforward. You will see in many sources that people complain about its misleading name. The better name to use is Slicer. By using a where clause you are able to query a slice of a cube instead of the whole cube (obviously you will have some performance gains when you query a slice instead of a whole cube).

FROM clause is used to query a cube or to query another MDX query i.e. subqueries. Subqueries are generally slower but are more powerful too. e.g. when you use a hierarchy as a slicer you cannot use it in an axis while with a subquery you can e.g. subqueries can change the hierarchies' structure.

In the above statement you see nested subqueries. If you know some MDX syntax it is easy to break and interpret each subquery or even move some to the slicer.

I am relatively new to the whole OLAP subject too. The two sources I have been using are: icCube MDX reference (I am not a Microsoft fan, icCube was a good, easy to use alternative) and Fast Track to MDX. I specially like Fast track to MDX because its explanation of essential MDX features and the terminology (like set, tuple, function, etc) is easy to follow, specially if you are using Microsoft BI tools. You may want to take a look at this post too.

Help understanding OLAP MDX Query

2 Answers