Mastering Enterprise BI: Introduction to Translations

Monday Sep 11th 2006 by William Pearson
Share:

Support Analysis Services information consumers with diverse languages via Translations, which debut in Analysis Services 2005. BI Architect Bill Pearson leads a hands-on exposure to both types of Translations within a sample cube we construct for our purposes.

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.

Working with Translations

Overview and Discussion

We will create an Analysis Services Project within the Business Intelligence Development Studio, to provide the environment and the tools that we need to design and develop business intelligence solutions based upon Analysis Services 2005. As we have noted in other articles of this series, the Analysis Services Project that we create within the Studio will assist us in organizing and managing the numerous objects that we will need to support our efforts to create our Analysis Services database.

We will leverage the Cube Wizard in this article to quickly design and create a cube, allowing us to focus on the subject matter of the article with minimal peripheral distraction. The Cube Wizard helps us simplify the design and creation of our cubes, as it did within Analysis Services 2000, although the Analysis Services 2005 Cube Wizard is more powerful, leveraging IntelliCube technology to examine and classify many of the attributes of our data. Regardless of whether we make a habit of using the wizard in our cube development efforts, it certainly provides a way to rapidly generate a cube, if only to eliminate part of the repetitive work involved to create a "starting point" model, which we can then "prune and groom" to more precisely meet the business requirements of our employers and customers.

Considerations and Comments

For purposes of the practice exercises within this series, we will be working with samples that are provided with MSSQL Server 2005 Analysis Services. The samples with which we are concerned include, predominantly, the Adventure Works DW Analysis Services database (with member objects). The Adventure Works DW database and companion samples are not installed by default in MSSQL Server 2005. The samples can be installed during Setup, or at any time after MSSQL Server has been installed.

The topics "Running Setup to Install AdventureWorks Sample Databases and Samples" in SQL Server Setup Help or "Installing AdventureWorks Sample Databases and Samples" in the Books Online (both of which are included on the installation CD(s), and are available from www.Microsoft.com and other sources), provide guidance on samples installation. Important information regarding the rights / privileges required to accomplish samples installation, as well as to access the samples once installed, is included in these references.

Hands-On Procedure

We will get started by creating a new project within the Business Intelligence Development Studio, wherein we will create a Data Source pointed to a database sample provided with MSSQL Server 2005. This way, anyone with access to the installed application set and its samples can complete the steps in the practice session.

Preparation

Create a New Analysis Services Project

We begin our preparation within Business Intelligence Development Studio, where we will create a new Microsoft SQL Server 2005 Analysis Services (SSAS) Project. As we have noted in other articles, those of us who have worked within earlier versions of Reporting Services, or within the Visual Studio development environment under other circumstances, will recognize the general look and feel of the Studio. Under this new style of development for Analysis Services, to which much of the documentation refers as "project mode," we create an Analysis Services Project, which houses a set of Analysis Services objects. The object set typically includes Data Sources, Data Source Views, Dimensions, Cubes, and other components.

As we have noted in other articles, using an Analysis Services Project template affords us many benefits, such as helping us to organize and manage the items that are required to build, debug, and deploy an Analysis Services database. The development of an Analysis Services cube, a preparatory step to support our primary objectives within this session, as well as one of the most common tasks in Analysis Services, can be accelerated though the use of the Analysis Services 2005 Cube Wizard, which both guides and simplifies the process (as is the way of wizards), as we noted earlier.

In an effort to remain focused on the essentials of working with Translations, we will not go into the myriad processes and structures that surround and underlie the UDM / cube in general. As we have noted previously, however, it is useful to make a mental note that all the objects that we create and store within an Analysis Services project are defined as files (using an XML representation) that eventually are deployed to an Analysis Services database. We will discuss how this transpires at the appropriate point within our practice session.

1. Click the Start button.

2. Select Microsoft SQL Server 2005 within the Program group of the menu.

3. Click SQL Server Business Intelligence Development Studio, as depicted in Illustration 1.


Illustration 1: Opening SQL Server Business Intelligence Development Studio

The Microsoft Visual Studio 2005 development environment opens, beginning with the Start page, as shown in Illustration 2.


Illustration 2: The Start Page, Microsoft Visual Studio 2005 Development Environment (Compressed View)

4. Close the Start Page.

5. Select File -> New on the Visual Studio main menu.

