Beauty of OData

[hobru]

-------- UPDATE 20.11.2018 --------
Embedded a video with all required steps.
---------------------------------------------------------

I guess not everyone remembers that the OData protocol was initiated by Microsoft over ten years ago.

What is quite common today was a rather big thing a few years ago when Microsoft handed over OData to the OASIS committee  and started the success of the protocol. In the meantime Microsoft is one of the biggest open source contributors and SAP and Microsoft are now chairs of the committee and drive the collaboration forward.

My former colleague andre.fischer keeps updating you on all the new achievements that are released (e.g.  OData V4 code based implementation – Overview ) and lots of very cool things are happening around this: lots of OData services are available on the SAP API Business Hub, the SAP Cloud Platform can expose OData Service, the iOS and the Android SDK are leveraging OData -- and of course thousands of SAP Fiori applications are leveraging OData.

But sometimes, it is the easy things that can best show the beautify of OData. Of course compared to lots of applications that consume "plain" rest services, OData seems to come with a big overhead, but the benefits to sticking to an open standard can be quite obvious.

In my last year (actually it is also my first) at Microsoft I have spoken to hundreds (probably thousands if I count also my presentations during the last events) of customers in Germany I have mainly focused on the benefits of running your SAP applications on the Microsoft Azure cloud. Recently I have added a focus of showing the benefits of combining Microsoft Azure with the SAP Cloud Platform as a Service features (see also the demo that we developed together with Bridging-IT,  and showcased during SAPPHIRE ). How you can leverage the SAP Translation Hub, the SAP Cloud Platform Integration services or "just" the Web IDE to create SAP Fiori Applications that not only consume data from SAP, but also for example from Office 365.

During one of my last presentations I also talked about exposing SAP data to Excel and got the feedback that although the business always wants this, it is exactly what IT doesn't want, because "data immediately gets outdated, as soon as it is in Excel. While the data in SAP lives, the data in Excel is static.". This statement made me wonder and I did a quick demo that immediately turned everything around: now they are happy with the business to use Excel. Similar like you should "run the cloud right" you should also "use Excel right" 🙂

Let's start with a simple SAP Fiori application. For my demo I am going to use the SAP Developer System ES5. If you don't know it, then head over to the Tutorial Catalog and create a user. Once you have enabled your P- oder S-User you can call the SAP Fiori UI via https://sapes5.sapdevcenter.com/sap/bc/ui5_ui5/ui2/ushell/shells/abap/FioriLaunchpad.html#Shell-home.

In my example lets open the Manage Products page and click on Start.

Here you can see a list of products in a beautiful Fiori UI. Now if you want to get this data in Excel (for whatever reason), this can be done extremely simple. Since both SAP Fiori and Excel can consume OData services, you just need to find out the underlying service. There are multiple ways how you can do this (e.g. you should use the SAP Fiori Apps Library to find the right service), but you can also use the Developer tool in your browser. If I click on F12 in Chrome (I could also have used Edge ...) then you can quickly see the $batch service that is used by SAP Fiori (https://sapes5.sapdevcenter.com/sap/opu/odata/sap/SEPMRA_PROD_MAN/$batch).

Within this $batch service you can see the relative URL of the OData services that retrieves the Products (GET SEPMRA_C_PD_Product?...). This results in the "full" URL of https://sapes5.sapdevcenter.com/sap/opu/odata/sap/SEPMRA_PROD_MAN/SEPMRA_C_PD_Product

Now we will open Excel. From here you can just go to Data -> Get Data -> From Other Sources -> From OData-Feed.

In the next step enter the URL of the OData Service, https://sapes5.sapdevcenter.com/sap/opu/odata/sap/SEPMRA_PROD_MAN/SEPMRA_C_PD_Product

Now just go head (you could select which columns to exclude) and click on Load

As a result (scroll to the right to see the actual data) you have SAP data in Excel.

The benefit of connecting your SAP System to Excel via OData is that if the data changes in the SAP system,

you can just click on Refresh All and the latest data from SAP is available in Excel.

This means that wherever the Excel is stored, users just hit Refresh to update their data in Excel.

Just give it a try -- it is extremely simple, but the reaction that I got from customers was quite interesting 🙂