Introduction

This plugin would allow SAP ECC enterprise users to specify one of the Inventory Management data sources as source in Cloud Data Fusion. Typical pipeline scenario would include specifying one of the data sources as source (under Sources plugins) and BigQuery as sink. For the sake of simplicity, this document will cover one data source (Material Movements from Inventory Management - 2LIS_03_BF).

User Experience and Configuration

SAP ECC Setup

SAP ECC users log into SAP Gateway Service Builder (segw) to create and expose the data source as SAP OData service. SAP has published following articles around this step:

User creates OData service for the data source (2LIS_03_BF). Output is RESTful OData service that can be accessed by Cloud Data Fusion.
User can also use SAP Netweaver Gateway client to test the service (with HTTP request and response)

...

Data type mismatch - OData output has data types (section 6) different than the data types available in BigQuery. Some sort of data type conversion needs to happen before the extracted data can be ingested into BigQuery. Below is the suggested mapping of OData data types to CDAP schema data types:

...

Field Level Lineage (FLL): Field level lineage should be available for all the sources and sinks.

OData Service API

Service Metadata Document

Describes the structure of all resources in the service.

Note: service metadata document has no JSON representation.

GET: http://vhcalnplci.dummy.nodomain:8000/sap/opu/odata/SAP/ZGW100_XX_S3_SRV/$metadata

...

Code Block

language	xml
title	Sample metadata response
linenumbers	true
collapse	true

