Using Translations with Analysis Services and MS-Excel 2013

Analysis Services Translations

Analysis Services (SSAS) provides an easy translation mechanism for a richer end-user experience. SSAS Translations allows for the translation of the captions of dimensions and facts as well as the translation of the content of dimension name columns. To easily show the cube in his preferred language the end-user has to use a tool that makes use of SSAS Translations. MS-Excel 2013 is such a tool. Depending on the installed display language of MS-Excel and provided the cube has a translation for the display language the cube and dimensions will automatically be shown in the default display language. Provided the necessary language packs for Office are installed, end-users can change the display language and create MS-Excel reports in the language they need, as long as the SSAS cube provides translations in the display language in MS-Excel.

Setup dimension tables with different languages in SQL-Server

Every dimension table that has name columns to display the dimension members, should have additional name columns for every language end users request. There is always a default name column.

For instance: a dimension table called DimBlocking

CREATE TABLE [dbo].[DimBlocking](

[BlockingId] [int] NOT NULL,

[BlockingName] [nvarchar](50) NOT NULL,

CONSTRAINT [PK_DimBlocking] PRIMARY KEY CLUSTERED

(

[BlockingId] ASC

) ON [PRIMARY]

) ON [PRIMARY]

This dimension has a key column ‘BlockingId’ and a name column ‘BlockingName’. This name column will serve as the default name column in the SSAS dimension specification.

This table example has 2 rows of data

BlockingId	BlockingName
0	Non-blocking
1	Blocking

To provide for other languages like Dutch and French, just add name columns of the same type and size as the default name column, e.g. BlockingNameNL for Dutch and BlockingNameFR for French.

