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

Comparing Levels in MDX and CONDITION vs. SCOPE in cell calculations

A follow up to the “Comparing Members in MDX” blog (archived here). I just run across a thread on OLAP forum hosted at sql.ru, where someone makes a suggestion to exclude level from calculation by applying this formula:

[Products].CurrentMember.Level.Name <> "Assortiment"

Similar arguments as in the “Comparing Members in MDX” apply here. From the correctness point of view this is almost always correct (It seems that it is unlikely to ever  cause correctness problems, since level names inside hierarchy are unique, but actually it can, although in pretty corner cases. The correctness issue arises from the fact that string comparison in MDX queries is done using the client locale (or more precisely using client collation). Usually, collation in string comparison only affects the order, i.e. "less" and "greater" operators, but in some Asian languages depending on collation different characters can be considered as equal or not equal , for example with Katakana and Hiragana in Japanese).

Some people address this by using level ordinal, i.e. assuming that Assortiment level is fifth level in the hierarhcy

[Products].CurrentMember.Level.Ordinal <> 5

This will work, and won't do string operations. However cleaner and more readable solution (and with better performance) would be the use of IS operator, i.e.

NOT ([Products].CurrentMember.Level IS [Products].[Assortiment])

But actually, many times those checks should not be done in the first place. In the example from the same sql.ru thread, the comparison was used in the context of cell calculation:

CREATE CELL CALCULATION <cube>.<name> 
FOR '([Measures].[Remainder])', 
CONDITION = 'NOT ([Products].CurrentMember.Level.Name IS [Products].[Assortiment])'

However, in this scenario, it is better not to use condition, but instead move it inside the scope of the calculation. Assuming that Assortiment is the last level of the Products hierarchy, the cell calculation will become then

CREATE CELL CALCULATION <cube>.<name> 
FOR '([Measures].[Remainder], 
      Descendants([Products].[All Member], [Products].[Assortiment], BEFORE))'

The difference between the two is that in the first one, for every single cell under the scope of the cell calculation condition should be evaluated, and even when the condition is written in the most efficient way (i.e. using IS operator), it is still evaluation of MDX expression for every cell. When the level is excluded by the scope of the cell calculation, the cells are excluded in much more efficient manner by checking whether or not they are part of precomputed subcube.

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }
Published Tuesday, November 09, 2004 1:57 PM by mosha
Anonymous comments are disabled
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement