Skip Headers
Oracle® Fusion Middleware Getting Started with SAP ABAP BW Adapter for Oracle Data Integrator
11g Release 1 (11.1.1)

Part Number E16597-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

6 Creating and Running the Integration Interface

This chapter describes how to create and run the integration interface.

It contains the following topics:

Creating the Integration Interface

Now that the ODI source and target data models have been created, it is possible to create the ODI interfaces to extract data from the following SAP BW data targets:

To create the integration interface, perform the following steps:

Info Cube Extraction

This section describes how to create an integration interface in ODI in order to load data from the SAP BW source model representing the InfoCube 0FIAP_C20 data target to the W_VENDR_BAL_DS Oracle target model.

Create the Interface

To create the interface:

  1. Open Designer.

  2. In the Projects tree view, expand the SAP BW Demo Project.

  3. Expand the SAP_BW_DataTargets folder.

  4. Select the Interfaces node.

  5. Right-click and select Insert Interface.

  6. In the Interface Definition tab, enter the interface name: InfoCube_SAP_BW_VendorBal.W_VENDR_BAL_DS.

  7. Select the Diagram tab.

  8. Click OK if the following information window appears.

    Description of image034.jpg follows
    Description of the illustration image034.jpg

The interface diagram looks as follows:

Description of image036.jpg follows
Description of the illustration image036.jpg

Define the Source and Target Datastores

To define the source and target datastores:

  1. In the Models tree view, select the W_VENDR_BAL_DS datastore from the Oracle_Target Model.

  2. Drag this datastore into the Target Datastore panel (right area of the Diagram tab). This panel appears now as shown below: Description of image038.jpg follows
    Description of the illustration image038.jpg

  3. In the Models tree view, select the 0FIAP_C20 Vendor Balances datastore from the SAP BW Source model.

  4. Drag this datastore into the Sources diagram panel (left area of the diagram tab).

  5. If a popup window prompting "Do you want to perform Automatic Mapping?" appears, click No.

  6. The Sources diagram should look as shown below: Description of image039.jpg follows
    Description of the illustration image039.jpg

Create the Mappings

To create the mappings of the target datastore:

  1. Select the 0COMP_CODE column from the target datastore.

  2. Drag the IOPK_0FIAP_C201_0COMP_CODE_PK_COMP_CODE column from the 0FIAP_C20 Vendor Balances source data target into the Implementation field of the Properties panel.

  3. Make sure that the Execution On is set to Source. The mapping should look as shown below: Description of image041.jpg follows
    Description of the illustration image041.jpg

  4. Repeat this operation to perform the following simple mappings:

    • W_VENDR_BAL_DS.OCREDITOR = 0FIAP_C20.IOPK_0FIAP_C202_0CREDITOR_PK_VENDOR

    • W_VENDR_BAL_DS.ORC ACCOUNT = 0FIAP_C20.IOPK_0FIAP_C202_0RC_ACCOUNT_PK_GL_ACCOUNT

    • W_VENDR_BAL_DS.TOTAL DEBIT POSTING = 0FIAP_C20.KF_0DEBIT

    • W_VENDR_BAL_DS.TOTAL CREDIT POSTING - 0FIAP_C20.KF_0DEBIT

    • W_VENDR_BAL_DS.CUMULATIVE BALANCE - 0FIAP_C20.KF_0BALANCE

Define the Interface Flow

To define the interface flow:

  1. Select the Flow tab. The Flow diagram looks as shown below;

    Description of image043.jpg follows
    Description of the illustration image043.jpg

  2. Select the source set SS_0 (SAP_BW) that contains Info Cube 0FIAP_C20 data targets. This source set represents the source dataset made up of SAP BW data target.

  3. In the LKM Selection box, select LKM SAP BW to Oracle (SQLLDR) or LKM SAP BW to SQL.

  4. Select the Target + Staging Area box. This represents the target Oracle database, which is also used as the staging area for this interface.

  5. In the LKM Selection, select LKM SAP BW to Oracle (SQLLDR).

  6. In the IKM Selection, select IKM Oracle Incremental Update.

  7. Set the IKM options as follows:

    1. Select No for the FLOW_CONTROL option.

    2. Leave other options as it is. The flow should look as shown below:

      Description of image045.jpg follows
      Description of the illustration image045.jpg

  8. Click OK to save and close your interface.

Info Object Extraction

This section describes how to create an integration interface in ODI in order to load data from a SAP BW source model representing the Info Object 0CREDITOR data target to the W_ACCNT_VENDR_DS Oracle target model.

Create the Interface

  1. Open Designer.

  2. In the Projects tree view, expand the SAP BW Demo Project.

  3. Expand the SAP_BW_DataTargets folder.

  4. Select the Interfaces node.

  5. Right-click and select Insert Interface.

  6. In the Interface Definition tab, enter the interface name: InfoObject_SAP_BW_VendorAcc.W_ACCNT_VENDR_DS.

  7. Select the Diagram tab.

  8. Click OK if the following information window appears. Description of image034.jpg follows
    Description of the illustration image034.jpg

    The interface diagram looks as follows:

    Description of image046.jpg follows
    Description of the illustration image046.jpg

Define the Source and Target Datastores

To define the source and target datastores:

  1. In the Models tree view, select the W_ACCNT_VENDR_DS datastore from the Oracle_Target Model.

  2. Drag this datastore into the Target Datastore panel (right area of the Diagram tab). This panel appears now as shown below: Description of image048.jpg follows
    Description of the illustration image048.jpg

  3. In the Models tree view, select the Info Object's main table 0CREDITOR Vendor datastore from the SAP BW Source model.

  4. Drag this datastore into the Sources diagram panel (left area of the diagram tab).

  5. If a popup window prompting "Do you want to perform Automatic Mapping?" appears, click No.

  6. Perform the same operation for the Info Object text table TXT_0CREDITOR Texts: Char. Vendor datastore from the SAP BW Source model.

    The Sources diagram should look as shown below: Description of image050.jpg follows
    Description of the illustration image050.jpg

Define Joins between Info Object's Main table and Text Table

To create joins between the source datastores of Info Object's main table and text table in the interface:

  1. In the Sources diagram when you drag the Info Object's text table TXT_0CREDITOR Texts: Char. Vendor the join will be automatically created on column vendor from the 0CREDITOR Vendor Info object's main table datastore and the Vendor column of Info Object's text table TXT_0CREDITOR Texts: Char. Vendor. This defines the first join on the vendor Key identifier, as shown below:

    Description of image052.jpg follows
    Description of the illustration image052.jpg

  2. In the Properties panel, select Ordered join and make sure that this join is executed on the Source as shown below:

    Description of image054.jpg follows
    Description of the illustration image054.jpg

  3. Make sure that table number of main table 0CREDITOR is smaller than that of text table TXT_0CREDITOR. In 1:n relationships the parent table has to be the first in a join.

  4. Change join type to left outer join (All rows from 0CREDITOR and matching ones from TXT_0CREDITOR.

Create the Mappings

To create the mappings of the target datastore:

  1. Select the 0COMP_CODE column from the target datastore.

  2. Drag the IOPK_0FIAP_C201_0COMP_CODE_PK_COMP_CODE column from the 0FIAP_C20 Vendor Balances source data target into the Implementation field of the Properties panel.

  3. Make sure that the Execution On is set to Source. The mapping should look as shown below:

    Description of image056.jpg follows
    Description of the illustration image056.jpg

  4. Repeat this operation to perform the following simple mappings:

    • W_ACCNT_VENDR_DS.PCOMPANY = 0CREDITOR.PCOMPANY

    • W_ACCNT_VENDR_DS.PHONE = 0CREDITOR.PHONE

    • W_ACCNT_VENDR_DS.PLANT = 0CREDITOR.PLANT

    • W_ACCNT_VENDR_DS.STREET = 0CREDITOR.STREET

    • W_ACCNT_VENDR_DS.MED_DESC = TXT_0CREDITOR.TXTMD

Define the Interface Flow

To define the interface flow:

  1. Select the Flow tab. The Flow diagram looks as shown below; Description of image058.jpg follows
    Description of the illustration image058.jpg

  2. Select the source set SS_0 (SAP_BW) that contains both the Info Object's main table 0CREDITOR and text table TXT_0CREDITOR. This source set represents the source dataset made up of Info Object's main table & text table joined SAP BW data targets.

  3. In the LKM Selection box, select LKM SAP BW to Oracle (SQLLDR) or LKM SAP BW to SQL.

  4. Select the Target + Staging Area box. This represents the target Oracle database, which is also used as the staging area for this interface.

  5. In the IKM Selection, select IKM Oracle Incremental Update.

  6. Set the IKM options as follows:

    1. Select No for the FLOW_CONTROL option.

    2. Leave other options as it is. The flow should look as shown below: Description of image060.jpg follows
      Description of the illustration image060.jpg

  7. Click OK to save and close your interface.

Info Object Hierarchy Extraction

This section describes how to create an integration interface in ODI in order to load data from a SAP BW source model representing the hierarchy data for Info Object 0GL_ACCOUNT data target to the W_GL_ACCOUNT_HIER_DS Oracle target model.

Create the Interface

  1. Open Designer.

  2. In the Projects tree view, expand the SAP BW Demo Project.

  3. Expand the SAP_BW_DataTargets folder.

  4. Select the Interfaces node.

  5. Right-click and select Insert Interface.

  6. In the Interface Definition tab, enter the interface name: InfoObject_SAP_BW_GlAccount.W_GL_ACCOUNT_HIER_DS.

  7. Select the Diagram tab.

  8. Click OK if the following information window appears. Description of image034.jpg follows
    Description of the illustration image034.jpg

    The interface diagram looks as follows:

    Description of image061.jpg follows
    Description of the illustration image061.jpg

Define the Source and Target Datastores

To define the source and target datastores:

  1. In the Models tree view, select the W_GL_ACCOUNT_DS datastore from the Oracle_Target Model.

  2. Drag this datastore into the Target Datastore panel (right area of the Diagram tab). This panel appears now as shown below:

    Description of image063.jpg follows
    Description of the illustration image063.jpg

  3. In the Models tree view, select the Info Object's hierarchy table HIER_0GL_ACCOUNT datastore from the SAP BW Source model.

  4. Drag this datastore into the Sources diagram panel (left area of the diagram tab).

  5. If a popup window prompting "Do you want to perform Automatic Mapping?" appears, click No.

  6. Add the following filter condition on the RSHIENM source column: HIER_0GL_ACCOUNT.RSHHIENM = 'ACI1'

    Please note that ACI1 is the name of a hierarchy defined for 0GL_ACCOUNT. A list of defined hierarchies is available when opening the hierarchy data store:

    Description of image065.jpg follows
    Description of the illustration image065.jpg

  7. After adding the filter condition the Sources diagram should look as shown below:

    Description of image067.gif follows
    Description of the illustration image067.gif

Create the Mappings

To create the mappings of the target datastore:

  1. Select the HIER_NAME column from the target datastore.

  2. Drag the RSHIENM column from the HIER_0GL_ACCOUNT source data target into the Implementation field of the Properties panel.

  3. Make sure that the Execution On is set to Source. The mapping should look as shown below:

    Description of image068.jpg follows
    Description of the illustration image068.jpg

  4. Repeat this operation to perform the following simple mappings:

    • W_GL_ACCOUNT_HIER_DS.NODEID = HIER_0GL_ACCOUNT.NODEID

    • W_GL_ACCOUNT_HIER_DS.INFOOBJECT_NAME = HIER_0GL_ACCOUNT.IOBJNM

    • W_GL_ACCOUNT_HIER_DS.NODENAME = HIER_0GL_ACCOUNT.NODENAME

    • W_GL_ACCOUNT_HIER_DS.TLEVEL = HIER_0GL_ACCOUNT.TLEVEL

    • W_GL_ACCOUNT_HIER_DS.PARENTID = HIER_0GL_ACCOUNT.PARENTID

    • W_GL_ACCOUNT_HIER_DS.CHILDID = HIER_0GL_ACCOUNT.CHILDID

    • W_GL_ACCOUNT_HIER_DS.NEXTID = HIER_0GL_ACCOUNT.NEXTID

Define the Interface Flow

To define the interface flow:

  1. Select the Flow tab. The Flow diagram looks as shown below:

    Description of image070.jpg follows
    Description of the illustration image070.jpg

  2. Select the source set SS_0 (SAP_BW) that contains both the Info Object's main table 0CREDITOR and text table TXT_0CREDITOR. This source set represents the source dataset made up of Info Object's main table & text table joined SAP BW data targets.

  3. In the LKM Selection box, select LKM SAP BW to Oracle (SQLLDR) or LKM SAP BW to SQL.

  4. Select the Target + Staging Area box. This represents the target Oracle database, which is also used as the staging area for this interface.

  5. In the IKM Selection, select IKM Oracle Incremental Update.

  6. Set the IKM options as follows:

    1. Select No for the FLOW_CONTROL option.

    2. Leave other options as it is.

  7. Click OK to save and close your interface.

Info Object Interval Hierarchy Extraction

This section describes how to extract data from InfoObject and InfoObject hierarchy table containing an interval hierarchy. The data is extracted from InfoObject 0GL_ACCOUNT and InfoObject hierarchy table HIER_0GL_ACCOUNT.

Create the Interface

  1. Open Designer.

  2. In the Projects tree view, expand the SAP BW Demo Project.

  3. Expand the SAP_BW_DataTargets folder.

  4. Select the Interfaces node.

  5. Right-click and select Insert Interface.

  6. In the Interface Definition tab, enter the interface name: InfoObject_InfoObjectHierarchy_join.0GL_ACCOUNT.

  7. Select the Diagram tab.

Define the Source and Target Datastores

To define the source and target datastores:

  1. In the Models tree view, select the TARGET_INTERVAL_HIERARCHY datastore from the Oracle_Target Model W_SD_DELIVERY_CUBE_SOLDTO

  2. Drag this datastore into the Target Datastore panel (right area of the Diagram tab). This panel appears now as shown below:

    Description of HierachyInfoobject_html_m1671e86c.png follows
    Description of the illustration HierachyInfoobject_html_m1671e86c.png

  3. In the Models tree view, select the InfoObject 0GL_ACCOUNT from the SAP BW Source model.

  4. Drag this datastore into the Sources diagram panel (left area of the diagram tab).

  5. If a popup window prompting "Do you want to perform Automatic Mapping?" appears, click No.

  6. Perform the same operation for the InfoObject hierarchy table HIER_0GL_ACCOUNT datastore from the SAP BW Source model. The Sources diagram should look as shown below:

    Description of HierachyInfoobject_html_m1544e8c9.png follows
    Description of the illustration HierachyInfoobject_html_m1544e8c9.png

Define Joins between Sources (Info Object Main table and InfoObject Hierarchy Table)

The join between InfoObject and InfoObject Hierarchy table may automatically be added by ODI. - There may be situations when more than one join shows up. E.g. when the same InfoObject is referenced more than once in different characteristics. Such unwanted extra joins have to be removed.

Apply source-side Filters on RSHIENM and VERSION:

Description of HierachyInfoobject_html_m712df372.png follows
Description of the illustration HierachyInfoobject_html_m712df372.png

Description of HierachyInfoobject_html_m78db267b.png follows
Description of the illustration HierachyInfoobject_html_m78db267b.png

Add joins: all joins must be executed on staging area. Not on source.

The join expression looks like this:

Description of HierachyInfoobject_html_287a41c0.png follows
Description of the illustration HierachyInfoobject_html_287a41c0.png

The final join and filter setup should look like this:

Description of HierachyInfoobject_html_9cc0776.png follows
Description of the illustration HierachyInfoobject_html_9cc0776.png

Create the Mappings

To create the mappings of the target datastore:

  1. Drag the HIER_0GL_ACCOUNT.NODEID column from the HIER_0GL_ACCOUNT Delivery service Source data target into the Implementation field of the Properties panel.

  2. Make sure that the Execution On is set to Source. The mapping should look as shown below:

    Description of HierachyInfoobject_html_24993572.png follows
    Description of the illustration HierachyInfoobject_html_24993572.png

  3. Repeat this operation to perform the following simple mappings:

    Description of HierachyInfoobject_html_317ab3ac.png follows
    Description of the illustration HierachyInfoobject_html_317ab3ac.png

Define the Interface Flow

To define the interface flow:

  1. Select the Flow tab and verify that LKM SAP BW to Oracle (SQLLDR) or LKM SAP BW to SQL has been set for every ODI Source Set. The Flow diagram looks as shown below;

  2. Verify that LKM SAP BW to Oracle (SQLLDR) or LKM SAP BW to SQLhas been set for every ODI Source Set.

    Description of HierachyInfoobject_html_61501ab6.png follows
    Description of the illustration HierachyInfoobject_html_61501ab6.png

  3. Verify that "IKM SQL Control Append" has been set for ODI Target+Staging Area.

    Description of HierachyInfoobject_html_6da09fb8.png follows
    Description of the illustration HierachyInfoobject_html_6da09fb8.png

  4. Execute Interface. Verify that data got populated into the target datastore.

ODS/DSO Extraction

This section describes how to create an integration interface in ODI in order to load data from a SAP BW source model representing the ODS/DSO 0BBP_PO data target to the W_PURCH_DS Oracle target model.

Create the Interface

  1. Open Designer.

  2. In the Projects tree view, expand the SAP BW Demo Project.

  3. Expand the SAP_BW_DataTargets folder.

  4. Select the Interfaces node.

  5. Right-click and select Insert Interface.

  6. In the Interface Definition tab, enter the interface name: ODS_SAP_BW_PurchOrdl.W_PURCH_DS.

  7. Select the Diagram tab.

  8. Click OK if the following information window appears.

    Description of image034.jpg follows
    Description of the illustration image034.jpg

    The interface diagram looks as follows:

    Description of image071.jpg follows
    Description of the illustration image071.jpg

Define the Source and Target Datastores

To define the source and target datastores:

  1. In the Models tree view, select the W_PURCH_DS datastore from the Oracle_Target Model.

  2. Drag this datastore into the Target Datastore panel (right area of the Diagram tab). This panel appears now as shown below: Description of image073.jpg follows
    Description of the illustration image073.jpg

  3. In the Models tree view, select the 0BBP_PO Purchase Order - Single Documents datastore from the SAP BW Source model.

  4. Drag this datastore into the Sources diagram panel (left area of the diagram tab).

  5. If a popup window prompting "Do you want to perform Automatic Mapping?" appears, click No.

  6. The Sources diagram should look as shown below:

    Description of image075.jpg follows
    Description of the illustration image075.jpg

Create the Mappings

To create the mappings of the target datastore:

  1. Select the PO_GUIDE column from the target datastore.

  2. Drag the BBP_POGUID column from the 0BBP_PO Purchase Order - Single Documents source data target into the Implementation field of the Properties panel.

  3. Make sure that the Execution On is set to Source. The mapping should look as shown below: Description of image077.jpg follows
    Description of the illustration image077.jpg

  4. Repeat this operation to perform the following simple mappings:

    • W_PURCH_DS.ORDER_ITEM_GUID = 0BBP_PO.BBP_POITGU

    • W_PURCH_DS.ACC_ASS_GUID = 0BBP_PO.BBP_ACGUID

    • W_PURCH_DS.GUIDE_CONTRCT = 0BBP_PO.BBP_CTGUID

    • W_PURCH_DS.ORDER_QUANT = 0BBP_PO.BBP_ASPQOU

    • W_PURCH_DS.NET_PRICE = 0BBP_PO.BBP_NETPRC

    • W_PURCH_DS.PRICE_UNIT = 0BBP_PO.BBP_PRCUNT

    • W_PURCH_DS.NO_OF_PURCH_ODR = 0BBP_PO.BBP_POCT

Define the Interface Flow

To define the interface flow:

  1. Select the Flow tab. The Flow diagram looks as shown below; Description of image079.jpg follows
    Description of the illustration image079.jpg

  2. Select the source set SS_0 (SAP_BW) that contains ODS/DSO 0BBP_PO data targets. This source set represents the source dataset made up of SAP BW data target.

  3. In the LKM Selection box, select LKM SAP BW to Oracle (SQLLDR) or LKM SAP BW to SQL.

  4. Select the Target + Staging Area box. This represents the target Oracle database, which is also used as the staging area for this interface.

  5. In the IKM Selection, select IKM Oracle Incremental Update.

  6. Set the IKM options as follows:

    1. Select No for the FLOW_CONTROL option.

    2. Leave other options as it is. The flow should look as shown below:

      Description of image081.jpg follows
      Description of the illustration image081.jpg

  7. Click OK to save and close your interface.

Info Cube & Related Info Object Extraction

This section describes how to create an integration interface in ODI in order to load data from a SAP BW source model representing the Info Cube 0FIAP_C20 and Info Object 0CREDITOR data targets join to the W_VENDER_CUBE_OBJ_DS Oracle target model.

Create the Interface

  1. Open Designer.

  2. In the Projects tree view, expand the SAP BW Demo Project.

  3. Expand the SAP_BW_DataTargets folder.

  4. Select the Interfaces node.

  5. Right-click and select Insert Interface.

  6. In the Interface Definition tab, enter the interface name: InfoCube_InfoObject_join.W_VENDER_CUBE_OBJ_DS.

  7. Select the Diagram tab.

  8. Click OK if the following information window appears.

    Description of image034.jpg follows
    Description of the illustration image034.jpg

    The interface diagram looks as follows:

    Description of image082.jpg follows
    Description of the illustration image082.jpg

Define the Source and Target Datastores

To define the source and target datastores:

  1. In the Models tree view, select the W_VENDER_CUBE_OBJ_DS datastore from the Oracle_Target Model.

  2. Drag this datastore into the Target Datastore panel (right area of the Diagram tab). This panel appears now as shown below:

    Description of image084.jpg follows
    Description of the illustration image084.jpg

  3. In the Models tree view, select the Info Cube 0FIAP_C20 Vendor Balances datastore from the SAP BW Source model.

  4. Drag this datastore into the Sources diagram panel (left area of the diagram tab).

  5. If a popup window prompting "Do you want to perform Automatic Mapping?" appears, click No.

  6. Perform the same operation for the Info Object's main table 0CREDITOR Vendor and text table TXT_0CREDITOR Texts: Char. Vendor datastore from the SAP BW Source model.

The Sources diagram should look as shown below:

Description of image086.jpg follows
Description of the illustration image086.jpg

Define Joins between Sources (Info Cube & Object)

To create joins between the source datastores of an interface:

  1. In the Sources diagram drag the IOPK_0FIAP_C202_0CREDITOR_PK_VENDOR column from the Info Cube 0FIAP_C20 Vendor Balances datastore onto the VENDOR column in the Info Object's main table 0CREDITOR Vendor. This defines the first join on the Vendor Key identifier, as shown below:

    Description of image088.jpg follows
    Description of the illustration image088.jpg

  2. In the Properties panel, you can see the join clause: 0FIAP_C20.IOPK_0FIAP_C202_0CREDITOR_PK_VENDOR=0CREDITOR.VENDOR

  3. In the Properties panel, select Ordered join and make sure that this join is executed on the Source as shown below:

    Description of image090.jpg follows
    Description of the illustration image090.jpg

  4. Make sure that table number of 0FIAP_C20 is smaller than that of 0CREDITOR. In 1:n relationships the parent data target has to be the first in a join.

  5. Change join between 0FIAP_C20 and 0CREDITOR to left outer join (All rows from 0FIAP_C20 and matching ones from 0CREDITOR.

  6. Change join between 0CREDITOR and TXT_0CREDITOR to left outer join (All rows from 0CREDITOR and matching ones from TXT_0CREDITOR.

Create the Mappings

To create the mappings of the target datastore:

  1. Select the COMP_CODE column from the target datastore.

  2. Drag the IOPK_0FIAP_C201_0COMP_CODE_PK_COMP_CODE column from the 0FIAP_C20 Vendor Balances source data target into the Implementation field of the Properties panel.

  3. Make sure that the Execution On is set to Source. The mapping should look as shown below:

    Description of image091.jpg follows
    Description of the illustration image091.jpg

  4. Repeat this operation to perform the following simple mappings:

    • W_VENDER_CUBE_OBJ_DS.VENDOR = 0FIAP_C20.IOPK_0FIAP_C202_0CREDITOR_PK_VENDOR

    • W_VENDER_CUBE_OBJ_DS.GL_ACCOUNT = 0FIAP_C20.IOPK_0FIAP_C202_0RC_ACCOUNT_PK_GL_ACCOUNT

    • W_VENDER_CUBE_OBJ_DS.SALES_FOR_PERIOD = 0FIAP_C20.KF_0SALES

    • W_VENDER_CUBE_OBJ_DS.CUM_BALANCE = 0FIAP_C20.KF_0BALANCE

    • W_VENDER_CUBE_OBJ_DS.PLANT = 0CREDITOR.PLANT

    • W_VENDER_CUBE_OBJ_DS.MEDIUM_DESC = TXT_0CREDITOR.TXTMD

Define the Interface Flow

To define the interface flow:

  1. Select the Flow tab. The Flow diagram looks as shown below;

    Description of image093.jpg follows
    Description of the illustration image093.jpg

  2. Select the source set SS_0 (SAP_BW) that contains both the Info Cube and Info Object tables. This source set represents the source dataset made up of two joined SAP BW data targets.

  3. In the LKM Selection box, select LKM SAP BW to Oracle (SQLLDR) or LKM SAP BW to SQL.

  4. Select the Target + Staging Area box. This represents the target Oracle database, which is also used as the staging area for this interface.

  5. In the IKM Selection, select IKM Oracle Incremental Update.

  6. Set the IKM options as follows:

    1. Select No for the FLOW_CONTROL option.

    2. Leave other options as it is. The flow should look as shown below:

      Description of image095.jpg follows
      Description of the illustration image095.jpg

  7. Click OK to save and close your interface.

Info Cube Extraction with Navigational Attributes

This section describes how to create an integration interface in ODI in order to load data from a SAP InfoCube and its navigation attributes. The data is extracted from Info Cube 0SD_C04 and InfoObject 0SOLD_TO.

Create the Interface

  1. Open Designer.

  2. In the Projects tree view, expand the SAP BW Demo Project.

  3. Expand the SAP_BW_DataTargets folder.

  4. Select the Interfaces node.

  5. Right-click and select Insert Interface.

  6. In the Interface Definition tab, enter the interface name: InfoCube_InfoObject_Nav_join.0SD_C04.

  7. Select the Diagram tab.

Define the Source and Target Datastores

To define the source and target datastores:

  1. In the Models tree view, select the 0SD_C04 datastore from the Oracle Target Model W_SD_DELIVERY_CUBE_SOLDTO.

  2. Drag this datastore into the Target Datastore panel (right area of the Diagram tab).The following panel appears:

    Description of image097.jpg follows
    Description of the illustration image097.jpg

  3. In the Models tree view, select the Info Cube 0SD_C04 Delivery service datastore from the SAP BW Source model.

  4. Drag this datastore into the Sources diagram panel (left area of the diagram tab).

  5. If a popup window prompting "Do you want to perform Automatic Mapping?" appears, click No.

  6. Perform the same operation for the Info Object main table 0SOLD_TO Customer datastore from the SAP BW Source model.

    The Sources diagram should appear as follows:

    Description of image099.jpg follows
    Description of the illustration image099.jpg

Define Joins Between Sources (Info Cube & Info Object)

The join between InfoCube and InfoObject will automatically be added by ODI. - There may be situations when more than one join shows up. E.g. when the same InfoObject is referenced more than once in different characteristics. Such unwanted extra joins have to be removed.

The final join setup with its join expression should look like this:

Description of image101.jpg follows
Description of the illustration image101.jpg

All InfoObject joins for retrieving navigation attributes must be executed on *staging* area. Not on source:

Description of image103.jpg follows
Description of the illustration image103.jpg

Create the Mappings

To create the mappings of the target datastore:

  1. Drag the 0SD_C04.IOPK_0SD_C041_0SOLD_TO_PK_CUSTOMER column from the 0SD_C041 Delivery service Source data target into the Implementation field of the Properties panel.

  2. Make sure that the Execution On is set to Source. The mapping should look as shown below:

    Description of image105.jpg follows
    Description of the illustration image105.jpg

  3. Repeat this operation to perform the following simple mappings:

    Description of image107.jpg follows
    Description of the illustration image107.jpg

Define the Interface Flow

To define the interface flow:

  1. Select the Flow tab and verify that "LKM SAP BW to Oracle (SQLLDR)" has been set for every ODI Source Set. The Flow diagramappears as follows:

    Description of image109.jpg follows
    Description of the illustration image109.jpg

  2. Verify thatLKM SAP BW to Oracle (SQLLDR) or LKM SAP BW to SQL has been set for every ODI Source Set.

    Note:

    This interface will generate several SAP ABAP extraction programs. One for retrieving any display attributes and key figures from the InfoCube and one for each InfoObject containing navigation attributes.

  3. Verify that "IKM SQL Control Append" has been set for ODI Target+Staging Area.

    Description of image111.jpg follows
    Description of the illustration image111.jpg

  4. Execute Interface. Verify that Info Cube and Navigational attributes got populated into the target datastore.

Open Hub Extraction

This section describes how to create an integration interface in ODI in order to load data from a SAP BW source model representing the Open Hub destination ZOHD_SALE (/BIC/OHZOHD_SALE) to the W_OPENHUB_SALES_DS Oracle target model.

Create the Interface

  1. Open Designer.

  2. In the Projects tree view, expand the SAP BW Demo Project.

  3. Expand the SAP_BW_DataTargets folder.

  4. Select the Interfaces node.

  5. Right-click and select Insert Interface.

  6. In the Interface Definition tab, enter the interface name: OpenHub_Sales.W_OPENHUB_SALES_DS.

  7. Select the Diagram tab.

  8. Click OK if the following information window appears.

    Description of image034.jpg follows
    Description of the illustration image034.jpg

    The interface diagram looks as follows:

    Description of image112.jpg follows
    Description of the illustration image112.jpg

Define the Source and Target Datastores

To define the source and target datastores:

  1. In the Models tree view, select the W_OPENHUB_SALES_DS datastore from the Oracle_Target Model.

  2. Drag this datastore into the Target Datastore panel (right area of the Diagram tab). This panel appears now as shown below:

    Description of image114.jpg follows
    Description of the illustration image114.jpg

  3. In the Models tree view, select the ZOHD_SALE datastore from the SAP BW Source model.

  4. Drag this datastore into the Sources diagram panel (left area of the diagram tab).

  5. If a popup window prompting "Do you want to perform Automatic Mapping?" appears, click No.

  6. The Sources diagram should look as shown below:

    Description of image116.jpg follows
    Description of the illustration image116.jpg

Create the Mappings

To create the mappings of the target datastore:

  1. Select the OHREQUID column from the target datastore.

  2. Drag the OHREQUID column from the ZOHD_SALE source data target into the Implementation field of the Properties panel.

  3. Make sure that the Execution On is set to Source. The mapping should look as shown below:

    Description of image118.jpg follows
    Description of the illustration image118.jpg

  4. Repeat this operation to perform the following simple mappings:

    • W_OPENHUB_SALES_DS.DATA_PACKAGE = ZOHD_SALE.DATAPAKID

    • W_OPENHUB_SALES_DS.DATA_RECORD = ZOHD_SALE.RECORD

    • W_OPENHUB_SALES_DS.CUSTOMER = ZOHD_SALE.D_CUSTOMER

    • W_OPENHUB_SALES_DS.MATERIAL = ZOHD_SALE.D_MATERIAL

    • W_OPENHUB_SALES_DS.MATERIAL_PRICE = ZOHD_SALE.PRICE_MAT

    • W_OPENHUB_SALES_DS.CURRENCY = ZOHD_SALE.CURRENCY

    • W_OPENHUB_SALES_DS.QUANTITY = ZOHD_SALE./BIC/ZQTY

    • W_OPENHUB_SALES_DS.TOTAL_REVENUE = ZOHD_SALE./BIC/ZREV

Define the Interface Flow

To define the interface flow:

  1. Select the Flow tab. The Flow diagram looks as shown below;

    Description of image120.jpg follows
    Description of the illustration image120.jpg

  2. Select the source set SS_0 (SAP_BW) that contains Open Hub Destination Table ZOHD_SALE. This source set represents the source dataset made up of SAP BW Open Hub table.

  3. In the LKM Selection box, select LKM SAP BW to Oracle (SQLLDR) or LKM SAP BW to SQL.

  4. Select the Target + Staging Area box. This represents the target Oracle database, which is also used as the staging area for this interface.

  5. In the IKM Selection, select IKM Oracle Incremental Update.

  6. Set the IKM options as follows:

    1. Select No for the FLOW_CONTROL option.

    2. Leave other options as it is. The flow should look as shown below:

      Description of image122.jpg follows
      Description of the illustration image122.jpg

  7. Click OK to save and close your interface.

Running the Integration Interface

This section contains the following topics:

Running the Interface

To run the integration interface:

  1. In the Projects tree view, expand the SAP Demo Project

  2. Expand the SAP_BW_DataTargets folder.

  3. Expand the Interfaces node.

  4. Select the InfoCube_SAP_BW_VendorBal.W_VENDR_BAL_DS interface.

  5. Right-click and select Execute.

  6. In the Execution window that appears, click OK.

  7. Click OK in the Session Started window.

What happens when you run the interface?

First, Oracle Data Integrator creates an ABAP program that does the following:

  1. It performs the extraction of the data on the SAP BW system. In this example, the extraction will be done on Info Cube source data targets and then returns the resultset in an extraction file.

  2. It uploads the extraction file into a FTP data server. This FTP host is specified via the File Logical Schema called File Server for SAP ABAP. This Logical Schema is mapped to a Physical Schema and therefore a data server in the given context. This data server contains the connection information for the FTP host.

Then, the ABAP code is uploaded using the OdiSapAbapExecute tool. The ABAP program is pushed into the SAP Function group given in the SAP_FUNCTION_GROUP_NAME LKM option. This phase can be suppressed by setting the UPLOAD_ABAP_CODE LKM option to No.

The ABAP code is executed also by the OdiSapAbapExecute tool. At the end of the ABAP code execution, the extraction file is available in the FTP host.

The Oracle Data Integrator agent is able to download this extraction file from the FTP host, or directly access it, depending on the FTP_TRANSFER_METHOD specified in the IKM option.

Finally, when the agent accesses the extraction file, it uses SQL*Loader or a JDBC Connection to load this file into the Oracle/non-Oracle staging area. The rest of the integration process takes place within the Oracle/non-Oracle engine.

Review the Interface Execution

To review the interface execution:

  1. Connect to Operator.

  2. In the Operator, select the Session List tree view.

  3. Expand the All Executions node in this tree view.

  4. The latest session is the first entry of this list and appears as shown below.

    Description of image124.jpg follows
    Description of the illustration image124.jpg

  5. Double click the InfoCube_SAP_BW_VendorBal.W_VENDR_BAL_DS node to see the session details and the number of lines processed.

Review the Resulting Data

To review the resulting data:

  1. In the Designer, in the Models tree view, expand the Oracle Target Model.

  2. Select the W_VENDR_BAL_DS datastore, right-click and select Data to view the data integrated into the target Oracle table.

  3. Repeat the same steps for remaining interfaces:

    • InfoObject_SAP_BW_VendorAcc.W_ACCNT_VENDR_DS

    • ODS_SAP_BW_PurchOrdl.W_PURCH_DS

    • InfoCube_InfoObject_join.W_VENDER_CUBE_OBJ_DS

    • OpenHub_Sales.W_OPENHUB_SALES_DS