How to extract data using BI Content Datasource in SAP BW

 This video and post is about how to extract data from SAP ECC to BW system using Business content standard Datasources.

The video outlines step by step approach on how to create the data model and schedule the delta loads in the BW system.

https://www.youtube.com/watch?v=Iggsfa7_eho

Aim

Data extraction / Acquisition from SAP R/3 ECC (OLTP) to SAP Netweaver BI (OLAP) using Business Content Datasources

Tcodes

SBIW, RSA9, RSA5, RSA6, RSA3, RSA7, RSA1, RSA2, RSA13, RSA14, RSDS, RSD1, RSD5, RSDCUBE, RSORBCT, RSORMDR, SE11, SE16, RSPC, RSPC1, RSPCM, RSMO, RSMON, LISTCUBE...

Part I - Data Modeling

Step 1: Logon to SAP R/3 ECC

Step 2: Activate / transfer application component hierarchy (need to be performed once) (RSA9 or SBIW)

Step 3: Install and activate the required Datasource

Enter tcode RSA5 (Installation of Datasource from business content) or enter tcode SBIW and expand 'Business content Datasources' and then execute 'Transfer Business Content datasources'

Expand SAP R/3

expand SD

Select the datasource 2LIS_01_S005 (Shipping point or delivery)

Note: To view the extract structure of the datasource, double click on the datasource

Extract structure: S005BIWS

Observe the fields

Press F3

Place the cursor on the datasource

Select 'Version Comparison'

msg: No differences found between version A and D

Press F3

Select the datasource

Select Activate datasource / transfer datasource

Select Continue

Continue with msg

Continue with TR

Press F3

Step 4: Check the datasource entry in the metadata repository of datasource

enter tcode RSA6

expand SAP R/3

Expand SD
Observe your datasource. It exists.

Step 5: Maintain / customize the datasource

Select your datasource

Note: Do not press double click

Select 'Change Datasource'

Select Continue

Extract Structure: S005BIWS

Direct Access: D (Virtual access not possible)

Delta Update: Checked (Implies this datasource supports delta update)

Maintain / customize the dataosurce by using selection, hide fields, Inversion or calculation field or reverse posting and field only known in customer exit.

Select 'selection' checkboxes to enable fields to have selection in the extract structure.

Select Save.

Note: Execute RSA3 transaction to check the data (this step is optional while modeling)

Step 6: Logon to SAP BI

Step 7: Check the R/3 source system

Enter tcode RSA13

Select 'choose source system'

expand 'SAP folder'

Select your R/3 source system

Context Menu

Select 'Check'

Step 8: Replicate metadata / datasource

8.1: Display the application component  hierarchy

Select your R/3 source system, context menu

select 'Display datasource tree' or Double click on R/3 source system

Expand 'SAP' folder

Expand 'SAP Application Component'

Expand 'Sales and Distribution'

Select your Datasource 2LIS_01_S005

Note: The datasource is in emulated version

Hence migrate the datasource to BI compatible

Select your datasource, context menu

Select 'Migrate', select 'with export'

8.2: Replicate the metadata / datasource

select your datasource 2LIS_02_S005

Context menu

Select 'Replicate metadata'

Select 'Refresh'

Step 9: Activate the datasource

Double click on the datasource 2LIS_02_S005

Select 'Extraction' tab

Adapter

Data format

Select 'Fields' tab

Note: InfoObject column not maintained

Hence we cannot enter the InfoObjects here. Therefore, transformations cannot be created automatically

You need to create transformation manually using RSOSFIELDMAP table

Select 'Change' icon

Select 'Activate'

Select 'Yes'

Note: System generates PSA table /BIC/B.....<10 digit numeral>

To see the PSA table name, select 'Manage' in the context menu of the Datasource, observe the PSA name

Step 10: Activate and install infoObjects catalog and relevant infocube from BI content (tcode: RSORBCT)

Select BI content

Select 'InfoObjects'

Expand 'CRM InfoArea'

Expand 'ERP Analytics'

Expand 'Sales and Distribution Analytics'

Observe the characteristic catalog 0SD_CHA01

Observe the key figure catalog 0SD_KYF01

Note: Perform pre installation steps

1. Maintain Source System Assignment: Select 'SS assignment' button and then select  your R/3 source system, continue
2. Maintain Grouping: Select Grouping - 'Only Necessary Objects'
3. Maintain Collection mode: Select 'Collect automatically'

Drag and drop the characteristics and keyfigure info object catalog into the collected objects pane

Step 11: Install the relevant Infocube

Note: To know the relevant InfoCube for relevant datasource, use update rules relationship

Select 'Object types' in BI content

Expand 'Update Rules'

Double click on 'Select objects'

Select 'Find' icon

Search term '2LIS_01_S005'

Press enter

Close the find window.

Observe the cube name '0SD_C02'

Select 'Cancel'

