Import SAP S/4HANA OData Service Data Into Ms Access via ODBC Driver

Introduction

In our previous blog we saw how to read JIRA data in SQL Server. Now let’s learn how to Import SAP S/4HANA OData Service Data Into MS Access. SAP S/4HANA provides OData REST API interface to access data in your application using HTTP Protocol. We will use ODBC XML Driver to read SAP data and load into MS Access.

So, let’s get started.

About SAP HANA / OData REST API Service

You can expose your SAP Data using ODATA REST API Service. Here is a good article which shows how to expose data as OData Service.   For more information on SAP OData Service feature check this SAP help page. If you are new to OData Standard then read here to know more how OData can facilitate data extraction using HTTP REST API. If you need Sample XML based OData Service then use below test URLs.

Now let’s look at how to read SAP Data using ODBC Driver. At this point we assume you have exposed your data as OData Endpoint.

Read SAP S/4HANA data using XML Driver

First let’s look at steps to configure XML Driver. We will read data from SAP S/4HANA OData Service and then in next section we will look at how to load data into MS Access or other target.

1. First, Windows search, write ODBC and select the ODBC Data sources (32 bits)
   

   Open ODBC Data Source

2. As a second step, in ODBC Data source Administrator press the Add button.
   

   Add ZappySys

3. In this step, create the new data source, select ZappySys XML Driver.
   

   Add new zappysys xml driver

4. Now edit that XML data source to configure it. Enter your OData Service URL its typically like below. Replace 3 parts with your own value (i.e. replace {MY-INSTANCE},  {MY-PROJECT}, {MY-TABLE})
   
   https://{MY-INSTANCE}/sap/opu/odata/sap/{MY-PROJECT}/{MY-TABLE}

   1	https://{MY-INSTANCE}/sap/opu/odata/sap/{MY-PROJECT}/{MY-TABLE}

   For example if you are hosting SAP HANA in Cloud Instance then your URL may look like below
   
   https://myXXXXXX-api.s4hana.ondemand.com/sap/opu/odata/sap/MyTestProject/PurchaseOrders

   1	https://myXXXXXX-api.s4hana.ondemand.com/sap/opu/odata/sap/MyTestProject/PurchaseOrders

   And create New ZS-HTTP connection in it select Basic Authentication and enter your SAP HANA UserID / Password to call OData Service and select the desire filter.
   

   XML Driver : SAP S/4HANA OData Service Configuration

5. Now go to Data Format / Compression (Zip/GZip) tab and select Data Format as OData to get all the records.
   

   Data Format : Odata

6. Finally, now using Query Builder and Code Generator we will generate the query and click on Preview the data.
   

   Generate Query

7. That’s it we are ready to load SAP S/4HANA OData Service data to MS Access.

Load SAP S/4HANA OData Service in MS Access

1. In MS Access, go to External Data Ribbon and select New Data Source and select From Other Sources and ODBC Database.
   

   A configuration in MS Access: Import Data From REST API

2. Select the Source and destination of the data, select Import the source data into a new table in the current database and press OK.
   

   Get External Data – ODBC Database

3. Select your newly created JSON Driver Data Source and click OK.
   
   

   Get External Data – Select your newly created ODBC Data Source (JSON Driver)

    

4. Select tables from the list of tables in the Import Objects screen and click OK.
   
   

   Get External Data – Select tables from Import Objects Screen

5. Select Save Import Steps from the screen and click on Save Import button.
   

   Get External Data – Select Save Import Steps

6. That’s it If everything is OK, you will be able to see the data.
   

   Data Imported Successfully in MS Access DB Table

How to link REST API data to Access

The linking process is very similar to importing data into the Access table. Follow steps of the previous paragraph – How to import REST API data to Access – but on step 2 select the second option to create a linked table instead. Then at last it’s asked to select a unique record identifier, don’t select anything:

Linked table approach – Don’t select a unique Record identifier

SAP S/4HANA Integration with Other BI Tools

ZappySys ODBC Drivers built using ODBC standard which is widely adopted by industry for a long time. Which mean the majority of BI Tools / Database Engines / ETL Tools already there will support native / 3rd party ODBC Drivers. Below is the small list of most popular tools / programming languages our Drivers support. If your tool / programming language doesn't appear in the below list, which means we have not documented use case but as long as your tool supports ODBC Standard, our drivers should work fine.  

BI / Reporting Tools Integration	ETL Tools Integration	Programming Languages Integration
Microsoft Power BI Tableau SSRS (SQL Reporting Services) QlikView /Qlik Sense MicroStrategy Google Sheet Microsoft Excel Microsoft Access Oracle OBIEE Many more (not in this list).....	Informatica PowerCenter (Windows) Informatica Cloud SSIS (SQL Integration Services) SQL Server Talend Data Studio Pentaho Kettle Oracle OBIEE Many more (not in this list).....	Visual Studio C# C++ JAVA Python PHP PowerShell T-SQL (Using Linked Server)

 

Conclusion

So in this blog, we learned how to Import SAP S/4HANA OData Service Data in Access using ODBC XML / SOAP API Driver in a very simple way. You can achieve many more functionalities with this tool. Check our blogs/articles on XML File / REST API Driver to find out what this tool is capable of more.

References

Finally, you can use the following links for more information about the use of SAP S/4HANA OData Service with our tools:

• SAP API Business Hub
• Landing Page for ODBC XML / SOAP API Driver, you can also find Tutorial Video here.
• Help File: Documentation of XML Driver.
• Blog/articles link: https://zappysys.com/blog/category/odbc-powerpack/odbc-drivers/xml-soap-api-driver/