Mastering Enterprise BI: Introduction to Translations

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.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles