Setting Up Multilanguage Processing and Running the Language Swap Utility

This section provides an overview of multilanguage processing and the language swap utility, and discusses how to run the language swap utility.

Understanding Multilanguage Processing

Many organizations conduct business globally and deploy their PeopleSoft source systems in various locations throughout the world. PeopleTools can store multiple translations of application data and PeopleTools objects in a single database. Each PeopleSoft database has a single base language. The base language of a PeopleSoft database is usually the language most commonly used by application users, and is the language in which data is stored in the core PeopleSoft tables known as base language tables.

All PeopleTools objects (such as pages, fields and queries) can be maintained in multiple languages. Descriptions of application data elements (such as departments, locations and job codes) can also be maintained in multiple languages. The key to maintaining this data in multiple languages is the use of special tables known as related language tables.

Related language tables store descriptions and other language-sensitive elements in all languages other than the base language of the database. In this way, while any table in the database can store data in the base language of that database, only tables that have related language tables can maintain the same data in multiple languages simultaneously. For example, it is unlikely that you would maintain the descriptions of your general ledger journal lines in multiple languages—the sheer volume of the journal lines in most systems would preclude any effort to maintain translations of their descriptions. The cost of hiring a translator to translate each journal line would be prohibitive, and in most cases only the person entering the journal line, and possibly that person's supervisor, would be likely to want to view that information again. However, for frequently used values, such as a chart of accounts, many users across your entire organization would often need to refer to this data. Therefore, you would most likely maintain the descriptions of each ChartField entry in each language spoken by your users. In this case, you would not need a related language table for your Journal Lines table, as you would be maintaining journal line descriptions in a single language, which would be in the base table. However, you would need a related language table for each of your ChartField tables.

When the system displays a language-sensitive field value, it retrieves the text from either the base table or the related language table, depending on the following:

• The current language preference.

• Whether any translated rows for the field exist in the related language table.

The language preference refers either to the PeopleSoft PIA sign-in language, or in the case of PeopleSoft Application Designer, to the language preference as determined by the PeopleSoft Configuration Manager language setting. If the current language preference is the system's base language, the text is retrieved from the base table. If the language preference is a non-base language, then the system looks for a translation of the text in the related language table. If it finds a translation, it displays the translated text; if no translation exists, the system uses the text in the base table. This enables developers to selectively translate portions of the data, while keeping the system fully functional at all times, even if not all rows have been translated.

EPM also uses related language tables to support multilanguage processing. In each of the three data warehouse layers (the OWS, OWE, and MDW), all records that have translatable description fields have corresponding related language tables. Related language tables are defined for every OWS, DIM, and D00 table that contain translatable values. For example, the table CUSTOMER_D00 has a corresponding related language table CUSTOMER_LNG. Related language tables have key structures identical to the related DIM and D00 table plus one additional key field called language code (LANGUAGE_CD). The language code field holds the source language value. Prepackaged ETL jobs extract this data from a PeopleSoft source system and populate the field with your source language value.

EPM extracts data from PeopleSoft source systems, which have their own base languages and supported foreign languages. Multilanguage infrastructure in PeopleSoft source systems store the base language in the base table and the foreign language descriptions in the related language table. If the base language of the source database and that of the EPM database are not the same (but the source database's base language is one of EPM warehouse's supported foreign languages), the description from the base table in source database must be stored in the related-language table in EPM to ensure consistency. If a supported foreign language in the source database is the EPM warehouse's base language, then that foreign language description must be stored in the base table in the EPM database. We achieve this consistency through use of the Language Swap Utility.

The Language Swap Utility and multilanguage processing enables you to:

• Import descriptions for any language into EPM target warehouse tables.

• Exchange descriptions in source tables with the related-language tables, when source defined language is different than the EPM defined language.

• Report in different languages.

The Language Swap utility abstracts the process of language swapping from all of the ETL maps that load data into the EPM database. As a result, the utility reduces the complexity and increases the maintainability of the ETL maps.

Understanding Multilanguage Setup

You must enable multilanguage processing before you can view your data in a different language or run multilanguage reports. Setting up multilanguage processing requires three simple steps:

• Define the base language of your source systems: Use the Define Warehouse Source page to perform this task.

  See Specifying Your EPM Sources.

• Run the language swap utility.

Note. Language Swap is OWS post process and so before running this utility, make sure to run all the staging jobs for base and language tables.

Understanding the Language Swap Utility

The language swap utility automatically detects the mismatched languages between the language defined in the source and the language defined in the EPM database, and sets the correct base language for incoming data. The utility compares the base language of the source database (as it is stored in the SRC_SYSTEM_TBL) and the base language of the EPM database. If they are different, the utility swaps the descriptions that are found in the base table and the related-language tables whenever possible. Once the data are in OWS and the base language swap utility has been performed, the reference data and their related language data in OWS are conformed to the PeopleSoft infrastructure for related language. This ensures proper synchronization and enables you to process and report in multiple languages. Note, however, that this process requires that descriptions be available in the source record. This process cannot be performed if the related language record doesn't have any description fields, or any other fields that are translatable, from the base table.