<?xml version="1.0" encoding="UTF-8"?>
<edmx:Edmx xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:sap="http://www.sap.com/Protocols/SAPData" Version="1.0">
  <edmx:DataServices m:DataServiceVersion="2.0">
    <Schema xmlns="http://schemas.microsoft.com/ado/2008/09/edm" Namespace="ZGW100_XX_S2_SRV" xml:lang="en" sap:schema-version="1">
      <EntityType Name="SalesOrder" sap:content-version="1">
        <Key>
          <PropertyRef Name="SoId" />
        </Key>
        <Property Name="SoId" Type="Edm.String" Nullable="false" MaxLength="10" sap:label="Sales Order ID" sap:creatable="false" sap:updatable="false" sap:sortable="false" sap:filterable="false" />
        <Property Name="Note" Type="Edm.String" Nullable="false" MaxLength="255" sap:label="Description" sap:creatable="false" sap:updatable="false" sap:sortable="false" sap:filterable="false" />
        <Property Name="BuyerId" Type="Edm.String" Nullable="false" MaxLength="10" sap:label="Business Partner ID" sap:creatable="false" sap:updatable="false" sap:sortable="false" sap:filterable="false" />
        <Property Name="BuyerName" Type="Edm.String" Nullable="false" MaxLength="80" sap:label="Company" sap:creatable="false" sap:updatable="false" sap:sortable="false" sap:filterable="false" />
        <Property Name="CurrencyCode" Type="Edm.String" Nullable="false" MaxLength="5" sap:label="Currency Code" sap:creatable="false" sap:updatable="false" sap:sortable="false" sap:filterable="false" sap:semantics="currency-code" />
        <Property Name="GrossAmount" Type="Edm.Decimal" Nullable="false" Precision="16" Scale="3" sap:label="Gross Amount" sap:creatable="false" sap:updatable="false" sap:sortable="false" sap:filterable="false" />
        <Property Name="NetAmount" Type="Edm.Decimal" Nullable="false" Precision="16" Scale="3" sap:label="Net Amount" sap:creatable="false" sap:updatable="false" sap:sortable="false" sap:filterable="false" />
        <Property Name="TaxAmount" Type="Edm.Decimal" Nullable="false" Precision="16" Scale="3" sap:label="Tax Amount" sap:creatable="false" sap:updatable="false" sap:sortable="false" sap:filterable="false" />
        <NavigationProperty Name="SalesOrderItems" Relationship="ZGW100_XX_S2_SRV.SalesOrderSalesOrderItems" FromRole="FromRole_SalesOrderSalesOrderItems" ToRole="ToRole_SalesOrderSalesOrderItems" />
      </EntityType>
      <EntityType Name="SalesOrderItem" sap:content-version="1">
        <Key>
          <PropertyRef Name="SoId" />
          <PropertyRef Name="SoItemPos" />
        </Key>
        <Property Name="SoId" Type="Edm.String" Nullable="false" MaxLength="10" sap:label="Sales Order ID" sap:creatable="false" sap:updatable="false" sap:sortable="false" sap:filterable="false" />
        <Property Name="SoItemPos" Type="Edm.String" Nullable="false" MaxLength="10" sap:label="Item Position" sap:creatable="false" sap:updatable="false" sap:sortable="false" sap:filterable="false" />
        <Property Name="ProductId" Type="Edm.String" Nullable="false" MaxLength="10" sap:label="Product ID" sap:creatable="false" sap:updatable="false" sap:sortable="false" sap:filterable="false" />
        <Property Name="Note" Type="Edm.String" Nullable="false" MaxLength="255" sap:label="Description" sap:creatable="false" sap:updatable="false" sap:sortable="false" sap:filterable="false" />
        <Property Name="CurrencyCode" Type="Edm.String" Nullable="false" MaxLength="5" sap:label="Currency Code" sap:creatable="false" sap:updatable="false" sap:sortable="false" sap:filterable="false" sap:semantics="currency-code" />
        <Property Name="GrossAmount" Type="Edm.Decimal" Nullable="false" Precision="16" Scale="3" sap:label="Gross Amount" sap:creatable="false" sap:updatable="false" sap:sortable="false" sap:filterable="false" />
        <Property Name="NetAmount" Type="Edm.Decimal" Nullable="false" Precision="16" Scale="3" sap:label="Net Amount" sap:creatable="false" sap:updatable="false" sap:sortable="false" sap:filterable="false" />
        <Property Name="TaxAmount" Type="Edm.Decimal" Nullable="false" Precision="16" Scale="3" sap:label="Tax Amount" sap:creatable="false" sap:updatable="false" sap:sortable="false" sap:filterable="false" />
        <Property Name="Quantity" Type="Edm.Decimal" Nullable="false" Precision="13" Scale="3" sap:label="Quantity" sap:creatable="false" sap:updatable="false" sap:sortable="false" sap:filterable="false" />
        <Property Name="QuantityUnit" Type="Edm.String" Nullable="false" MaxLength="3" sap:label="Unit of Measure" sap:creatable="false" sap:updatable="false" sap:sortable="false" sap:filterable="false" sap:semantics="unit-of-measure" />
      </EntityType>
      <Association Name="SalesOrderSalesOrderItems" sap:content-version="1">
        <End Type="ZGW100_XX_S2_SRV.SalesOrder" Multiplicity="1" Role="FromRole_SalesOrderSalesOrderItems" />
        <End Type="ZGW100_XX_S2_SRV.SalesOrderItem" Multiplicity="*" Role="ToRole_SalesOrderSalesOrderItems" />
        <ReferentialConstraint>
          <Principal Role="FromRole_SalesOrderSalesOrderItems">
            <PropertyRef Name="SoId" />
          </Principal>
          <Dependent Role="ToRole_SalesOrderSalesOrderItems">
            <PropertyRef Name="SoId" />
          </Dependent>
        </ReferentialConstraint>
      </Association>
      <EntityContainer Name="ZGW100_XX_S2_SRV_Entities" m:IsDefaultEntityContainer="true" sap:supported-formats="atom json xlsx">
        <EntitySet Name="SalesOrderSet" EntityType="ZGW100_XX_S2_SRV.SalesOrder" sap:creatable="false" sap:updatable="false" sap:deletable="false" sap:pageable="false" sap:addressable="false" sap:content-version="1" />
        <EntitySet Name="SalesOrderCollection" EntityType="ZGW100_XX_S2_SRV.SalesOrder" sap:creatable="false" sap:updatable="false" sap:deletable="false" sap:pageable="false" sap:addressable="false" sap:content-version="1" />
        <EntitySet Name="SalesOrderItemSet" EntityType="ZGW100_XX_S2_SRV.SalesOrderItem" sap:creatable="false" sap:updatable="false" sap:deletable="false" sap:pageable="false" sap:addressable="false" sap:content-version="1" />
        <EntitySet Name="SalesOrderItemCollection" EntityType="ZGW100_XX_S2_SRV.SalesOrderItem" sap:creatable="false" sap:updatable="false" sap:deletable="false" sap:pageable="false" sap:addressable="false" sap:content-version="1" />
        <AssociationSet Name="SalesOrderSalesOrderItems_AssocSet" Association="ZGW100_XX_S2_SRV.SalesOrderSalesOrderItems" sap:creatable="false" sap:updatable="false" sap:deletable="false" sap:content-version="1">
          <End EntitySet="SalesOrderSet" Role="FromRole_SalesOrderSalesOrderItems" />
          <End EntitySet="SalesOrderItemSet" Role="ToRole_SalesOrderSalesOrderItems" />
        </AssociationSet>
      </EntityContainer>
      <atom:link xmlns:atom="http://www.w3.org/2005/Atom" rel="self" href="http://vhcalnplci.dummy.nodomain:8000/sap/opu/odata/SAP/ZGW100_XX_S2_SRV/$metadata" />
      <atom:link xmlns:atom="http://www.w3.org/2005/Atom" rel="latest-version" href="http://vhcalnplci.dummy.nodomain:8000/sap/opu/odata/SAP/ZGW100_XX_S2_SRV/$metadata" />
    </Schema>
  </edmx:DataServices>
</edmx:Edmx>

...

Service Document

Lists the available top-level resources.

GET: http://vhcalnplci.dummy.nodomain:8000/sap/opu/odata/SAP/ZGW100_XX_S3S2_SRV/SalesOrderSet

Response:

Code Block

language	xml
title	Sample 'GetEntitySet' responseservice XML document
linenumbers	true
collapse	true

<?xml version="1.0" encoding="UTF-8"?>
<feed<app:service xmlns:app="http://www.w3.org/20052007/Atomapp" xmlns:datom="http://schemaswww.microsoftw3.comorg/ado/2007/08/dataservices2005/Atom" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlxmlns:basesap="http://vhcalnplciwww.dummysap.nodomain:8000com/sap/opu/odata/SAP/ZGW100_XX_S3_SRV/">
  <id>httpProtocols/SAPData" xml:lang="en" xml:base="http://vhcalnplci.dummy.nodomain:8000/sap/opu/odata/SAP/ZGW100_XX_S3S2_SRV/SalesOrderSet</id>">
  <title type="text">SalesOrderSet</title>
  <updated>2019-08-21T10:29:14Z</updated> <app:workspace>
  <author>     <name /><atom:title type="text">Data</atom:title>
   </author>   <link href<app:collection sap:creatable="SalesOrderSetfalse" relsap:updatable="selffalse" titlesap:deletable="SalesOrderSetfalse" />
  <entry>
    <id>http://vhcalnplci.dummy.nodomain:8000/sap/opu/odata/SAP/ZGW100_XX_S3_SRV/SalesOrderSet('500000000')</id>sap:pageable="false" sap:addressable="false" sap:content-version="1" href="SalesOrderSet">
         <title<atom:title type="text">SalesOrderSet('500000000')</>SalesOrderSet</atom:title>
    <updated>2019-08-21T10:29:14Z</updated>     <category term="ZGW100_XX_S3_SRV.SalesOrder" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" /> <sap:member-title>SalesOrder</sap:member-title>
      </app:collection>
      <link href="SalesOrderSet('500000000')" rel="edit" title="SalesOrder" />
    <content type="application/xml">
      <m:properties>
        <d:SoId>500000000</d:SoId>
        <d:BuyerId>100000000</d:BuyerId>
        <d:BuyerName>SAP</d:BuyerName>
      </m:properties>
    </content>
  </entry>
  <entry>
    <id>http<app:collection sap:creatable="false" sap:updatable="false" sap:deletable="false" sap:pageable="false" sap:addressable="false" sap:content-version="1" href="SalesOrderCollection">
         <atom:title type="text">SalesOrderCollection</atom:title>
         <sap:member-title>SalesOrder</sap:member-title>
      </app:collection>
      <app:collection sap:creatable="false" sap:updatable="false" sap:deletable="false" sap:pageable="false" sap:addressable="false" sap:content-version="1" href="SalesOrderItemSet">
         <atom:title type="text">SalesOrderItemSet</atom:title>
         <sap:member-title>SalesOrderItem</sap:member-title>
      </app:collection>
      <app:collection sap:creatable="false" sap:updatable="false" sap:deletable="false" sap:pageable="false" sap:addressable="false" sap:content-version="1" href="SalesOrderItemCollection">
         <atom:title type="text">SalesOrderItemCollection</atom:title>
         <sap:member-title>SalesOrderItem</sap:member-title>
      </app:collection>
   </app:workspace>
   <atom:link rel="self" href="http://vhcalnplci.dummy.nodomain:8000/sap/opu/odata/SAP/ZGW100_XX_S2_SRV/" />
   <atom:link rel="latest-version" href="http://vhcalnplci.dummy.nodomain:8000/sap/opu/odata/SAP/ZGW100_XX_S2_SRV/" />
</app:service>

GET: http://vhcalnplci.dummy.nodomain:8000/sap/opu/odata/SAP/ZGW100_XX_S2_SRV/?$format=json

Response:

Code Block

title	Sample service JSON document
linenumbers	true
collapse	true

{
  "d":{
    "EntitySets":[
      "SalesOrderSet",
      "SalesOrderCollection",
      "SalesOrderItemSet",
      "SalesOrderItemCollection"
    ]
  }
}

