extract r3 to bi 7 generic datasource using view.pdf

SAP COMMUNITY NETWORK SDN - sdn.sap.com | BPX - bpx.sap.com | BOC - boc.sap.com | UAC - uac.sap.com

© 2010 SAP AG 1

Step by Step Procedure to Extract

Data from R/3 to BI 7.0 using

Generic DataSource

Applies to:

SAP BI 7.0. For more information, visit the EDW homepage.

Summary

This article gives a detail procedure for a full load generic extraction from R/3 to BI using a Database View. It also describes the creation of BI objects like InfoObjects, DTP, Transformations, Setting up rules in transformation and also includes the steps to install Objects from BI Content.

Author: Jerry Jerome

Company: Applexus Software Solutions (P) Ltd.

Created on: 06 October 2010

Author Bio

Jerry Jerome is working as SAP Technology Consultant with Applexus Software Solutions (P) Ltd. He has experience in SAP ABAP and SAP BI.

https://www.sdn.sap.com/irj/sdn/businessone

Step by Step Procedure to Extract Data from R/3 to BI 7.0 using Generic DataSource


© 2010 SAP AG 2

Table of Contents

Steps in R/3 ........................................................................................................................................................ 3

Step 1: Creating a View .................................................................................................................................. 3

Step 2: Creating Generic DataSource ............................................................................................................ 6

Step 3: Testing Generic DataSource .............................................................................................................. 8

Steps in BI side ................................................................................................................................................... 9

Step 1: Selecting Source System ................................................................................................................... 9

Step 2: Replicate Metadata ............................................................................................................................. 9

Step 3: Creating InfoPackage and uploading data to PSA ........................................................................... 11

Step 4: Installing InfoObjects from BI Content .............................................................................................. 13

Step 5: Creating InfoArea.............................................................................................................................. 15

Step 6: Creating DSO ................................................................................................................................... 16

Step 7: Creating Transformation ................................................................................................................... 18

Step 8: Creating Transformation rules using routines (ABAP Coding) ......................................................... 19

Step 9: Creating DTP (Data Transfer Process) ............................................................................................ 21

Step 10: View contents of DSO and move data from New to Active table ................................................... 24

Related Content ................................................................................................................................................ 27

Disclaimer and Liability Notice .......................................................................................................................... 28



© 2010 SAP AG 3

Steps in R/3

Step 1: Creating a View

There are three options to create generic DataSource. They are :- a. View/Table

b. SAP Query

c. Function Module

Here I am dealing with creating generic DataSource based on a view/table. So in R/3 system take the tcode se11 and type the name of the view to create. Click on ‘Create’ button. The name of view is ‘ZBI_EKPO’.

Note: Only transparent tables or database views can be used to create generic extractors in ‘View/Table’. Among the four options select ‘Database View’.

In the database view select the tab ’Table/Join Conditions’. Give the name of the table in the ‘Tables’ section. Write ‘EKPO’ in the tables section.



© 2010 SAP AG 4

Select the tab ‘View/Fields’ and click ‘Table Fields’ button to select the fields of the table which we need.

Select the checkboxes of the fields which we want to include in our view. In our view we are going to include EBELN (Purchasing Document Number), EBELP (Purchasing Document Item), MATNR (Material Number), MENGE (Purchase Order Quantity) and MEINS (Purchase Order Unit of Measure).



© 2010 SAP AG 5

Select the fields and click on the ‘Copy’ button. Then the fields will be copied to the view. After it is transferred click on the ‘Activate’ button to activate it.



© 2010 SAP AG 6

Step 2: Creating Generic DataSource

RSO2 is the tcode used to create a Generic DataSource. Select the radio button for ‘Transaction Data’. The name of DataSource is ‘ZJJ_DS_EKPO’.

Click on create button.

Data is going to be fetched from EKPO table which deals with purchasing documents. So select ‘MM’ as application component. In the area for ‘View/Table’ give the name of the view which was created in se11. Click on ‘Save’ button. When the DataSource is saved an extract structure is created for our DataSource which is shown in the figure given below.



© 2010 SAP AG 7

Double click on the extract structure. Extract structure is shown in the figure below. It is exactly the same as the view.

Save the DataSource.



© 2010 SAP AG 8

Step 3: Testing Generic DataSource

RSA3 is the tcode used to check a DataSource. RSA3 is known as extractor checker. Take RSA3 and give the name of DataSource. Either click on ‘Extraction’ button or press ‘F8’. It shows the number of records extracted. That means DataSource is working fine.

RSA6 is the tcode used to check whether a DataSource is available for replication or not. Go to tcode RSA6 and check whether DataSource is available under the application component SAP r/3->MM. In the figure given below DataSource ‘ZJJ_DS_EKPO’ is available and thus it is available for replication.



