How to extract data using BI Content Datasource in SAP BW
For enhancement of datasource refer ebook, click here
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.
Data extraction / Acquisition from SAP R/3 ECC (OLTP) to SAP Netweaver BI (OLAP) using Business Content Datasources
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
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
Place the cursor on the datasource
Select ‘Version Comparison’
msg: No differences found between version A and D
Select the datasource
Select Activate datasource / transfer datasource
Continue with msg
Continue with TR
Step 4: Check the datasource entry in the metadata repository of datasource
enter tcode RSA6
expand SAP R/3
Observe your datasource. It exists.
Step 5: Maintain / customize the datasource
Select your datasource
Note: Do not press double click
Select ‘Change Datasource’
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.
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
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
Select ‘Replicate metadata’
Step 9: Activate the datasource
Double click on the datasource 2LIS_02_S005
Select ‘Extraction’ tab
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
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
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
- Maintain Source System Assignment: Select ‘SS assignment’ button and then select your R/3 source system, continue
- Maintain Grouping: Select Grouping – ‘Only Necessary Objects’
- 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’
Close the find window.
Observe the cube name ‘0SD_C02’
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
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
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
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)
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
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’
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