Microsoft OLAP by Mosha Pasumansky

Converting Strings to Numbers in MDX or StrToValue vs. Val

In Analysis Services 2000 there often a need to convert string to number. Usually it happens when there is a level which has member property, and the values for that member property represent some number. For example, in the FoodMart 2000 sample database, the [Store Name] level of the [Store] hierarchy has member property called [Store Sqft] which represents size of the store in square feet. However, the only way in Analysis Services 2000's to access those values from MDX is by using .Properties function, which always returns a string regardless of the data type of the member property. It is worth to note, that internally, Analysis Services doesn't store member properties as strings, it really preserves the true data type, but unfortunately this data type is not exposed in MDX. So the user now needs to convert back this string to the number in order to perform some calculations using it. Normally one looks at MDX function list, and quickly spots the StrToValue function, which accepts string and returns number - so from the first look it seems like the perfect choice, so user writes something like

StrToValue([Store].CurrentMember.Properties("Store Sqft"))

and it also appears to work fine. However, this expression while it looks very natural has both correctness and performance problems. Let's find out what really happens here. In order to do that, we will run the following simple query:

WITH MEMBER Measures.x AS 'StrToValue([Store].CurrentMember.Properties("Store Sqft"))' SELECT {x} ON 0, [Store].[Store Name].Members ON 1 FROM Sales

We will notice immediately, that some stores (like [Store 21], [HQ], [Store 24] etc) have #ERR against them instead of the number. What happened ? It turns out that for those stores the dimension table doesn't contain values for the store size in sqft - the column is blank. So [Store].CurrentMember.Properties("Store Sqft") returns for those stores empty string, and StrToValue("") for empty string raises an error. So it is at first puzzling, why would StrToValue return an error for empty string input, and it is even more puzzling looking into error description. Analysis Services 2000 version of the error reads

Formula error - syntax error - unexpected end of expression

And Analysis Services 2005 words it a little better:

An MDX expression was expected. A full statement was specified

It is still not clear what expression was expected, and why it had unexpected end. The answer is that StrTo... family of functions (i.e. StrToValue, StrToMember, StrToSet etc), parse their input strings using MDX parser, build an MDX expression from it and then execute it. I.e. they treat the input string as MDX expression itself. Therefore, all of the following is valid and work:

StrToValue("1+1"), StrToValue("[Store].CurrentMember.Children.Count") etc

When MDX parser is fed with empty string, it cannot reduce it to any MDX expression - hence an error is raised. (Analysis Services 2005 MDX parser actually recognizes it as a statement, albeit empty statement, which is allowed by XML/A standard, but obviously StrToValue cannot deal with it, since it needs an expression to execute, not a statement)

Now this explanation also highlights a performance problem of using StrToValue in order to convert string to member - there is relatively big overhead in parsing MDX, building execution tree, running expression evaluation etc for such a simple string as conversion of string to number. So what should be used instead ? The answer is Val function. Val function is not actually an MDX  function - it is VBA function, and Analysis Services automatically make all of the applicable VBA functions part of accepted MDX. So for clarity reasons only, I will write VBA!Val instead of Val, but really both of them are the same. So the correct way to write the string to number conversion is

VBA!Val([Store].CurrentMember.Properties("Store Sqft"))

It is worth to remind again that in Analysis Services 2005, such constructs will be extremely rare, because the UDM model allows to truly access member properties as attributes with their native data types - but probably this would be a subject of another article...

posted Tuesday, November 09, 2004 9:09 PM by mosha

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:

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

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.

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:

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

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.

posted Tuesday, November 09, 2004 10:19 AM by mosha