Select 'Infoprovider' under BI content

Search for 0SD_C02. Drag and drop it to the collected objects pane.

Select 'Activate and install' - 'Install'

Note: If the activation and installation  process fails, repeat the installation process till all required objects are installed successfully

Check for the objects installation - select modeling tab-Infoproviders. Click on Refresh. Search for 0SD_C02.

Step 12: Create transformation

Go to 'Datasource' tab and select your source system. Then, search for '2LIS_01_S005'. In the context menu of this datasource, select 'create transformation'

Enter target of the transformation as Infocube 0SD_C02

msg: Proposals cannot be generated automatically, create proposals manually

Note: To know the relationship between fields of the datasource and infoObjects of the InfoProvider, use table RSOSFIELDMAP

enter tcode SE11

give the table name and select 'Display'. Then select 'Display table contents'

In the selection screen give OLTP source as '2LIS_02_S005' and select 'Execute'

From the table screen, observe the field name and  identify the relevant infoObject

Perform the mapping in the transformation

Activate your transformation. Press F3.

Note: Select the datasource and click refresh. Observe the transformation.

Step 13: Show data flow or display graphics

Go to the infoprovider tab and select your infocube

Press refresh

In the infocube context  menu, select 'Display data flow'

Observe the graphics.

Result: Data Modeling Completed

Part II - Data Extraction / Data Acquisition

Step 1: Schedule / Load the data into PSA with full update

Create InfoPackage and load the data

Select your datasource, context menu  -'Create Infopackage'

Give a description ' IP for 2LIS_01_S005' for shipping point for Full Update

Continue. Select extraction tab. Leave all the settings as default. In the Update tab, select Full update and schedule 'Immediately'.

Step 2:  Go to Monitor and keep refreshing to see the number of records loaded

Step 3: Create DTP from the context menu of the infocube 0SD_C02. Select the mode as 'Full' in the extraction tab and activate the DTP. Finally execute the DTP.

Monitor the data load.

Part III - Delta Management

Step 1: Logon to SAP R/3 ECC 6.0

Step 2: Check for the datasource entry in the BW delta queue

enter tcode RSA7

The datasource 2LIS_01_S005 does not exist. Press F3.

Step 3: Logon to BI

Create Infopackage for initialize delta and schedule

• Select your datasource, context menu - create infopackage
• Give a description -'IP for 2LIS_01_S005 for Shipping point with delta update'
• Keep all setting in the infopackage as default, in the update tab-select update mode as 'Initialize delta process'.
• Then start data load immediately

Step 4: Monitor the data load - the data load is successful with 1 initialization record

Press F3

Step 5: Now logon to R/3 system. Go to RSA7 and check your datasource entry in the BW delta queue

The datasource can be seen

i.e. it is ready to capture delta records

Step 7: Perform end user transactions

i.e. Create sales order or modify sales order (by end user)

Note: To know the end user transactions such as sales order creation etc. in SAP easy access- In the home screen, expand 'Logistics'-'Sales and Distribution'-'Sales'-'Order'

Observe the tcodes.

VA01 - Create

VA02 - Change

:

Select VA01

Note: If the transaction code is locked, go to tcode SM01 to unlock it

Double click on VA01 to create a sales order

Once the document is saved, it will be stored in database tables VBAK, VBAP etc.

Check the data in the BW delta queue in the DI/RDI modes

Enter tcode: RSA7

Select your datasource

Select 'Display entries'

Select 'Delta update' (DI mode)

Select 'Execute'

Observe the data. Press F3.

Select the datasource. Select 'display data entries'.

Update mode - Delta repetition or Repeat Delta (RDI mode)

Select execute. Observe the data. Press F3.

Check the data in the datasource using the extractor checker.

Enter the tcode RSA3

Datasource: 2LIS_01_S005

Update mode: D

Target system: BI server

Select 'Start Extraction', then select 'List'

Press F3, F3

Step 11: Logon to BI

Step 12: Create Infopackage for delta update and schedule it in the background  periodically (e.g. every 4 hours) using the process chain

Select 'Datasource' tab. In the context menu of your datasource, select 'Create Infopackage'.

Give a description as "IP for 2LIS_01_S005' for deliveries with Delta update'

Continue.

In the extractor tab - keep default settings

In the Update tab, select update mode as 'Delta update'

In the Schedule tab, Select start data load immediately and Save

Step 13: Create DTP with delta update but do not execute it as it will will executed via process chain

Step 14: Goto RSPC to create the process chain. Enter a process chain name

Create a start variant. Select as periodic job with period values - 'Others' - 4 hours

Select 'restrictions' Calendar IO: Browse and choose your client's calendar

Save and press F3

Add steps in your PC - Infopackage, drop index of 0SD_C02, delta DTP, Create index of 0SD_C02

Activate your PC an schedule it.

Step 15: Assign the PC to your monitor daily process chain in the tcode RSPCM