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

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...

Published Tuesday, November 9, 2004 10:23 AM by mosha
Anonymous comments are disabled
Privacy Statement