Generic Datasources / Extractors – Cross Application (RSO2, SBIW)
Master data / Attributes extraction/ acquisition from SAP R/3 ECC (OLTP) to SAP BI (OLAP) using generic data sources – cross application
Eg. 1. Using a Database table
T-codes: SBIW, RSO2, SE11, SE16, RSA3, RSA7, RSA11, RSA13, RSA14, RSD1, RSDS, RSMO, RSMON, LISTCUBE, RSPC, RSPC1, RSPCM
Part 1: Data Modeling
Step 1: Logon to SAP R/3 system
Step 2: Check the data in the table
Enter tcode SE11
Select a master data table created in earlier post: ZSALESPERSON
Select ‘Display’. Then select ‘Contents’ and Execute.
Observe the data. Press F3, F3, F3.
Step 3: Create datasource for master data attributes using generic extractors (cross-application)
Enter the t-code: RSO2 or SBIW
Expand generic datasources
Execute ‘Maintain generic datasources’
Select the radio button ‘Master data attributes’. Give a datasource name as ‘ZDS_SPER’
Browse and choose the application component as ‘SD’.
Give short, medium and long text for the DS: ‘MD ATTR DS for SPER’
Select ‘Extraction from DB view’ button. Give the table name as ‘ZSALESPER’
Continue with the package and a TR.
Observe the extract structure name.
Observe the extractor settings: 1-Extractor does not support preaggregation.
Maintain / customize the datasource by using selection / hide / Inversion , Cancellation fields or Reverse posting and field only known in customer exit.
Select the ‘Selection’ check boxes to display in the data selection screen. Press F3, F3, F3.
Step 4: Check the data in the datasource using extractor checker.
Enter tcode: RSA3.
Select ‘Start Extraction’
Some data records are extracted and message is shown.
Continue and select ‘Display List’
Double click on the data packet number. Press F3, F3, F3.
Step 5: Logon to SAP BI
Step 6: Check for SAP R/3 source system. Enter tcode RSA13. Select the SAP source system and go to the context menu by right clicking and select ‘Check’
Step 7: Replicate metadata/datasource
7.1: Display the application component hierarchy. Select your R/3 source system. Go to the context menu and select ‘display datasource tree’ or double click on the R/3 source system.
Select ‘Sales and distribution’. Go to the context menu and select ‘Replicate metadata’.
Select the radio button ‘As datasource (RSDS) (BI 7.0)
Msg: ‘Replication completed successfully’
Find the datasource. Find ‘ZDS_SPER’. Press enter.
Step 8: Double click on the datasource to know whether the datasource is active or inactive. Active version: does not exist. Select ‘Extraction’ tab.
Adapter: Access to SAP data through Service API
Data format: Fixed length
Select ‘Fields’ tab.
Note: The infoObject column is not maintained because the source system is SAP R/3
Select ‘Change’ icon.
Note: System generates PSA table /BIC/Bnnnnnnn000
where nnnnnnn is a 7 digit random number generated.
Note: To know the PSA table name. Select ‘Manage’ for Datasource.
Observe the PSA name and cancel the window.
Step 9: Create infoObjects (RSA14, RSD1, RSDS)
9.1: Create InfoArea
9.2 Create Characteristic InfoObject Catalog
9.3 Create Characteristic InfoObject
ZSPER_ID – Salesperson ID
Give the data type and length same as that defined in the source table.
Deselect the checkbox ‘With Master Data texts’
Select ‘With master data attributes’
9.5 Create Attributes
Note: If you choose time-dependent property then system will maintain two additional columns ‘DATEFROM’ and ‘DATETO’ automatically.
Select ‘Activate dependent objects’ and continue.
Note: System generates attribute table (P) and DB view (M) and a S table.
Step 10: Convert the InfoObject (characteristic) with InfoProvider / datatype by using ‘Insert characteristic as InfoProvider’ function (RSA11)
Select ‘InfoProvider’ under modeling.
Select your InfoArea. Go to the context menu and select ‘Insert characteristic as InfoProvider’. Give the InfoObject name as ZSPER_ID and observe the attributes.
Step 11: Create transformation (at datasource level or at InfoProvider level).
Select datasources under Modeling. Select your datasource. Go to the context menu and select ‘Create Transformation’
Target of the transformation: Give object type as ‘InfoObject’. Select subtype of object as ‘Attributes/texts’ and give name as ‘ZSPER_ID’
Continue. Check the mappings and activate your transformation.
Refresh. Expand your datasource and observe the transformation.
Step 12: Show the data flow / display graphics
Select the InfoProvider under modeling. Expand your InfoArea. Select ‘Refresh’. Select your InfoObject. In the context menu, select ‘Display data flow’.
Close the graphics.
Result: Data modeling completed.
Part II: Schedule / load the data into PSA
1.1 Create an InfoPackage and load the data.
Select your datasource, go to the context menu and select ‘Create InfoPackage’
InfoPackage Description: ‘IP for ZDS_SPER’ for SPER MD ATTR
Select the datasource.
Select ‘Extraction’ tab.
Adapter: ‘Access to SAP data through Service API’
Data format: ‘Fixed length’
Select ‘Processing tab’
Update data – Only PSA
Select ‘Update’ tab
Update mode ‘Full Update’
Select ‘Schedule’ tab. Start data load immediately.
Step 2: Monitor the data load (RSMO, RSMON)
Select the monitor icon, select the ‘Request status’ and select ‘PSA maintenance’
Select Continue. Press F3, F3, F3.
Step 3: Create DTP to transfer data from PSA to Attribute target.
Select InfoProvider. Expand your InfoArea.
Expand your master data key.
Select your Attribute target.
Context menu: Select ‘Create DTP’
Data type ‘Standard (Can be scheduled)’
Select ‘Extraction tab’
Extraction mode: Full
Select Update tab.
Error handling: ‘Valid records update, No reporting, Request Red’
Select ‘Execute’ tab
Processing mode: ‘Serial extraction/ immediate parallel processing’
Activate your DTP and ‘Execute’
Select ‘yes’ and ‘Refresh’
Check the data in the target.
Select ‘Administer data target’
Select ‘Attribute target’
Observe the data.
Step 4: Check the data in the underlying data dictionary table (P table, S table, M table and PSA)
Enter tcode SE11
Give the PSA database table name /BIC/Bnnnnnnn000
Select ‘Display contents’ and Execute.
Press F3, F3, F3.
Database table: /BIC/PZSPER_ID
Select ‘Display contents’ execute.
Note: The number of columns in Attribute table are C+2
C is the actual number of columns maintained by customer
2 – additional columns maintained by system – Object Version and Changed Flag
Object Version: In BI 7, there are six object versions:
- A – Active
- M – Revised
- N – New
- D – Content
- H – Historic
- T – Transport
only Active version objects are executable, usable.
Changed flag: It includes 3 values.
Space – No Change
I – Entry marked for insertion
D – Entry marked for deletion
The number of records/rows in the attribute table are R+1
R – the actual number of records loaded by the customer
1 – Initialization record or record of initial values
The initial values for a field of datatype char are spaces
The initial values for a field of data type NUMC, DEC, INT4 are zeroes.
Press F3, F3, F3.
Database table: /BIC/SZSPER_ID
Database table: /BIC/MZSPER_ID