Generic Datasource extraction in BW from Master Data table in ECC

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’

Select ‘Create’

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’

Select ‘Save’.

Continue with the package and a TR.

Extraction:

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.

Datasource: ZDS_SPER

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)

Select ‘Continue’

Msg: ‘Replication completed successfully’

Select ‘Refresh’.

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.

Select ‘Activate’.

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.

Select refresh.

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

  • Name
  • Age
  • Addr
  • Phone

Press enter.

Note: If you choose time-dependent property then system will maintain two additional columns ‘DATEFROM’ and ‘DATETO’ automatically.

Select ‘Activate’

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 ‘Continue’

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.

Select ‘Start’

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 ‘Continue’

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’

Select ‘Contents’

Select ‘Execute’

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

 

 

Leave a Reply

Your email address will not be published.