Expose Data Sources and Check if your Field is included

knutheusermann · ‎2015 Mar 12

The SAP Business ByDesign Operational Data Provisioning (ODP) Interface can be used to extract business data from ByDesign. The most popular use case is to connect ByDesign as source system to SAP Business Warehouse (BW).

You'll find a documentation of the integration scenario "Analytics Integration with SAP Business Warehouse" on SAP Business ByDesign – SAP Help Portal Page > Integration Scenarios.

One frequently asked question is, if the data of a specific ByD report or a specific report field can be extracted using ODP, including:

Which data can I extract using ODP?

How can I expose my data source via ODP?

Which fields are supported?

Below I explain how get those information via ByD UI or via ByD web services and finally provide an example to retrieve the data of a ByD data source using the ByD ODP interface.

Let's assume the following scenario:

You work with ByD analytics report Financial Statement Consolidation Preparation - Data Export and you want to know if this report and the included field G/L Movement Type can be extracted using the ByD ODP interface.

Furthermore you would like to know which data source (Operational Data Provider ID) includes the data of your analytics report.

Expose Data Sources and Check if your Field is included

Step 1:

Open report Financial Statement Consolidation Preparation - Data Export and check the report details (click on button "Details" on the upper right side).

Write down the data source used by the report, in our example this is data source FINGLAU01.

Step 2:

Open work center view Analytics > Design Data Sourcesand find the data source FINGLAU01.

Check if the data source is “Externally exposed” (only externally exposed data sources can be used by ODP):

If the data source is not yet exposed, you can expose it by button “Expose”.

If the data source is already exposed, you can use it in the ODP interface (incl. all fields).

Step 3:

Click on the documentation icon on the very right column to open the data source documentation with a list of all fields incl. field G/L Movement Type which we were looking for.

Exposing Joined Data Sources, Combined Data Sources and Cloud Data Sources:

Joined Data Sources and Cloud Data Sources cannot be exposed externally and hence cannot be extracted using the ODP interface.

The recommendation is to separately extract the part providers with appropriate selection criteria and join the data in the target system (for example SAP Business Warehouse).

Combined Data Sources can be externally exposed and extracted using the ODP interface if they are not composed of any Joined Data Source and Cloud Data Source.

Get Data Source Metadata via the ODP Interface

ByD supports Operational Data Provisioning via web service OperationalDataProvisioningIn. The following interface operations are relevant to implement a synchronous data extraction:

GetList

GetDetails

FetchDataDirect

The first two operations enable you to retrieve metadata incl. a list of exposed ByD data sources and the field structure of an exposed data source.

The 3rd operation can be used to fetch data from some data source.

You can create a user to access the interface OperationalDataProvisioningInby creating a communication arrangement for communication scenario "Analytics Integration".

Step 1:

Make sure that the data source FINGLAU01 is externally exposed (see chapter "Expose data source and check if your field is included" above).

Step 2:

Get the Operational Data Provider ID:

The operation GetList provides you a list of all exposed data sources.

The web service request

<n0:OperationalDataProvisioningGetListRequest xmlns:n0=”http://sap.com/bw”>

   <SubscriberTypeID>SAP_BW</SubscriberTypeID>

   <ContextID>BYD_MDAV</ContextID>

   <LanguageCode>EN</LanguageCode>

   <GradualIndicator>true</GradualIndicator>

</n0:OperationalDataProvisioningGetListRequest>

returns the operational data provider FINGLAU01$F.

Step 3:

Get the field structure of operational data provider FINGLAU01$F using operation GetDetails:

The web service request

<n0:OperationalDataProvisioningGetDetailsRequest xmlns:n0="http://sap.com/bw">

   <SubscriberTypeID>SAP_BW</SubscriberTypeID>

   <ContextID>BYD_MDAV</ContextID>

   <OperationalDataProviderID>FINGLAU01$F</OperationalDataProviderID>

</n0:OperationalDataProvisioningGetDetailsRequest>

returns the response xml with the field structure including field G/L Movement Type:

Retrieve ByD Data via ODP Interface

... and finally you can retrieve the ByD data via the ODP interface operation FetchDataDirect:

<n0:OperationalDataProvisioningFetchDataDirectRequest xmlns:n0="http://sap.com/bw">

   <SubscriberTypeID>SAP_BW</SubscriberTypeID>

   <ContextID>BYD_MDAV</ContextID>

   <OperationalDataProviderID>FINCACU16$F</OperationalDataProviderID>

   <SelectionStructure>

      <FieldID>COMPANY</FieldID>

      <InclusionExclusionCode>I</InclusionExclusionCode>

      <OperatorCode>EQ</OperatorCode>

      <LowerBoundaryDescription>00163E07381E1ED3BDF8E372EB316DFC</LowerBoundaryDescription>

   </SelectionStructure>

   <SelectionStructure>

      <FieldID>SETOFBKS</FieldID>

      <InclusionExclusionCode>I</InclusionExclusionCode>

      <OperatorCode>EQ</OperatorCode>

      <LowerBoundaryDescription>FISC</LowerBoundaryDescription>

   </SelectionStructure>

   <SelectionStructure>

      <FieldID>ACCYEARPER</FieldID>

      <InclusionExclusionCode>I</InclusionExclusionCode>

      <OperatorCode>EQ</OperatorCode>

      <LowerBoundaryDescription>2014009</LowerBoundaryDescription>

   </SelectionStructure>

   <AggregationStructure>

      <FieldID>COSTCTR</FieldID>

   </AggregationStructure>

   <AggregationStructure>

      <FieldID>CSTREVELE</FieldID>

   </AggregationStructure>

   <SupportXMLFormatCode>BAS_XML</SupportXMLFormatCode>

</n0:OperationalDataProvisioningFetchDataDirectRequest>

Note: The data source content included in the web service response XML is base64 encoded.

Some more Example ODP Requests

Employee master data (data source HCMEMPB😞

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:bw="http://sap.com/bw">

   <soapenv:Header/>

   <soapenv:Body>

      <bw:OperationalDataProvisioningFetchDataDirectRequest>

         <SubscriberTypeID>SAP_BW</SubscriberTypeID>

         <ContextID>BYD_MDAV</ContextID>

         <OperationalDataProviderID>HCMEMPB$P</OperationalDataProviderID>

         <!--Zero or more repetitions:-->

         <SelectionStructure>

            <FieldID>EE_ID</FieldID>

            <InclusionExclusionCode>I</InclusionExclusionCode>

            <OperatorCode>EQ</OperatorCode>

            <LowerBoundaryDescription>E0202</LowerBoundaryDescription>

         </SelectionStructure>

         <SupportXMLFormatCode>ABAP_XML</SupportXMLFormatCode>

      </bw:OperationalDataProvisioningFetchDataDirectRequest>

   </soapenv:Body>

</soapenv:Envelope>

Employee times (data source HCMTLMU01😞

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:bw="http://sap.com/bw">

   <soapenv:Header/>

   <soapenv:Body>

      <bw:OperationalDataProvisioningFetchDataDirectRequest>

         <SubscriberTypeID>SAP_BW</SubscriberTypeID>

         <ContextID>BYD_MDAV</ContextID>

         <OperationalDataProviderID>HCMTLMU01$F</OperationalDataProviderID>

         <!--Zero or more repetitions:-->

         <SelectionStructure>

            <FieldID>EMPLOYEE_UUID</FieldID>

            <InclusionExclusionCode>I</InclusionExclusionCode>

            <OperatorCode>EQ</OperatorCode>

            <LowerBoundaryDescription>0000000000011DEFBAD7B5F31A26CC36</LowerBoundaryDescription>

         </SelectionStructure>

         <SupportXMLFormatCode>ABAP_XML</SupportXMLFormatCode>

      </bw:OperationalDataProvisioningFetchDataDirectRequest>

   </soapenv:Body>

</soapenv:Envelope>

Cost Center (data source /MOM/COSTCTR😞

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:bw="http://sap.com/bw">

   <soapenv:Header/>

   <soapenv:Body>

      <bw:OperationalDataProvisioningFetchDataDirectRequest>

         <SubscriberTypeID>SAP_BW</SubscriberTypeID>

         <ContextID>BYD_MDAV</ContextID>

         <OperationalDataProviderID>/MOM/COSTCTR$P</OperationalDataProviderID>

         <SupportXMLFormatCode>ABAP_XML</SupportXMLFormatCode>

      </bw:OperationalDataProvisioningFetchDataDirectRequest>

   </soapenv:Body>

</soapenv:Envelope>

Company (data source /MOM/COMPANY😞

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:bw="http://sap.com/bw">

   <soapenv:Header/>

   <soapenv:Body>

      <bw:OperationalDataProvisioningFetchDataDirectRequest>

         <SubscriberTypeID>SAP_BW</SubscriberTypeID>

         <ContextID>BYD_MDAV</ContextID>

         <OperationalDataProviderID>/MOM/COMPANY$P</OperationalDataProviderID>

         <SupportXMLFormatCode>ABAP_XML</SupportXMLFormatCode>

      </bw:OperationalDataProvisioningFetchDataDirectRequest>

   </soapenv:Body>

</soapenv:Envelope>

G/L Account master data (data source FINGLAU17😞

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:bw="http://sap.com/bw">

   <soapenv:Header/>

   <soapenv:Body>

      <bw:OperationalDataProvisioningFetchDataDirectRequest>

         <SubscriberTypeID>SAP_BW</SubscriberTypeID>

         <ContextID>BYD_MDAV</ContextID>

         <OperationalDataProviderID>FINGLAU17$P</OperationalDataProviderID>

         <SupportXMLFormatCode>ABAP_XML</SupportXMLFormatCode>

      </bw:OperationalDataProvisioningFetchDataDirectRequest>

   </soapenv:Body>

</soapenv:Envelope>

G/L Account Period Totals / Balances (data source FINGLAU01😞

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:bw="http://sap.com/bw">

   <soapenv:Header/>

   <soapenv:Body>

      <bw:OperationalDataProvisioningFetchDataDirectRequest>

         <SubscriberTypeID>SAP_BW</SubscriberTypeID>

         <ContextID>BYD_MDAV</ContextID>

         <OperationalDataProviderID>FINGLAU01$F</OperationalDataProviderID>

         <SelectionStructure>

            <FieldID>COMPANY</FieldID> <!-- GUID 32 -->

            <InclusionExclusionCode>I</InclusionExclusionCode>

            <OperatorCode>EQ</OperatorCode>

            <LowerBoundaryDescription>0000000000011DDFB9FF7CEB3214ABF2</LowerBoundaryDescription>

         </SelectionStructure>

         <SelectionStructure>

            <FieldID>FISCYEAR</FieldID> <!-- numc 4 -->

            <InclusionExclusionCode>I</InclusionExclusionCode>

            <OperatorCode>EQ</OperatorCode>

            <LowerBoundaryDescription>2016</LowerBoundaryDescription>

         </SelectionStructure>

         <SelectionStructure>

            <FieldID>FISCPER</FieldID> <!-- numc 3 -->

            <InclusionExclusionCode>I</InclusionExclusionCode>

            <OperatorCode>EQ</OperatorCode>

            <LowerBoundaryDescription>003</LowerBoundaryDescription>

         </SelectionStructure>

         <SelectionStructure>

            <FieldID>SETOFBKS</FieldID> <!-- char 4 -->

            <InclusionExclusionCode>I</InclusionExclusionCode>

            <OperatorCode>EQ</OperatorCode>

            <LowerBoundaryDescription>9011</LowerBoundaryDescription>

         </SelectionStructure>

         <SupportXMLFormatCode>ABAP_XML</SupportXMLFormatCode>

      </bw:OperationalDataProvisioningFetchDataDirectRequest>

   </soapenv:Body>

</soapenv:Envelope>

G/L Account Items (data source FINGLAU02😞

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:bw="http://sap.com/bw">

   <soapenv:Header/>

   <soapenv:Body>

      <bw:OperationalDataProvisioningFetchDataDirectRequest>

         <SubscriberTypeID>SAP_BW</SubscriberTypeID>

         <ContextID>BYD_MDAV</ContextID>

         <OperationalDataProviderID>FINGLAU02$F</OperationalDataProviderID>

<!-- Some possible selection parameter:

  COMPANY_UUID (guid 32)

  SETOFBKS (char 4)

  FISCYEAR (numc 4)

  FISCPER (numc 3)

  GLACCT (char 10)

  POSTING_DATE (dats 😎

  CREATION_DATE (dats 😎

  CREATION_TIME (time 6)

  SA_CR_DATE_TIME Created on DateTime (char 23)

-->      

         <SelectionStructure>

            <FieldID>COMPANY_UUID</FieldID> <!-- GUID 32 -->

            <InclusionExclusionCode>I</InclusionExclusionCode>

            <OperatorCode>EQ</OperatorCode>

            <LowerBoundaryDescription>0000000000011DDFB9FF7CEB3214ABF2</LowerBoundaryDescription>

         </SelectionStructure>

         <SelectionStructure>

            <FieldID>SETOFBKS</FieldID> <!-- char 4 -->

            <InclusionExclusionCode>I</InclusionExclusionCode>

            <OperatorCode>EQ</OperatorCode>

            <LowerBoundaryDescription>9011</LowerBoundaryDescription>

         </SelectionStructure>

         <SelectionStructure>

            <FieldID>FISCYEAR</FieldID> <!-- numc 4 -->

            <InclusionExclusionCode>I</InclusionExclusionCode>

            <OperatorCode>EQ</OperatorCode>

            <LowerBoundaryDescription>2016</LowerBoundaryDescription>

         </SelectionStructure>

         <SelectionStructure>

            <FieldID>FISCPER</FieldID> <!-- numc 3 -->

            <InclusionExclusionCode>I</InclusionExclusionCode>

            <OperatorCode>EQ</OperatorCode>

            <LowerBoundaryDescription>003</LowerBoundaryDescription>

         </SelectionStructure>

         <SelectionStructure>

            <FieldID>GLACCT</FieldID> <!-- char 10 -->

            <InclusionExclusionCode>I</InclusionExclusionCode>

            <OperatorCode>EQ</OperatorCode>

            <LowerBoundaryDescription>241010</LowerBoundaryDescription>

         </SelectionStructure>

         <SelectionStructure>

            <FieldID>POSTING_DATE</FieldID> <!-- dats 8 -->

            <InclusionExclusionCode>I</InclusionExclusionCode>

            <OperatorCode>BT</OperatorCode>

            <LowerBoundaryDescription>20160314</LowerBoundaryDescription>

            <UpperBoundaryDescription>20160320</UpperBoundaryDescription>

         </SelectionStructure>

         <SelectionStructure>

            <FieldID>SA_CR_DATE_TIME</FieldID>

            <InclusionExclusionCode>I</InclusionExclusionCode>

            <OperatorCode>BT</OperatorCode>

            <LowerBoundaryDescription>20160314000000.0000000</LowerBoundaryDescription>

            <UpperBoundaryDescription>20160320000000.0000000</UpperBoundaryDescription>

         </SelectionStructure>

         <SupportXMLFormatCode>ABAP_XML</SupportXMLFormatCode>

      </bw:OperationalDataProvisioningFetchDataDirectRequest>

   </soapenv:Body>

</soapenv:Envelope>

G/L Balance Audit Trail (data source FINGLAU11😞

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:bw="http://sap.com/bw">

   <soapenv:Header/>

   <soapenv:Body>

      <bw:OperationalDataProvisioningFetchDataDirectRequest>

         <SubscriberTypeID>SAP_BW</SubscriberTypeID>

         <ContextID>BYD_MDAV</ContextID>

         <OperationalDataProviderID>FINGLAU11$F</OperationalDataProviderID>

         <SelectionStructure>

            <FieldID>GLACCT</FieldID>

            <InclusionExclusionCode>I</InclusionExclusionCode>

            <OperatorCode>EQ</OperatorCode>

            <LowerBoundaryDescription>102000</LowerBoundaryDescription>

         </SelectionStructure>

         <SupportXMLFormatCode>ABAP_XML</SupportXMLFormatCode>

      </bw:OperationalDataProvisioningFetchDataDirectRequest>

   </soapenv:Body>

</soapenv:Envelope>

In general I strongly recommend to carefully choose and set selection parameter as some data sources like for example journal entry items or project expenses may rapidly grow over time. You find a complete list of selection parameter in the data source metadata, that you get using interface operation GetDetails.

Some Trouble Shooting

Issue: Financial data sources (e.g. FINGLAU01) do not return any data

Using report “Trial Balance” you can see that the system shows data as expected.

However when you try to extract the underlying data source FINGLAU01 using the ODP interface, the system does not return any data and it looks like the data source is empty.

Solution:Financial data sources for balance reports require to set the mandatory selection parameter COMPANY, SETOFBKS, FISCYEAR and FISCPER (see example above).

Issue: ByD returns an internal error

The ByD Analytics integration scenario and the ByD ODP interface has some constraints with regards to maximum time allowed to process the data extraction, maximum size of memory allocated to extract the data in ByD and the number of data sources that can be accesses at the same time using the ODP interface. Please details in your ByD help center.

Access ByD Data Sources using the ByD ODP Interface

Expose Data Sources and Check if your Field is included

Get Data Source Metadata via the ODP Interface

Retrieve ByD Data via ODP Interface

Some more Example ODP Requests

Some Trouble Shooting

Issue: Financial data sources (e.g. FINGLAU01) do not return any data

Issue: ByD returns an internal error

Useful documents on SCN

Evolution of ABAP

Analytics in S/4HANA - real shape of embedded analytics and beyond embedded analytics