About me

I work as SQL Server DBA / Developer and BI Consultant in Toronto, Canada.Canada Flag More...
Vidas Matelis picture

MVP Logo

Search

Report Portal
blank

Customizing object names in Analysis Services database using translations

November 13th, 2007 by Vidas Matelis

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

I added translation for “State-Province” and “Postal Code” attributes and rename them to “State” and “Zip”:

Geography Dimension Translation

After deploying this change to there server, there are results in Excel 2007:

Results in Excel 2007

Posted in SSAS | 10 Comments »

10 Responses

  1. Ramunas Balukonis Says:

    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

  2. Olivier Moreau Says:

    It’s only work with the Enterprise Edition :-(

  3. Vidas Matelis Says:

    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.

  4. Vidas Matelis Says:

    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.

  5. Ramunas Balukonis Says:

    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.

  6. Anonymous Says:

    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

  7. Vidas Matelis Says:

    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.

  8. Neil Ashworth Says:

    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

  9. Miguel Chavez Says:

    Thanks . Everybody publish all these great tips.

  10. bloger7791 Says:

    It’s interesting

XL Cubed