THE SQL Server Blog Spot on the Web

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

Microsoft OLAP by Mosha Pasumansky

What is 'MDX Missing Members Mode' in Analysis Services 2005 ?

There were many enhancements in Analysis Services 2000 to make MDX closer to SQL, both syntactically and semantically. After all, UDM is the model for both relational reporting and multidimensional analysis, and in order to support relational reporting style queries, UDM and MDX must be able to express relational semantics. One of the areas where MDX and SQL were very different was the question of how to treat missing or non-existing attribute values. Indeed, let's consider the following SQL query

SELECT City, SUM(Sales) FROM Sales WHERE City = 'Redmond' OR City = 'Leningrad' GROUP BY City 

It can be translated into the following MDX query

SELECT {Measures.Sales} ON COLUMNS, {City.Redmond, City.Leningrad} ON ROWS FROM Sales

What will happen, if the dimension table has city of Redmond, but doesn't have city of Leningrad. Well, for SQL it is pretty clear what will happen - the second part of the boolean operator OR in the WHERE clause will always be false, so the resulting rowset will have single row containing total sales for Redmond. But what will happen in MDX ? In Analysis Services 2000, it will for sure raise an error - "Cannot find dimension member City.Leningrad". For reporting scenarios, especially for ad-hoc queries, where users may type in the values of the parameters, it is very important not to fail the query, but simply ignore the missing value. Another scenario where such behavior is helpful is in the client application which saves MDX query together with the view. Next time when the view is loaded, MDX query may fail, because the it may referenced members which are no longer there after cube reprocessing.

On the other hand, in SQL if the attribute (i.e. column) name is misspelled - it will result in the error. From MDX point of view, though, ROWS and COLUMNS are completely symmetrical - they are just two of the axes that can be used in SELECT. Therefore we decided that the decision whether or not unrecognized member name should raise an error should be made per dimension. Really speaking, it should've been made per attribute, or perhaps per hierarchy, but we decided that granularity on dimension was good enough. Therefore dimension has property MdxMissingMembersMode which initially could be one of the following values: Error or Ignore - which are self explanatory. The default value is Ignore, but migrated cubes would have it as Error to preseve backward compatibility. The only dimension where this property cannot be set - is Measures dimension, because it doesn't have corresponding object in AMO and engine DDL. So for measures it is always Error, which kind of makes sense.

After we released Beta 2 with this feature, we got lots of feedback. Some of it was positive, but most was negative. People were complaining that now engine silently ignores misspelled member names which makes development harder and they prefer errors. After carefully looking into each scenario, we found out, that in all of them, people didn't like this new behavior when they were authoring calculations in the MDX script or authoring dimension security or authoring KPIs etc. Again - this makes perfect sense to raise an error if member name is misspelled in the expression used in MDX script, and for dimension security it could've even been considered security hole to ignore misspellings. Imagine that you want to define dimension security by allowing access to all cities except Las Colinas. The MDX expression for denied set will look like { [City].[Las Colinas] }, but if the author makes a typo - the denied set will end up empty - which means there will be no dimension security at all. It is much safer to raise an error in this case. Therefore in the recent CTP builds, we hardcoded Mdx Missing Members Mode to always be Error when evaluating server side calculations, security, KPIs etc.

After that we discovered interesting discrepancy. The MDX Debugger is implemented by issuing same commands as in the MDX Script, but on the session. Since they are no longer cube based calculations during debugging session, the handling of Mdx Missing Members Mode changes, which means that we may produce different results during debugging from the real execution. Of course this is unacceptable, so we made the final change by adding new connection string property MdxMissingMembersMode with values Error, Ignore and Default. Normally it is set to Default, which means respect the cube dimension settings. But it can be changed - inside MDX Debugger it is overwritten to Error to match semantics of MDX Script. Also some reporting applications may choose to use Ignore in order to fully match SQL semantics regardless of the cube dimension settings.

Published Thursday, June 9, 2005 9:28 PM by mosha
Filed under:
Anonymous comments are disabled
Privacy Statement