IBM InfoSphere SAP OData Connector stage was introduced in the version 8.2 of the IBM® InfoSphere® Information Server Pack for SAP Applications. You can use this stage to extract the data from SAP systems by using the SAP exposed OData services.

SAP OData connectors allow you to browse different OData services exposed in the SAP server through its Catalog service. You can select any service of your choice, retrieve the meta-data of various entities exposed in the selected service and then design a DataStage job to extract data for the given entity set.

For more information, see knowledge centre documentation -  SAP OData Connector stage.

Data Integration with SAP BW Application by using SAP OData connector

For SAP, OData services remain as a popular choice to expose SAP system resources to variety of clients. To ease and standardize the process of exposing OData services on a variety of SAP Objects like RFCs or BAPIs , the SAP introduced SAP Gateway Foundation, which is a software component available within the SAP Net Weaver.

This component provides you the development and generation tools to create OData services. In simple words, this component through its virtue to expose OData services on different SAP Objects, has provide a link between SAP Business Suite data to the variety of the target applications, platforms including the 3^rd Party applications like DataStage.

This whitepaper explains few business scenarios on how you can achieve the data integration between the SAP BW/4HANA systems with the DataStage by using the functionality provided by SAP Gateway Foundation and SAP OData connector.

Integration Scenarios

Extracting Data by using OData Service exposed on BEx Query

Introduction

From this business scenario, you can get to know how to retrieve  the reporting or analysis result data set from your BEx query by using the SAP OData Connector that is available as part of Information Server Pack for SAP Applications.

Modelling in SAP BW/4HANA

BEx Query

In SAP BW Applications, data is organized in the self-contained business data areas called InfoProviders. For proper business insights, you need to analyse the data stored in the InfoProviders. You can do that by defining the Business Explorer (BEx) query which consists of a selection of characteristics and key figures (InfoObjects) for the data of selected InfoProvider. A query is based on one InfoProvider, but multiple queries can be defined for an InfoProvider.

Creating a simple BEx Query in BW/4HANA

In this scenario, you can create a BEx query for the sales report. By using the following BW Query you can extract the sales data details for example,  Quantity, Sales value, Tax amount and cost based on Customer, Material, Plant, Sales Organization, Distribution channel, Division for Company code = 1000. For preparing this report, you can use standard ADSO: /IMO/CMSD31. The ADSO serves as a corporate memory DSO for the DataSource Billing Document Item Data: 2LIS_13_VDITM.

Switch to your HANA Studio application and follow the steps to display BEx query:

• Log into BW/4HANA system and connect to your BW project.
• Select the Advanced DSO: /IMO/CMSD31 (It can be a Master data, Advanced DSO, Composite provider, or Open ODS view)à Right click on the info provider à New à Query
• Specify the Query Name,  ZQRY_CMSD31_001,  and description, Sales Report à Create

• From Sheet Definition Tab, select the Rows and Columns fields that you want to show in the query.

• You can also apply filter as required.

• Save the query.

Running your BEx Query

Go to T-code: RSRT à Execute the BW Query: ZQRY_CMSD31_001.

Query output in Excel Analysis:

You can open the results of the BEx Query in Excel Analysis.

Exposing the OData Service on Bex Query

Follow these steps to publish the service:

• Go to the t-Code,  /n/IWFND/MAINT_SERVICE à Add Services . You can see the query with _SRV as extension.

• Select the Technical Service Name à Click the Add Service à Provide the model name, its description and assign the package.

• Click the SAP Gateway Client button

• Click EntitySets à Select ZQRY_CMSD31_001Results as EntitySet Name à In the Menu bar, SAP Gateway Client à Call Browser

• By now, you have your OData Service which has been exposed for the getting the results of BEx query. In this case the OData Service URL would be like below

http://<server-host-name>:<port>/sap/opu/odata/sap/ZQRY_CMSD31_001_SRV/ZQRY_CMSD31_001Results

Checking Data from the OData Service

To check the data of the OData Service, you can try running the service in Browser or some other client like Postman. You can see the following :

Modelling at DataStage

Querying metadata of the exposed BEx Query using IMAM

You can use the DataStage Metadata Asset Manager (IMAM) for importing the metadata of the OData Service exposed for the BEx Query. You can learn in detail how to use the SAP OData Connector Stage in the IMAM here.

Follow these steps:

• Create a new Import Area
• Select existing connection or create a new connection to the SAP BW/4HANA system.

• Select the assets to import.
  1. Locate the service from the list of sapodata<database> which you have exposed above. It is displayed in the following IMAM screen. 
  2. Select the required entity set for which the data is to be extracted. In this case the name of the entity set is “ZQRY_CMSD31_001Results” 
  3. Select all the fields to be extracted in DataStage for this entity set. You get all the fields exposed to the selected entity set
  4. You can click the “Share to Repository” to share the metadata of the selected OData Service to DataStage.

Designing DataStage Job for data extraction from Bex Query

You can design a DataStage job by using the SAP OData Connector to extract data from the exposed OData Service in BEx query and load it to the target. From this whitepaper, you can learn to extract data from the OData Service and load into a sequential file.

For the detailed steps on how to design a job by using the SAP OData Connector stage, see here.

Follow these steps:

• Import the metadata of the OData Service in DataStage that is shared earlier to repository from the Metadata Asset Manager. You can find the detailed steps here
  1. By using “Repository>Metadata Sharing > Create Table Definition from the Shared Table” , you can browse the database exposed from the Metadata Asset Manager
     
  2. On clicking create, following table definition is created:
     

• Create a simple job as shown:

• You can design the SAP OData Stage as explained in the following  steps:
  1. Define the SAP OData Connection
     
  2. Select the required OData Service and Entity Set
     
  3. Define the columns by loading the metadata of the created table definition
     

• Save the job
• Run the design job to extract the BEX Query Results data

You can see a total of 10414 record that are successfully extracted and loaded into the Sequential File.

Extracting data by using the OData Service Exposed on ADSO

Introduction

In this section, you can learn another use case for extracting the data from the Advanced DSO by using the SAP OData Connector Stage that is available as part of the Information Server Pack for SAP Applications

What is ADSO and its relevance in BW/4HANA ?

Advanced DSO (DataStore Object) is the latest object introduced SAP BW 7.4 onwards which supports the data persistency while doing data modelling. This has significantly reduced the modelling complexities particularly in the SAP BW/4HANA system where ADSO has taken a centerstage by replacing all other persistent objects like PSA, DSOs InfoCube and HybridProvider with ADSO. It is the only persistent object in the BW/4HANA.

ADSO can easily handle the following characteristics of the classical modelling in the BW/4HANA.

• Supports both based on the field  and, InfoObject modelling
• Provides 3-table approach as in Standard DSOs for the
• Can act as source Object for the Open DSO
• Supports frequent data loads with large data volumes
• Can support 120 Keys fields as against only 16 key fields in standard DSO
• Provides extensive modelling support through new Eclipse based HANA Studio

Modelling in SAP BW/4HANA

Creating a simple ADSO in BW/4HANA  

1. BW modelling tools in HANA studio. In the context menu for your BW project, choose Start of the navigation path “New > Data Store Object (advanced)”
   
2. In the dialog box for creating Data Store objects (advanced), assign an Info Area to your advanced Data Store object and give it a name and description. The name can be between three and nine characters long. If there is  a namespace for the Data Store object (advanced), the name can only be eight characters long.  You can select an object template or a template for the data warehousing layer
   
3. In Overview tab, modelling properties allow you to control how you use your ADSO.
   
4. In Details tab, fields can be defined as per business requirement.
   
5. In Setting tab, settings can be maintained as per the business requirements.
       

Exposing the OData Service in ADSO

OData is connected to the ODP data replication API as a communication protocol. The ODP framework provides the subscriber type OData access for operational data provisioning (ODP), which enables OData access to ODP data.

Data of ADSO can be extracted using OData service exposed on the ODP created on the top of the ADSO. Following block-diagram depicts the same

 

Follow these steps for exposing ODP on the ADSO and then exposing that ODP as OData Service.

1. Go to T-Code SEGW, create a project with type “Service with SAP Annotations” and specify the necessary details as shown :
   
2. A project is created with the following structure. Click Save to save the project.
   
3. Define the data model extraction type. In the context menu choose to Redefine a ODP Extraction.
   
4. In the Pop-up wizard, enter the required fields.
5. Entities, Data model, and services are created and available in the project.
   
6. After the data model is generated, you need to create service runtime objects (ABAP Classes). Select the project in Edit mode and click on the “Generate Runtime objects” icon as shown.
   
7. All the objects generated are editable and you can change them.
   
8. Once the objects are generated, the runtime objects are created in the following location.
   
9. After all the artifacts are created, you need to register the service that enables you to display HTTP responses that occur at runtime. To register, go to T-code /n/iwfnd/maint_service, and click Add service option.
   
10. To register , select the service from backend services list and click ‘Add Selected Services’ option.
    
11. After it is enabled, below information is displayed.
    

Checking Data from the Exposed OData Service
To test the data generated through OData, go to SAP Gateway client (/n/iwfnd/gw_client), and choose the entity set that you want to see.

 

Modelling in DataStage

Querying metadata of the exposed ADSO by using IMAM

You can use the DataStage Metadata Asset Manager (IMAM) for importing the metadata of the OData Service exposed for the ADSO. You can learn in detail on how to use the SAP OData Connector Stage in the IMAM here.

Following these steps:

• Create a new Import Area
• Select the existing connection or create a new connection to the SAP BW/4HANA system.

• Select the assets to import.
  1. Locate the service from the list of “sapodata<database> that is exposed . This will be seen in the followig IMAM screen 
     
  2. Select the required entity set for which the data must be extracted. In this case, the name of the entity set is “FactsOfAD_MAT_2” .
     
  3. Select all the fields to be extracted in the DataStage for this entity set. You can get all the fields exposed to the  selected entity set
     

Designing DataStage Job for data extraction from ADSO

You can design a DataStage job by using the SAP OData Connector to extract data from the exposed OData Service on ADSO and load it to the target. In this whitepaper, you can learn to extract data from the OData Service and load it into the Sequential file.

For the detailed steps on how to design a job by using the SAP OData Connector Stage, you can see here.

Following these steps:

• Import the metadata of the OData Service in DataStage that is  shared to repository from the Metadata Asset Manager. You can find the detailed steps here
  1. Using “Repository>Metadata Sharing > Create Table Definition from Shared Table”,  you can browse the database exposed from the Metadata Asset Manager
     
  2. On clicking create, following table definition is created.
     

• Create a simple job as shown below

• You can design the SAP OData Stage as explained in the following steps:
  1. Define the SAP OData Connection.
     
  2. Select the required OData Service and Entity Set.
     
  3. Define the columns by loading the metadata of the created table definition.
     

---