Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 11, 2006

Mastering Enterprise BI: Introduction to Translations

By William Pearson

About the Series ...

This article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server Analysis Services, with each installment progressively presenting features and techniques designed to meet specific real - world needs. For more information on the series, please see my initial article, Creating Our First Cube.

Note: To follow along with the steps we undertake, the following components, samples and tools are recommended, and should be installed according to the respective documentation that accompanies MSSQL Server 2005:

  • Microsoft SQL Server 2005 Database Engine

  • Microsoft SQL Server 2005 Analysis Services

  • Microsoft SQL Server 2005 Integration Services

  • Business Intelligence Development Studio

  • Microsoft SQL Server 2005 sample databases

  • The Analysis Services Tutorial sample project and other samples that are available with the installation of the above.

To successfully replicate the steps of the article, you also need to have:

  • Membership within one of the following:

    • the Administrators local group on the Analysis Services computer

    • the Server role in the instance of Analysis Services

  • Read permissions within any SQL Server 2005 sample databases we access within our practice session, as appropriate.

Note: Current Service Pack updates are assumed for the operating system, MSSQL Server 2005 ("MSSQL Server"), MSSQL Server 2005 Analysis Services ("Analysis Services"), MSSQL Server 2005 Reporting Services ("Reporting Services") and the related Books Online and Samples. Images are from a Windows 2003 Server environment, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2005 and its component applications.

About the Mastering Enterprise BI Articles ...

Having implemented, and developed within, most of the major enterprise BI applications for many years, and having developed an appreciation for the marriage of ease of use and analytical power through my background in Accounting and Finance, I have come to appreciate the leadership roles Cognos, Business Objects, and other vendors have played in the evolution of OLAP and enterprise reporting. As I have stated repeatedly, however, I have become convinced that the components of the Microsoft integrated business intelligence solution (including MSSQL Server, Analysis Services, and Reporting Services) will commoditize business intelligence. It is therefore easy to see why a natural area of specialization for me has become the conversion of other enterprise business intelligence to the Microsoft solution. In addition to converting formerly dominant business intelligence systems, such as Cognos, Business Objects / Crystal, MicroStrategy and others, to the Reporting Services architecture, I regularly conduct strategy sessions about these conversions with large organizations in a diverse range of industries – the interest grows daily as awareness of the solution becomes pervasive. Indeed, the five-to-six-plus figures that many can shave from their annual IT budgets represent a compelling sweetener to examining this incredible toolset.

The purpose of the Mastering Enterprise BI subset of my Introduction to MSSQL Server Analysis Services series is to focus on techniques for implementing features in Analysis Services that parallel – or outstrip - those found in the more "mature" enterprise OLAP packages. In many cases, which I try to outline in my articles at appropriate junctures, the functionality of the OLAP solutions within well-established, but expensive, packages, such as Cognos PowerPlay Transformer and Cognos PowerPlay, can be met – often exceeded – in most respects by the Analysis Services / Reporting Services combination – at a tiny fraction of the cost. The vacuum of documentation comparing components of the Microsoft BI solution to their counterparts among the dominant enterprise BI vendors, to date, represents a serious "undersell" of both Analysis Services and Reporting Services, particularly from an OLAP reporting perspective. I hope, within the context of the Mastering Enterprise BI articles, to demonstrate that the ease of replicating popular enterprise BI features in Analysis Services will be yet another reason that the Microsoft integrated solution will commoditize business intelligence.

For more information about the Mastering Enterprise BI articles, see the section entitled "About the Mastering Enterprise BI Articles" in my article Relative Time Periods in an Analysis Services Cube, Part I.


Translations act as representations of Analysis Services objects. Translations are similar in concept to Perspectives (see Mastering Enterprise BI: Introduction to Perspectives, a member of my Database Journal series Introduction to MSSQL Server Analysis Services). In the case of Translations, the representations are language-specific, and provide server-based support for client applications that have users with multiple languages. The idea, of course, is to extend the value of our cubes to consumers of varying backgrounds: various nationalities within a global organization can view and understand relevant metadata and data within the cube. As an illustration, an information consumer in Spain can access a cube created and maintained in the United States, yet can view various object property values in Spanish.

Analysis Services supports two general types of Translations. In addition to Cube Translations, which provide representations for Analysis Services objects (such as display folders or captions), Analysis Services also supports Dimension Translations. A Dimension Translation is a language-specific representation of the name of a dimension, its members, or attributes (for example, captions, member names, hierarchy levels, and the like).

We can define Translations for the individual Analysis Services objects via the respective properties presented in Table 1.






Caption / Display Folder

Cube Dimension




Key Performance Indicator (KPI)

Caption / Description / DisplayFolder



Named Set


Calculated Member


Table 1: Cube Objects, with Associated Properties, for Which We Can Define Translations

As we shall see in the hands-on practice session that follows, the creation and maintenance of Translations is handled in a manner similar to the performance of the same actions for Perspectives (see Mastering Enterprise BI: Introduction to Perspectives for the details of Perspective creation and maintenance). Both types of Translations are managed from a perspective Translations tab. For Cube Translations, this tab is accessed from the Cube Designer. For Dimension Translations, we perform creation and maintenance from the Translations tab within the Dimension Designer. We will get exposure to both types of Translations in the practice session that follows. Moreover, we will compare and contrast the two types as we explore them, so as to reinforce our understanding of the differences and similarities as we take what we have learned into our respective working environments.

In this article, we will examine Translations, and gain exposure to the process of adding them to a basic cube we construct within the Business Intelligence Development Studio. We will overview the creation of Translations, and discuss ways in which they can enable us to offer flexibility to the end users of our cubes and solutions / applications. As a part of our examination of the steps, we will:

  • Prepare Analysis Services, and our environment, by creating an Analysis Services Project to house our development steps, and to serve as a platform for the design of a quick cube model, within which to perform subsequent procedures in our session;

  • Create a Data Source containing the information Analysis Services needs to connect to a database;

  • Create a Data Source View containing schema information;

  • Build a cube based upon our Data Source and Data Source View, containing data from sample relational tables;

  • Add examples of Measure Groups as part of cube design;

  • Examine the creation of a Spanish Translation for a hypothetical intended audience;

  • Examine the steps involved, within the respective Designers' Browsers, in "switching" between Translations.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM