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.
Introduction
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.
Object | Properties |
Cube | Caption |
Measure | Caption / Display Folder |
Cube Dimension | Caption |
Perspective | Caption |
Key Performance Indicator (KPI) | Caption / Description / DisplayFolder |
Action | Caption |
Named Set | Caption |
Calculated Member | Caption |
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.
