Share via

SSAS translation for client application

  • Article

Tested on: Excel 2013, Excel 2016, Reporting Services 2012/2014/2016.

 

Hello Everyone, in this blog I will discuss on how to view SSAS translation in different client Application.

 

Before I begin with the details on this blog, if anyone doesn’t know what Translation in SSAS is, then I request you to go and check out the link below:

/en-us/sql/analysis-services/translation-support-in-analysis-services

 

Requirement: I am sure everyone wants to view the Translation language of SSAS cube in the client application that they are using to pull the data from SSAS. So, how can we do it?

Well here I will talk about 3 client application- Excel, Reporting Services and Power BI.

 

For Excel:

 

  1. Open an Excel sheet, go to the Data section-> Get Data and choose from Analysis Services.
  2. Enter the server name, credentials and choose the database and cube that you want and click on Next.
  3. In the next page note down the File Name where the model will be saved (it will be in .odc extension) and click on Finish then Import the data.
  4. Now close the Excel and go to the folder where the model is saved.
  5. Open the .odc file in a notepad.
  6. In the Connection String section, you will have to add a parameter called “Locale Identifier”
  7. The default is 1033 which is English default (e.g.: Locale Identifier=1033)
  8. You need to add the value as per your translation language in SSAS. Please the link below to know the Locale Identifier code for each language.

https://msdn.microsoft.com/en-us/library/ms912047(v=winembedded.10).aspx

  1. Once the Locale Identifier is set, save the file.
  2. Now a new Excel file and go to the Data section.
  3. Click on Existing Connection and choose the connection which you have create recently.
  4. Once done import the data and you will see that the Language will show as per the SSAS translation language.

 

For Reporting Services:

 

  1. Go to create Data Source section.
  2. You can either choose to write the connection string or you can also choose to Build the connection string.
  3. You can do this for both shared Data source and embedded data source.
  4. While writing a connection string, apart from data source and Initial catalog, add the parameter “Locale Identifier=1034” (For my case I have used Spanish(Spain)).
  5. If you choose to build the connection string, then click on Advanced button and look for Locale Identifier parameter.
  6. Set the Locale Identifier value as per your translation language in SSAS by referring the link above.
  7. Once done, save the data source.
  8. Now while creating the data set you will see that the Language will show as per the SSAS translation language.
  9. Please note that the report must be created based on the SSAS translation and the translation cannot be changed while viewing the report.

 

For Power BI:

 

Well for Power Bi we don’t have official support yet from Microsoft and there is no ETA as in when it will be supported.

HTH

 

Author:      Jaideep Saha Roy –Support Engineer, SQL Server BI Developer team, Microsoft

Reviewer:   Kane Conway – Support Escalation Engineer, SQL Server BI Developer team, Microsoft