Customizing object names in Analysis Services database using translations-SSAS

For 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:

Dimension Translation Tab