Requesting EntitySet

GET: http://vhcalnplci.dummy.nodomain:8000/sap/opu/odata/SAP/ZGW100_XX_S3_SRV/SalesOrderSet

Response:

Code Block

language	xml
title	Sample 'GetEntitySet' response
collapse	true

<?xml version="1.0" encoding="UTF-8"?>
<feed xmlns="http://www.w3.org/2005/Atom" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xml:base="http://vhcalnplci.dummy.nodomain:8000/sap/opu/odata/SAP/ZGW100_XX_S3_SRV/">
  <id>http://vhcalnplci.dummy.nodomain:8000/sap/opu/odata/SAP/ZGW100_XX_S3_SRV/SalesOrderSet</id>
  <title type="text">SalesOrderSet</title>
  <updated>2019-08-21T10:29:14Z</updated>
  <author>
    <name />
  </author>
  <link href="SalesOrderSet" rel="self" title="SalesOrderSet" />
  <entry>
    <id>http://vhcalnplci.dummy.nodomain:8000/sap/opu/odata/SAP/ZGW100_XX_S3_SRV/SalesOrderSet('500000000')</id>
    <title type="text">SalesOrderSet('500000000')</title>
    <updated>2019-08-21T10:29:14Z</updated>
    <category term="ZGW100_XX_S3_SRV.SalesOrder" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <link href="SalesOrderSet('500000000')" rel="edit" title="SalesOrder" />
    <content type="application/xml">
      <m:properties>
        <d:SoId>500000000</d:SoId>
        <d:BuyerId>100000000</d:BuyerId>
        <d:BuyerName>SAP</d:BuyerName>
      </m:properties>
    </content>
  </entry>
  <entry>
    <id>http://vhcalnplci.dummy.nodomain:8000/sap/opu/odata/SAP/ZGW100_XX_S3_SRV/SalesOrderSet('500000001')</id>
    <title type="text">SalesOrderSet('500000001')</title>
    <updated>2019-08-21T10:29:14Z</updated>
    <category term="ZGW100_XX_S3_SRV.SalesOrder" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <link href="SalesOrderSet('500000001')" rel="edit" title="SalesOrder" />
    <content type="application/xml">
      <m:properties>
        <d:SoId>500000001</d:SoId>
        <d:BuyerId>100000002</d:BuyerId>
        <d:BuyerName>DelBont Industries</d:BuyerName>
      </m:properties>
    </content>
  </entry>
  <entry>
    <id>http://vhcalnplci.dummy.nodomain:8000/sap/opu/odata/SAP/ZGW100_XX_S3_SRV/SalesOrderSet('500000002')</id>
    <title type="text">SalesOrderSet('500000002')</title>
    <updated>2019-08-21T10:29:14Z</updated>
    <category term="ZGW100_XX_S3_SRV.SalesOrder" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <link href="SalesOrderSet('500000002')" rel="edit" title="SalesOrder" />
    <content type="application/xml">
      <m:properties>
        <d:SoId>500000002</d:SoId>
        <d:BuyerId>100000005</d:BuyerId>
        <d:BuyerName>TECUM</d:BuyerName>
      </m:properties>
    </content>
  </entry>
  ...

GET: http://vhcalnplci.dummy.nodomain:8000/sap/opu/odata/SAP/ZGW100_XX_S2_SRV/SalesOrderCollection?$format=json

Response:

Code Block

title	Sample 'GetEntitySet' JSON response
collapse	true