© 2010 SAP AG 9

Steps in BI side

Step 1: Selecting Source System

Take RSA1 tcode. Click on ‘DataSource’ section present in the left side panel under ‘Modeling’ section. Select appropriate source system. Click on the button ‘Choose Source System’. Select source system as R/3.

Step 2: Replicate Metadata

Now select Application Component. So select ‘Materials Management’ and check whether DataSource is available under it. It won’t be there because it was created in R/3. So right click on Materials Management’ and click on ‘Replicate Metadata’. Then all the DataSource under MM will be regenerated in BI.



© 2010 SAP AG 10

It asks for options for the version of DataSource. That is whether we need it in 3.x version or as (RSDS) DataSource. We have to select rsds DataSource. If the version is rsds option then the object will be available in BI7.0 otherwise it will be available only in 3.x version.

It may take some time to replicate the DataSource to BI. When replication is completes DataSource is available under the Application Component ‘Materials Management’.

Double click on DataSource and click on ‘Activate’ button to activate it.



© 2010 SAP AG 11

Step 3: Creating InfoPackage and uploading data to PSA

When DataSource is in activated state right click on it and select ‘Create InfoPackage’.

In the update tab of InfoPackage only ‘Full Update’ option is there because delta update mode was not selected when the DataSource was created in r/3 side. In the section for InfoPackage click on ‘Start’ button which is under ‘Schedule’ tab. Since it’s a full upload it extracts all records from ‘EKPO’ table in r/3.



© 2010 SAP AG 12

When the process is over right click on the DataSource and select the option ‘Manage’.

In the next screen select the request and click F5 or the icon called ‘PSA maintenance’.

In the next screen select the first row showing the number of records retrieved and press enter or click on the tick mark. A window is shown with the number of records fetched.



© 2010 SAP AG 13

Step 4: Installing InfoObjects from BI Content

Now data is in the PSA table. The InfoObjects corresponding to the fields in DataSource are not active in BI side. So InfoObjects have to be installed from BI content.

Go to RSA1 tcode and click on BI content on the left side of the panel. Expand InfoObject and double click on the option ‘Select Objects’.

In the window which pops up click on find button to search for the InfoObjects that are required. For example, search for ‘Purchasing document number’ and click on ‘Transfer Selections’ button if it is found.



© 2010 SAP AG 14

Find out order item, material number, quantity and unit with the same method and transfer all these InfoObjects.

Transferred InfoObjects are transferred to the collected objects section which is in the right side.

Here we can see the InfoObjects transferred. I haven’t transferred material number because I had already transferred and activated it.

‘Install’ checkbox is highlighted by default for InfoObjects that are being transferred for the first time. It is also checked if active version of these InfoObjects does not exist in the system

‘Match (X)’ checkbox will be selected by default. The Match indicator is set as default in order to prevent the customer version being unintentionally overwritten by delivery version.

‘Grouping’ combines the objects from a particular area. The default ‘Grouping’ mode is ‘Only Necessary Objects’. It means only necessary objects required to work with these InfoObjects are transferred and installed.

Click on ‘Install’ button to install the collected objects

It may take some time to install the objects.



© 2010 SAP AG 15

Step 5: Creating InfoArea

In RSA1 select InfoProvider under ‘Modeling’ tab. Right click on InfoProvider and select ‘Create InfoArea’.

Give a name and description for the InfoArea.



© 2010 SAP AG 16

Step 6: Creating DSO

Right click on the InfoPackage created and select the option ‘Create DataStore Object’.

Give name and description for DSO and then click on ‘Create’ button.



© 2010 SAP AG 17

Now we get the maintenance screen for our DSO. We can find our InfoObjects in the middle part of our window. Select the button for ‘All InfoObjects’. Drag mouse over the buttons and you can see the description of buttons. They are marked in the below figure. Click on ‘Find’ button and type the technical name of the InfoObject which was installed from ‘BI Content’. For example, give ‘0oi_ebeln’ and search to get Purchase document number.

Now click and drag the InfoObject to the right hand side. Drag 0oi_ebeln to the node ‘key fields’. Similarly find and drag all other fields in our DataSource. Drag and drop 0oi_ebeln and 0oi_ebelp should come under ‘Key fields’. Similarly put 0d_po_qty and 0gt_matnr under ‘Data fields’.

When we drag all fields into our DSO click on the activate button to activate our DSO.



© 2010 SAP AG 18

Step 7: Creating Transformation

