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 🙂

[BJarkowski]

Great post Holger, I didn't know it is possible.

I think it's a cool feature for all people who'd like to perform further analysis in Excel.

Too bad that getting the OData link is not straightforward (for non-tech users). SAP Fiori apps should contain a button which would generate Excel sheet connected to the data source through OData. It would be much simpler for end users. Something for the SAP Influence program 🙂

One more time thanks - I will let know my customers about this feature! 🙂

 

[hobru]

Thanks! Regarding the button: you can add $format=xlsx to the URl and get an Excel file, e.g. https://sapes5.sapdevcenter.com/sap/opu/odata/sap/SEPMRA_PROD_MAN/SEPMRA_C_PD_Product?$format=xls

The problem with that is that it is again "offline" and not linked directly to the OData service. But still an easy way to generate an Excel file.

 

[BJarkowski]

End users usually are not bothered about the OData source and asking them to go to debug mode is in my opinion not so great approach ? A simple button could make their live easier.

But of course that's something SAP would have to develop.

[nabheetscn]

Super Blog, this is awesome

[speri]

Awesome.. never knew it was this easy. Thanks!

[former_member241606]

Great information..Thanks for sharing.

[former_member537644]

Nice blog, connecting Excel to a OData service is one of the hidden gems of Excel and oData.

I used this a lot for my SIT and Meetup registration apps. User registered in the mobile / web UI5 app (even offline with sync was possible), and I used Excel to get the registration information. What I was missing is to have Excel not only consume the data (read only), but being able to write data back to the service.

If now this would also only work with Excel for Mac. On Mac, the option to import data from OData is not available.

[hobru]

Another great example of how OData can bring value! Nice showcase on leveraging Mobile / web UI5 apps and Office!

I agree that writing back information from Excel to the OData service is something that is missing. At SAP we used to have SAP Gateway for Microsoft which had a really nice Excel add-in that did enable this functionality. The main issue is the complexity in tracing which fields have changed and how to deal with conflicts.

I know there are a few third party solutions available which still allow you to write back.

And you can tell I am no Mac user... On my Windows laptop Excel is working fine 🙂

[douglasmaltby]

Thanks for an excellent post, Holger! Federation via OData within Excel is a great capability in the SAP ecosystem. E.g. in Central Finance where many users live in Excel.

I was able to easily and successfully consume the live ES5 SAP data via OData in Excel in Windows. Developer/debug isn't ideal, so exposing the OData source somehow would be nice. I also wasn't aware we had auth to edit product descriptions via the Manage Products Fiori app in ES5 to prove out the demo. Getting the service URL

Too bad MS hasn't enabled the OData feed in Excel for Mac, as Tobias said. I couldn't find any way around that (explored web query), so resorted to a remote Windows VM with Excel. I'd encourage MS to fill this OData gap in O365 for Mac, and enable write-back via the service.

Thanks again!

Doug

[hobru]

Doug, I will try to reach out to the colleagues in the Excel Mac team. Not sure why OData support isn't enabled there -- if I get some information I will let you know!

Holger.

 

[cyclingfisch_]

Hi Holger,

great blog!

I really liked SAP Gateway for Microsoft, because I really saw it as the tool that finally stops ABAP developers to discuss about any Excel interface they have to develop. It was a very useful productivity tool. Unfortunately SAP put a very expansive price tag on it and therefore it never reached the business users and in the end the product died... To access the OData service, already a SAP user is needed. I don't get why SAP needs to make money with Excel AddIns.

Cheers,

Martin

 

[whatsap]

Excellent Blog !

[joao_sousa2]

It is something quite obvious to a more technical person that creates Fiori Apps but you are completely right to point it out, as most people will miss this nice consequence of the separation of concerns between frontend (Fiori) and backend (OData).

If you really want to go a step further you can develop a Excel Add-on that connects to OData directly make a formal UI in Excel for more important services.

PS: Unlike the old Excel Add-ons the new ones are built on Javascript and are web based, so you no longer have problems updating local excel installations.

[hobru]

You are absolutely right -- and that's why I highlighted the OData piece of the integration story. Since both leverage the same standard you can connect these tools quite easily -- without any additional development.

It is quite obvious, but the feedback to this blog already shows how little known this is.

If you want to develop add-ins then I agree that the "new" web-based add-ons for Office that leverages JavaScript are a really nice thing. Actually when I was at SAP we had developed a few sample. They are pretty old, but the concepts are still valid.

• GitHub https://github.com/OfficeDev/OData-Excel-Integration-Add-In-Sample and


• Connecting Apps for Office with SAP - Part II: Excel App - https://archive.sap.com/documents/docs/DOC-50279

 

[joao_sousa2]

Yeah the "new" add-ons aren't so new anymore, but I remember the pains I had with he old framework and maintenance. I was basically the only SAP guy who could write C#, so when it came to updating the damn things it was always "Why didn't you do this in ALV Grid????".

You mention Fiori but I think the most important usage of this, is CDS exposed as OData which you get for free in consumptions CDSs.

 

 

 

[babu_kilari4]

Excellent Blog. Very Helpful !!

[Andre_Fischer]

The $format=xlsx has several limitations.

SAP Fiori Apps are now able to generate the Excel File themselves.

https://blogs.sap.com/2018/04/23/new-excel-export-functionality-available/

[ShyamuMurthy]

