RSS Feed
Customizing object names in Analysis Services database using translations
November 13th, 2007 by Vidas MatelisFor the past few years I was working on data warehouse project that is customized and installed for different clients. As part of customization, sometimes clients might request to use different names for dimensions, attributes or measures. For example in Geography dimension for US customers that do business just in US you would want to have hierarchy Country-State-City, but for Canadian customer the same hierarchy would be Country-Province-City. For customers that do business in US and Canada, the same hierarchy could be called Country-State|Province-City.
Analysis Services 2005 provides easy interface to rename dimensions, attributes or measures. But things become more complicated when these object names are used in cube MDX script – these renames can break script. Instead of renaming actual object, you might consider using “Translation” feature of Analysis Services 2005. I usually create translation for language that matches clients PCs language. This way Excel 2007 uses translated names without any additional steps. For other SSAS clients you might have to specify locale ID in connection string parameter, example: “Language Identifier=1033;”. With translations you can choose to rename just a few objects. If no translations exists, Analysis Services uses default object name assigned during object creation.
Here is a quick example on how to rename a few attributes in Adventure Works database. This is how Geography translation tab looks with shipped version of Adventure Works database:
I added translation for “State-Province” and “Postal Code” attributes and rename them to “State” and “Zip”:
After deploying this change to there server, there are results in Excel 2007:
Posted in SSAS | 10 Comments »
November 14th, 2007 at 2:51 am
Vidas,
Good idea to use translations to rename objects. I use translations only for primary purpose. This feature and unicode as well as supporting currencies made SSAS 2005 much powerfull and usefull for enterprise enviroment across countries.
Btw, from my expirience working with translations: you have to fill translations for all terms, because Excel 2003/2007 does not open Pivot in some cirsumstance and Pivot table becomes empty.
Ramunas
November 16th, 2007 at 6:23 am
It’s only work with the Enterprise Edition :-(
November 16th, 2007 at 9:29 am
Oliver,
You absolutely right – translations work just with Enterprise Edition of SQL Server:
http://www.microsoft.com/midsizebusiness/products/sql/comparison.mspx
I should have pointed this in my blog.
November 19th, 2007 at 4:06 pm
Ramunas,
We are using Excel 2007 as a client and we specify just a very few translations. Do you know in what cases Pivot might not open? Any specify error? We have not seen that yet.
November 21st, 2007 at 4:53 am
Vidas,
Sorry, I was unable to reproduce this error on Adventure Works database and don’t known exact reason. But i’ll keep in mind that you are interesting in this and when I’ll catch error, I’ll leave one more replay.
July 31st, 2008 at 10:15 am
Vidas,
You said you can edit the connection string to “hard code” the locale ID. We are using Excel 2007, how would you edit the string and what to?
thanks
Neil
July 31st, 2008 at 10:34 pm
Neil,
In Excel 2007 when you open pivot table, select menu “Data”, then click on the button “Connections”, select you conection, then Properties. In the “Definition” tab you should see file name where connection information is saved. You can edit that file with notepad and add anything you need to connection string.
August 1st, 2008 at 3:24 am
Vidas,
Thanks thats great, got it working. However the syntax seems to be
“Locale Identifier=1033;” not “Language Itentifier=1033;”
thanks for the help
neil
April 9th, 2010 at 10:48 am
Thanks . Everybody publish all these great tips.
March 2nd, 2012 at 3:33 am
It’s interesting