Multi-language support for SSAS

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.

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.

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

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.

ALTER TABLE [dbo].[DimCustomer]

ADD

[SinhalaEducation] NVARCHAR(40) COLLATE Indic_General_90_BIN,

[SinhalaOccupation] NVARCHAR(40) COLLATE Indic_General_90_BIN

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.

UPDATE [dbo].[DimCustomer] SET [SinhalaEducation] = 'උපාධිය'

WHERE [EnglishEducation] = 'Bachelors'

UPDATE [dbo].[DimCustomer] SET [SinhalaEducation] = 'අර්ධ විද්‍යාලය'

WHERE [EnglishEducation] = 'Partial College'

UPDATE [dbo].[DimCustomer] SET [SinhalaEducation] = 'උසස් පාසල'

WHERE [EnglishEducation] = 'High School'

UPDATE [dbo].[DimCustomer] SET [SinhalaEducation] = 'අර්ධ උසස් පාසල'

WHERE [EnglishEducation] = 'Partial High School'

UPDATE [dbo].[DimCustomer] SET [SinhalaEducation] = 'උපාධිධාරී උපාධිය'

WHERE [EnglishEducation] = 'Graduate Degree'

Then the EnglishOccupation attribute is updated from the below script.

UPDATE [dbo].[DimCustomer] SET [SinhalaOccupation] = 'වෘත්තීය'

WHERE [EnglishOccupation] = 'Professional'

UPDATE [dbo].[DimCustomer] SET [SinhalaOccupation] = 'ලිපිකරු'

WHERE [EnglishOccupation] = 'Clerical'

UPDATE [dbo].[DimCustomer] SET [SinhalaOccupation] = 'නුපුහුණු කම්කරු'

WHERE [EnglishOccupation] = 'Manual'

UPDATE [dbo].[DimCustomer] SET [SinhalaOccupation] = 'කළමනාකරණ'

WHERE [EnglishOccupation] = 'Management'

UPDATE [dbo].[DimCustomer] SET [SinhalaOccupation] = 'පුහුණු කම්කරු'

WHERE [EnglishOccupation] = 'Skilled Manual'

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.

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.

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.

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

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.

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.

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.

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

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.

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.

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.

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.

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.

Dinesh Asanka

Dinesh Asanka is MVP for SQL Server Category for last 8 years. He has been working with SQL Server for more than 15 years, written articles and coauthored books. He is a presenter at various user groups and universities. He is always available to learn and share his knowledge.

View all posts by Dinesh Asanka

Introduction

Data Values

Attribute Names in Multi-language

Conclusion

References

Related posts: