Analysis Services 2005 has strong support for translations. Most of the translation support is about metadata translations, i.e. names of cubes, hierarchies, measures etc. But there is also a way to specify translations for the attribute members. For example, Adventure Works database has French and Spanish translations for Category and Product attributes in the Product dimension. What is the best way to retrieve these localized captions through MDX ? The standard way to do so is through the use of LocaleIdentifier session property. I.e., if we will connect with LocaleIdentifier=3082 (Spanish), and issue the following query
SELECT [Product].[Category].MEMBERS ON 0, {} ON 1
FROM [Adventure Works]
Then the built-in member property CAPTION for the members on Axis0 will have values of "Bicicleta" for Bikes, "Prenda" for Clothing etc. If we wanted to access the member captions from calculations in MDX then we could've used the following statement
WITH MEMBER Measures.CategoryCaption AS
Product.Category.CurrentMember.Member_Caption
SELECT [Product].[Category].MEMBERS ON 0
FROM [Adventure Works]
WHERE Measures.CategoryCaption
But we would still only get translations for the locale specified in the LocaleIdentifier property. And while at XMLA level it is considered to be command property (XMLA doesn't have concept of session properties), AS2005 really considers it a session property, i.e. any attempt to issue XMLA command with the value of LocaleIdentifier different from the one at which the XMLA session was established will result in the following error:
http://schemas.xmlsoap.org/soap/envelope/">
http://schemas.xmlsoap.org/soap/envelope/">
XMLAnalysisError.0xc10c0047
XML for Analysis parser: The LocaleIdentifier property is not overwritable and cannot be assigned a new value.
So it seems that we are forced to open new session for every language for which we want to get caption translation. Besides the fact that it seems excessive to create whole new session, there are also caching implications, since the calculations caches cannot be safely shared across multiple locales. Fortunatelly, AS2005 provides a way to access multiple translated captions on the same session. There is a special built-in member property LCID to handle this. However, it cannot be static member property, since there could be many different languages for which translations are needed. So LCID is a dynamic built-in member property, much like KEY is a dynamic member property to support members with composite keys. The word LCID must be concatenated with the desired LocaleID to obtain the instantiation of the property name. This is best illustrated by the example:
WITH
MEMBER Measures.CategoryCaption AS Product.Category.CurrentMember.MEMBER_CAPTION
MEMBER Measures.SpanishCategoryCaption AS Product.Category.CurrentMember.Properties("LCID3082")
MEMBER Measures.FrenchCategoryCaption AS Product.Category.CurrentMember.Properties("LCID1036")
SELECT
{ Measures.CategoryCaption, Measures.SpanishCategoryCaption, Measures.FrenchCategoryCaption } ON 0
,[Product].[Category].MEMBERS ON 1
FROM [Adventure Works]
As simple as that !
P.S. I am writing and publishing this blog post using Windows Live Writer. So far it seems like an awesome tool for writing blog entries (with its support for offline mode and very clean HTML generated), let's see how well will it interop with sqljunkies's CommunityServer...