{"id":7506,"date":"2019-07-27T06:34:32","date_gmt":"2019-07-27T06:34:32","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=7506"},"modified":"2020-05-27T08:55:31","modified_gmt":"2020-05-27T08:55:31","slug":"import-sap-s-4hana-odata-service-data-ms-access-via-odbc-driver","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/import-sap-s-4hana-odata-service-data-ms-access-via-odbc-driver\/","title":{"rendered":"Import SAP S\/4HANA OData Service Data Into Ms Access via ODBC Driver"},"content":{"rendered":"

Introduction<\/span><\/h2>\n

\"SAP<\/a><\/p>\n

In our previous blog we saw how to read JIRA data in SQL Server<\/a>. Now let\u2019s 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\u00a0ODBC XML Driver<\/a>\u00a0to read SAP data and load into MS Access.<\/p>\n

So, let\u2019s get started.<\/p>\n

\n

<\/h2>\n

<\/h2>\n

Requirements<\/span><\/h2>\n
    \n
  1. A first requirement, make sure that Microsoft Access installed<\/li>\n
  2. Finally, make sure to have\u00a0ZappySys ODBC PowerPack<\/a>\u00a0installed.<\/li>\n<\/ol>\n<\/div>\n

    About SAP HANA \/ OData REST API Service<\/span><\/h2>\n

    You can expose your SAP Data using ODATA REST API Service. Here is a\u00a0good article<\/a>\u00a0which shows how to expose data as OData Service.\u00a0 \u00a0For more information on SAP OData Service feature check this\u00a0SAP help page<\/a>. If you are new to OData Standard then\u00a0read here<\/a>\u00a0to 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.<\/p>\n

    https:\/\/services.odata.org\/Northwind\/Northwind.svc\/\r\nhttps:\/\/services.odata.org\/Northwind\/Northwind.svc\/Customers\r\nhttps:\/\/services.odata.org\/Northwind\/Northwind.svc\/Orders\r\nhttps:\/\/services.odata.org\/Northwind\/Northwind.svc\/Invoices\r\nhttps:\/\/services.odata.org\/Northwind\/Northwind.svc\/Products<\/pre>\n
    Now let\u2019s look at how to read SAP Data using ODBC Driver. At this point we assume you have exposed your data as OData Endpoint.<\/p>\n
    Read\u00a0SAP S\/4HANA data using XML Driver<\/span><\/h2>\n
    First let\u2019s 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.<\/p>\n
      \n
    1. First,\u00a0Windows search<\/strong>, write\u00a0ODBC<\/strong>\u00a0and select the\u00a0ODBC Data sources (32 bits)<\/strong>\n
    2. As a second step, in ODBC Data source Administrator press the\u00a0Add<\/strong>\u00a0button.\n
    3. In this step, create the new data source, select ZappySys\u00a0XML Driver.<\/strong>\n
    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},\u00a0 {MY-PROJECT}, {MY-TABLE})\n
      https:\/\/{MY-INSTANCE}\/sap\/opu\/odata\/sap\/{MY-PROJECT}\/{MY-TABLE}<\/pre>\n
      For example if you are hosting SAP HANA in Cloud Instance then your URL may look like below<\/p>\n
      https:\/\/myXXXXXX-api.s4hana.ondemand.com\/sap\/opu\/odata\/sap\/MyTestProject\/PurchaseOrders<\/pre>\n
      And create New ZS-HTTP connection in it select\u00a0Basic Authentication<\/a>\u00a0and enter your SAP HANA UserID \/ Password to call OData Service and select the desire filter.<\/p>\n
      \"XML<\/a>
      XML Driver : SAP S\/4HANA OData Service Configuration<\/p><\/div><\/li>\n
    5. Now go to Data Format \/ Compression (Zip\/GZip) tab and select Data Format as OData to get all the records.\n
    6. Finally, now using Query Builder and Code Generator we will generate the query and click on Preview the data.\n
    7. That\u2019s it we are ready to load SAP S\/4HANA OData Service data to MS Access.<\/li>\n<\/ol>\n
      Load SAP S\/4HANA OData Service in MS Access<\/h2>\n
        \n
      1. In MS Access, go to External Data<\/strong>\u00a0Ribbon and select\u00a0New Data Source<\/strong> and\u00a0select From Other Sources<\/strong> and\u00a0ODBC\u00a0Database<\/strong>.\n
      2. Select the Source and destination of the data, select\u00a0Import the source data into a new table in the current database<\/strong>\u00a0and press OK.\n
      3. Select your newly created JSON Driver Data Source and click\u00a0OK.
        \n<\/strong><\/strong><\/strong><\/p>\n
      4. Select tables from the list of tables in the Import Objects screen and click\u00a0OK.
        \n<\/strong><\/strong><\/p>\n
      5. Select Save Import Steps<\/strong> from the screen and click on Save Import<\/strong> button.\n
      6. That’s it If everything is OK, you will be able to see the data.\n
        \"Data<\/a>
        Data Imported Successfully in MS Access DB Table<\/p><\/div><\/li>\n<\/ol>\n
        How to link REST API data to Access<\/span><\/h2>\n
        The linking process is very similar to importing data into the Access table. Follow steps of the previous paragraph –\u00a0How to import REST API data to Access<\/a>\u00a0– but on step 2 select the second option to create a linked table<\/strong><\/em> <\/i><\/b>instead. Then at last it’s asked to select a unique record identifier, don’t select anything:<\/p>\n
        \"Link
        Linked table approach – Don’t select a unique Record identifier<\/p><\/div>\n
        SAP S\/4HANA Integration with Other BI Tools<\/span><\/h2>\n
        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.\r\n\r\n \r\n\r\n\"ZappySys\r\n\r\n\r\n\r\n\r\n
        \r\n
        BI \/ Reporting Tools\r\nIntegration<\/strong><\/p>\r\n<\/td>\r\n
        		ETL Tools\r\nIntegration\r\n<\/strong><\/td>\r\nProgramming Languages<\/strong>\r\nIntegration<\/strong><\/td>\r\n<\/tr>\r\n
        \r\n\r\n\r\n