Cube Translation in Excel 2007

In Strategy Companion Analyzer, we have “Show/Hide Language Panel” icon on the toolbar, i.e. the one with “abc” and Chinese writing below:

If we click it, it shows the Language Panel:

Once we choose a language, the dimension names, attribute names and measures names will be displayed using the cube translation for that language:

For comparison this is the Translation tab on BIDS:

But in Excel 2007 we don’t have language selection icon. So how do we change the language?

The answer is not on Regional and Language Options:

It’s not on Microsoft Office Language Settings (which can be accessed via Excel Options):

But it’s on the data source files:

Open Adventure Works.odc on notepad and you’ll see the connection string:

<odc:ConnectionString>Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Data Source=localhost;Initial Catalog=Adventure Works DW 2008</odc:ConnectionString>

And add “Locale Identifier = 1036” like this:

<odc:ConnectionString>Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Data Source=localhost;Initial Catalog=Adventure Works DW 2008; Locale Identifier = 1036</odc:ConnectionString>

For other languages: 1031 = German, 1033 = English, 1034 = Spanish, 1036 = French, 1041 = Japanese. For a complete list of Locale Identifier see MS TechNet here. Not Language ID, but Locale ID.

Save the .ODC file and open Excel 2007 again, Data menu, Existing Connection, choose Adventure Works, click Open:

And now we see the dimension names, attribute names and measure names in French:

Vincent Rainardi, 11/8/2010, vrainardi@gmail.com, www.datawarehouse.org.uk