The transformation process allows you to consolidate, cleanse, and integrate data. It maps fields from the source object to the target object. A transformation converts the fields of the source into the format of the target. Right click on the DSO and select ‘Create Transformation’.

In the source of transformation we have to give the DataSource name and source system.



© 2010 SAP AG 19

Drag and connect arrows from the source side to their corresponding fields in the target side.

In this DSO rules have to be created for transformation. Because when I uploaded data from r/3, I got error for the value ‘PC’ in ‘Base Unit of Measure’. It’s because ‘PC’ is saved as ‘ST’ in BI. In order to solve it a rule has to be created for ‘Base Unit of Measure’.

Step 8: Creating Transformation rules using routines (ABAP Coding)

Right click on the InfoObject ‘0BASE_UOM’ and select ‘Rule Details’.



© 2010 SAP AG 20

In the next pop up box select Rule type as ‘Routine’ and click on the pencil button on the right side of Rule type which is shown in the figure below.

It opens an editor to type code. The code should be inserted under the comment ‘insert your code here’. The structures to be used in the code can be found out by reading the commented out areas in the editor. ‘Source_Field-Meins’ is the name of the structure which gets value from source system and ‘Result’ is the variable which passes value of Base Unit of Measure to the target side. The code written is in the below figure.



© 2010 SAP AG 21

Click on ‘Check’ button to check for errors and save it if its error free.

Now for the Base Unit of Measure there is no equal sign. Instead an editor icon is present because a rule has been set for it.

Click on ‘Activate’ button to activate the transformation.

Step 9: Creating DTP (Data Transfer Process)

Data Transfer Process (DTP) is used to transfer data from source objects to target objects in BI. Right click on DSO and select ‘Create Data Transfer Process’.



© 2010 SAP AG 22

A window is popped up with the required fields filled.

A pop up will appear saying that ‘DTP has been changed. Do you want to save?’ Click ‘Yes’.

In the ‘Extraction’ tab select the extraction mode as ‘Full’. Click on ‘Update’ tab. Select ‘Valid record Update, No Reporting (Request Red)’ option under error handling section. This option allows you to update valid data. This data is only released for reporting after the administrator checks the incorrect records that have not been updated and manually releases the request (using a QM action, that is, by setting the Overall status on the Status tab page in the monitor).



© 2010 SAP AG 23

Click on ‘Activate’ button. If DTP is active then click on ‘Execute’ button under ‘Execute’ tab.

The DTP window shows that the package extracted 28152 records.



© 2010 SAP AG 24

Step 10: View contents of DSO and move data from New to Active table

Right click on the DataSource and select ‘Manage’ to see the contents of DSO. Click on the ‘Contents’ tab and click on ‘New Data’. Press ‘F8’ button to see the records.

We have to activate the data in the new table to make it available in the table for ‘Active Data’.

To activate the data, click on the ‘Requests’ tab and select request. Click on ‘Activate’ button.

In the next window (below figure) select the request and click on ‘Start’ button.

It may take some time to load data into the table for active data. Click on the ‘Refresh’ button to refresh the request. After some time the request will be in green color.



© 2010 SAP AG 26

When the request is in green color it means that the data is active now. Click on ‘Contents’ tab and click on ‘Active Data’ button. Press ‘F8’ and it will show the records in DSO.

Press ‘F8’ and then we can see the records fetched.



© 2010 SAP AG 27

Related Content

How to install business content InfoObjects..

Data extraction from r3 to sap bi 7.0

Creating Data Transfer Processes

Maintaining Generic DataSource

For more information, visit the EDW homepage.

http://forums.sdn.sap.com/message.jspa?messageID=4870414

http://forums.sdn.sap.com/thread.jspa?threadID=1008331

http://help.sap.com/SAPhelp_nw04s/helpdata/en/42/fa50e40f501a77e10000000a422035/content.htm

http://help.sap.com/saphelp_nw04/helpdata/en/3f/548c9ec754ee4d90188a4f108e0121/content.htm

https://www.sdn.sap.com/irj/sdn/businessone



© 2010 SAP AG 28

Disclaimer and Liability Notice

This document may discuss sample coding or other information that does not include SAP official interfaces and therefore is not supported by SAP. Changes made based on this information are not supported and can be overwritten during an upgrade.

SAP will not be held liable for any damages caused by using or misusing the information, code or methods suggested in this document, and anyone using these methods does so at his/her own risk.

SAP offers no guarantees and assumes no responsibility or liability of any type with respect to the content of this technical article or code sample, including any liability resulting from incompatibility between the content within this document and the materials and services offered by SAP. You agree that you will not hold, or seek to hold, SAP responsible or liable with respect to the content of this document.

extract r3 to bi 7 generic datasource using view.pdf

Documents