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.
Image: Language swap process flow
This example illustrates the Language swap process flow.
The swapping process works as follows:
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.
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.
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.
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:
|
ProductID |
Description |
|---|---|
|
P101 |
Libro |
|
P102 |
Lápiz |
|
P103 |
Pluma |
Here is the Product Language table from the source database:
|
ProductID |
Lang CD |
Description |
|---|---|---|
|
P101 |
ENG |
Book |
|
P102 |
ENG |
Pencil |
|
P101 |
FRA |
Livre |
|
P102 |
FRA |
Crayon |
|
P103 |
FRA |
Stylo |
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:
|
ProductID |
Description |
|---|---|
|
P101 |
Book |
|
P102 |
Pencil |
|
P103 |
Pluma |
Here is the OWS Product Language table from the source database:
|
ProductID |
Lang CD |
Description |
|---|---|---|
|
P101 |
SPA |
Libro |
|
P102 |
SPA |
Lápiz |
|
P103 |
SPA |
Pluma |
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
|
Time Key |
Product Key |
Store Key |
Quantity |
Amount |
|---|---|---|---|---|
|
1 |
1 |
1 |
5 |
10 |
|
1 |
2 |
1 |
1 |
3 |
|
1 |
3 |
2 |
2 |
3 |
This table shows an example of the related Product dimension table
|
Product Key |
SKU |
Description |
|---|---|---|
|
1 |
A123 |
Bread |
|
2 |
B234 |
Marmalade |
|
3 |
C345 |
Milk |
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):
|
Product Key |
Language Code |
Description |
|---|---|---|
|
1 |
ENG |
Bread |
|
1 |
GER |
Brot |
|
1 |
ITA |
Pane |
|
2 |
ENG |
Marmalade |
|
2 |
GER |
Marmelade |
|
2 |
ITA |
Marmellata di agrumi |
|
3 |
ENG |
Milk |
|
3 |
GER |
Milch |
|
3 |
ITA |
Latte |
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.