Let’s start with simple example. We have simple Sales measure and query for it with
SELECT Measures.Sales on COLUMNS FROM Sales
If this query is executed in MDX Query pane of SQL Workbench or in MDX Sample it will display the following result:
$266,000
Both of these tools, as well as most of the well-behaving AS client tools are displaying is FORMATTED_VALUE cell property of the cell. We know from OLEDB for OLAP and XMLA specifications, that FORMATTED_VALUE is derived from two other cell properties – VALUE and FORMAT_STRING by using OLE Automation VarFormat function. Let’s check these properties with the following query
SELECT Measures.Sales on COLUMNS FROM Sales
CELL PROPERTIES VALUE, FORMAT_STRING
Their respective values are 266000 and “Currency”. So applying format string “Currency” to the number 266000 yields formatted string “$266,000”.
The question that we are going to discuss in this article is how did AS decide to put $ sign in front of the number when formatting it. I.e., how did it know that Sales measure represents sales in US Dollars and not, say, in Yens, Shekels or Rubles. In order to solve this problem AS2005 extends the abovementioned specifications and introduces additional cell property called LANGUAGE. And FORMATTED_VALUE is derived using not two, but all three cell properties – VALUE, FORMAT_STRING and LANGUAGE. Let’s examine it for our example:
SELECT Measures.Sales on COLUMNS FROM Sales
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE
The value of LANGUAGE cell property is 1033. This is LCID for US English, and therefore the cell value is formatted as US Dollars. The exact documentation about which LCIDs produce which currency sign can be found in documentation of VarFormat function. What is more important for us now is to understand where AS gets value of LANGUAGE cell property from. There are several layers of answers to that. If you have looked at metadata model of AS2005 (either by looking at AMO or directly at AS XML DDL schema), you know that each metadata object has Language property. It can be specified explicitly on objects such as dimension or cube, and if it is not specified – it is inherited from its parent, i.e. database, and if it is not specified on database, then it is inherited from the server object. Since my server is US English, and I didn’t overwrite language property on any of the objects explicitly, all my cubes have Language 1033. If nothing else is done – MDX will use Language property of the cube to which MDX query was sent to.
So we learned how to control currency formatting per entire cube, and it works fine if all the data in the cube is in the same currency. However, it is possible, that data in the cube’s fact tables is reported in different currencies (by different subsidiaries of the company) and/or there is currency conversion set in the cube - either by using measure expressions, or by running Currency Conversion Wizard, or my using custom MDX Script calculations. Usually, either different measures are in different currencies, or, more commonly, there is (one or more) Currency dimension/attribute and each Currency member means different currency. At the extreme, every cell in the cube could be in different currency. Luckily, AS2005 allows to control it at any granularity. It is done in MDX Script, exactly the same way as other calculation properties are defined in MDX Script. Just like you can use assignments in the form
<subcube> = <expr>;
To assign values to the cells in specified subcube, and
BackColor(<subcube>) = <expr>;
To change BACK_COLOR of the cells in the specified subcube, you also can write
Language(<subcube>) = <expr>;
To set LANGUAGE of the cells in the specified subcube. Let’s run through couple of examples. If we have different currencies for different measures, we could write the following in the MDX Script:
Language(Measures.[Sales US]) = 1033;
Language(Measures.[Sales Yen]) = 1041;
Language(Measures.[Sales NIS]) = 1037;
Language(Measures.[Sales Rub]) = 1049;
In the more common scenario, when there is a Currency dimension, let’s assume that we have LCID attribute in it, which is non-aggregatable and related to the Currency Code attribute. This attribute will hold proper LCID values for different currencies. The old AS2000 way of using related attributes would’ve been to write
Val(Currency.[Currency Code].CurrentMember.Properties(“LCID”))
However, exploiting the fact that calculation model is aware of attribute relationships, the best way to write it in would be instead
Language(this) = Currency.LCID.MemberValue;
(see blog entry “
MDX Functions in AS2005” for more discussion about .MemberValue vs. .Properties). Unfortunatelly, Currency Conversion Wizard doesn’t have UI to select such LCID attribute, and as a result it doesn’t generate MDX Script to change the Language property, but after reading this article – you should be able to easily do it yourself.
My previous
post, more specifically section about Bad/Deprecated MDX functions caused some controversy in the comments. I actually hesitated while I was writing that section, because I was worried that some of my comment would be misunderstood. So, I will try to clarify some things here. The opinion on which functions are "bad" is somewhat subjective. I tried to steer as much as possible from controversy - for example, I also think that IIF function in MDX is bad - but I bet most of the people will disagree with me. (I still maintain that IIF should almost never be used, and whenever I work on customer's cube, first thing I try to do is to eliminate IIF's). But even for functions which I dubbed "bad", there are scenarios when they should be used. For example, I marked CalculationPassValue as "bad", but MDX Debugger internally uses it for color coding in pretty unique setting, and there is no way to achive the same thing without it. Or .Properties function, which cannot be avoided if attribute is marked as non-browsable. Likewise, good functions can be misused or not used optimally. So I tried to strike a balance and single out these functions which are almost always "bad". Perhaps I should've been more tolerant to the StrTo family of functions. Radim reminded in the
comments to the origianl post that StrTo functions are needed when dealing with parametric MDX queries. One common case is parametric queries for Reporting Services. Another example is sample code for querying KPIs in Olivier's
blog, that I linked to some time ago. The root cause for it is that in AS2005 it is not possible to pass strongly typed parameters to parametric MDX queries, but only numbers or strings, therefore if the member object is required, StrToMember is has to be used. I still suggest that if you use one of those functions, at least specify the optional CONSTRAINED flag, i.e. in Radim's example it will become StrToMember(@ChoosenCurrency, CONSTRAINED).
I hope this clarifies the matters.