Bookshelf Home | Contents | Index | PDF     

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

Creating an Extract Mapping

Extract mappings are used to extract source data and store it in the staging table. They usually contain the following components:

  • Business Component mapplet
  • Expression transformations
  • Staging tables

NOTE:  Universal Source extract mappings do not contain business components. For information on Universal Source mappings, see About Integrating Data from Source Systems Without Prepackaged Business Adapters.

About the Basic Structure of an Extract Mapping

M_I_PURCH_ORDERS_EXTRACT, shown in Figure 47, is an example of a basic extract mapping.

Figure 47. Basic Structure of an Extract Mapping

The Business Component mapplet extracts data from the source tables. For more information on creating a new Business Component, see the discussion on creating a new Business Component in Process of Creating and Modifying Business Adapters. After the Business Component mapplet extracts the source data, it then passes the data to at least one Expression transformation, which configures the Source ID and the Key ID. The data can pass through as many different types of Expression transformations as necessary to transform the data into a usable state.

After the transformations occur, the data is passed to the staging area target table. When creating a new extract mapping, create a new staging table as well; do not use any existing staging tables as it may impact performance of other mappings using that same table. For information on creating a new staging table, see the discussion on the staging table format in Staging Table Format. For information on working with extension columns, see Overview of Integrating Additional Data.

About Integrating Data from Source Systems Without Prepackaged Business Adapters

If you are adding generic source data—that is data that is extracted from source systems other than the prepackaged sources, such as Oracle, PeopleSoft, and SAP—then you do not need a Business Component mapplet in the extract mapping. You can omit this step, because generic sources must be in a ready-to-load state and do not need business components. Transformations are not prepackaged in the Universal Adapter mappings that extract, transform, and load this type of data. For more information on adding generic source data, see About Integrating Data from Source Systems Without Prepackaged Business Adapters.

NOTE:  If a new staging table is created, you must create a new load mapping to move the data into the data warehouse. For information on creating a new load mapping, see Creating a Load Mapping. If an existing staging table's extension columns are used, the load mapping is prepackaged to move any data from the Staging table's extension columns into the Siebel Customer-Centric Enterprise Warehouse's extension columns.

To create an extract 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.

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

  5. From the Repository Navigator, open the mapplet folder and drag the Business Component mapplet you require into Mapping Designer.
  6. Open the transformation folder, and drag the reusable Expression transformation EXP_SOURCE_ID_FORMATION into Mapping Designer.

    NOTE:  If no reusable Expression transformation is available, proceed to Step 10.

  7. Double-click on the Expression transformation to open the Edit Transformations window, and select Rename.
    1. Enter a new name for the transformation and click OK.

      The naming convention for Expression transformations is EXP_[SUBJECT], where Subject is the subject that identifies the entity such as Suppliers or Customers.

    2. Enter a Description of the new Expression transformation for future reference, and click OK.
  8. Drag the output ports from the Business Component mapplet to the Expression transformation input ports to connect them.
  9. Create a KEY_ID column in the Expression transformation, and then:
    1. On the Ports tab, edit the definition of the KEY_ID.

      The Key ID uniquely identifies records within a source. The KEY_ID port has a data type of string(80). This port is an output only port—select output (O) flag.

    2. In the Expression column, enter the definition for the Key ID.

      You must include all columns that make the records unique.

      NOTE:  While forming the Key ID, do explicit type conversions inside the Expression Editor box for those ports that are not of string data type. After you create your new KEY_ID port, you may view it in the Expression transformation box.

      TIP:   It is recommended you keep the columns having the greatest number of different values first and the columns with the least distinct values last, separating each column name with a tilde (~).

  10. If a reusable transformation was available as defined in Step 9, continue directly to Step 12.
    1. If a reusable transformation is not available to provide a Source ID, you must create a new one by selecting Transformation > Create.
    2. Select Expression from the Select Transformation Type list window.

      Enter a new name for the transformation and select Create, then Done.

    3. Drag the output ports from Copy the Business Component mapplet to the Expression transformation input ports to connect them.
  11. Double-click the transformation to open the Edit Transformation window and create the Source ID.

    NOTE:  In addition to creating a Key ID, you must also create the Source ID.

    1. Edit the Expression transformation to add another column called SOURCE_ID, with data type of string, 30.
    2. On the Ports tab, edit the definition of the SOURCE_ID to an output port only.
    3. Enter an abbreviation for the source.

      The abbreviations for preconfigured source systems are shown in the following table.

      Source
      Source Abbreviation for SOURCE_ID

      Oracle 11i

      OAP11i

      PeopleSoft

      PSFT75

      SAP R/3

      SAPR3

      NOTE:  You may change the default Source ID if you use multiple instances of the same source. For example, you may be running multiple instances of SAP and, therefore, want separate Source IDs for each instance. For example, you might call the first instance SAPR3_1, and the second instance SAPR3_2, and so on.

      After you create your new SOURCE_ID port, you may view it in the Expression transformation box.

  12. Open the Target folder, and drag and drop the staging table into Mapping Designer.
  13. Connect the ports from the Expression transformation to the staging area target table.

    The target table may have extra ports or extension columns created for later customization. Leave these empty if there is no corresponding port coming in from the Expression transformation.

  14. Validate the mapping and save your changes to the repository.
  15. In PowerCenter Workflow Manager, you must now create a session for this mapping.

    Be sure to set the target load properties to truncate the staging table by selecting the Truncate check box.

    
Siebel Customer-Centric Enterprise Warehouse Installation and Configuration Guide