Awesome! Never knew this could be possible. Some of my customers will surely appreciate - many thanks!

[hobru]

Apparenty the Mac for Office team is already working on this -- among other topics. There is also a site where customers can vote on features, which helps prioritze topics. Feel free to head over to https://excel.uservoice.com/forums/304933-excel-for-mac/suggestions/8995483-add-support-for-get-transform-formerly-power-qu and give your vote.

Regards,

Holger.

(I just submitted my vote)

[smarchesini]

Thanks, good blog

[mushtaq_shaik]

Hi hobruche,

Excellent Blog.  Thanks.

I have a question, can we use this technique to leverage powerful excel charts dashboards generated dynamically on OData Feeds from SAP OData services. if you have any relevant blog on it kindly share.

Regards, 

Mushtaq Ahmed

[hobru]

Hi Mushtaq,

thank you! Once the data is in Excel, you can do all the charts and dashboard that you want. What I typically do is have one sheet where I get the data from the SAP OData service and on another sheet I do my dashboards. This allows me to easily update the data in Excel, which also updates the dashboards.

Regards,

Holger.

[former_member182874]

We also have export to excel feature available in smart tables in Fiori elements. User can enable this using SAP Visual editor in Web IDE.

[hobru]

That is a great feature! The only thing I am not so sure about -- I have not tested it for some time -- is that it again "only" dumps the data to Excel. There is no live-connection from the SAP Fiori elements table to Excel (like outlined in this example) as far as I remember.

So whereas here the Excel user can simply click on Refresh, the Export to Excel requires the user to open SAP Fiori first. This might not be a problem, but think about Excels floating around in your company which are all more or less outdated. With a simple click on Refresh you can get the latest data.

[former_member182874]

Correct, no live connection

[former_member634564]

Hi,

thanks for a great information,

I have a question: is it possible to make a post request for the sap demo site?https://sapes5.sapdevcenter.com/sap/bc/ui5_ui5/ui2/ushell/shells/abap/FioriLaunchpad.html#

i am trying to find a way to create a demo application.

 

[hobru]

Hi,

yes, you can also Post requests to the site. Obviously you need to be authenticated with your user. But keep in mind that this is a shared infrastructure.

If you want to work on your own system, using the Cloud Appliance Library might also be an option: https://cal.sap.com/

 

Regards,

Holger.

[Johnny_B_]

Dear Holger,

thank you for sharing this, others are as excited as I am.

Question: If I have a custom Business object in S/4HANA Cloud (just a flat table...) i can easily publish this as API. Now, following your tutorial, I could load the data easily into Excel. great.

But will it also work for UPDATING the data via the OData service? Especially for the custom BO, but potentially also using other existing APIs that enable WRITE operations.

What do you think?

Johannes

[hobru]

Hi Johannes,

updating from Excel is not possible via the steps mentioned above. However, you can do this with some custom development. One quick way is to use Script Lab for this. Maybe this video can help, https://www.youtube.com/watch?v=Rldcbmp5cno

Regards,

Holger.

 

[babu_kilari4]

Hi hobru2000

Is it possible to fetch the data via OData when the user authentication via federation is activated in an organization ? For example:- I do not logon to my SAP Fiori screen using SAP user id and password rather there is an internal federation that maps the SAP system to the corresponding intranet credentials. In this case, how to achieve this as I noticed in youtube video that you need to have a definite user id and password for the SAP system while trying to connect to the OData via Excel Data Source option ?

Appreciate your comment !

[hobru]

Unfortunately Out of the box authentication options in Excel are quite limited and you cannot extend it via this way.

If you have "special" authentication requirements, I would recommend to look at a custom add-in (like via Script lab as indicated here,  https://www.youtube.com/watch?v=Rldcbmp5cno

[vonholzen]

Hi, when creating such thing and sending the file to other users, it seems they can't refresh the data? Or is there anything needed to be done in addition?

 

thanks,

Thomas

[hobru]

They should be able to refresh. What happens if they click on the Refresh button? Or when they delete the whol content in Excel and refresh the page?

Regards,

Holger.

 

[vonholzen]

Hi Holger, thanks for your reply. They click "refresh al"l but nothing at all happens. If you have any ideas why that might be, I'm happy to hear.

 

Thanks,

Thomas

[Fidan]

This is amazing! Thanks for sharing hobruche !

Quick question, is there an option of reverse interaction from Excel to SAP? Like if the description of the item is changed in Excel will that somehow also update the description in SAP? 

BR

Fidan. 

[hobru]

Hi Fidan,

no, this scenario is only to read data from SAP into Excel. If you also want to write back data to SAP, you could do something like this: Using Power Automate with Excel to update SAP data - YouTube or use some Excel upload feature and work with the changed data.

Regards,

Holger.

 

[former_member782322]

Hi hobruche

I need to consume an entity from oData services in excel, that is not a problem since it is explained in this post.

The question is:
Can i consume an oData from excel when the implemented method is "get_expanded_entityset" and not "get_entityset"?

[hobru]

Hi Dario,

unfortunately I don't think that is possible at the moment. While I was able to load a URL like /sap/opu/odata/iwbep/GWSAMPLE_BASIC/SalesOrderSet?$expand=ToLineItems 

and see the table structure in the preview

It didn't load the actual data in the Excel sheet.

I guess if you want to do something like that you need to prepare the data upfront yourself.

Regards,

Holger.