6. Select Project from the cascading menu, as depicted in Illustration 3.


Illustration 3: Beginning a New Project ...

The New Project dialog appears.

7. Select Business Intelligence Projects in the Project types pane of the dialog.

8. Select Analysis Services Project in the Templates pane, as shown in Illustration 4.


Illustration 4: Select Analysis Services Project

NOTE: The templates that appear in your template pane may differ, depending upon which SQL Server 2005 components are installed in your environment, as well as upon whether additional templates (for Business Intelligence Projects or other types of projects that can be created) have been defined in Visual Studio.

9. Change the project Name (currently displaying a default) to the following:

ANSYS051 Translations

The Solution Name changes to match the project Name by default.

10.  Navigate to a convenient location to store the Project and Solution files, modifying the Location box accordingly (Visual Studio will create a directory based upon our input here).

The relevant section of the New Project dialog appears similar to that depicted in Illustration 5.


Illustration 5: The New Project Dialog (Relevant Section), with our Input

11.  Click OK to accept our input and to create the new Analysis Services Project.

The New Project dialog closes. Visual Studio creates the project, which appears underneath solution ANSYS051_Translations, within the Solution Explorer as shown in Illustration 6.


Illustration 6: The New Analysis Services Project Appears

Having created a new Analysis Services Project, we are ready to define a Data Source and Data Source View.

Define a Data Source

Defining a Data Source, as we have discovered in other articles of our series, is typically the first step we take with a new Analysis Services Project. A project can have multiple Data Sources, within which we are essentially defining the connection string used to connect to the actual source of our data. While we can define connections to source databases on remote computers in the business environment, we will, in this article, be working with one of the sample databases that are available to us when we install MSSQL Server 2005, the AdventureWorksDW database.

Let's create a Data Source within our new ANSYS051_Translations project by taking the following steps:

1. Right-click Data Sources folder within the Solution Explorer.

2. Select New Data Source ... from the context menu that appears, as depicted in Illustration 7.


Illustration 7: Initial Step in Creating a New Data Source

The Data Source Wizard appears, opening with the Welcome to the Data Source Wizard page, as shown in Illustration 8.


Illustration 8: Welcome to the Data Source Wizard ...

3. Click Next.

The Select how to define the connection page appears. This page allows us to define a Data Source based upon a new or existing connection. (It also allows us to base a Data Source upon a connection that has been previously created.)

4. Click New on the Select how to define the connection page.

The Connection Manager dialog appears.

5. Ensure that .Net Providers\SqlClient Data Provider is selected in the Provider selection box atop the Connection Manager dialog.

6. Type / select the appropriate server name / server name with instance into the Server name box.

We can substitute "localhost" or "localhost\<instance name>" here, if it is appropriate to our environment. (My server name with instance is MOTHER1\MSSQL2K5, as may be seen in several of the illustrations in this and other articles.)

7. Select / complete the appropriate authentication choice for the local environment in the section labeled Log on to the server. (I selected the radio button to the left of Use Windows Authentication in the lab environment in which I prepared this article.)

8. Select AdventureWorksDW in the Select or enter a database name selector box, within the Connect to a database section of the dialog.

The Connection Manager dialog appears similar to that depicted in Illustration 9.


Illustration 9: Connection Manager Dialog in My Lab Environment

9. Click the Test Connection button (in the bottom left corner of the Connection Manager dialog) to ascertain that we have a valid connection.

We receive a positive confirmation message, Test connection succeeded, assuming settings in the dialog are in accordance with the above steps, as appropriate for our own environments. The confirmation message box appears as shown in Illustration 10.


Illustration 10: Confirmation Message Indicating Successful Connection Test

10.  Click OK to dismiss the message box and return to the Connection Manager dialog.

11.  Click OK on the Connection Manager dialog to accept our settings and create the data connection.

We return to the Select how to define the connection page of the Data Source Wizard, which appears similar to that depicted in Illustration 11.


Illustration 11: "Select How to Define the Connection" Page, with New Data Connection

12.  Ensuring that the new Data Connection is selected, click Next.

We arrive at the Impersonation Information page, where we define connection credentials for the data source.

13.  Click the radio button to the immediate left of Use the service account (or supply other appropriate local credentials), as shown in Illustration 12.


Illustration 12: Select the Use the Service Account Option

14.  Click Next.

The Completing the Wizard page appears, as depicted in Illustration 13.


Illustration 13: Completing the Wizard ...

We will leave the assigned Data Source name in place for purposes of our practice session.

15.  Click Finish to create the new Data Source, and to dismiss the Data Source Wizard.

The Wizard closes, and the new Data Source appears in the Data Sources folder within the Solution Explorer as shown in Illustration 14.


Illustration 14: The New Data Source Appears in the Solution Explorer

Having established the basic prerequisites, we are ready, at this stage, to create a Data Source View.

Define a Data Source View

Having defined the Data Source within our Analysis Services Project, our next steps surround the creation of the Data Source View, a procedure that is customary at this stage in most Analysis Services Projects. The Data Source View provides a single, unified view of the metadata from the tables and views that concern us within our project.

NOTE: For more information on Data Source Views, see my article Introduction to MSSQL Server Analysis Services: Introducing Data Source Views at Database Journal.

To define a Data Source View, we will take the following steps:

1. Right-click Data Source Views folder within the Solution Explorer.

2.  Select New Data Source View ...from the context menu that appears, as depicted in Illustration 15.


Illustration 15: Initial Step in Creating a New Data Source View

The Data Source View Wizard appears, opening with the Welcome to the Data Source View Wizard page, as shown in Illustration 16.


Illustration 16: The Initial Page of the Data Source View Wizard

3.  Click Next.

We arrive at the Select a Data Source page, where we see our Adventure Works DW Data Source in the Relational data sources list box on the left side of the page, as depicted in Illustration 17.


Illustration 17: Adventure Works DW Appears in the Relational Data Sources List

Our newly created Data Source is positioned as the default, and will serve us in meeting the objectives of our practice exercise.

4.  Click Next.

Analysis Services scans the Adventure Works DW schema, and then we arrive at the Select Tables and Views page. Here we see the various tables of the Adventure Works DW data source appear in the Available objects list box on the left of the page. We will be selecting two fact tables, to allow us to house both sales and "budget" data within our cube.

5.  Click FactInternetSales in the Available objects list to select it.

6.  Click the button marked ">" to move FactInternetSales to the Included objects list on the right half of the page.

7.  Click FactSalesQuota in the Available objects list to select it.

8.  Click the button marked ">" to move FactSalesQuota to the Included objects list on the right half of the page.

The added tables appear within the Included objects list of Select Tables and Views page as shown in Illustration 18.


Illustration 18: Table Selections for Inclusion in the Data Source View

9.  Click the newly added FactInternetSales table in the Included objects list to select it.

10.  Click the Add Related Tables button, located underneath the Included objects list.

11.  Click the newly added FactSalesQuota table in the Included objects list to select it.

12.  Click the Add Related Tables button, located underneath the Included objects list, once again.

Several related tables appear within the Included objects list, as depicted in Illustration 19.


Illustration 19: Our Tables Selection for the Data Source View

13.  Click Next.

The Completing the Wizard page appears, as shown in Illustration 20.


Illustration 20: The Final Page of the Data Source View Wizard

As indicated in the Completing the Wizard page, our selection includes the following tables:

  • FactInternetSales
  • FactSalesQuota
  • DimProduct
  • DimCurrency
  • DimTime
  • FactInternetSalesReason
  • DimSalesTerritory
  • DimPromotion
  • DimCustomer
  • DimEmployee

14.  Click Finish to create the new Data Source View, and to dismiss the Data Source View Wizard.

Our new Data Source View, Adventure Works DW, appears in the Data Source Views folder within the Solution Explorer window, as depicted in Illustration 21.


Illustration 21: The New Data Source View in the Solution Explorer

The Data Source View is also presented within the Data Source View Designer, which opens simultaneously with the Data Source View. The contents of the Designer appear within Business Intelligence Development Studio, as shown in Illustration 22.


Illustration 22: The New Data Source View in the Data Source View Designer

One of several designers within the Studio, the Data Source View Designer for our new Data Source View contains numerous elements that aid us in our organization and design efforts. We can modify the composition of the tables that comprise our view, as well as performing other activities, here.

We will next create a cube based upon the Data Source and Data Source View we have assembled. As a part of this process, we will also set up, and at least partially align, Measure Groups.

Create a Cube with Basic Objects

Create a Cube

We will create a basic cube, with measures we will add as a part of cube creation, within which we will examine Translations, the focus of our practice session. We will concentrate upon other areas within the overall design and creation process in other articles of the series – our primary objective here is to gain some familiarity with Translations – not to concern ourselves with the myriad other options and topics contained within general cube design and development.

1. Right-click the Cubes folder within the Solution Explorer.

2. Select New Cube ... from the context menu that appears, as depicted in Illustration 23.


Illustration 23: Select New Cube to Begin Cube Creation

The Welcome to the Cube Wizard dialog appears, as shown in Illustration 24.


Illustration 24: The Welcome to the Cube Wizard Dialog Appears ...

3. Click Next.

4. Ensure that Build the cube using a data source checkbox is selected on the Select Build Method dialog that appears next, along with the Auto build checkbox, and with Create attributes and hierarchies selected in the drop-down selector underneath the Auto build setting.

These default settings are depicted in Illustration 25.


Illustration 25: Ensure the Default Settings on the Select Build Method Dialog

5. Click Next.

6. Ascertain that the Data Source View we have created, Adventure Works DW, is selected within the Available data source views list on the left side of the Select Data Source View dialog, which appears next. (The tables contained within the Data Source View are displayed on the right side of the dialog).

The Select Data Source View dialog appears, as shown in Illustration 26.


Illustration 26: Select AdventureWorks DW as the Data Source View

7. Click Next.

The Cube Wizard begins scanning the relational schema, identifying fact and dimensional tables, as indicated on the Detecting Fact and Dimension Tables page that appears next. We quickly receive a message indicating that the Wizard has completed its analysis of the relationships between tables in the Data Source View, and that it proposes its suggestions on the following page, as depicted in Illustration 27.


Illustration 27: The Detecting Fact and Dimension Tables Page Indicates Completion ...

8. Click Next.

The Identify Fact and Dimension Tables dialog next appears. Let's select a Time dimension table, and narrow the table selection somewhat, for the purposes of our rudimentary cube.

9. Select the DimTime table in the drop-down Time dimension table selector.

10.  Uncheck the following checkbox, on the Tables tab of the dialog.

Dimension Column: DimCurrency

NOTE: We can also make table selections graphically via the Diagram tab of the Identify Fact and Dimension Tables dialog.

The Identify Fact and Dimension Tables dialog appears, as shown in Illustration 28.


Illustration 28: The Identify Fact and Dimension Tables Dialog, with Our Modifications

11.  Click Next.

The Select Time Periods dialog appears.

12.  Leaving any unspecified columns blank, select the Time Table Columns values shown in Table 2 for each of the listed Time Property Names.

Time Property Name

Time Table Columns

Year

CalendarYear

Quarter

CalendarQuarter

Month

EnglishMonthName

Date

FullDateAlternateKey


Table 2: Selections within Respective Time Table Columns in Select Time Periods Dialog

The Select Time Periods dialog appears, with our selections, as depicted in Illustration 29.


Illustration 29: The Select Time Periods Dialog, with Our Selections

13.  Click Next.

The Select Measures dialog appears next.

14.  Uncheck the following checkboxes:

  • Revision Number
  • Discount Amount
  • Product Standard Cost
  • Tax Amt
  • Freight

The Select Measures dialog appears, as partially shown in Illustration 30.


Illustration 30: The Select Measures Dialog (Partial View), with Adjusted Selections

15.  Click Next.

The Detecting Hierarchies page appears next, as hierarchy detection begins. We receive rapid indication that dimension tables have been scanned and hierarchies detected, as depicted in Illustration 31.


Illustration 31: The Detecting Hierarchies Page, with Indication of Relationships Detected

16.  Click Next.

We move to the Review New Dimensions page, which appears as shown in Illustration 32.


Illustration 32: The Review New Dimensions Page

17.  Click Next.

The Completing the Wizard page appears next.

18.  Change the default Cube name to the following:

ANSYS051 – Translations 

The Completing the Wizard page appears as depicted in Illustration 33.


Illustration 33: The Completing the Wizard Page

19.  Click Finish.

The Wizard concludes, and the Cube Designer opens, as shown in Illustration 34.


Illustration 34: The Cube Designer Opens ...

Perform Basic Modifications to Measure Groups

Because our cube now incorporates three fact tables, FactInternetSales, FactSalesQuota, and FactInternetSalesReason, which we included within the underlying Data Source View, the Cube Wizard has generated a default Measure Group for each originating fact table. (The Measure Groups are named after the tables they represent, by default.) We can verify this fact by examining the tree within the Measures pane that appears in the upper left corner of the Cube Structure tab, which itself appeared by default within the Designer when the Cube Wizard concluded its work.

1. In the Measures pane, expand the three Measure Groups, Fact Internet Sales, Fact Sales Quota and Fact Internet Sales Reason.

The expanded Measure Groups, with member measures exposed, appear as depicted in Illustration 35.


Illustration 35: The Measures within their Respective Groups ...

While we can alter the Measure Groups to suit the nature of the measures, and would likely normally do so to manage their respective granularities, and so forth, we will leave these and other cube objects largely as they have been created by default, to allow us to focus upon the central subject matter of this session. We will make just a few alterations, to finish fleshing out a representative set of objects for general examination as we explore Translations within the Procedure section that follows.

NOTE: For more information on the creation and management of Measure Groups, see Mastering Enterprise BI: Working with Measure Groups, a member of my Introduction to MSSQL Server Analysis Services series at Database Journal.

We will make our minimal modifications to the Measures Groups within the Cube Designer, before making alterations to suit the nature of the measures. The basis of our modifications, typically enough, is that the granularities of the Measure Groups are different. As an illustration, Sales Amount, together with the other measures from the Fact Internet Sales table, is related to the actual date (the "TimeKey" field in the Time dimension) of the transaction, while the Sales Amount Quota measure, from the Fact Sales Quota table, is a "budget" value, relating to the year level of the Time dimension. To separate conceptual "single apples from baskets of apples," we need to ensure that our Measure Groups are dimensionally aligned to reflect their true nature.

1. Click the Dimension Usage tab atop the Cube Designer, as shown in Illustration 36.


Illustration 36: Click the Dimension Usage Tab ...

The Dimension Usage page appears.

2. Click the box currently containing "TimeKey," at the intersection of the Fact Sales Quota (Measure Group) column and the Dim Time (Dimension) row.

3. Click the ellipses ("...") button that appears to the immediate right of the selected box, as depicted in Illustration 37.


Illustration 37: Accessing a Dimension Usage Setting ...

The Define Relationship page appears.

4. In the selector box labeled Select relationship type, ensure that Regular remains selected.

5. In the selector box labeled Granularity attribute, select CalendarYear.

6. Click the selector box that appears at the intersection of the row labeled CalendarYear (under the column marked Dimension Columns) and the column marked Measure Group Columns, within the Relationship table of the Define Relationships page.

7. Select CalendarYear in the selector, as shown in Illustration 38.


Illustration 38: Select CalendarYear in the Relationships Table ...

The Define Relationship page appears, with our new settings, as depicted in Illustration 39.


Illustration 39: The Define Relationship Page with Our Settings

8. Click OK to accept settings and close the Define Relationship page.

9. Within the Dimension Usage settings, click the ellipses ("...") button to the immediate right of the box (currently unoccupied) at the intersection of the Fact Internet Sales (Measure Group) column and the Dim Time (Dimension) row, as shown in Illustration 40.



Illustration 40: Defining Relationship for Fact Internet Sales – Dim Time

The Define Relationship page appears.

10.  In the selector box labeled Select relationship type, select Regular.

The page expands to display previously hidden fields, which support definition of the Regular dimension type.

11.  In the selector box labeled Granularity attribute, select TimeKey.

12.  Click the selector box that appears at the intersection of the row labeled TimeKey (under the column marked Dimension Columns) and the column marked Measure Group Columns, within the Relationship table of the Define Relationship page.

13.  Select ShipDateKey in the selector, as depicted in Illustration 41.


Illustration 41: The Define Relationship Page with Our Input

(We have selected ShipDateKey for purposes of our practice exercise, as if it were organizational accounting policy to record sales as of Ship Date. This is only to make the exercise more convenient.)

14.  Click OK to accept settings and close the Define Relationship page.

The Dimension Usage settings appear, with all modifications to this point, as shown in Illustration 42.


Illustration 42: Dimension Usage Settings, with Modifications ...

We can obviously perform myriad enhancements and extensions to, and operations surrounding, our cube from within the multi-tabbed Cube Designer component of the Business Intelligence Development Studio. We examine the many options in other articles of my Introduction to MSSQL Server Analysis Services series, where we define business needs and then meet them with the appropriate functionality(ies). At this point we have sufficiently aligned measures with dimensions to complete our preparation of an environment within which we can get some hands-on exposure to the definition of Translations within our cube.

Procedure

Define Translations within the Cube

Create a Cube Translation

Let's first create a Cube Translation within our new cube, to get some experience with the process. As we shall see, we create and manage Cube Translations on the Translations tab of the Cube Designer. For purposes of our practice session, we will say that we have been asked by the Adventure Works organization to add the capability for Spanish-speaking consumers, which comprise a growing proportion of worldwide employees within the organization, to be able to work with the same cube that we use in operational analysis in the United States.

1. Click the Translations tab atop the Cube Designer, as depicted in Illustration 43.


Illustration 43: Click the Translations Tab ...

The Translations page appears. Here, we see a list of the Cube Objects (similar to the list that we saw on the Perspectives tab in Mastering Enterprise BI: Introduction to Perspectives), grouped by object type, and subgrouped by parent structures, for which we can define Translations. We will say, for purposes of our practice session, that we are creating a Spanish Translation for Adventure Works users in Spain.

2. Right-click the "blank" space to the right of the objects list.

3. Select New Translation from the context menu that appears, as shown in Illustration 44.


Illustration 44: Creating a New Translation

The Select Language dialog box appears.

4. Scroll down to the Spanish (Spain) entry in the list underneath the title "Select a language for the new translation".

5. Click the Spanish (Spain) entry, to select it, as depicted in Illustration 45.


Illustration 45: Creating a New Translation

6. Click OK, to select the entry and to close the dialog.

We see a new column, labeled Spanish (Spain), appear on the right of the grid on the Translations tab, as shown in Illustration 46.


Illustration 46: The New Translation Appears in the (Partially Shown) Grid ...

In this column, we define the captions by typing in the local phrases as appropriate. Note that we can supply Spanish words here for the Cube, Measure Groups, Measures and Dimensions. Because we would need to supply a large number of phrases in the Translation language to completely localize our metadata, we will move to our next section at this point. In our business environments, this process might be most accurately and efficiently undertaken by a native speaking employee or contractor.

Let's take a look at the steps involved in adding Dimension Translations in the next section.

Create a Dimension Translation

As we mentioned in our introductory section, Analysis Services also supports Dimension Translations, which include representations of dimension and member names. We noted that a Dimension Translation is a language-specific representation of the name of a dimension object or of its members / attributes, such as a caption, member name, or hierarchy level. In effect, we are applying Translations to data, just as we did to the structure (or metadata) with Cube Translations earlier.

Let's take a look at the steps involved in adding Dimension Translations.

1. Right-click the Customer dimension in the Solution Explorer.

2. Select Open from the context menu that appears, as depicted in Illustration 47.


Illustration 47: Opening the Dimension Designer for the Customer Dimension ...

The Dimension Designer opens for the Customer dimension, where we will be working with the Translations tab, just as we did within the Cube Designer for Cube Translations in the previous section.

3. Click the Translations tab atop the Dimension Designer, as shown in Illustration 48.


Illustration 48: Click the Translations Tab ...

The Translations page for the Dimension Designer appears.

Here, we see a list of the Dimension Attributes and Hierarchies for the Customer dimension of the sample cube, as depicted in Illustration 49.


Illustration 49: Customer Dimension - Translations Tab of the Dimension Designer

Here we see the Dimension, its Attributes and Hierarchies, grouped by object type, and subgrouped by parent structures, for which we can define Translations. Let's say, once again for purposes of our practice session, that we are creating a Spanish Translation for Adventure Works users in Spain.

4. Right-click the "blank" space to the right of the objects list on the Translations tab.

5. Select New Translation from the context menu that appears, as shown in Illustration 50.


Illustration 50: Creating a New Translation

The Select Language dialog box appears, just as it did in our efforts with Cube Translations earlier.

6.  Scroll down to the Spanish (Spain) entry in the list underneath the title "Select a language for the new translation".

7.  Click the Spanish (Spain) entry, to select it, as depicted in Illustration 51.


Illustration 51: Creating a New Translation

8.  Click OK, to select the entry and to close the dialog.

We see a new column, labeled Spanish (Spain), appear on the right edge of the grid on the Translations tab, again in a similar manner to that which we observed on the Translations tab within the Cube Designer earlier, as shown in Illustration 52.


Illustration 52: The New Translation Appears in the (Partially Shown) Grid ...

In this column, we define the captions by typing in the local phrases as appropriate, just as we would do for Cube Translations within the Cube Designer. Also similar to the corresponding steps for adding Cube Translations, we might consider delegating the task of assigning the potentially large number of phrases required in the Translation language to a native speaking employee or contractor.

Depending upon the number of languages represented by users of our organization's information consumer audience, we might assign multiple Cube and Dimension Translations in a similar manner to that we have examined.

Browsing Translations

Upon completing the addition of our Translations, we would next deploy the solution, as we have done in various articles of this series. Once this has been accomplished, we can fully leverage the Browser that the Cube Designer and Dimension Designer each make available to us. While we have not input any Translations, we can still examine the simple steps involved in "switching Translations" within the Cube Browser by taking the following steps:

1. Return to the ANSYS051_Translations.cube tab, which we opened via the Cube Designer earlier (re-open if necessary), by clicking the appropriately marked tab, as depicted in Illustration 53.


Illustration 53: Return to the ANSYS051_Translations Cube Designer View ...

2. Click the Browser tab to initialize the Browser for the Designer.

Once the Browser loads, we receive a message here indicating that a connection cannot be made, due to the fact that we have not deployed the solution and performed a reconnection. Our point here is to show that, had we populated the Translations, and then performed the deployment / reconnection, our next step would be to switch among the Translations for the data displayed by selecting the desired Translation from the Language selector (to the immediate right of the Perspectives selector, atop the Browser tab). Doing so for Spanish (Spain), we would make the appropriate selection within the Language selector, which would be populated once deployed / reconnected, as shown in Illustration 54.


Illustration 54: Shifting between Translations in the Cube Designer Browser ...

Performing the switch between Translations is quite similar within the Dimension Designer, and consists of the following steps:

3. Return to the Dim Customer.dim tab, which we opened via the Dimension Designer earlier (re-open if necessary), by clicking the appropriately marked tab, as depicted in Illustration 55.


Illustration 55: Return to the Dim Customer.dim Dimension Designer View ...

4. Click the Browser tab to initialize the Browser for the Designer.

Here, as was the case with the Cube Browser, had we populated the Translations, and then performed the deployment / reconnection, our next step would be to switch among the Translations for the data displayed by selecting the desired Translation from the Language selector (to the immediate right of the Hierarchy selector, atop the Browser tab). Doing so for Spanish (Spain), we would make the appropriate selection within the Language selector, which would be populated once deployed / reconnected, as shown in Illustration 56.


Illustration 56: Shifting between Translations in the Dimension Designer Browser ...

And so it becomes clear that shifting among Translations within the Browsers is a simple matter of making a choice of a valid Translation with the respective dropdown selector, once we have deployed / reconnected with the appropriate Translation entries in place.

5. Select File -> Save All from the main menu to save our work through this point, as depicted in Illustration 57.


Illustration 57: Saving All Work from Our Session

6. Select File -> Exit, when ready, to leave the Business Intelligence Development Studio.

Conclusion

In this article, we introduced and overviewed Translations, a feature that allows us to support localized presentations of Analysis Services objects with ease. After introducing the concept of Translations, we overviewed their addition, and discussed ways in which they can offer flexibility in supporting our cube and solution / application end users. In preparation for our examination of the steps involved in extending our cubes to leverage Translations, we prepared Analysis Services, and our environment, by creating an Analysis Services Project to house our development efforts, and to serve as a platform for the design of a quick cube model, within which to perform subsequent procedures in our session. We next performed the steps that are common to the design and creation of any cube within Analysis Services, including the creation of a Data Source, containing the information Analysis Services needs to connect to a database, and a Data Source View containing schema information.

We then created a basic cube, referencing our Data Source and Data Source View, based upon data from our sample relational tables. Next, we discussed the detailed steps of adding Translations, via the Translations tab of the Cube and Dimension Designers. Finally, after discussing the need for deploying and reconnecting our solution, after adding the specific Translation values for both types of Translations, we discussed the steps involved in switching between available Translations within each of the Cube and Dimension Browsers.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved