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.
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.
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:
| Object | Translated 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 |
Tip
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. |
Tip
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.
Now click OK. A special table icon will appear at the left side of the Nombre del Mes cell, as shown in Figure 4.
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.
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.
