SQL Server 2005 : Advanced OLAP - Translations

1/2/2013 6:25:36 PM

Much like you do with perspectives, you define translations on their own tab in the cube designer, in a grid containing distinct rows for each object in the cube, with each translation occupying its own column in the grid. Rather than soliciting a name and providing check boxes next to each object, however, the Translations tab solicits a language and culture and provides an editable cell for the translated name of each object.

For our exercise, click on the Translations tab, and then click the New Translation toolbar button (second from the right), the Cube/New Translation... main menu option, or the New Translation... shortcut menu option. Select Spanish (Spain) in the Select Language dialog box, and then click OK. A translation should contain localized strings for each object in the cube, but we will translate just the Time dimension for this exercise. Type Tiempo in the cell at the intersection of the Spanish (Spain) translation’s column and the Time dimension row, as shown in Figure 1.

Figure 1. The Translations tab with the Time dimension translation entry for Spanish (Spain)

Our work on the Translations tab is finished, but our work in defining the translation is not. We still need to define translated names for each attribute and hierarchy, and we need to explore how to provide translations for members within the attributes. All attributes of our Time dimension happen to be numeric, so let’s add a new attribute that displays the name of the month rather than its number. Then we can look at how to provide Spanish translations for each member of the new attribute.

To create the attribute, first run the script tblMonthNames.sql. This adds to the Northwind database a table called tblMonthNames that contains, in separate columns, English and Spanish names for each month of the year, as well as the corresponding month number. After the script has run, add the tblMonthNames table to the Northwind data source view with the data source view designer’s Add/Remove Objects toolbar button (on the far left), the Data Source View/[Add/Remove Tables...] main menu option, or the Add/Remove Tables... shortcut menu option. Then create a relationship from tblTime.Month to tblMonthNames.Month Number. (Click Yes when asked if you’d like to create a logical primary key.) Save your changes and click OK when asked if you’d like to proceed in updating the Sales cube as well.

Next, open the Time dimension in its designer and show the tblMonthNames table in the Data Source View pane, using the Data Source View/Show Tables... main menu option, the Show Tables... option on the shortcut menu, or the Show Table toolbar button (fifth from the left). Now create a new attribute by dragging and dropping the English Month Name field from the tblMonthNames table in the Data Source View pane onto the Attributes pane. Rename the new attribute from English Month Name to Month Name and change its OrderBy property to Key. Change its KeyColmns property to tblMonthNames.MonthNumber by clicking the property’s ellipsis button and changing the Source.ColumnId property in the DataItem Collection Editor dialog box (Figure 2) and clicking OK.

Figure 2. Changing the Month Name attribute’s KeyColumns property in the DataItem Collection Editor dialog box

Finally, change the Month Name attribute’s NameColumn property to tblMonthNames.EnglishMonthName.

Now onto the translations work. Click the dimension designer’s Translations tab, and then add a new translation (using any of the options discussed earlier for adding the cube’s translation). As in the cube designer, select Spanish (Spain) in the Select Language dialog box, and then click OK. Now enter the following Spanish strings for the dimension, each of its attributes, and its hierarchy:

ObjectTranslated Name
Time (dimension)Tiempo
Year (attribute)Año
Quarter (attribute)Trimestre
Month (attribute)Mes
OrderDate (attribute)Fecha de la Orden
Time Key (attribute)Llave de Tiempo
Month Name (attribute)Nombre del Mes
Year - Quarter - Month - OrderDate (hierarchy)Año - Cuarto - Mes - Fecha de la Orden


To type the ñ in año, open Notepad or Microsoft Word, and hold down the Alt key while entering the number 164 on your keyboard’s numeric keypad. Then cut and paste the character from your document into the dimension designer’s Translations tab.


Translation strings entered for attributes are automatically copied into the cells of any corresponding user hierarchy levels. You can manually overwrite these default level translations if you want.

We have entered a translated name for each attribute but have not yet specified the translated names for the members of the new MonthName attribute. Member names are not static; they are derived from data in the dimension table, so their translated names must be data-driven as well. We must, in effect, inform Analysis Services that the Spanish translation should use the tblMonthNames.SpanishMonthName column for the Month Name attribute in place of the EnglishMonthName column.

Doing so is quite easy: Simply click in the Nombre del Mes cell in the Translations tab grid, and then click the ellipsis button that becomes visible to open the Attribute Data Translation dialog box. In the Translation Columns tree view, select the SpanishMonthName column, as shown in Figure 3.

Figure 3. Configuring Nombre del Mes members in the Attribute Data Translation dialog box

Now click OK. A special table icon will appear at the left side of the Nombre del Mes cell, as shown in Figure 4.

Figure 4. The Translations tab of the dimension designer with the attribute data translation icon highlighted

Save and deploy your changes, and then check them on the Browser tab of the dimension designer. (We’ll also do this in the cube browser shortly.) Select Spanish (Spain) from the Languages drop-down list, and then click the drop-down arrow for the Hierarchy list. Before making a selection, notice that each attribute and hierarchy appears with its translated name. Select Nombre del Mes, and then drill down on the All node in the browser tree view. Notice that each month name appears in Spanish and that the months are sorted chronologically. Change Language back to Default to see each month name change to its corresponding name in English.

Now open the cube designer and click on the Browser tab. Reconnect, select Sales from the Perspectives drop-down list and, once again, select Spanish (Spain) from the Languages drop-down list. In the metadata tree view, notice that the Time dimension’s name changes to Tiempo. Drill down on the dimension to reveal that all of the attribute names are displayed in Spanish. Drill down on the Nombre del Mes node and its Nombre del Mes child node to reveal that all member nodes are displayed in Spanish as well. Using the Nombre del Mes attribute in the query grid will demonstrate equally well that the Spanish names we’ve created are displayed in place of their English counterparts. All of this is illustrated in Figure 5.

Figure 5. The cube browser, with the Language set to Spanish (Spain) and translated objects highlighted

Note that the Total Sales measure is displayed in the query grid in English because we provided no translation for it.

Another, perhaps better, way to test translations is by changing your workstation’s local language and country settings. To do this, select the Regional and Language Options applet from the Windows Control Panel, change the Standards And Formats setting on the Regional Options tab to Spanish (Spain), and then click Apply. Close and reopen Visual Studio (and your project), and then enter the cube designer Browser tab (for the Sales cube). With Language set to Default—not to Spanish (Spain)—you should see the Time dimension appear as Tiempo and its child objects appear in Spanish as well.

This same technique will force the Metadata tab in the Management Studio MDX query window to display the dimension and its child objects in Spanish; in fact, this technique is the only way to do so. (To see this change, you must open a new MDX query window if one was open when you changed your workstation’s regional settings, but you should not need to restart Management Studio itself.) When you’re done experimenting, go back to the Regional And Language Options applet, switch back to your normal language and culture setting, and then click OK. Close and reopen the Management Studio MDX query window, as well as Visual Studio and your Analysis Services project, and everything should return to English.

Top 10
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
The latest Audi TT : New angles for TT
Era of million-dollar luxury cars
Game Review : Hearthstone - Blackrock Mountain
Game Review : Battlefield Hardline
Google Chromecast
Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
Michael Kors Designs Stylish Tech Products for Women
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone