THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

To quote or not to quote (in expressions of MDX calculations)

When you write MDX statements to create calculated members, named sets or cell calculations - do you use quotes around the expression or not ? Let's compare the following two statements.

CREATE MEMBER Sales.[Measures].[Profit] AS ' [Measures].[Sales] - [Measures].[Cost] '

and

CREATE MEMBER Sales.[Measures].[Profit] AS [Measures].[Sales] - [Measures].[Cost]

Which one should you use ? Everybody who had an experience with Analysis Services 2000 will immediately tell, that the first statement is correct one, and the second one will raise an error. This is indeed so in Analysis Services 2000, but let's open OLEDB for OLAP specification which is formal spec for MDX, and we will find out there, that actually the second statement is correct one, and the first one instead of defining dynamic expression Sales - Cost specifies static string, because quotes are the string delimiters ! So why there is such a discrepancy between the spec and implementation ? The reason is very prosaic. Back in January 1997 there were only 3 developers who worked on entire OLAP Services. Amir coded all the UI and DSO object model, Sasha worked on dimensions, partitions, processing and data storage, and I was working on MDX, networking, metadata and server infrastructure. Only MDX wasn't really defined back then. We had very good idea of how MDX expressions should look like, but not MDX statements. Indeed, if you can find Beta 1 version of OLEDB for OLAP, you will see that all the syntax is very different from familiar MDX's SELECTs - there was CONTEXT clause instead of FROM, FILTER clause instead of WHERE, there were no SELECT statement at all etc. So I coded the parser for MDX expressions only with the idea to extend it to the full statement syntax later. Only when the statement syntax was finalized, there were not enough time to change the parser, because it was so close to shipping of 7.0 - and as a result we just put separate statement parser on top of existing one. This of course had the drawback of requiring all the expressions to be quoted in order for the statement parser which didn't understand expressions to know their boundaries. So we shipped OLAP Services 7.0 like that. When time came for Analysis Services 2000, the schedule was tight again, there were many more important things to do - and we left things the way they were. Then came Yukon. One of the things that we really wanted to do was to bring our MDX into full compliance with OLEDB for OLAP, especially because around that time XML for Analysis specification started to take off, and it borrowed MDX syntax from OLEDB for OLAP, and obviously we wanted to be fully compliant with it. In Yukon there is single unified parser for both statements and expressions, as well as for different query dialects that Analysis Services supports such as MDX, DMX and SQL. This enabled us to fix the quote issue, and allow to support the spec defined syntax. But, of course, we couldn't break backward compatibility either. All the MDX that had been written for AS2K, must work exactly the same in AS2K5. Therefore AS2K5 supports quoted expressions as well with exactly same semantics as AS2K did. So, by now, it appears that in AS2K5 there is no difference between two forms, since both of them are supported. Actually, there is still a difference which makes the spec compliant form with no quotes to be preferable. This difference arises in error handling module. The way Yukon's MDX parser implements form with quotes, is that it treats it first as a string, and then reparses. Some location information is lost during that double step, and as a result the error messages more accurately show the location where error occured when no-quote form is used. Let's compare in Yukon. If we execute the following statement:

with member measures.x as 1 + kuku
select x on 0 from sales

We will get this nice error, which points to exact line and column inside the query where error occured

Query (1, 31) The dimension '[kuku]' was not found in the cube when the string, [kuku], was parsed.

If, however, we will use old syntax with quotes

with member measures.x as '1 + kuku'
select x on 0 from sales

The error will be the same, but less specific about the exact location:

The dimension '[kuku]' was not found in the cube when the string, [kuku], was parsed.

Conclusion is that while you don't have to rewrite all the MDX that you had for AS2K - it will work fine, for the new MDX, it is better to use the spec-compliant syntax.

Published Saturday, April 02, 2005 1:02 PM by mosha
Filed under:
Anonymous comments are disabled
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement