Chapter 8. The tabular presentation layer
One important consideration that is often ignored when designing tabular models is usability. You should think of a tabular model as a user interface for the data it contains. To a large degree, the success or failure of your project depends on whether your end users find that interface intuitive and easy to use.
This chapter covers several features that the tabular model provides to improve usability, such as the ability to sort data in a column and control how the measure values are formatted. It also covers perspectives, translations, and key performance indicators (KPIs). Although these features might seem less important than the ability to query vast amounts of data and perform complex calculations, you should not dismiss them or view them as having only secondary importance. The functionality they provide is vital to helping your users make the most effective use of your tabular model.
What’s new in SSAS 2016
This chapter includes a description of the new translations feature. In addition, several parts of the chapter are new or have been partially rewritten because of the new compatibility model (1200), the JSON format to save the tabular model, and the new Power BI Desktop client tool.
Setting metadata for a Date table
When you have a table containing calendar dates in a tabular model, it is important to set the right metadata so the engine can take advantage of them and enable you to use all the time intelligence functions available in DAX. In this section, you will see how to set these metadata correctly in the tabular model.
Many DAX functions that operate over dates (such as TOTALYTD, SAMEPERIODLASTYEAR, and many others) require a Date table to exist and assume that a column of the date data type exists. To specify the column to use for date calculations in date tables, you must use the Mark as Data Table dialog box in SSDT to select the Date column, as shown in Figure 8-1. (In this figure, the Date table in the Contoso database has been selected.) To open this dialog box, open the Table menu, choose Date, and select Mark as Date Table.
After you mark a table as a Date table, you can change the Date column by opening the Mark as Date Table dialog box again—this time by opening the Table menu, choosing Date, and selecting Date Table Settings.
Naming, sorting, and formatting
The first (and probably most important) aspect of the tabular presentation layer to be considered is the naming, sorting, and formatting of objects.
Naming objects
The naming of tables, columns, measures, and hierarchies is one area in which business intelligence (BI) professionals—especially if they come from a traditional database background—often make serious mistakes with regard to usability. When developing a tabular model to import data from various data sources, it is all too easy to start without first thinking about naming. As the development process continues, it becomes more difficult to change the names of objects because doing so breaks existing calculations and queries (including Microsoft Excel PivotTables and Power View reports). However, from an end user’s point of view, naming objects is extremely important. It helps them not only to understand what each object represents, but also to produce professional-looking reports that are easy for their colleagues to understand.
As an example, consider the section of a field list shown in the Microsoft Excel PivotTable Fields pane in Figure 8-2.
Now ask yourself the following questions:
Do your users know what a fact table is? If not, will they understand what the Fact prefix in the FactSalesSmall table name means? Most likely, it would be clearer to call the table Sales.
Your users might know what a dimension is. Still, will they want all their dimension table names to be prefixed with Dim, as in DimDate and DimProduct? Date and Product might look better.
Technical naming conventions often remove spaces from names, but what purpose does this achieve in a tabular model? Putting spaces in names might be more professional and help readability.
Even if the Sum of SalesAmount measure does return the sum of values in the SalesAmount column, will your users want to build reports with Sum of SalesAmount as a column header and then show that report to their colleagues? Wouldn’t using Sales Amount as a measure name be a better option?
Measures and table columns share the same namespace. This can present a dilemma when you want to build a measure from a column, such as SalesAmount, and expose the column so it can be used on the rows or columns of a query. In this case, calling the measure Sales Amount and the underlying column Sales Amount Values might be appropriate. But in cases like this, you should always let your end users make the final decision.
Figure 8-3 shows what the same PivotTable field list looks like after these issues have been fixed.
It is important to discuss the names of tables, columns, measures, and hierarchies with your users at a very early stage in the project life cycle. As mentioned, this saves you many hours of tedious work in fixing calculations and queries at the end of your project. Sometimes users decide they cannot work with the names you have given them, so you should prepare for that. A strict naming convention is usually not necessary (and, indeed, can be counterproductive) as long as you follow the following two rules:
Use names that help users understand what the data means in business terms. For example, if users always talk about volume rather than units sold, use the term volume in your measure names.
Use names that a user could place unaltered on a report shown to the CEO. Again, think about which names work best from a business standpoint rather than an IT standpoint. For example, the object names in Figure 8-2 might not be clear to most people, including top executives.
Hiding columns and measures
As noted several times in this book, if a column in your data warehouse will not be useful to your end users, you should not import it into your model because it will use up memory and increase the processing time. Useless columns also hurt usability because the more columns there are to choose from, the harder it is for your end users to find the columns they want to use in their reports. Some columns, however, are necessary for properties such as Row Identifier (described in the section “Table behavior properties” later in this chapter) and for use in calculations, so they must be present even if the user does not want to use them in a report. In these cases, the Hidden property of the column should be set to True because the smaller the number of columns that are displayed to the end user, the easier the model is to use. The Hidden property is also available for all the measures, so you can hide those measures that are for internal use only—for example, measures used only by other measures.
Organizing measures and columns
One useful piece of functionality is the ability to group multiple measures and table columns into display folders so that users can find what they are looking for more easily. The Display Folder property, which is available for columns and measures, controls the folder hierarchy used to arrange their visualization to the users. The string you write in the Display Folder property corresponds to the name of the folder that contains the column or measure. If you want to use nested folders, you simply type the complete path from the root folder to the leave folder, separating names by a backslash (). For example, Figure 8-4 shows the columns of the Product table displayed to users when the values for the Display Folder property (shown in Table 8-1) are used.
Sorting column data
In many cases, the order in which values appear in a column is irrelevant. By default, Tabular does not apply any sorting to data in columns, and this usually causes no problems. The most common situations when alphabetical ordering for data is not suitable occur on Date dimension tables. Days of the week and months of the year have their own inherent order, which must be used in any reports built on your model. You can sort data in columns by using the Sort by Column property of a column, or through the Sort by Column dialog box shown in Figure 8-5. (To open this dialog box, open the Column menu, choose Sort, and select Sort by Column.)
The Sort by Column property is fairly straightforward. For any column in a table, it enables you to specify another column in the same table to control the sort order. However, there are three important things to know about this property:
It is possible to sort only in ascending order, according to the data in the Sort by Column property.
The data in the column to be sorted and the data in Sort by Column property must be of the same granularity. So, for each distinct value in the column, there must be a single, equivalent value in Sort by Column.
For MDX client tools, this property affects how members are ordered in a hierarchy, but it does not directly affect DAX queries. Client tools, such as Power BI, should read this property value from the model metadata and then generate an appropriate ORDER BY clause in the queries they generate.
For the Month column shown in Figure 8-5, there is another column already in the Date table that can be used for sorting: Month Number. This contains the month numbers from 1 to 12, with 1 corresponding to January and 12 to December. Setting the Sort by Column property for Month to Month Number means that the Month column will be displayed in PivotTables, as shown in Figure 8-6.
What happens if there is not an obvious candidate for a column to use for sorting? Consider the Calendar Year Month column shown in Figure 8-7, which contains the month name concatenated with the year. For example, it returns values such as February 2006 or April 2007.
If there is no column with the same granularity (which means you need a column with one distinct value for each distinct value in Calendar Year Month) by which to sort, you could try to sort by using a column with a different granularity. However, if you choose a column with a larger number of distinct values, such as the Date column, you get the error shown in Figure 8-8. The error message is as follows:
Cannot sort Calendar Year Month by Date because at least one value in Calendar Year Month has multiple distinct values in Date. For example, you can sort [City] by [Region] because there is only one region for each city, but you cannot sort [Region] by [City] because there are multiple cities for each region.
If you sort by a column that has a smaller number of distinct values, such as Calendar Year Number, you will not get an error, but the values in the column will not be sorted correctly, either. Instead, you will see each month sorted by year, but you cannot guarantee the order within the year, as shown in Figure 8-9.
One solution is to get a column with the correct granularity specifically to sort the Calendar Year Month column. For example, you might create a calculated column using the following definition:
Date[Calendar Year Month Number] = 'Date'[Calendar Year Number] * 100 + 'Date'[Month Number]
This expression, for example, returns the value 200707 for the month of July 2007. You can also create the column in your relational data source using a similar logic in the view you use in the relational database. (Using views to retrieve data for a tabular model is always a good practice.) In Figure 8-10, you can see the value of the Calendar Year Month Number column that has the same granularity as the Calendar Year Month column.
In Figure 8-11, you see that the Calendar Year Month content is now sorted properly in the PivotTable after the Calendar Year Month Number column is used in the Sort by Column property.
Note
Analysis Services checks that a column of appropriate granularity is used for Sort by Column only when that property is first set. Therefore, new data can be loaded into the table that breaks the sorting, with no errors raised. You must be careful that this does not happen. Including a check for this in your extract, transform, and load (ETL) process might be a good idea.
Formatting
You can apply formatting to numeric values in columns and measures. It is important to do this because unformatted, or raw, values can be extremely difficult to read and interpret.
Formatting columns
You can set number formats for numeric data in both normal columns and calculated columns with the Data Format property. The values available for this property are determined by the value of the Data Type property, discussed in Chapter 3, “Loading data inside Tabular,” and the type is set for the values held in the column. Depending on the value selected for Data Format, other properties might become enabled that further control formatting. As with the Sort by Column property, number formatting is applied automatically only when connecting through an MDX client tool. DAX queries do not display formatted values. They display only the raw data. If you are running DAX queries, you must read the metadata to determine the appropriate format and then apply it to your results yourself.
The available Data Format property values for each data type (excluding Text) are as follows:
For the Date type, a General format shows the date and time in the default format for the locale of the client that is querying the model. (See the following for more details about how language affects formatting.) There is also a long list of built-in formats for showing dates, times and dates, and times together in different formats. In addition, you can enter your own formats.
For the Whole Number and Decimal Number types, the following formats are available:
• General This shows the number in the default format for the client tool.
• Decimal Number This shows the value formatted as a decimal number. When this format is selected, two further properties are enabled:
• Decimal Places This sets the number of decimal places displayed.
• Show Thousand Separator This sets whether the thousand separator that is appropriate for the language of the client is displayed.
• Whole Number This formats the value as a whole number. The Show Thousand Separator property is enabled when this format is selected.
• Currency This formats the value as a monetary value. When this format is selected, two further properties are enabled:
• Decimal Places By default, this is set to 2.
• Currency Symbol This sets the currency symbol used in the format. The default symbol used is the symbol associated with the language of the model.
• Percentage This formats the value as a percentage. Note that the formatted value appears to be multiplied by 100, so a raw value of 0.96 will be displayed as 96%. The Decimal Places and Show Thousand Separator properties are enabled when this format is selected.
• Scientific This formats the value in scientific form using exponential (e) notation. (For more details on scientific form, see http://en.wikipedia.org/wiki/Scientific_notation.) The Decimal Places property is enabled when this format is selected.
• Custom This allows the introduction of any custom format string in the Format String property. However, any format string that corresponds to one of the patterns described by other formats is automatically converted in said format description in the SSDT user interface.
For the True/False type, values can be formatted as TRUE or FALSE only.
Note
Number formats in the tabular model are designed to be consistent with PowerPivot, which is designed to be consistent with Excel.
Formatting measures
You can format measures in much the same way as columns. However, in the case of measures, the property used is Format. The property values available for Format are the same as the values available for the Data Format property of a column.
Language and Collation properties
A tabular model has both a Language property and a Collation property (visible in the Property pane when the BIM file of your model is selected in Solution Explorer in SSDT). Both are fixed when the model is created, and they cannot be changed. The collation of a model controls how the sorting and comparison of values behave. The Language of a model influences how formatting takes place for the General formats for dates and numeric values (for example, it controls the default currency symbol used), but you can and should override these defaults when configuring the Data Format and Format properties. The locale of the machine that is querying the tabular model also plays an important role. For example, the date of the 4th of August, 2012, when formatted using the General format, will be displayed as 8/4/2012 00:00:00 on a machine that uses the English (US) locale, but it will be displayed as 04/08/2012 00:00:00 on a machine that uses the English (UK) locale. In a similar case, the thousands and decimal separators vary by locale. A value that is displayed as 123,456.789 in the English (US) locale will be displayed as 123.456,789 in the Italian locale.
The following links describe in more detail how languages and collations work and how they are set when a model is created:
https://msdn.microsoft.com/en-us/library/ms174872(v=sql.130).aspx
Perspectives
Most tabular models that work on large and complex data sources contain many tables, each with many columns in it. Although hiding tables and columns that should never be used in queries or reports will go a long way toward improving the usability of a model, it may still be the case that some groups of users will never want to see or use some parts of the model. For example, if a single model contains data from the HR, Marketing, and Finance departments, some users want to see and use all this data. However, it is equally possible that some users in the HR department want to use only the HR data, while some users in Marketing will want to use only the Marketing data, and so on. Perspectives enable you to meet this requirement by creating something like a view in a relational database. You can create what looks like a new model for specific groups of users by hiding the parts of the underlying model that these users do not want to see.
To create a new perspective, follow these steps:
1. Open the Model menu in SSDT, choose Perspectives, and select Create and Manage. This opens the Perspectives dialog box, which displays all perspectives in the model.
2. Click the New Perspective button.
3. Enter a name for the new perspective at the top of the new column that appears.
4. Select all the fields and measures in the dialog box under the new perspective’s name to add them to the perspective, as shown in Figure 8-12. Then click OK.
You can use the perspective in SSDT. To do so, open the Model menu, select Perspectives, choose Select, and select the perspective’s name. This hides tables and columns not included in the perspective from the SSDT user interface. This could be useful to reduce the number of tables you operate with when editing a large data model. To test a perspective from the user point of view, click the Analyze in Excel button on the toolbar in SSDT. Then, in the Analyze in Excel dialog box, open the Perspective drop-down menu and choose the name of the desired perspective, as shown in Figure 8-13.
This opens Excel with a new PivotTable connected to the perspective. As an alternative, if you want to create a new connection yourself in Excel in the same way that end users would do, select the perspective name from the list of cubes in the Data Connection Wizard, as shown in Figure 8-14.
Note that in the PivotTable field list, you can see only the tables, measures, and columns that you added to the perspective. In all other respects, however, the experience is the same as querying the whole model. You will have a similar user experience in Power BI when you connect live to a tabular model. After selecting the server, you can select one of the available perspectives to use in the list under the database name. The default name of the data model is Model, but it could be modified in the Cube Name property of the project property dialog box in SSDT.
Important
Perspectives are not a substitute for security and cannot be secured as objects. Even if a user cannot see an object in a perspective, that user can still write DAX or MDX queries that return data from those objects if he or she knows the object’s name.
Power View–related properties
Several properties in the tabular model act as metadata for Power View (or any other client tools that want to read them), influencing how the model is displayed in a client tool. However, the PivotTable in Excel does not directly use the metadata that is specific for Tabular, nor does Power BI support many of these properties (as of January 2017). Thus, you must pay attention to which clients support each of these properties.
Default field set
The default field set for a table is a set of columns and measures that is displayed automatically in a Power View canvas when a user double-clicks that table in the field list. As of January 2017, a similar feature is not available in Power BI, but it could be implemented in future releases. Setting up a default field set makes it faster for Power View users to create a report because it reduces the amount of dragging and dropping needed.
To set up a default field set, follow these steps:
1. Select the table in SSDT and click the Default Field Set property or select a measure or column in a table and click the Table Detail Position property. This opens the Default Field Set dialog box, shown in Figure 8-15.
2. Specify which columns and measures are to be displayed in the default field set and the order in which they should appear. This information is stored as a 1-based index in the Table Detail Position property for a column or measure.
3. After the model has been saved and deployed, connect to the model in a Power View report in Excel. Then double-click the name of the table in the field list to create a table on the canvas that contains the selected columns and measures, as shown in Figure 8-16.
Table behavior properties
You can find the table behavior properties by clicking a table in SSDT. Then, in the Properties pane, click the Table Behavior property group to open the Table Behavior dialog box, shown in Figure 8-17. (Do not try to expand the property group in the pane and edit the properties outside the dialog box. It is not possible to do this, except for the Keep Unique Rows property, but there is a specific dialog box for that.)
The properties that can be set in this dialog box are as follows:
Row Identifier This property enables you to specify a single column to act like the primary key of the table. (Note that it must have a unique value for each row.) You can also modify the corresponding Row Identifier property on a column. Just be aware that setting a column to True will set all the other columns to False. Setting a Row Identifier property also enables the Keep Unique Rows, Default Label, and Default Image properties. It also affects where subtotals are displayed in Matrix controls in reports. The process of marking a table as a Date table, which you saw at the beginning of this chapter, involves setting the Row Identifier property. After this is done, you cannot change the Row Identifier property manually. Finally, the Row Identifier property can be useful to avoid circular dependencies in a calculated column if a table does not have relationships with other related tables, as explained in https://www.sqlbi.com/articles/understanding-circular-dependencies/.
Keep Unique Rows This property enables you to select columns whose uniqueness is determined by the column specified in the Row Identifier property when used in a Power BI or Power View report. For example, this would be important on a Customer dimension table in which some customers might have the same name. If there were a Customer Key column with a numeric key that identified individual customers, as well as a Customer Name column, you could set the Row Identifier property of the table to Customer Key and specify Customer Name in Keep Unique Rows. That way, when you use the Customer Name column alone in a table in a Power BI report, there is one row for each unique customer, not for each unique name. That is, if there were two customers with the name John Smith, there would be two rows with that name. This property does not have any automatic effect on DAX queries. It simply informs Power BI (and any other clients that look at this property in the metadata, such as Power View) that it should include the column specified in Row Identifier in any queries that use any of the columns selected in the Keep Unique Rows property. Note that the PivotTable in Excel does not use this property.
Default Label This property sets the column that contains the name or label that should be used for each unique row in the table. For example, consider the Customer dimension table example used in the previous bullet. If each row in the table represents a customer, the Customer Name column should be used as the Default Label. This property is used as the default label for a card or a chart in a Power View report. Power BI ignores this property.
Default Image This property sets the column that contains either binary data that contains an image or text data that can be interpreted as the URL of an image. (For a text column to be selectable in this case, it must have its Image URL property set to True.) This image is then used by Power View either in the navigation pane of a tile report or on the front of a card. Power BI ignores this property.
As noted, many of these properties are not used by Power BI. They exist only for compatibility with Power View reports. Because the most important property (Row Identifier) is also available in the columns’ properties, you can create a data model in Tabular and ignore the settings in the Table Behavior dialog box if your users do not use Power View as a client.
Key performance indicators
A key performance indicator (KPI) is a way of comparing the value of one measure with another. For example, you might want to analyze profitability by comparing the cost of goods used to make a product with the value at which the product is sold, or to see whether the time taken to deal with support calls on a help desk is within the acceptable thresholds.
To create a KPI in a tabular model, you must start with an existing measure. In this case, we will use the Sales table in Contoso. Follow these steps:
1. Create the following measures: Sales Amount, Cost, Margin, and Margin %. (These are also shown in Figure 8-18.)
Sales[Sales Amount] := SUMX ( Sales, Sales[Net Price] * Sales[Quantity] )
Sales[Cost] := SUMX ( Sales, Sales[Unit Cost] * Sales[Quantity] )
Sales[Margin] := [Sales Amount] - [Cost]
Sales[Margin%] := [Margin] / [Sales Amount]
2. Select the Margin measure and click the Create KPI button in the toolbar. Alternatively, right-click the measure and choose Create KPI from the context menu. This opens the Key Performance Indicator (KPI) dialog box, shown in Figure 8-19. The disabled KPI Base Measure (Value) drop-down list displays the name of the measure you have just selected.
3. Define a target value. This can be either another measure in the model or an absolute value, which you must enter. In this example, select the Sales Amount measure. The ratio of the base measure to the target is compared to a set of thresholds that determine the status: good, intermediate, or bad.
4. Click one of the four rectangular boxes in the center-right portion of the dialog box to choose a threshold type. For example, the top-left threshold type defines a low ratio value as bad (red), a middling ratio value as intermediate (yellow), and a high ratio value as good (green). In contrast, the bottom-left threshold type defines a low ratio value as good, a middling ratio value as intermediate, and a high ratio value as bad. In this case, select the top-left box.
5. Use the slider in the center of the screen, above the boxes, to set the threshold values as percentages. In this example, set a value of 48% for the lower threshold and 56% for the higher threshold.
6. Select an icon style for your client tools to use. (Note that the client tool will not necessarily use the icons shown in the dialog box; this is just a guide to the style of icon to use.)
7. Click the Descriptions arrow in the bottom-left corner of the dialog box to display a series of text boxes. Here, you can enter a description for the KPI, the value, the status, and the target.
8. Click OK. The KPI appears in the measure grid in SSDT in the same place as the base measure, as shown in Figure 8-20. You can edit the KPI by right-clicking that cell.
After the KPI has been created, it can be used in several clients, such as an Excel PivotTable or a Power BI report, as shown in Figure 8-21. It appears as a special semaphore icon in the list of fields, which contains three measures:
Value This is the value of the measure.
Goal This corresponds to the value of the target measure.
Status This is the KPI icon.
These last two names are displayed in the report as suffixes of the original measure name, so you can include multiple KPIs in the same report.
Translations
The entities of the semantic model defined by Analysis Services Tabular can be translated in other languages and cultures. Such a feature is available in models created at the 1200 compatibility level, but it is not supported in previous compatibility levels. The feature available for tabular models translates only object metadata, such as column, table, and measure names. However, it does not support loading translated data, such as the translation of product names or descriptions. That feature is available in a multidimensional model, even in previous versions of Analysis Services.
The translated metadata is included in the JSON file that contains the definition of the tabular data model. These translations are in a specific section of the data model: cultures. Every object has a reference to the original element in the data model, and properties such as translatedCaption, translatedDescription, and translatedDisplayFolder contain the corresponding translated strings, as shown in Figure 8-22.
SSDT does not provide a user interface to directly manipulate the translations in the tabular model. Instead, you can export a file that contains only the translations in one or more languages. You can then import this file back into the model. The idea is that you initially export an empty translation file, which you pass to someone who can insert the translated names in the proper places. Once the translation file contains the translated names, you import this file in the data model. SSDT provides tools to export and import translation files, requiring you to complete the following steps:
1. Create a translation file.
2. Write translated names in translation file.
3. Import a translation file.
4. Test translations using a client tool.
In the following sections, you will see a description of these steps and a few best practices to avoid common mistakes.
Creating a translation file
You export a translation file from a data model by using the Manage Translations dialog box in SSDT. Follow these steps:
1. Open the Model menu, choose Translations, and select Manage Translations to open the Manage Translations dialog box. The first time you open this dialog box, you can choose the languages you want to add as translations in your data model, as shown in Figure 8-23.
2. Select one or more languages on the left side of the dialog box and click the >> button to add the selected languages to the list on the right, as shown in Figure 8-24.
3. Select one or more languages in the list on the right side and click the Export Selected Languages button. This creates a single JSON file that contains placeholders to insert the translations for all the languages selected.
Tip
Even If you can export more languages in the same file, it is probably better to have only one file for each language.
4. Name and save the file. Then give it to someone who can translate the names from your tabular data model into the selected language.
Writing translated names in a translation file
The JSON file that contains translations has two important sections:
referenceCulture This contains the objects of the tabular model that could be translated, such as tables, columns, and measures. This is where you can find the original names, the descriptions, and the display folders of columns and measures. These names should not be changed—if they are, the changes will be ignored during import. They simply provide a reference for the translation.
cultures This contains a reference to the objects in the model (the name property of tables, columns, and measures). It also contains the following properties for the translations, which are initially empty strings:
• translatedCaption This is the name of the table, column, or measure that is displayed to the users.
• translatedDescription This is the description of a table, column, or measure that can be displayed to users by certain clients.
• translatedDisplayFolder This is the translation of the folder name that is used to group columns and measures. In the case of nested folders, it includes the path of the folders separated by backslash characters ().
The file created by the Manage Translations dialog box initially contains empty strings for translated names. If imported, an empty string will not define any translation, and the original name will be displayed in the translation instead. Thus, you must include a string only for the objects you need to translate. Because it could be useful to see the original string during the translation, the structure of the file requires you to keep two copies of the file open or to split the editor window in two parts.
Figure 8-25 shows an empty translation file opened in Visual Studio using the JSON editor. The upper part of the JSON editor in Visual Studio 2015 contains information about the original value of displayFolder and its description properties (the latter is not included in this example). The lower part shows the same tables and columns in the cultures section. If you consider the Manufacturer column of the Product table, you probably need to know the original value of displayFolder (Info) to write the translated version in the translatedDisplayFolder property. This round trip is usually not necessary for the translatedCaption property because the name property is also the original caption in the data model.
Figure 8-25 The translation file that contains the original model and the translated properties in two different sections.
The JSON editor is available only if you installed a complete version of Visual Studio 2015, not just the shell that is required by SSDT. The Visual Studio Community edition includes the JSON editor. It is a free tool, and you can download it from https://www.visualstudio.com/downloads/download-visual-studio-vs.aspx.
Choosing an editor for translation files
You do not need Visual Studio to edit translation files. You can use any text editor for that. One alternative to Visual Studio is Notepad++ (https://notepad-plus-plus.org/), which correctly manages the encoding of the files that are required for translations. Because several languages use non-ANSI characters, however, you must make sure that the file is saved in Unicode UTF-8. This is the file type generated by the export language feature you saw earlier. It is also the encoding expected by the import feature discussed in the next section.
If you do use Visual Studio, be aware that by default, JSON files that contain no ANSI characters higher than 127 are saved as a standard ANSI file, without using Unicode. Visual Studio controls this behavior via the Save with Encoding option of the Save File As dialog box, which displays the Advanced Save Options dialog box shown in Figure 8-26. Be sure you have the same Encoding settings for your translation file when you save it from Visual Studio. Otherwise, many special characters used in specific languages will be converted incorrectly.
Figure 8-26 The correct encoding setting for JSON translation files that are saved by Visual Studio.
Important
If you create the initial model in the English language, chances are the translation file does not contain any special character. Therefore, if you open it in Visual Studio, it will not be saved as Unicode, even if you create strings that use special characters. If you see strange characters in the resulting data model, reopen the translation file in Visual Studio and save it using the encoding settings shown in Figure 8-26. Then import the translation again.
The JSON format is not very user-friendly, but at least it is a text file. However, it is relatively easy to load a JSON file in a program that provides a better user experience. You might want to write your own script to transform the JSON format into a simpler file to edit. Or you could use some specific editor to manipulate such a file format. Kasper De Jonge has created an initial release of a tool named Tabular Translator, which receives updates from other contributors. You will find links to download the executable and source code at https://www.sqlbi.com/tools/ssas-tabular-translator/. The user interface of this tool is shown in Figure 8-27. Such a system displays the original and translated names of each entity in a single row, making it easier to manage the translation files. Tabular Translator also manages more languages that are included in the same translation file.
Importing a translation file
After you obtain the translated file, you must import it in the tabular model. Follow these steps:
1. Open the Model menu, choose Translations, and select Import Translations. This opens the Import Translations dialog box shown in Figure 8-28.
2. Choose the file to import.
3. Select any of the following options, according to your needs:
• Overwrite Existing Translations When this option is unchecked, only languages that are not already defined in the tabular model are imported. When it is checked, languages already defined in the tabular model are overwritten.
A translation must be complete. If there are missing objects in the translation file that are already present and translated in the tabular model, the existing translation will be removed, and only the translations included in the imported file will be stored in the tabular model after the import.
• Ignore Invalid Objects When this option is checked, any reference to objects that are no longer in the tabular model will be ignored. If it is unchecked, then references to objects no longer in the model will cause the import action to stop and will display a dialog box with an error message.
• Write Import Results to a Log File This option specifies whether a log file should be saved in the project folder. At the end of the import, a dialog box shows the complete path and file name of the log file saved.
• Backup Translations to a JSON If this option is checked, a JSON file is created with the backup of the translations for only the languages that are imported. It is useful when you select the Overwrite Existing Translations check box and you want to be able to recover the previous version of the translation if something goes wrong. The backup is created in the project folder with a name that includes the date and time of the operation.
4. Click the Import button. If there are no errors, your model will include the new translations.
5. Save the model file. If you do not do so, the previous version of the translations will remain.
Testing translations using a client tool
To test the translations, you must use a client tool that can display translated metadata. For example, the PivotTable in Excel supports translations of the data model, and you can connect the PivotTable by enforcing a particular culture setting in the connection string, overriding the default culture that is inherited by default user settings. Thanks to the default behavior, Tabular users will typically get their favorite language automatically, but you need to enforce a different culture in case you want to test the translations. The easiest way to do that is to use the Analyze in Excel dialog box, shown in figure 8-29. (To open it, click the Analyze in Excel button in Visual Studio.) The Culture setting provides a list of the available translations, and you can choose the one you want to use.
When you’re finished, you can navigate in a PivotTable that displays the translated names of tables, columns, hierarchies, folders, measures, and KPIs, as shown in Figure 8-30. This way, you can verify that at least all the visible objects have been translated.
Note
The translation file contains all the objects, regardless of their visibility state. When you navigate in the PivotTable, you can see only the visible objects, not the hidden ones. Although it is a good idea to also translate invisible objects, it is not strictly required for the user interface because they will never be displayed to the user.
Removing a translation
You can remove a translation from a tabular model by using the Manage Translations dialog box in SSDT. Follow these steps:
1. Open the Model menu, choose Translations, and select Manage Translations. The Manage Translations dialog box opens. The right pane contains a list of the existing translations, as shown in Figure 8-31.
2. Select one or more languages in the right pane and click the << button to remove the selected languages from the model. This removes all the translated strings.
3. A dialog box appears to confirm the removal, which is irreversible. Click Export Selected Languages to complete the operation.
Best practices using translations
When you manage translations, you export and import parts of the tabular model in JSON format. Therefore, you should use the following best practices to avoid problems during project development and maintenance:
Export only one language per file. Managing different languages in the same file can be complex, especially if you do not use specific editors.
After you export the translation file, do not rename any object in the tabular model. If the translation references an object that you later rename in the model, subsequent import operations will fail. To complete the import, you must rename the same object in the translation file before importing it or restore the original name of the object in the model.
If you create new objects in the data model after you export a translation file, these objects will not be translated after you import the translation. You must export a new file. Note, however, that the translation file contains all the objects, so you cannot merge partial translations and/or export only the new strings that require a translation. Due to these limitations, you should start the translation at the very end of the project lifecycle.
If you use the JSON editor in Visual Studio to edit the translation files, remember to save them using the Unicode UTF-8 encoding.
Consider using specific editors for translation files, such as the SSAS Tabular Translator (https://www.sqlbi.com/tools/ssas-tabular-translator/).
Selecting culture and collation in a tabular model
Every tabular model has two settings that affect the formatting, sort order, and string comparisons: Language and Collation. These settings appear as read-only properties of the model, and they cannot be changed in the Visual Studio user interface. You must change them in the JSON file, following the instructions described later in this section.
The Language property defines the character set to use for strings. By default, it corresponds to the current Windows language identifier when you create a new tabular project. For example, Figure 8-32 shows the Language property set to English (United States).
The Language property corresponds to the culture property in the JSON file, which you can open by using the View Code context menu in the Solution Explorer window. Figure 8-33 shows an excerpt of the JSON file.
The Collation property defines the ordering of characters and their equivalence, which affects the way string comparisons are made. Every instance of SQL Server Analysis Services (SSAS) has a default collation that is defined during setup. Every model that does not specify a particular collation will inherit the behavior defined by the default collation of the SSAS instance. By default, Visual Studio does not set the Collation property for a new empty tabular project. The Collation property corresponds to the collation property in the JSON file. Figure 8-34 shows a JSON file where both the culture and collation properties are explicitly set to specific values.
As a quick reference, the following are commonly used values of the collation property, which use different styles of the Latin1_General collation designer:
Latin1_General_CS_AS Case-sensitive, accent-sensitive
Latin1_General_CS_AI Case-sensitive, accent-insensitive
Latin1_General_CI_AS Case insensitive, accent sensitive
Latin1_General_CI_AI Case insensitive, accent insensitive
The values available for the collation are the same for SQL Server. You can find a complete description of these values at https://msdn.microsoft.com/en-us/library/ff848763.aspx and a detailed explanation of the collation options at https://msdn.microsoft.com/en-us/library/ms143726.aspx#Collation_Defn.
If you want to modify the culture and/or collation properties in the JSON file, the deployment of the model must happen on a server where such a database does not exist. That means you must remove the workspace database to apply the change in Visual Studio, and you must delete an existing deployed database before deploying such a change. If you try to modify one of these properties, you will get the following error message when you try to deploy the database or try to open the designer window in Visual Studio:
Culture and Collation properties of the Model object may be changed only before any other object has been created.
Removing a workspace database in Visual Studio to apply these changes is not intuitive. You can find a step-by-step description of this procedure in the following sections, depending on the type of workspace database you have: integrated workspace or workspace server.
Changing culture and collation using an integrated workspace
If you have an integrated workspace, you must close Visual Studio before removing the directory that contains the files of the workspace database. When you reopen Visual Studio, the workspace database is created from scratch using the new settings. Follow these steps:
1. Open the context menu in the Solution Explorer and choose View Code to open the JSON file in Visual Studio.
2. Modify or add the culture and/or collation settings in the model object using the same syntax you saw in Figure 8-34.
3. Save and close the Model.bim file in Visual Studio.
4. Select the Model.bim file in the Solution Explorer window and take note of the file’s Full Path property. You need to know the directory where the Model.bim file is stored. (We will refer to this directory as <path> in the following steps.)
5. Close Visual Studio.
6. Open Windows Explorer and point to the <path>in folder.
7. Remove the Data folder in <path>in.
8. Open Visual Studio and use the designer to open the tabular model. Visual Studio automatically creates a new version of the data model in the integrated workspace.
9. Open the Model menu, choose Process, and select Process All to process the model.
At the end of these steps, you will have a tabular model in the integrated workspace that uses the Language and Collation settings that correspond to the culture and collation properties you specified in the Model.bim file.
Changing culture and collation using a workspace server
If you have a workspace server, you need not close Visual Studio before removing the workspace database form the workspace server. Instead, you close the designer window. Follow these steps:
1. Open the context menu in the Solution Explorer and choose View Code to open the JSON file in Visual Studio. When you do, Visual Studio also checks that you do not have a designer window open on the same file.
2. Modify or add the culture and/or collation settings in the model object using the same syntax you saw in Figure 8-34.
3. Save and close the Model.bim file in Visual Studio.
4. Select the Model.bim file in the Solution Explorer window and take note of the file’s Workspace Database and Workspace Server properties.
5. Open SQL Server Management Studio (SSMS) and connect to the Tabular instance specified by the Workspace Server property of the tabular model.
6. In SSMS, select and delete the tabular database that corresponds to the name specified in the Workspace Server property of the tabular model.
7. Switch to Visual Studio and use the designer to open the tabular model. Visual Studio automatically creates a new version of the data model in the workspace server.
8. Open the Model menu, choose Process, and select Process All to process the model.
At the end of these steps, you have a tabular model in the workspace server that uses the Language and Collation settings that correspond to the culture and collation properties that you specified in the Model.bim file.
Summary
In this chapter, you learned how important usability and a professional presentation is to tabular models, as well as the various features available to improve the experience of your end users. Naming, hiding or showing, sorting, and formatting columns and measures, as well as providing translations and creating perspectives and KPIs, can make the difference between your model being rejected or embraced by your users. Therefore, they determine the success of your project. If you are using Power View on your project, configuring the Default Field Set and Table Behavior properties can make creating reports much easier for users.