The following graphic depicts the language swap process.

The swapping process works as follows:

1. Check if the base language of the source database is the same as the base language of the EPM database.

   If the languages are the same, then continue to the last step in this process. Otherwise, proceed to the next step.

2. Check if the base language of the source database is a supported foreign language in the EPM database.

   If the language is the same, then create a corresponding entry in the related-language table for every new (not yet swapped) row in the base table. The LANGUAGE_CD used for tagging the new entry in the related-language table is the LANGUAGE_CD for the source database as it is found in the SRC_SYSTEM_TBL table.

3. Locate the description in the related language table where LANGUAGE_CD for the row is the same as the base language code of the EPM database. Once identified, perform the swap between the description in the base table and the related-language table.

4. Delete from the related-language table any rows where the LANGUAGE_CD is the base language code of the EPM database or is not any of the supported foreign languages in the EPM

The language swap utility is embedded in prepackaged ETL jobs and should be run only when your source database language is different from the language defined for the EPM database. Run the Language Swap utility after your source data is completely extracted into the OWS, but before you run any subsequent ETL jobs to transform OWS data into OWE or MDW data.

Outrigger Tables

An outrigger table represents foreign language descriptions for data in the base dimension table. The structure of the outrigger table is the same as the structure of an MDW dimension related language table. It has the base table's keys, an additional key to represent the language code (LANGUAGE_CD), and as many columns as there are translatable columns in the base table.

The difference between an outrigger table and a related language table lies in the content. Outrigger tables contain not only the foreign language descriptions of the data, but also the base language descriptions for every row of data found in the base table, even though some data do not have foreign language translations. For example, if there are ten entries in the base table and there are three supported languages, there will be 30 entries in the corresponding outrigger table. If, however, there is no corresponding description (that is, translation) for a particular entry for one of the languages in the related language, the description defaults to the value in the base table.

The advantage of an outrigger table over a related language table for reporting in third-party tools is that the outrigger table contains descriptions in the base language, as well as any supported foreign language, for all data in the base table. The completeness of the content in the outrigger table simplifies the logic for displaying the foreign language description in the third-party reporting tool, which does not have the built-in multilanguage infrastructure like PeopleSoft applications do.

Sample Outcome of the Language Swap Process

Assume you have one source database, SRC01, whose base language is Spanish and supported foreign languages are English and French. In addition, assume your EPM database has English as the base language, and Spanish as the only supported foreign language.

Here is the Product table from the source database:

Here is the Product Language table from the source database:

After the data is extracted into the OWS and the Language Swap utility is run, the following changes result in the tables:

Here is the OWS Product table from the EPM database:

Here is the OWS Product Language table from the source database:

Notice that the French translations that are available in the source database are no longer found in the EPM OWS because French is neither the EPM base language nor its supported foreign language. The Spanish descriptions that are originally in the base table are now in the related language table, while the English descriptions are now in the base table. Product P103 does not have an English description and retains its original description from the source database "pluma" – that is, "pen" in English.

This table shows an example of a Sales fact table:

This table shows an example of the related Product dimension table:

Typically, if a dimension table is used in conjunction with an outrigger table, the dimension table does not have any attributes that are country-specific. This is to prevent duplicate attributes in the dimension table and the outrigger table.

This table shows an example of the related Product outrigger table, assuming the base language is English (ENG) and the supported languages are English (ENG), German (GER), and Italian (ITA):

When you constrain language code to a single value, your reporting tool uses the attributes from the outrigger table (Product Description) and the Product dimension table (SKU and Description) to qualify the metrics (Amount and Quantity) and produce the description in the selected language.

Note. In this example, if the related language table did not contain a German description for Bread, the description for Bread in German in this outrigger table would contain Bread, rather than Brot.

You use ETL to populate outrigger tables at the time that you populate the MDW layer.

Running the Language Swap Jobs

The language swap process is a part of the PeopleSoft ETL sequencer job SEQ_J_Run_LangSwap. Simply run the job to initiate the language swap process. Please note, however, that this process should not be run until you run all staging jobs that populate base tables and language tables.

Perform the following steps to run the language swap jobs:

1. In IBM WebSphere DataStage Director navigate to the language swap jobs by expanding the nodes in the left navigation panel using the following path: Jobs, EPM_Utilities, Language_Swap, Sequence, SEQ_J_Run_LangSwap.

2. Highlight the jobs and click the "Run" button.

   If you want the job to use the values that are defined in the IBM WebSphere DataStage Administrator, then click "Run" button. If you want to override the values then type the appropriate values and then click "Run" button.