{
  "d":{
    "results":[
      {
        "__metadata":{
          "id":"http://vhcalnplci.dummy.nodomain:8000/sap/opu/odata/SAP/ZGW100_XX_S2_SRV/SalesOrderCollection('500000000')",
          "uri":"http://vhcalnplci.dummy.nodomain:8000/sap/opu/odata/SAP/ZGW100_XX_S2_SRV/SalesOrderCollection('500000000')",
          "type":"ZGW100_XX_S2_SRV.SalesOrder"
        },
        "SoId":"500000000",
        "Note":"EPM DG: SO ID 0500000000 Deliver as fast as possible",
        "BuyerId":"100000000",
        "BuyerName":"SAP",
        "CurrencyCode":"USD",
        "GrossAmount":"14385.850",
        "NetAmount":"12088.950",
        "TaxAmount":"2296.900",
        "SalesOrderItems":{
          "__deferred":{
            "uri":"http://vhcalnplci.dummy.nodomain:8000/sap/opu/odata/SAP/ZGW100_XX_S2_SRV/SalesOrderCollection('500000000')/SalesOrderItems"
          }
        }
      },
      {
        "__metadata":{
          "id":"http://vhcalnplci.dummy.nodomain:8000/sap/opu/odata/SAP/ZGW100_XX_S3_SRV/SalesOrderSet('500000001')</id>
    <title type="text">SalesOrderSetS2_SRV/SalesOrderCollection('500000001')</title>",
    <updated>2019-08-21T10:29:14Z</updated>     <category term="ZGW100_XX_S3_SRV.SalesOrder" scheme="uri":"http://schemasvhcalnplci.microsoftdummy.comnodomain:8000/adosap/2007opu/08odata/dataservices/scheme" />
    <link href="SalesOrderSetSAP/ZGW100_XX_S2_SRV/SalesOrderCollection('500000001')" rel="edit" title="SalesOrder" />,
         <content "type="application/xml">":"ZGW100_XX_S2_SRV.SalesOrder"
        <m:properties>},
        <d:SoId>500000001</d:SoId>"SoId":"500000001",
        <d:BuyerId>100000002</d:BuyerId>
        <d:BuyerName>DelBont Industries</d:BuyerName>"Note":"EPM DG: SO ID 0500000001 Deliver as fast as possible",
        "BuyerId":"100000002",
 </m:properties>      </content>
  </entry> "BuyerName":"DelBont Industries",
   <entry>     <id>http://vhcalnplci.dummy.nodomain:8000/sap/opu/odata/SAP/ZGW100_XX_S3_SRV/SalesOrderSet('500000002')</id>"CurrencyCode":"USD",
       <title type="text">SalesOrderSet('500000002')</title>
    <updated>2019-08-21T10:29:14Z</updated> "GrossAmount":"15117.760",
       <category term="ZGW100_XX_S3_SRV.SalesOrder" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />"NetAmount":"12704.000",
       <link href="SalesOrderSet('500000002')" rel="edit" title="SalesOrder" />"TaxAmount":"2413.760",
       <content type="application/xml">SalesOrderItems":{
      <m:properties>    "__deferred":{
    <d:SoId>500000002</d:SoId>         <d:BuyerId>100000005</d:BuyerId>"uri":"http://vhcalnplci.dummy.nodomain:8000/sap/opu/odata/SAP/ZGW100_XX_S2_SRV/SalesOrderCollection('500000001')/SalesOrderItems"
          }
<d:BuyerName>TECUM</d:BuyerName>       </m:properties> }
   </content>   </entry>},
  ...

Querying Data

GET: http://vhcalnplci.dummy.nodomain:8000/sap/opu/odata/SAP/ZGW100_XX_S2_SRV/SalesOrderCollection?$top=1&$select=BuyerName

...

EDM primitive type	CDAP Schema Data Type	Comment
Edm.Binary	bytes
Edm.Boolean	boolean
Edm.Byte	int	Unsigned 8-bit integer value
Edm.DateTime	timestamp	Datetime in the following format: 'yyyy-mm-ddThh:mm[:ss[.fffffff]]'
Edm.Decimal	decimal
Edm.Double	double
Edm.Single	float
Edm.Guid	string
Edm.Int16	int
Edm.Int32	int
Edm.Int64	long
Edm.SByte	int	Represents a signed 8-bit integer value
Edm.String	string
Edm.Time	time	Represents the time of day with values ranging from 0:00:00.x to 23:59:59.y, where x and y depend upon the precision
Edm.DateTimeOffset	timestamp	Represents date and time as an Offset in minutes from GMT, with values ranging from 12:00:00 midnight, January 1, 1753 A.D. through 11:59:59 P.M, December 9999 A.D Example 1: 2002-10-10T17:00:00Z

References

OData service

SAP Netweaver Gateway

Overview - https://blogs.sap.com/2013/01/24/a-simple-overview-on-sap-netweaver-gateway/
SAP Netweaver Gtw and OData Tutorials: https://sapyard.com/tutorials-on-odata-sap-netweaver-gateway/

Microsoft Data Factory - SAP ECC Connector

Plugin Type

Batch Source
Batch Sink
Real-time Source
Real-time Sink
Action
Post-Run Action
Aggregate
Join
Spark Model
Spark Compute

...

Versions Compared

Old Version 9

New Version 10

Key

Introduction

User Experience and Configuration

OData Service API

Service Metadata Document

Requesting EntitySet

Querying Data

References

Plugin Type

Page Comparison

Versions Compared

Old Version 9

New Version 10

Key

User Experience and Configuration

OData Service API

Service Metadata Document

Requesting EntitySet

Querying Data

References

Plugin Type