CREATE TABLE [dbo].[DimBlocking](

    [BlockingId] [int] NOT NULL,

    [BlockingName] [nvarchar](50) NOT NULL,

    [BlockingNameNL] [nvarchar](50) NOT NULL,

    [BlockingNameFR] [nvarchar](50) NOT NULL,

After adding the translation columns:

BlockingId	BlockingName	BlockingNameNL	BlockingNameFR
0	Non-blocking	Niet-blokkerend	Non-bloquant
1	Blocking	Blokkerend	Bloquant

Setup MS-Excel to use the different languages provided by a cube in SSAS

Office Language preferences

By default MS-Excel is installed with one Display language. This can be verified in the Options dialog window of MS-Excel, under the Language tab. In the screenshot below, Office 2013 was installed with English Display language.

Install additional language packs from an Office language pack as needed. There are a few considerations when making translations for a country like Belgium where ‘Dutch (Belgium)’ is installed as Windows language, but the language pack for Office 2013 only offers Dutch from The Netherlands: ‘Nederlands (Nederland)’. Take care to setup SSAS translations to match the name of the Display language and not of the Editing language.

Setup dimension translations for different languages in SSAS

Data Source View

Make sure that the Data Source View is refreshed to read the metadata for the new name columns. If your dimension table is based on a query, then adapt the query to read the new name columns. The table DimBlocking has been given the Friendly name ‘Blocking’.

Dimension designer - Structure

The dimension has a key and a name column. Most often a simple dimension with a key and a name has one Attribute, in this example called ‘Blocking’ with KeyColumn = Blocking.BlockingId and NameColumn = Blocking.BlockingName.

Dimension Designer – Translations

In the Translations design, we can add a new translations for the captions, for the Attributes and also for the [All] and [Unknown] members.

Preparation to translate [All] and [Unknown] members

The captions of these 2 members appear by default when browsing a dimension:

To make [All] and [Unknown] translatable we can explicitly specify them in the Dimension properties.

In the Dimension Structure design, select the Dimension in the Attributes Pane, and look for the following 2 Dimension properties:

• AttributeAllMemberName: set this to ‘All’ to enable translation. (Or use another word, e.g. Everything)
  


• UnknownMemberName: set this to ‘Unknown’ to enable translation (Or use any other word, like ‘Unidentified’)

Add Translations

In the Dimension Designer go to the Translations tab.

The Dimension properties that have translatable captions:

• Caption


• AttributeAllMemberName


• UnknownMemberName.

And for the Attributes:

• Caption

Click on the ‘Add new translation’ button

The Select language dialog box opens:

Select the language needed and make sure you have an exact match with the display language installed in MS-Office. E.g. choose ‘Dutch (Netherlands)’ when the Office 2013 language pack for ‘Nederlands (Nederland)’ is installed. Because I have a Dutch Office 2013 installation, the display language is shown translated by MS-Office.

For the Dimension Caption and Member names, type text in the Translation column.

For the Attribute translation, there is a translation for the caption, but here you can select a column name to use as translation for the attribute data. Click inside the translation column and you see a button. Click on the button to open the Attribute Data Translation dialog window. Enter the Translated caption and select the Translation column from the dimension table.

Save and deploy this dimension, make sure it is processed.

Connect MS-Excel to the SSAS cube

Use the default display language

The Dutch version of MS-Excel is setup to use ‘Nederlands’ as display language:

In the spreadsheet, connect to the Analysis Services Cube and create a new pivot table. The Pivot table fields shows the Blocking dimension with translation.

After selection of this dimension in the Rows section, the pivot table shows the Attribute data translated in Dutch.

Excel row 2, 3 and 4 show the translated members of the ‘Blocking’ attribute.

MS-Excel automatically translates ‘Row-labels’ and ‘Grand total’ as ‘Rijlabels’ and ‘Eindtotaal’. This is not a function of the SSAS translation.

Change to another display language

• Change the Display language to French.


• Close and restart MS-Excel.


• Open the same spreadsheet.


• In the Data section, click on the Refresh button.


• The pivot table is now shown with French captions and values.

Process SSAS cube in good order with SSIS package

Based on an article by Benny Austin about SSIS Package to Process SSAS Cube

I have decided to process the cubes I have to maintain in the same order as suggested in his article.

1. Process Dimensions
1. Process default
2. Process Update
2. Process Facts
1. Process default
1. Process Partitions – default
2. Process Measure groups – default
3. Process Cubes – default
2. Process Data
1. Process Partitions – data
2. Process Measure groups – data
3. Process Cubes – data
3. Process Index
1. Process Dimensions - index
2. Process Cube measures - index
3. Process Partitions – index

A screenshot of what the SSIS package looks like in VS 2012:

Sort date names order in a matrix report based on SSAS Data Source

 

I created a report based on a simple SSAS cube with a fact table containing a value per day per division. The report has a year parameter and needs to show the sum per month in a pivot table, with 12 months in the columns and a row for each division.

The report is easy to generate with the report wizard, but it results in columns where months are sorted by name: April, August, December, February, …

Problem

The Report designer sorts the columns alphabetically by the name of the month.

Solution

The months are supplied by the SSAS MDX in correct order, it is the report definition that sorts the resulting columns alphabetically.

The solution is quite easy: remove the sort order from the Month Column Group.

in the Group properties of the Month column group, go to the Sorting options and remove the sort on the Month:

So that no sorting is set:

The report shows the months in chronological order:

Error occurred while the attribute of the dimension from the database was being processed–snowflake dimension

Problem

While designing a new snowflake dimension in an Analysis services project, I received the following error message during deployment with Visual Studio of the project to the Analysis Server:

Errors in the OLAP storage engine: An error occurred while the 'Plant' attribute of the 'Device' dimension from the '<projectname>' database was being processed.

The snowflake dimension ‘Device’ is a combination of related tables: Country – Region – Plant – Device. The Dimension the same hierarchy as in the one-to-many relationship.

Each table is composed of a primary key Id column, a name column and various other properties. The snowflake dimension will be used for grouping and totalling production data, so that users can drill down from Country level to Regional, to Plant or to Device. The users do not know the Id values, they are shown the names.

The dimension attribute properties are setup to use the Id as Key, but to display the name and order the listed values by name, e.g. for Region

• KeyColumns: Region.id
• NameColumn: Region.Name
• OrderBy: Name

After the initial design of this dimension I wanted to deploy to the server, so as to be able to test my design in the Dimension Browser. Then I received the abovementioned error message.

SOLUTION: look for NULL values in Foreign key column that refers to parent table

The error message was displayed in the Error List window in Visual Studio. The Output window shows some more information about the error.

Warning -2128674815 : Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'dbo_Region', Column: 'Id', Value: '0'. The attribute is 'Region'.

When I queried SQL database table Plant for foreign key RegionId with value 0, none were found. When I queried for foreign key field being NULL, I found one such record in the Plant table. This record happens to be the main office of the company and is there for other purposes, so it must not be selected.

• Go to the Data Source View, right click on Plant table and select ‘Replace Table –> With New Named Query…
• In the query designer add the condition WHERE RegionId IS NOT NULL
• Save the query.
• Deploy the project

The project will now deploy without any further error messages, unless you have other data to clean up.

Analysis services 2012: a connection cannot be made, ensure that the server is running

After the installation of a new SQL Server 2012 with Analysis Services, I found that I could reach the Analysis services database perfectly well when I was remotely logged in on the Server and connected with SSMS locally. When I tried to connect with SSMS from another computer  I received the error message:

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

On the internet some advice is given about this connection problem.On the server with Analysis Services 2012 the SQL Server Browser must be running under the LOCALSYSTEM account. On my Server the browser service was running under LOCALSERVICE. After I changed this I still could not connect remotely.

Another advice was to try to connect to the Analysis Service using <Servername>:2383, in order not use SQL Server Browser. But I still received the same error message.

Finally it dawned to me that the firewall was probably blocking this port. On the Server with Analysis Services 2012, open ‘Windows Firewall with Advanced Security’ and add a new inbound rule, for TCP port 2383, to allow the connection in the Domain. This rule is similar as the rule one must setup for SQL Server Service in port 1433.

open ‘Windows Firewall with Advanced Security’

Select Inbound Rules, In Actions, click on ‘New Rule…’

In the New Inbound Rule Wizard, Rule Type, select Port, click Next

In the Protocols and Ports, Select ‘TCP’, Specific local ports, type ‘2383’, click Next

In Action, Select ‘Allow the connection’, Next

In Profile, check only ‘Domain’ (because I only want to grant access from my corporate domain). Next

In Name, type the name: ‘Sql Server analysis service port’ (or some other name that allows you to recognize your inbound rule). Finish.

As soon as the Inbound rule is created, the connection from another computer to the analysis service will work.

SSAS The connected user is not an Analysis Services server administrator.

Problem

On a new development system where my windows account has local admin rights, and sysadmin rights in the SQL Server 2012 with SSAS installation.

With SQL server Data Tools 2010 I want to create and deploy an Analysis Services Data mining project. When the Data source, views and Mining structures are setup, I want to deploy on the server for the first time, But I get this error message:

‘The connected user is not an Analysis Services server administrator.’

Solution

Reason of the problem is the UAC on de development OS. Solution is to add my windows account explicitly as an Analysis Services System account. But when I use SSMS to connect to analysis services and try to add my name I will get a similar error message.

the solution is:

• run SSMS as an administrator
• connect to the Analysis Services Server
• right-click on the server name and select ‘Properties..’
• In the Security page of the Server Properties window, click on the ‘Add…’ button to add a new account to the Server administrators, select your own windows account and add to the servers.

By explicitly adding your own account to the Analysis Services Server administrators, you can now create and deploy projects to the Analysis Services Server without running SSMs or SQL Server Data Tools.

SSAS error Unable to connect to the localhost server. Make sure that the server is started.

Problem

An error message on a new Development system with SQL Server 2012. The first time I created a SSAS Data Mining project with BI studio 2010.

When the Data source connection and a view was made, I switched over to the Mining Model Viewer, and got this error message:

‘Unable to connect to the localhost server. Make sure that the server is started.‘

This occurs because the SQL server was setup with a named instance. When you create a new Analysis Services project, the project defaults to the ‘localhost’ as Deployment Server.

Solution

Localhost is fine as long as the default instance is the standard instance.

To fix the problem, we need to set the Deployment Server in the project to the Named instance of the local SQL server.

In the Solution Explorer, right-click on the Project and select Properties…

In the Project property Pages tree view, under Configuration Properties, Select Deployment

In the pane on the right-hand side, in the Server property replace ‘localhost’ by the named instance, e.g. ‘MyServer\MyInstance’ and click OK to close the dialog.

In the Mining Model Viewer, click on the little refresh button next to the empty viewer dropdown box.

then the name of the Viewer selected during the Views setup appears in the dropdown together with a dialog box to build and deploy the project.

From then on you can continue your Analysis Services project.

No cubes can be found

Using VS2008 to create a new dataset in a reporting services project, based on a SSAS cube. The query designer responds with the error message: ‘No cubes can be found’

From this this article on Microsoft connect

“After connecting to SSAS in SQL Server Management Studio and attempting to browse a cube, the cube browser doesn't show any cubes. Attempting to open a dataset that uses an MDX query in Reporting Services results in the following error:
No cubes can be found“

“This was caused by the cube Visible property being set to False but what flips this property from True to False is unknown.“

Although the Visible property was set to true in Visual Studio 2008, I have had to explicitly reset it to True and then redeploy the cube. After that I could create a shared dataset based on this cube.

The target principal name is incorrect while trying to connect to Analysis Services server

A sql server had been setup for testing purposes but never been used with SSAS. The service was installed. Today when trying to connect to the AS server I received this error message:

The target principal name is incorrect.

The solution is to use th IP address of the server or the fully qualified domain name. e.g. instead of using ‘SERVER01’, use ‘SERVER01.somedomainname.com’
Thanks to Patrick Purviance who wrote about this problem in his blog: target principal is incorrect