Dinesh Asanka
Select Tables and Views to create the OLAP Cube.

Multi-language support for SSAS

April 8, 2021 by

Introduction

After discussing many features in SQL Server Analysis Services (SSAS) in order to carry out much richer analytics activities, we are going to discuss another feature in SSAS named Multi-language support for SSAS. In a previous article, we discussed how to create SSAS OLAP Cubes and how to access the OLAP cube using Excel. Further, we discussed how to include hierarchies in SSAS in order to improve the data analysis capabilities. In addition to those features, we discussed how to create perspectives in OLAP Cubes and how to perform management activities in SSAS.

Since SSAS is an analytical tool, we are looking at how to perform multiple language analysis in SQL Server Analysis Services so that OLAP cube can be used across different nationals. Multi-language can be applied in two ways, one is using data values and the other involves attribute names.

Data Values

Typically, we are analyzing data through dimension attributes in order to perform descriptive and diagnostic analysis. Let us look at the sample database, AdventureworksDW database which we have been using in many examples. Let us look at the DimProduct dimension. You will see there are two sets of columns as shown below.

Product name is included for English, Spanish and French

As shown in the above figure, the Product name is included for English, Spanish and French. You can see that Production Description is extended for different languages such as English, French, Chinese, Arabic, Hebrew, Thai, German, Japanese and Turkish.

Production Description is extended for different languages such as English, French, Chinese, Arabic, Hebrew, Thai, German, Japanese and Turkish

Similar to the DimProduct dimension, we can see that the multiple languages are included for the DimCustomer dimension as well.

Multiple languages are included for the DimCustomer dimension

You will notice in the above DimCustomer dimension table that Education and Occupation are extended for English, Spanish and French languages. Let us see how we can include a new language for the DimCustomer. Unlike fact tables, dimension tables will not have a large volume of records. Since there is a small number of records in a dimension table, adding a column and updating the added column will not have a major performance impact.

Let us add two columns called SinhalaEducation, SinhalaOccupation to the Dimcustomer in order to extend to the multi-language support for SSAS using the following script.

We need to include proper collation so that it has proper language properties. For example, different language has different sorting properties. In order to facilitate these language properties, we need to include the correct collation. Then data should be updated as below for EnglishEducation attribute/s.

Now new data is updated and it is ready for the newly added language. Let’s see how these columns are used to enhance Multi Language support for SSAS. As previously discussed in an article, OLAP Cubes in SQL Server, let us create SSAS OLAP Cube and we will select the following tables from the adventureworksdw database.

Select Tables and Views to create the OLAP Cube.

After the Data Source view is created using the above selected tables, we need to create the OLAP cube. Let us see how we select attributes for the Customer dimension. By opening the DimCustomer, we can include the following attributes for the DimCustomer.

Attributes for DimCustomer

We have selected attributes that are language-related and other attributes as shown in the above customer Dimension. Let us build the cube and see how we can analyze the data from a pivot table.

Analysing data usng the pivot table.

As you can see in the above pivot table, you have the option to select the needed attribute depending on the language you need.

Using Pivot table with Multi Language support for SSAS.

You can see that Education and Occupation in the French language are selected along with the Gender so that Multi-language support for SSAS is enabled. To enhance the operability for Multi-Language support for SSAS, we can create hierarchies with separate languages as shown below.

Hierarchies with the separate languages

When accessing the cube with the above hierarchies, it is simply a matter of choosing the hierarchy with the relevant language. Further, you can incorporate perspectives in OLAP and separate perspectives can be created for each language. This will allow users to select the specific perspective with the relevant language they want.

Hierachies in Pivot Table with different languages.

When you need to include another language, it only a matter of adding the column and update with the relevant language values. Then you need to process the OLAP cube by adding newly added columns to the OLAP cube dimension. With that, your cube is ready for another language.

Attribute Names in Multi-language

If you look at the above option, you will see that the language change only applied to the data. Then the obvious question is what about the names of the Measures and attribute names, for example, measures names like Sales Amount, Product Cost, Quantity should be in the relevant language if you are extending the Multi-language support for SSAS.

In the OLAP cube, there is an option called Translation that can be used to further extend the Multi-language support for SSAS. In Translation, you can choose the needed language as shown in the following figure.

Selecting the language for different translation.

First, you can add the French language to the cube as shown below.

Adding french language support for the measures and dimension names

Similarly, we can extend the Multi-language support for SSAS for the Dimension attributes by selecting the translation option in the relevant dimension. Let’s see how we can do this for attributes of the Customer Dimension.

Adding translation to the customer dimension in French

Now you have done the translation for the OLAP cube and it is time to see how we can use them in the client tools. In the SQL Server Management Studio or Visual Studio, there is a simple drop-down to select the language. However, changing the language in Excel is not as simple as SQL Server Management Studio.

After importing data from using external data source using the SQL Server Analysis service, it will display the data in the default language in Excel. You need to change the connection properties in Excel and need to include the relevant language as shown in the below figure.

Modifing the connection properties in Excel to include French Language.

Please remember that there should not be a space between LocaleIdentifier. In the given example, 1036 is used which is the language code for the French. You can find the relevant code for the different languages in the link given in the references section. After specifying the language code, you just need to refresh the pivot table. Now, you will see the pivot table in the French language as shown in the below figure.

Pivot table table in French language

When you choose the measures and dimension attributes, you will see that data as well as the attributes in French as shown in the below figure.

Output after the french language.

Multi-language support for SSAS can be extended to multiple languages in OLAP Cube. You can configure the OLAP cube for multiple translations. However, excel is supported for one translation at a time. Therefore, you need to change the language code when required.

Conclusion

We looked at extending the multi-language support for SSAS in this article. We identified that there are two configurations that need to be done in order to extend the multi-language support for SSAS. First, we need to look at including the values which can be achieved by adding an attribute to the dimension and update them with the relevant language. We can use the hierarchies and perspectives, in order to receive better analytics features. To incorporate multiple languages into the attribute names, we have the option of using the Translation feature in SSAS.

Both of these options can be done at a later stage than the time of the design of the OLAP cube. Further, both of these options will not add heavy overhead to the OLAP cube so that these options can be used much easier.

References

Dinesh Asanka
393 Views