Localization of Data module metadata for multilingual data modules

This paper discusses the methods for localizing the data and metadata of a data module so that a user of that data module will be able to see metadata and data in the language of his preference.

Summary

One cool Framework Manager (FM) feature is that you can create one model which can be used by any number of users, in many locales, provided that those locales are supported by Cognos.

This functionality is now fully available in a data module, with the release of Cognos Analytics (CA) 12.0.1. With 12.0.1 you can now translate the metadata of a data module. This allows a user to see the metadata of the data module (query subjects, query items, calculations, filters etc.) in a language which he prefers.

For CA 12.0.1 you can use a data module with metadata translation in reporting. Dashboard support will be implemented in a future release.

With a localized data module, a report can be created in one language and be viewed in any number of other languages.

There is greater flexibility in the translation of metadata than with FM, especially with respect to administration.

Multilingual metadata translation sources are external to the data module. They are stored in uploaded files or tables in a data base, rather than in the model, as with FM. As such the data module is not cluttered up by them. Updates and refinements to translations do not necessarily require editing of the data module.

Labels, descriptions, and screen tips are translatable.

You can control and refine the scope of localization. A string can be translated in different ways, depending on the context, through the identifier of the object and through matching the case of the string.

You can preview translations.

Rules for resolving translations will attempt to match some defined translation so that if the exact locale value of a user has not been modelled for translation but a related locale has been specified in a translation definition there will be an attempt to use that translation. If a translation for a language and country (i.e., fr-be) does not exist then if a translation for the language (fr) exists, it will be used. If that does not exist, then the first other related language (i.e., fr-fr or fr-ca etc.) translation definition which is in the list of translations will be used. If no appropriate translation definition exists, then the metadata of the design locale of the data module will be used.

You can build data modules on top of a translated data module, allowing users to seamlessly extend a data module from one locale to another.

You can use metadata translation to give to your users more understandable object labels rather than the more cryptic ones often found in data bases. In essence it would be translating from DBA to business user.

You can also translate the data. The methods for that are also described in this document.

Overview
There are two basic activities necessary to enable a data module to support multiple locales.

One is the localization of data, so that the data is presented to the user in his language.

The other is the localization of the metadata which is intended to be displayed in the reporting applications which use data modules and in data modules which consume the localized data module and build extensions to the source data module.

For the former, you would use some method, usually macros, to look up the run locale of the user, or something similar, and return the metadata which is appropriate. That metadata would be used to generate the query so that values which are appropriate to the user’s locale are returned.

The support of macros in data modules has been implemented for some time. The support for metadata translation has been implemented for CA 12.0.1.

The user interface and properties such as aggregation settings are translated automatically.

With localization, reports and (in a future release) dashboards which use a data module which has been localized could be viewed by users in any number of locales, without modification and the data module would be usable by those users in their preferred language for their exploration.

The translation of metadata is driven by what metadata is to be exposed to users.

Since that is driven by the requirements, the localization of data, which is basically providing a way for queries to return data in an appropriate language, which is to say query planning for localization, should precede the localization of metadata.

The first stage of the modelling exercise of localization is the determination of localization requirements. Are you required to provide translated values to your users? For example, do you need to have a user see the names of things in his report in his language and instead of Camping equipment for him to see Campingausrüstung? In some situations, this might be driven by statutory requirements. In Canada, a report provided to a person by the Federal government is by law required to be available in either English or French, with not just the metadata but the data translated.

In other situations, translating the metadata of the data module should be sufficient.

You need to know this as it is possible that changes to your data warehouse would be needed to support translation of data so that queries can be generated to return the localized values. Subsequently you need to know what is available in your data warehouse and what can be made available.

In a sense which is not quite as seemingly circular as it might initially sound, your metadata -- how your localized data is stored -- drives how you would model for the localization of data. That, in turn, will drive how you would model for the localization of metadata.

Data module localization workflow

1.      Define the localization requirements.

2.      Determine what data and metadata are available and determine how to address the requirements.

3.      Choose between implementing the necessary changes or cutting your coat to fit your cloth.

4.      Model to localize the data.

5.      Model to localize the metadata.

6.      Refine the model.

 

Localization of data

The objective of the modelling exercise for localizing data is to determine how a query is to know where to find the appropriate data for any particular locale.

When you are localizing a module, you need to recognize the patterns of metadata which exist in your data sources which have multilingual data in them.

The modelling exercises are quite similar to that of Framework Manager. If you have modelled multi-lingual metadata in FM, you will see similar techniques in data modules and the exercise will not be particularly difficult to map that knowledge to the work in a data module.

One exercise is to identify the columns which you would like to use.

Another is to identify the structure of the metadata. They fall into two basic patterns.

Multiple record entities

One is where a column has multiple records for each entity. Each record has the value of the entity in a specific language.

An example is shown in this table.

Product line key	Product line	Language locale
1	Camping equipment	EN
2	Golf equipment
3	Mountaineering equipment	EN
4	Personal accessories	EN
5	Outdoor protection	EN
1	‪ ‪Matériel de camping	FR
2	Matériel de golf	FR
3	Matériel de montagne	FR
4	Accessoires personnels	FR
5	Articles de protection	FR
1	Campingausrüstung	DE
2	Golfausrüstung	DE
3	Bergsteigerausrüstung	DE
4	Accessoires	DE
5	Outdoor-Schutzausrüstung	DE

The table contains 15 records. For each of the five product line entities there are three records, one for English, one for French, and one for German.

For this pattern you would want to filter the data so that only the locale-specific data is returned. For example, if the user is German then he should only see that data where the value for the language locale column is DE.

The method to deal with this is to create an expression editor filter in the query subject similar to this.

Language_locale IN (#sq(toupper(substr($runLocale,0,2))) #)

$runLocale is a session parameter. It looks up the current user’s locale, which is derived from the content language preferences, and returns the ISO standard entry for that locale.

These values would either be in a two-character format, which represents the language, or a five-character format, which represents the language and the country. You will have observed similar things in places such as your browser’s languages setting and in your own profile and settings preferences which you can set in the personal menu in Cognos.

For example, en is the ISO standard for English. The ISO standard for French is fr and the standard for German is de. The ISO standard for country specific settings will look like en-ca (English Canada), en-us (English United States), fr-ca (French Canada), fr-fr (French France) and fr-be (French Belgium).

You can see the list of session parameters available to you in the expression editor of FM but not in that of a data module.

Because the language locale column values of the example use a two-character format, whether the locale is two-character or five character is not relevant and I have used the substr function to get the first two characters of the locale. The primary factor for me is that my table has the values for language locale as two-character values. The country of the locale values is not significant for this example. You might need to take country values into consideration and your expressions would need to reflect that.

The two-character value is then converted to upper case. This last action is done because, as you saw in the table, the data in the column language locale is upper case.

That value is enclosed in single quotes, as it is a string, and the data type of the column is a string, with the sq macro function.

This value would then be used in the query to filter the table by the appropriate language. The resolved value would be similar to any of the following:

Language_locale IN ('EN')

Language_locale IN ('DE')

Language_locale IN ('FR')

This technique is used in parts of the FM sample models.

The extended sample for metadata translation also demonstrates this technique.

https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=metadata-using-sample-files-create-multilingual-data-module

Multiple columns

The other metadata pattern is that of multiple columns, each containing the values of a language.

Here is an example of such a table.

Product line key	PRODUCT_LINE	PRODUCT_LINE_German	PRODUCT_LINE_French
1	Camping equipment	Campingausrüstung	‪ ‪Matériel de camping
2	Golf equipment	Golfausrüstung	Matériel de golf
3	Mountaineering equipment	Bergsteigerausrüstung	Matériel de montagne
4	Personal accessories	Accessoires	Accessoires personnels
5	Outdoor protection	Outdoor-Schutzausrüstung	Articles de protection

 

The objective in this case is to create a calculation in the query subject and put into it an expression which will resolve to the identifier of the column you want to use for that locale.

For situations like in the table the macro would be something similar to this.

#let column=

case  substr($runLocale,0,2)

when 'en' then 'PRODUCT_LINE'

when 'de'then 'PRODUCT_LINE_German'

when 'fr' then 'PRODUCT_LINE_French'

else 'PRODUCT_LINE_Korean'

end;

column#

In other situations, the column identifiers would lend themselves to macros similar to this.

# 'PRODUCT_LINE_'+ toupper( substr($runLocale,0,2)) #

This macro would resolve to values similar to these, which are identifiers in a data module. You can use single part and two-part names, depending on which would be appropriate. The former would be for a calculation which references an identifier from another query item in the query subject. The latter would be for a calculation which references an identifier from a query item from another query subject.

PRODUCT_LINE_EN

PRODUCT_LINE_DE

A lengthy discussion of macros is out of scope for this article, but this should give you an idea.

You can hide the source columns and only expose the calculation. Subsequently only that column’s label would need to be translated.

Metadata localization

Overview of translation of data module metadata

The ability to localize the metadata of a data module is new for CA 12.0.1. This allows you to translate the labels of tables, columns, calculations, filters, and other data module objects into locales which your users understand. You can also translate the screen tip and description properties.

Metadata translation consists of language translation definitions, which match the labels, description, and screen tip properties of objects in the data module to values for a language and dynamically presents the translated strings to the user of the data module.

When a data module which has metadata translation set up is used a report or (in a future release) a dashboard, the preferences set by the user in CA will display the labels of those objects in the appropriate locale. It is possible to create a data module in one locale, create the report with it in another locale, and run the report in a third locale.

In addition, you can further refine the grain of translation by including the long identifier in the translation definition. This allows multiple instances of a label to be translated with different values, as the original label value might not be appropriate for use in the translation in all contexts. This is not something which is available in FM. The long identifier consists of the object’s identifier and the identifier of the object’s parent.

For example, assume you have a column called city in your customer dimension as well as in your store dimension. If you needed to, you could use the long identifier, which provides the context which would say where each city column exists, to qualify that the label city in the customers dimension is translated with a different value than that of the store dimension city column. For example, in one instance ville would be appropriate and in the other cité would be appropriate.

Metadata localization workflow

1.      Export metadata.

2.      Get it translated.

3.      Put it into a data base or uploaded files.

4.      Import the translations sources.

5.      Create the translation definitions.

6.      Test the translation definitions.

7.      Refine the translations.

Translation definitions

The core of metadata localization is the translation definition. A translation definition is how you map the strings which comprise the label, description, and screen tip metadata of your data module to translations of those strings.

A translation definition consists of a label, the to language, from language, Original text field, translated text field, long identifier field and a switch to indicate whether the translation definition is active or not.

Label

The label is the name of the translation.  The default value of a label is the language name as set by the to language property. For example, if the to language property is French then the label value will be French. If the to language property is a country specific language setting such French (Canada) the label property will be that. If you change the to language property then the label will change. 

You can change the label as well to whatever you want it to be. If you do this, then the synchronization between the to language property and the label will not continue.

You can use the clear label button to clear the text you have entered and restore the synchronization between the to language property and the label.

To language      

The to language property defines the locale of the translation. If the locale of the user matches the to language value then that translation will be used. Consult the section on resolution rules for more information.

The user’s locale is set in the content language preference setting of CA.

The list in the to language property is alphabetically sorted for the locale. For example, as viewed by a Japanese user the first value in the list is Icelandic.

From language 

The from language property indicates the locale of the original text column. The locale of the original text could be different from the design language property, which is a property of the data module.

This gives great flexibility, allowing explicit specification of the original text column’s language rather than inferring from the design language. This is so the locale of the original text column is always known.

Specifying the from Locale allows the modeler to override translations from source modules that could be in a different design locale than the data module.

The from and to languages can be the same. You can use this to give to your users more understandable object labels rather than the more cryptic ones often found in data bases.

Original text      

The original text is the text of the label, description, or screen tip of the data module. This text is the source string for translation. The instances of that string will be translated with the translation string.

For example, assume you have a query item called Retailer country. Assume that the long identifier is called page.Retailer_country.   (This example is from the samples metadata translation module). When the data module is used the label, description, or screen tip value in original text will be replaced by the value defined in the translation field.

In the case of the sample data module, if the locale of the user is French the query item would be displayed as Pays du détaillant. In Chinese it would be 零售商国家/地区. This is shown in the picture below.

 

There is an optional setting for the case of the original text to be taken into consideration during translation. It is the Match the case of the original text checkbox. This would mean that the case of the original text would need to exactly match that of the object in the data module in order for the translation to take place.

Assume that you have a screen tip with the string Retailer Country. (i.e., the difference between it and the query item label is that the first character in country is capitalized.). Assume that there is no row value with Retailer Country as the original text, just Retailer country.

If the Match the case of the original text checkbox is not set, then the two stings would be translated with the same string. If it is set on, then each string will be translated using the appropriate translation string. If no such original text value exists which matches the text of the object being translated, then that object will not be translated.

In our example if the setting is not on then the screen tip would get the translation. If the setting is on, then it would not get translated.

See the mapping rules section for more information.

Translation        

The translation is the string which will be substituted for the original text.

Long identifier

The long identifier consists of the identifier of the object plus any qualifying identifiers. More information about identifiers is in the export language section plus my blog about identifiers, Identifiers and their use. 
https://community.ibm.com/community/user/businessanalytics/blogs/ian-henderson1/2020/12/21/identifiers-and-their-use

You can use the long identifier to refine the scope of localization so that a string can be translated in different ways for different objects which have the same label.

The long identifier property is optional.

There is an optional setting which allows further refining of scope by matching the case of the long identifier. By default, the matching is case insensitive.

Can only have one translation defined for a locale pair (i.e., the to language and from language properties) and set as active. You can have any number of inactive translations for a locale pair.

Translation sources

The sources of a translation can be anywhere, provided that a valid query can be generated with them.

Usually, a translation will come from a table, with columns such as original text, the translation value, and, if you want to use it, the long identifier. You can also use a set of tables, provided that they are related and, that at run time, a valid query can be generated.

You can also have other columns such as property and short identifier and any other column of your own, such as a column describing to the translator what the object is so he can have a better idea of the nuances of the translation.

The translation sources are separate from the data module. The module only needs to have them in the data module to set up the translation definitions. The data of the translations remain in the translation sources, which can be either in uploaded files or data bases.

In the sample localization data module, the source metadata for the data module is in a file called Global_retail_sales_multilingual.xlsx. The translation source is in a file called Metadata.xlsx. It contains translations for French and Chinese. You do not need to have all of your translations in one source.

You can get the sample files and read more about them here:
https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=metadata-using-sample-files-create-multilingual-data-module

The independence of translation sources is unlike FM, which stores the translations in the FM model. In FM changes to translations requires you to republish any packages which would be affected by the changes. Maintenance and updating of translations will not affect the data modules and will not require that the data modules be updated except if the metadata of the translation sources has changed.

One possible technique you could try would be to create translation definitions with translation source stubs. The stubs would just contain the metadata of the translation source or, possibly, some of the translated strings. Since the translation sources are separate from the data modules and the translated strings are retrieved at run time it does not necessarily matter to the modeler if the translations have come back from the translators yet.

You can have any number of translation sources. A source can be used in any number of translations.

This flexibility means you could create many copies of your exported metadata file and give one to each of your translators. Once they have returned their version of the file you could upload it and use it to create a translation definition.

An alternative would be to merge the translations into one file. A method to do that would be to sort the translations by the original text column, which presumably would be common to all of the translated files. You would then copy, for each of the translations, the original text, long identifier (if you intend to use it), and translation columns into the proposed translation collection file. You would then use the spreadsheet’s utilities, such as EXACT, to ensure that each translation has all of the exported metadata. If that is so, then you could then use that file as your translation source.

That alternative is possibly more cumbersome than using many translation files and the exercise of combining them inherently has risks of corruption given that you are {doing stuff}.

The exercise of reconciliation of the translated files to the metadata needs to be executed no matter what and you would need to ensure that no metadata was accidently removed or altered by the translator.

You can use uploaded files or data base tables as the translation sources.

Preview

You can preview a translation. A new browser tab will be created, and the translated metadata will be presented. There will be a green border around the previewed metadata. This can allow you to see how the translation definition has translated the metadata.

I have an enhancement request to get validation to detect when an object does not have a translated string.

You can save a previewed data module. The localized strings are saved in the module. It is probably a good idea not to overwrite the source data module with the preview.

Metadata translation resolution rules

Rules for resolving translations will attempt to match some defined translation to the user’s locale. If no locale is specified for the user’s locale, then the next nearest locale’s translation defined will be used.

The order of resolution will start with an attempt to match the user’s locale to a translation definition which has the same to language value.

For example, assume that the user’s locale has both language and country defined, such as fr-be (French-Belgium). The first attempt will be to see if fr-be exists as a to language value in a translation definition.

If such a translation definition does not exist, the next attempt is to see if a translation definition exists which has just the language specified. In the case of the example, it would be fr (French).

If that definition does not exist, then an attempt will be made to walk down the list of translation definitions.  The first other related language which is in the list of translations will be used.

In the example if there were translations for fr-fr and fr-ca then the first one which is encountered will be used. The translation definitions will be placed at the beginning of the list of translations. That is to say the oldest translation definitions will be at the bottom of the list.

If no translation definition can be found the design language strings will be used.

In the following table the modelled translations appear in the order in which they appear in the data module. For example, in the last example fr-fr appears before fr-be because it appears earlier in the list of translations. It appears there because it was modelled after fr-be.

Modelled translation	User’s locale	Translation used	Comments
·        fr	fr-ca	fr	Because the user’s locale is not defined in a translation but there is a translation defined for a related locale, the related locale translation will be used.
·        fr-fr	fr-ca	fr-fr	Because the user’s locale is not defined in a translation but there is a translation defined for a related locale, the related locale translation will be used.
·        fr-fr	Fr	fr-fr	Because the user’s locale is not defined in a translation but there is a translation defined for a related locale, the related locale translation will be used.
·        fr ·        fr-ca	fr-ca	fr-ca	There is a direct match of the user locale and the locale of a translation so that translation will be used.
·        fr ·        fr-fr	fr-ca	fr	Because the user’s locale is not defined in a translation, the simplest, nearest locale specified will be used.
·        fr-fr ·        fr-be	fr-ca	fr-fr	This is because fr-fr is the first of the French translations in the list of translations.

Exported metadata

When you perform the export language action a CSV file is generated. It will contain columns which represent the choices you made in the export language dialog.

For each exported object there will be several columns, which help identify the proper context.

They are original text, which will always be there, property, short identifier, and long identifier.

You have control over whether you export the labels of objects, description, or screen tip properties.

The original text column will always be present in the file. This column will contain the strings of the properties you have chosen to export.

The property column identifies whether the object is a label, screen tip, or description.

The short identifier contains the identifier of the object.

The long identifier column is comprised of the object’s identifier property plus those identifiers which are needed to identify where the object is located.

For example, assume you have a standalone filter called Active Employees. The original text column value in the exported language file will be Active Employees. The value for the property column for it will be Label. The short identifier will be Active_Employees. The long identifier will be Active_Employees. This is because objects such as stand-alone filters and calculations are children of the data module and their identifiers are globally unique within the data module, which is to say that the module is the namespace.

If you have a query subject called Product Forecast Fact the value for original text will be Product Forecast Fact. The value for the property column will be Label. The short identifier will be DIST_PRODUCT_FORECAST_FACT and the long identifier will be DIST_PRODUCT_FORECAST_FACT.

If you have a column in that query subject called Expected Volume, the value for original text would be Expected Volume. The value for property would be Label. The value for short identifier would be EXPECTED_VOLUME. The value for long identifier would be DIST_PRODUCT_FORECAST_FACT.EXPECTED_VOLUME. You will notice that DIST_PRODUCT_FORECAST_FACT needs to be part of the long identifier. This is because the query subject identifier is necessary to uniquely identify the column.

If you have relative time defined for a measure then you will see something similar for the relative time measures.

Current Month [Expected Volume] Label Current_Month  DIST_PRODUCT_FORECAST_FACT.EXPECTED_VOLUME.Current_Month

For more information about identifiers, consult the Cognos documentation and my blog topic on it, Identifiers and their use.

https://community.ibm.com/community/user/businessanalytics/blogs/ian-henderson1/2020/12/21/identifiers-and-their-use

You can control whether to include hidden objects or not. The default is not to include them. The control over the decision to include or exclude hidden objects is the Exclude hidden objects /include hidden objects switch. Implicitly hidden objects are included.

By default, all but one variants of case for an object are ignored. There is a switch which will include all of them. It is the ignore case-sensitivity / maintain case-sensitivity switch.

You can also include objects from another data module which is being used in your current data module using the exclude external references / include external references switch.

When you define a language translation you will use the original text values and the translations which your translators have written. You can optionally use the long identifier to refine the grain of translation.

The list of objects in the file and in the export language dialog will be alphabetically sorted. You can sort the properties into another order by using the sorting functionality of whatever application you use to edit the exported language CSV.

You have control over whether to export the entire data module or performing a selective export. What objects are exported depends on what you have selected. If you select the data module tree root node, then everything in the data module will be exported. If you select a folder, then only the folder and its contents are exported. If you select a query subject only the query subject and its child objects are exported. If you select several objects only the metadata of those objects are exported.

You would need to understand what would be the best approach for you, taking into account the tradeoffs entailed by either choice. Some factors to consider include the costs of translation. It could be possible that translating several bits of the data module could cost more than exporting and translating the entire data module.

You will notice that folders have a null for their long identifier. This is because folders are organizing objects and not namespace relevant. This is the same as folders in FM.

Hidden objects

The exclude hidden objects setting excludes from the export language file only those objects which have been explicitly set as hidden. This will mean that although the parent object’s label will be excluded, all child objects, except for those which have been set as hidden by setting the hide from users toggle switch, will be included in the exported language file.

For example, if you hide a folder, the objects in the folder are implicitly set as hidden and if you move some of those objects out of the folder they will not be hidden. They are hidden because their parent is hidden.

If you exported a module which has such a folder, the folder label, description, and screen tip will not be exported but the properties of the objects in the folder will. If you set the include hidden objects setting on, then the folder label will be included in the file.

This can produce a result where labels, descriptions, and screen tips are exported for objects which are hidden and the intention for which is to always be hidden. The tradeoff is between exporting and going to the bother and expense of translating strings which will never be exposed to users and the coverage of translation.

You can make multiple selections in the module metadata tree and choose export languages. This could allow you to export exactly what you want.

Another factor to consider is modelers or other users working with a data module itself, not just using the data module as their data source in a dashboard or report.

If you intend to allow people to work with a data module in their own language – for example, if you provide a data module which is then extended by some other modeler or which is used in another data module then having implicitly (and indeed explicitly ) hidden objects translated could be helpful to that modeler as the object labels would have been translated into a language in which he is more comfortable.

So, the considerations for the decision about how to deal with hidden objects are multi-dimensional.

I have an enhancement request to honour implicit hiding as it will be better than the bother of schlepping through the data module and marking everything you want to be hidden as explicitly hidden.

A selected object will always be included in the exported metadata even if it was explicitly set to hide from users.

Validation

Validation will detect if a translation definition has any of the mandatory fields not filled in and display an error message. It will also detect if there are duplicate translation definitions.

I have an enhancement request to get validation to detect when an object does not have a translated string.

Mapping rules 

When the metadata is translated, a query is made to get the translations from their sources. The results of that query are then sent to decorate the metadata of the data module metadata tree.

This query will be sorted ascending. If there are duplicate values for original text, then the last value will be used.

For example, assume you have a translation definition which uses original text only for the mapping between the original text of the metadata and the translation. Further, assume that your translation source has two translations for camping equipment.

Original text	Translation
Camping equipment	Campingausrüstung
Camping equipment	Campingausruestung

If this was sorted ascending, then Campingausrüstung would be last. Because of that, it will be used to translate the original text of Camping equipment.

Because of this, if you need to have control it would be best to ensure that you do not have duplication.

If the long identifier is used in the translation definition, then the sorting is performed on that.

Design locale

As part of the implementation of multilingual metadata support, the design language property has been exposed. It is a property of the data module.

Another property of a data module is the translate content switch. If it is set off, then no metadata translation will take place.

Using translated data modules

You can use a data module which has translation defined in another data module, in another locale, where additional modelling is performed. The second data module would be an extension, leveraging the first data module.

The metadata of the source data module presented in the destination data module will be of the locale of the destination data module. This is set in the design language property of the data module.

Expression separators of one locale should not be a problem if they exist in a source data module being used in a data module of another locale. Queries which use objects from both the source data module and the destination data module should work seamlessly.

A good practice is to model with a language neutral separator. It is the semi-colon symbol,;. The comma is not supported as a separator in some locales. This practice holds true for other places such as data security filters in a schema.

Standalone calculations and filters brought into another data module are not references, unlike other objects. Because of that language non-neutral expressions will be a problem.

You can use the from locale in a new translation definition to override the existing translations from source data modules that could be in a different design locale than the data module.

Translation pool data module

One technique I experimented with and found useful was a translation pool data module. Instead of importing translation sources into each data module I imported them into one data module, which was to serve as the translation source pool, and import that data module into my other modules.

Changes to the metadata of the translation pool- added columns for example—would be picked up by the data modules, which eased maintenance slightly.

If you add new columns to a file or table if you have a translation pool there is only one place to add the column. The new column will appear in the sources the next time you open a data module which consumes the translation pool data module and all you need to do is drag it into the data module.

Another aspect of this technique is that it is easier to find your translations if they are in one module. It is also possible that other modelers will want to use the translations.

String escaping

Commas and single and double quotes do not seem to cause a problem for the exported metadata. I performed tests which verified that the escaping of such characters was executed. This is slightly more important than you might initially think as some languages make use of things such as single quotes in their normal language usage.

 

Model modification and maintenance

As new metadata is added you can add in new translations. You are not strictly held to getting translations once the module has been fully modelled.

It is probably a good idea to hold off the translation exercise to latter on in the data module development cycle, when the data module metadata will be fairly settled. Otherwise, you will probably be wasting time and resources translating things which are later changed.

Durable model

In FM, you needed to follow the durable model technique where you would set your design locale to be some value, such as en-zw, which would ensure that all object references would be stable no matter what locale the user of the FM package was in. It was a poor man’s identifier.

The identifier of an object in a data module is stable from one locale to another. You can create a report in one language and it can be seamlessly used by users in any number of other locales.

The identifier is used in expressions and thus in the generated SQL and queries. The metadata translation is isolated from the translation and a report written in one language should be available in one of the other languages modelled in the data module, in their languages.

Because what metadata you will need to translate is determined by what you have in the data module and what you want to expose to consumers, you probably should work on the localization of data first.

Sorting

In general, I think that if you are going to need to model for multiple locales and have sorting then you would be best to use a sort index rather than rely on custom sorting. An illustration is shown below. Each of Month English, Month French, and Month German will have been sorted by Month sort index. Month would pick up the appropriate column. Because all three of the possible source columns are sorted in the same way a German report consumer would see everything sorted in the same order that his French and English counterparts will see.

Month	Month English	Month French	Month German	Month sort index
Some expression usually using a macro to look up the user’s locale and substitute the appropriate column identifier	January	Janvier	Januar	1
	February	Février	Februar	2
	March	Mars	März	3

A discussion about sorting can be found here:

https://community.ibm.com/community/user/businessanalytics/blogs/ian-henderson1/2023/12/18/sorting-and-members-part-1-sorting

How data module metadata translations are better than FM’s analogue

In FM if you have changes you need to export the model strings (if the changes have taken place in FM), import the translations, and then republish the packages which were affected by the changes. Also, because the translations exist in the FM model, it makes it a lot larger than need be.

In a data module the translation sources are essentially like all other uploaded files and data bases and will automatically resolve at run time, without any need to edit and save the module when translation changes are made.

If you need to revise translations the action will not require working with the data module as the changes will be picked up dynamically.

If new objects are added to the data module you will need to export the metadata. This could be done by either just exporting the new objects or exporting the entire module again.

For the former you would need to append the new translated metadata to the translation source. If the translation source is an uploaded file that is done easily enough. I don’t know how to do the action for data bases so I can’t speak on that but I believe that it is not a particularly intellectually challenging exercise.

For the latter you would need to copy the existing translations into the new exported metadata file and send it off to the translators, who would just need to fill in the empty cells.

Extended sample metadata translation

For 12.0.1 a new sample has been created for the demonstration of localization of data modules. The sample demonstrates both the localization of data and the localization of metadata.

The sample consists of a single table. In that table are records for multiple languages. There is a second file, which contains the translated strings for French and Chinese.

Here are the links to the relevant files.

•        Data source 

https://public.dhe.ibm.com/software/data/sw-library/cognos/mobile/C11/data/Global_retail_sales_multilingual.xlsx

•        Multilingual metadata

https://public.dhe.ibm.com/software/data/sw-library/cognos/mobile/C11/data/Metadata.xlsx

Modelling instructions

https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=metadata-using-sample-files-create-multilingual-data-module

Troubleshooting

If you observe untranslated strings perform the following actions.

•        Confirm that it exists in the exported translation files.

•        Confirm that the original text value matches that of the object which is not being translated.

•        Confirm that the identifier value matches that of the object which is not being translated (if you are using identifiers)

•        Review the case of the strings.

•        Review whitespaces

If nothing is translated confirm the following.

•        Is the translate content switch on? (It is a property of the data module)

•        Does a translation definition exist for that language?

•        Is the translation definition set as inactive?

It is difficult to determine when an object has not been translated other than schlepping through the metadata tree in preview or in reporting. I have an enhancement request to get validation to detect when an object does not have a translated string.

Relative time measures are particularly troublesome as you need to get the translated value for the parent measure name, which is embedded in the label of the relative time measure, and then add it to the translated string. For example, Current Month [Quantity] would be Mois en cours [Quantité]. The translator would need to keep in mind that he needs to know that the string Quantity, which is in another row in the exported translation file, is the same as in this row and that he will need to translate both consistently.