6 Creating and Running the Mapping
This chapter includes the following sections:
Creating the Mapping
Now that the ODI source and target data models have been created, it is possible to create the ODI mappings to extract data from the following SAP BW data targets:
-
The Info Cube
0FIAP_C20
data target toW_VENDR_BAL_DS
Oracle table. -
The Info Object
0CREDITOR
data target toW_ACCNT_VENDR_DS
Oracle table. -
The Hierarchy data from Info Object
0GL_ACCOUNT
toW_GL_ACCOUNT_HIER_DS
Oracle table. -
The Interval Hierarchy data from InfoObject
0GL_ACCOUNT
toTARGET_INTERVAL_HIERARCHY
Oracle table. -
The ODS/DSO
0BBP_PO
data target toW_PURCH_DS
Oracle table. -
The Info Cube
0FIAP_C20
and Info Object0CREDITOR
data targets join toW_VENDER_CUBE_OBJ_DS
Oracle table. -
The InfoCube and Navigation Attribute data from
IC 0SD_C04 / IO 0SOLD_TO
toTARGET_NAVIGATIONAL_ATTRIBUTES
Oracle table. -
The Open Hub destination
ZOHD_SALE (/BIC/OHZOHD_SALE)
toW_OPENHUB_SALES_DS
Oracle table. -
Delta Extraction can be done on any above said data targets except Info Object. How to perform delta extraction is described in Creating a Package for Delta Extraction.
To create the mapping, perform the following steps:
Info Cube Extraction
This section describes how to create a mapping 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 Mapping
To create the mapping:
-
Open Designer.
-
In the Projects tree view, expand the SAP BW Demo Project.
-
Expand the
SAP_BW_DataTargets
folder. -
Select the Mappings node.
-
Right-click and select New Mapping.
-
In the Mapping Definition tab, enter the mapping name:
InfoCube_SAP_BW_VendorBal.W_VENDR_BAL_DS
. -
From the File menu, click Save to save your mapping.
Define the Source and Target Datastores
To define the source and target datastores:
-
In the Models tree view, select the
W_VENDR_BAL_DS
datastore from theOracle_Target
Model. -
Drag this datastore into the Target Datastore area of the Logical diagram (right area of the Logical diagram).
-
In the Models tree view, select the
0FIAP_C20 Vendor Balances
datastore from theSAP BW Source
model. -
Drag this datastore into the Sources area of the Logical diagram (left area of the Logical diagram).
Create the Mappings
To create the mappings of the target datastore:
-
Select the
0COMP_CODE
attribute from the target datastore. -
Drag the
IOPK_0FIAP_C201_0COMP_CODE_PK_COMP_CODE
attribute from the0FIAP_C20 Vendor Balances
source datastore onto the COMP_CODE attribute of the target datastore. -
Make sure that the Execute on Hint is set to Source. The mapping should look as shown below:
-
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 Mapping Flow
To define the mapping flow:
-
Select the Physical diagram. The Flow diagram looks as shown below:
-
In the target group, select the access point for the source group that contains Info Cube
0FIAP_C20
data target. This source group represents the source dataset made up ofSAP BW
data target. -
In the Property panel, under Loading Knowledge Module, select
LKM SAP BW to Oracle (SQLLDR)
orLKM SAP BW to SQL
from the Loading Knowledge Module drop-down list. -
In the target group, select the target Oracle database, which is also used as the staging area for this mapping.
-
In the Property panel, under Integration Knowledge Module, select
IKM Oracle Incremental Update
from the Integration Knowledge Module drop-down list. -
Set the IKM options as follows:
-
Select No for the FLOW_CONTROL option.
-
Leave other options as they are.
-
-
From the File menu, click Save and close your mapping.
Info Object Extraction
This section describes how to create a mapping 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 Mapping
-
Open Designer.
-
In the Projects tree view, expand the
SAP BW Demo
Project. -
Expand the
SAP_BW_DataTargets
folder. -
Select the Mappings node.
-
Right-click and select New Mapping.
-
In the Mapping Definition tab, enter the mapping name:
InfoObject_SAP_BW_VendorAcc.W_ACCNT_VENDR_DS
. -
From the File menu, click Save to save your mapping.
Define the Source and Target Datastores
To define the source and target datastores:
-
In the Models tree view, select the
W_ACCNT_VENDR_DS
datastore from theOracle_Target
Model. -
Drag this datastore into the Target Datastore area of the Logical diagram (right area of the Logical diagram).
-
In the Models tree view, select the Info Object's main table
0CREDITOR Vendor
datastore from theSAP BW
Source model. -
Drag this datastore into the Sources area of the Logical diagram (left area of the Logical diagram).
-
Perform the same operation for the Info Object text table
TXT_0CREDITOR Texts: Char.Vendor
datastore from theSAP BW
Source model.
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 mapping:
-
In the Logical diagram when you drag the Info Object's text table
TXT_0CREDITOR Texts: Char.Vendor
the join will be automatically created on attribute vendor from the0CREDITOR Vendor
Info object's main table datastore and theVendor
attribute of Info Object's text tableTXT_0CREDITOR Texts: Char. Vendor
. This defines the first join on the vendor Key identifier, as shown below: -
In the Properties panel, select
Join Order
and make sure that the Execute on Hint option is set to Source as shown below: -
Make sure that table number of main table
0CREDITOR
is smaller than that of text tableTXT_0CREDITOR
. In 1:n relationships the parent table has to be the first in a join. -
Change join type to left outer join (All rows from
0CREDITOR
and matching ones fromTXT_0CREDITOR
.
Create the Mappings
To create the mappings of the target datastore:
-
Select the
VENDOR
attribute from the target datastore. -
Drag the
VENDOR
attribute from theOCREDITOR
source data target onto the VENDOR attribute of the target datastore. -
Make sure that the Execute on Hint option is set to Source. The mapping should look as shown below:
-
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 Mapping Flow
To define the mapping flow:
-
Select the Physical diagram. The Flow diagram looks as shown below:
-
In the target group, select the access point for the source group that contains both the Info Object's main table
0CREDITOR
and text tableTXT_0CREDITOR
. This source group represents the source dataset made up of Info Object's main table & text table joinedSAP BW
data targets. -
In the Property panel, under Loading Knowledge Module, select
LKM SAP BW to Oracle (SQLLDR)
orLKM SAP BW to SQL
from the Loading Knowledge Module drop-down list. -
In the target group, select the target Oracle database, which is also used as the staging area for this mapping.
-
In the Property panel, under the Integration Knowledge Module, select
IKM Oracle Incremental Update
from the Integration Knowledge Module drop-down list. -
Set the IKM options as follows:
-
Select No for the FLOW_CONTROL option.
-
Leave other options as they are.
-
-
From the File menu, click Save and close your mapping.
Info Object Hierarchy Extraction
This section describes how to create a mapping 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 Mapping
-
Open Designer.
-
In the Projects tree view, expand the
SAP BW Demo
Project. -
Expand the
SAP_BW_DataTargets
folder. -
Select the Mappings node.
-
Right-click and select New Mapping.
-
In the Mapping Definition tab, enter the mapping name:
InfoObject_SAP_BW_GlAccount.W_GL_ACCOUNT_HIER_DS
. -
From the File menu, click Save to save your mapping.
Define the Source and Target Datastores
To define the source and target datastores:
-
In the Models tree view, select the
W_GL_ACCOUNT_DS
datastore from theOracle_Target
Model. -
Drag this datastore into the target area of the Logical diagram (right area of the Logical diagram).
-
In the Models tree view, select the Info Object's hierarchy table
HIER_0GL_ACCOUNT
datastore from theSAP BW
Source model. -
Drag this datastore into the sources area of the Logical diagram (left area of the Logical diagram).
-
Add the following filter condition on the RSHIENM source attribute:
HIER_0GL_ACCOUNT.RSHHIENM = 'ACI1'
Please note that
ACI1
is the name of a hierarchy defined for0GL_ACCOUNT
. A list of defined hierarchies is available under Description section of the Definition tab when opening the hierarchy data store. -
After adding the filter condition the Sources diagram should look as shown below:
Create the Mappings
To create the mappings of the target datastore:
-
Select the
HIER_NAME
attribute from the target datastore. -
Drag the
RSHIENM
attribute from theHIER_0GL_ACCOUNT
source data target onto the HIER_NAME attribute of the target datastore. -
Make sure that the Execute on Hint option is set to Source. The mapping should look as shown below:
-
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 Mapping Flow
To define the mapping flow:
-
Select the Physical diagram. The Flow diagram looks as shown below:
-
In the target group, select the access point for the source group that contains both the Info Object's main table
0CREDITOR
and text tableTXT_0CREDITOR
. This source group represents the source dataset made up of Info Object's main table & text table joinedSAP BW
data targets. -
In the Property panel, under Loading Knowledge Module, select
LKM SAP BW to Oracle (SQLLDR)
orLKM SAP BW to SQL
from the Loading Knowledge Module drop-down list. -
In the target group, select the target Oracle database, which is also used as the staging area for this mapping.
-
In the Property panel, under Integration Knowledge Module, select
IKM Oracle Incremental Update
from the Integration Knowledge Module drop-down list. -
Set the IKM options as follows:
-
Select No for the FLOW_CONTROL option.
-
Leave other options as they are.
-
-
From the File menu, click Save and close your mapping.
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 Mapping
-
Open Designer.
-
In the Projects tree view, expand the
SAP BW Demo
Project. -
Expand the
SAP_BW_DataTargets
folder. -
Select the Mappings node.
-
Right-click and select New Mapping.
-
In the Mapping Definition tab, enter the mapping name:
InfoObject_InfoObjectHierarchy_join.0GL_ACCOUNT
. -
From the File menu, click Save to save your mapping.
Define the Source and Target Datastores
To define the source and target datastores:
-
In the Models tree view, select the
TARGET_INTERVAL_HIERARCHY
datastore from theOracle_Target Model W_SD_DELIVERY_CUBE_SOLDTO
. -
Drag this datastore into the target area of the Logical diagram (right area of the Logical diagram).
-
In the Models tree view, select the InfoObject
0GL_ACCOUNT
from the SAP BW source model. -
Drag this datastore into the sources area of the Logical diagram (left area of the Logical diagram).
-
Perform the same operation for the InfoObject hierarchy table
HIER_0GL_ACCOUNT
datastore from the SAP BW Source model.
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:
Add joins: all joins must be executed on staging area. Not on source.
The join expression looks like this:
The final join and filter setup should look like this:
Create the Mappings
To create the mappings of the target datastore:
-
Drag the
NODEID
attribute from the HIER_0GL_ACCOUNT source data target onto the NODEID attribute of the TARGET_INTERVAL_HIERARCHY target datastore. -
Make sure that the Execute on Hint option is set to Source. The mapping should look as shown below:
-
Repeat this operation to perform the following simple mappings:
-
TARGET_INTERVAL_HIERARCHY.RSHIENM = HIER_OGL_ACCOUNT.RSHIENM
-
TARGET_INTERVAL_HIERARCHY.
PARENTID = HIER_OGL_ACCOUNT.PARENTID
-
TARGET_INTERVAL_HIERARCHY.
INTERVL = HIER_OGL_ACCOUNT.INTERVAL
-
TARGET_INTERVAL_HIERARCHY.
LEAFFROM = HIER_OGL_ACCOUNT.LEAFFROM
-
TARGET_INTERVAL_HIERARCHY.
LEAFTO = HIER_OGL_ACCOUNT.LEAFTO
-
TARGET_INTERVAL_HIERARCHY.
VERSION = HIER_OGL_ACCOUNT.VERSION
-
TARGET_INTERVAL_HIERARCHY.
CHRT_ACCTS = OGL_ACCOUNT.CHRT_ACCTS
-
TARGET_INTERVAL_HIERARCHY.
GL_ACCOUNT = OGL_ACCOUNT.GL_ACCOUNT
-
TARGET_INTERVAL_HIERARCHY.
BAL_FLAG = OGL_ACCOUNT.BAL_FLAG
-
TARGET_INTERVAL_HIERARCHY.
GLACCEXT = OGL_ACCOUNT.GLACCEXT
-
TARGET_INTERVAL_HIERARCHY.
INCST_FLAG = OGL_ACCOUNT.INCST_FLAG
-
Define the Mapping Flow
To define the mapping flow:
-
Select the Physical diagram. The Flow diagram looks as shown below:
-
In the target group, select the access point for the ODI Source Group.
-
In the Property panel, under Loading Knowledge Module, verify that
LKM SAP BW to Oracle (SQLLDR)
orLKM SAP BW to SQL
has been set. -
In the target group, select the target Oracle datastore, which is also used as the staging area for this mapping.
-
In the Property panel, under Integration Knowledge Module, verify that IKM SQL Control Append has been set.
-
Execute the Mapping. Verify that data got populated into the target datastore.
ODS/DSO Extraction
This section describes how to create a mapping 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 Mapping
-
Open Designer.
-
In the Projects tree view, expand the
SAP BW Demo
Project. -
Expand the
SAP_BW_DataTargets
folder. -
Select the Mappings node.
-
Right-click and select New Mapping.
-
In the Mapping Definition tab, enter the mapping name:
ODS_SAP_BW_PurchOrdl.W_PURCH_DS
. -
From the File menu, click Save to save your mapping.
Define the Source and Target Datastores
To define the source and target datastores:
-
In the Models tree view, select the
W_PURCH_DS
datastore from theOracle_Target
Model. -
Drag this datastore into the targets area of the Logical diagram (right area of the Logical diagram).
-
In the Models tree view, select the
0BBP_PO Purchase Order - Single Documents
datastore from theSAP BW
Source model. -
Drag this datastore into the sources area of the Logical diagram (left area of the Logical diagram).
Create the Mappings
To create the mappings of the target datastore:
-
Select the
PO_GUIDE
attribute from the target datastore. -
Drag the
BBP_POGUID
attribute from the0BBP_PO Purchase Order - Single Documents
source data target onto the PO_GUIDE attribute of the target datastore. -
Make sure that the Execute on Hint option is set to Source. The mapping should look as shown below:
-
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 Mapping Flow
To define the mapping flow:
-
Select the Physical diagram. The Flow diagram looks as shown below:
-
In the target group, select the access point for the source group that contains
ODS/DSO 0BBP_PO
data targets. This source group represents the source dataset made up ofSAP BW
data target. -
In the Property panel, under Loading Knowledge Module, select
LKM SAP BW to Oracle (SQLLDR)
orLKM SAP BW to SQL
from the Loading Knowledge Module drop-down list. -
In the target group, select the target Oracle database, which is also used as the staging area for this mapping.
-
In the Property panel, under Integration Knowledge Module, select
IKM Oracle Incremental Update
from the Integration Knowledge Module drop-down list. -
Set the IKM options as follows:
-
Select No for the FLOW_CONTROL option.
-
Leave other options as they are.
-
-
From the File menu, click Save and close your mapping.
Info Cube & Related Info Object Extraction
This section describes how to create a mapping 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 Mapping
-
Open Designer.
-
In the Projects tree view, expand the
SAP BW Demo
Project. -
Expand the
SAP_BW_DataTargets
folder. -
Select the Mappings node.
-
Right-click and select New Mapping.
-
In the Mapping Definition tab, enter the mapping name:
InfoCube_InfoObject_join.W_VENDER_CUBE_OBJ_DS
. -
From the File menu, click Save to save your mapping.
Define the Source and Target Datastores
To define the source and target datastores:
-
In the Models tree view, select the
W_VENDER_CUBE_OBJ_DS
datastore from theOracle_Target
Model. -
Drag this datastore into the target area of the Logical diagram (right area of the Logical diagram).
-
In the Models tree view, select the Info Cube
0FIAP_C20 Vendor Balances
datastore from theSAP BW
Source model. -
Drag this datastore into the sources area of the Logical diagram (left area of the Logical diagram).
-
Perform the same operation for the Info Object's main table
0CREDITOR Vendor
and text tableTXT_0CREDITOR Texts: Char. Vendor
datastore from theSAP BW Source
model.
Define Joins between Sources (Info Cube & Object)
To create joins between the source datastores of a mapping:
-
In the Sources area of the Logical diagram drag the
IOPK_0FIAP_C202_0CREDITOR_PK_VENDOR
attribute from the Info Cube0FIAP_C20 Vendor Balances
datastore onto theVENDOR
attribute in the Info Object's main table0CREDITOR Vendor
. This defines the first join on the Vendor Key identifier, as shown below: -
In the Properties panel, you can see the join clause:
0FIAP_C20.IOPK_0FIAP_C202_0CREDITOR_PK_VENDOR=0CREDITOR.VENDOR
-
In the Properties panel, select Generate ANSI Syntax and Join Order options, and make sure that Execute on Hint option is set to Source as shown below:
-
Make sure that table number of
0FIAP_C20
is smaller than that of0CREDITOR
. In 1:n relationships the parent data target has to be the first in a join. -
Change join between
0FIAP_C20
and0CREDITOR
to left outer join (All rows from0FIAP_C20
and matching ones from0CREDITOR
. -
Change join between
0CREDITOR
andTXT_0CREDITOR
to left outer join (All rows from0CREDITOR
and matching ones fromTXT_0CREDITOR
.
Create the Mappings
To create the mappings of the target datastore:
-
Select the
COMP_CODE
attribute from the target datastore. -
Drag the
IOPK_0FIAP_C201_0COMP_CODE_PK_COMP_CODE
attribute from the0FIAP_C20 Vendor Balances
source data target onto theCOMP_CODE
attribute of the target datastore. -
Make sure that the Execute on Hint option is set to Source. The mapping should look as shown below:
-
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 Mapping Flow
To define the mapping flow:
-
Select the Physical diagram. The Flow diagram looks as shown below:
-
In the target group, select the access point for the source group that contains both the
Info Cube
and Info Object tables. This source group represents the source dataset made up of two joinedSAP BW
data targets. -
In the Property panel, under Loading Knowledge Module, select
LKM SAP BW to Oracle (SQLLDR)
orLKM SAP BW to SQL
from the Loading Knowledge Module drop-down list. -
In the Property panel, select the target Oracle database, which is also used as the staging area for this mapping.
-
In the Property panel, under Integration Knowledge Module, select
IKM Oracle Incremental Update
from the Integration Knowledge Module drop-down list. -
Set the IKM options as follows:
-
Select No for the FLOW_CONTROL option.
-
Leave other options as they are.
-
-
From the File menu, click Save and close your mapping.
Info Cube Extraction with Navigational Attributes
This section describes how to create a mapping 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 Mapping
-
Open Designer.
-
In the Projects tree view, expand the
SAP BW Demo
Project. -
Expand the
SAP_BW_DataTargets
folder. -
Select the Mappings node.
-
Right-click and select New Mapping.
-
In the Mapping Definition tab, enter the mapping name:
InfoCube_InfoObject_Nav_join.0SD_C04
. -
From the File menu, click Save to save your mapping.
Define the Source and Target Datastores
To define the source and target datastores:
-
In the Models tree view, select the
0SD_C04
datastore from the Oracle Target ModelTARGET_NAVIGATIONAL ATTRIBUTES
. -
Drag this datastore into the targets area of the Logical diagram (right area of the Logical diagram).
-
In the Models tree view, select the Info Cube
0SD_C04 Delivery service datastore
from theSAP BW
Source model. -
Drag this datastore into the sources area of the Logical diagram (left area of the Logical diagram).
-
Perform the same operation for the Info Object main table
0SOLD_TO Customer datastore
from theSAP BW
Source model.
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:
All InfoObject joins for retrieving navigation attributes must be executed on *staging* area. Not on source:
Create the Mappings
To create the mappings of the target datastore:
-
Drag the
0SD_C04.IOPK_0SD_C041_0SOLD_TO_PK_CUSTOMER
attribute from the0SD_C041 Delivery service
Source data target onto the IOPK_0SD_C041_0CUST_GROUP_PK_CUST_GROUP attribute of the target datastore. -
Make sure that the Execute on Hint option is set to Source. The mapping should look as shown below:
-
Repeat this operation to perform the following simple mappings:
Define the Mapping Flow
To define the mapping flow:
-
Select the Physical diagram. The Flow diagram appears as follows:
-
In the target group, select the access points for the ODI Source Datastores.
-
In the Property panel, verify that
LKM SAP BW to Oracle (SQLLDR)
orLKM SAP BW to SQL
has been set for every ODI Source Datastore.Note:
This mapping 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.
-
In the target group, select the target Oracle database, which is also used as the staging area for this mapping.
-
In the Property panel, under Integration Knowledge Module, verify that IKM SQL Control Append has been set for the target datastore.
-
Execute the Mapping. Verify that Info Cube and Navigational attributes got populated into the target datastore.
Open Hub Extraction
This section describes how to create a mapping 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 Mapping
-
Open Designer.
-
In the Projects tree view, expand the
SAP BW Demo
Project. -
Expand the
SAP_BW_DataTargets
folder. -
Select the Mappings node.
-
Right-click and select New Mapping.
-
In the Mapping Definition tab, enter the mapping name:
OpenHub_Sales.W_OPENHUB_SALES_DS
. -
From the File menu, click Save to save your mapping.
Define the Source and Target Datastores
To define the source and target datastores:
-
In the Models tree view, select the
W_OPENHUB_SALES_DS
datastore from theOracle_Target
Model. -
Drag this datastore into the targets area of the Logical diagram (right area of the Logical diagram).
-
In the Models tree view, select the
ZOHD_SALE
datastore from theSAP BW Source
model. -
Drag this datastore into the sources area of the Logical diagram (left area of the Logical diagram).
Create the Mappings
To create the mappings of the target datastore:
-
Select the
OHREQUID
attribute from the target datastore. -
Drag the
OHREQUID
attribute from theZOHD_SALE
source data target onto theOHREQUID
attribute of the target datastore. -
Make sure that the Execute on Hint option is set to Source. The mapping should look as shown below:
-
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 Mapping Flow
To define the mapping flow:
-
Select the Physical diagram. The Flow diagram looks as shown below:
-
In the target group, select the access point for the source group that contains Open Hub Destination Table
ZOHD_SALE
. This source group represents the source dataset made up ofSAP BW
Open Hub table. -
In the Property panel, under Loading Knowledge Module, select
LKM SAP BW to Oracle (SQLLDR)
orLKM SAP BW to SQL
from the Loading Knowledge Module drop-down list. -
In the target group, select the target Oracle database, which is also used as the staging area for this mapping.
-
In the Property panel, under Integration Knowledge Module, select
IKM Oracle Incremental Update
from the Integration Knowledge Module drop-down list. -
Set the IKM options as follows:
-
Select No for the FLOW_CONTROL option.
-
Leave other options as they are.
-
-
From the File menu, click Save and close your mapping.
Running the Mapping
This section contains the following topics:
Running the Mapping
To run the mapping:
-
In the Projects tree view, expand the
SAP Demo
Project -
Expand the
SAP_BW_DataTargets
folder. -
Expand the Mappings node.
-
Select the
InfoCube_SAP_BW_VendorBal.W_VENDR_BAL_DS
mapping. -
Right-click and select Run.
-
In the Execution window that appears, click OK.
-
Click OK in the Session Started window.
What happens when you run the mapping?
First, Oracle Data Integrator creates an ABAP program that does the following:
-
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.
-
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 Mapping Execution
To review the mapping execution:
-
Connect to Operator.
-
In the Operator, select the Session List tree view.
-
Expand the All Executions node in this tree view.
-
The latest session is the first entry of this list.
-
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:
-
In the Designer, in the Models tree view, expand the
Oracle Target
Model. -
Select the
W_VENDR_BAL_DS
datastore, right-click and select Data to view the data integrated into the target Oracle table. -
Repeat the same steps for remaining mappings:
-
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
-