Get Different Language from SSAS into Excel
I
have been to a number of customer visits and many times I show them
capabilities around using Excel 2007 as a powerful tool for doing BI. In these
end-customer interactions, I have been asked about localization and sometimes
globalization questions. One such question triggered me in writing this tip.
Now, let us take a simple scenario where-in I am going to use the SQL Server
2008 Samples and have built the Enterprise version of the Analysis Services
Cube of AdventureWorksDW. The interesting part of this built-in AS cube is that
it has translations done at the Dimensions level and the Measure Groups (as in
figure below).
Now,
let me quickly browse through the cube and show you how these change the
language. From the BIDS studio it is very easy to show how we can change the
locale and see how it changes all the values.
The
best part is once we move to a scenario where we consume the same data from
Excel, the default is English and folks ask me how we can change it the
different locale that we have already defined inside SSAS. This tip
fundamentally revolves around this concept only. The idea is to create a .odc
file and then edit it as a normal text file. In this case I just edited the
connection string and added the appropriate "Locale Identifier" in this case
Spanish.
Once
this step is done, get into Excel 2007 and connect to the ODC file created and
let Analysis Services automatically send you data of the desired locale to you.
Here is the typical Excel Output as in the below figure.
Thats
it !!! Now you have the same outputs as you got with BIDS studio into the one
and only Excel. BTW, people have asked me what-if the locale is NOT available.
Dont worry, SSAS will automatically give you the data in the default language
aka in this case English.
BTW,
these are not specific to SQL 2008, you can do the same with SQL 2005 and it
works like gem.
|