Siebel Customer-Centric Enterprise Warehouse Installation and Configuration Guide > Integrating Additional Data > Creating New Mappings >

Creating a Load Mapping

Load mappings transform data from a staging table and load it into the IA and OD target tables. They usually contain the following components:

• Staging table
• Shortcut to a load control area (OD_*) table or IA_* table
• Source Adapter mapplet
• Shortcut to ADI mapplet
• Shortcut to IA and OD target tables

The staging table provides temporary storage for extracted source data. The load mapping compares extracted source data in the staging table against the load control table for updates to existing data.

If there is no load control table, use a shortcut to the IA table. The IA table and the staging table pass the data to the Source Adapter. The Source Adapter contains most of the business logic used in preparing the data for the Analytic Data Interface (ADI). The Source Adapter uses an Expression transformation to handle data type conversions, source-specific lookups, and to create control columns used in loading.

The load mapping prepares data for the ADI, matching its output with the input of the ADI mapplet. The ADI mapplet contains source-independent transformation logic, such as:

• Lookups to resolve keys and codes for dimension table loads
• General calculations, such as currency conversions for fact table loads

The mapping, M_I_PURCH_ORDERS_LOAD, which is shown in Figure 48, provides a sample of a typical fact load mapping.

Figure 48. Basic Structure of a Fact Load Mapping

The mapping, M_I_CUSTOMRS_LOAD, which is shown in Figure 49, provides a sample of a typical dimension load mapping.

Figure 49. Basic Structure of a Dimension Load Mapping

To create a load mapping

1. In PowerCenter Designer, open the applicable source configuration folder.
2. Select Tools > Mapping Designer.
3. Select Mappings > Create to create the mapping.
4. Enter the mapping name, and click OK.

   For a list of naming conventions, see the Siebel Customer-Centric Enterprise Warehouse Data Model Reference.

5. From the Repository Navigator window, drag and drop the following items into Mapping Designer:
   • Staging table founds in IA_[SOURCE]_STAGE. For example, IA_ORA_STAGE for Oracle Applications.
   • Shortcut to Control table (OD table or IA table) found in IA_[SOURCE]_CONTROL. For example, IA_ORA_CONTROL for Oracle Applications.
   • Source Adapter mapplet from the Mapplet folder. For example, MPLT_SAO_AR_XACTS in Oracle Applications.
   • Shortcut to ADI mapplet from the Mapplet folder. For example, MPLT_ADI_AR_XACTS.
   • Shortcut to IA and OD target tables from the Target folder. For example, IA_AR_ACTIVITY.

     NOTE:  If you are going to require Type II slowly changing dimension support, you must import two instances of the IA target table into your dimension load mappings. For more information on Type II slowly changing dimension, see Type I and Type II Slowly Changing Dimensions.

6. Delete any unnecessary Source Qualifier transformations.

   If set up to do so, PowerCenter Designer creates separate Source Qualifier transformations for each source table you drag into a mapping.

   For example, if you drag in a staging table and a control table (or even multiple instances of the same table) as the sources for your mapping, you have one Source Qualifier for each of them. Because only one Source Qualifier is required, delete all other Source Qualifier transformations.

7. After deleting any unnecessary Source Qualifier transformations, connect all ports to the one remaining Source Qualifier:

   NOTE:  In the previous example, the control table ports and the staging table ports are both connected to the same Source Qualifier transformation.

   1. Connect the Source Qualifier output ports to the Source Adapter mapplet input ports.
   2. Connect the Source Adapter mapplet output ports to the ADI mapplet input ports.
   3. Connect the first set of ADI mapplet output ports, classified under the MAPO_[SUBJECT]_IA1 heading, to one of the IA table instances.

      NOTE:  If you require only Type I changing dimension support, there is only one instance of the IA table and you can move directly to Step 9. For more information on Type I slowly changing dimension, see Type I and Type II Slowly Changing Dimensions.

8. If it is a dimension load mapping with Type II support, connect the second set of mapplet output ports, classified under the MAPO_[SUBJECT]_IA2 heading, to the second instance of the IA table.

   This set is different from the main set of ports for the IA table; it only has the surrogate key and the control ports.

9. Connect the third set of mapplet output ports, classified under the MAPO_[SUBJECT]_OD heading, to the control table. If the dimension does not have a control table, then you only need to connect to the IA tables.
10. Double-click the Source Qualifier to open the Edit Transformations box.

    You need to modify the User Defined Join and the SQL Query ports.

11. Click the Properties tab, and then click the small arrow in the Value column by User Defined Join to open the SQL Editor.
12. Edit the SQL statement for the User Defined Join port, as shown in the following figure, and enter the join condition between the source tables in this port.

    Generally, the join is based only on the KEY_ID and SOURCE_ID. (Sometimes, SRC_EFF_FROM_DT, in addition to the KEY_ID and SOURCE_ID, is used in the join condition.) The join condition is an outer join between the staging area and the OD table. All staging area records are selected, whether or not they are present in the OD table.

13. Click the small arrow in the Value column by SQL Query to open the SQL Editor.
14. Edit the SQL Query statement.

    Generate the SQL statement by selecting the Generate SQL button.

15. Validate the query by clicking the Validate button.

    TIP:   Before validating the query, you must remove the WHERE clause from the SQL statement, and temporarily store the text until you validate the SQL statement. PowerCenter uses a database-independent outer join syntax, and the WHERE clause is one of the database independent outer join syntaxes not understood by the SQL statement. After validation, replace the WHERE clause.

16. Save your changes to the repository.
17. In PowerCenter Workflow Manager, you must now create a session for this mapping.

    For information on how to resolve dimension keys, see About Resolving Dimension Keys.