Skip to Content

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 🙂

To report this post you need to login first.

22 Comments

You must be Logged on to comment or reply to a post.

  1. Bartosz Jarkowski

    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! 🙂

     

    (0) 
      1. Bartosz Jarkowski

        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.

        (0) 
  2. Tobias Hofmann

    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.

    (2) 
    1. Holger Bruchelt Post author

      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 🙂

      (0) 
      1. Douglas Maltby

        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

        (0) 
        1. Holger Bruchelt Post author

          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.

           

          (2) 
          1. Holger Bruchelt Post author

            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)

            (0) 
      2. Martin Fischer

        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

         

        (1) 
  3. Joao Sousa

    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.

    (0) 
    1. Holger Bruchelt Post author

      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.

       

      (0) 
      1. Joao Sousa

        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.

         

         

         

        (1) 
  4. Mushtaq Ahmed

    Hi Holger Bruchelt,

    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

    (0) 
    1. Holger Bruchelt Post author

      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.

      (0) 

Leave a Reply