I’ve worked with several companies taking their initial steps to SAP Fiori. Often, what happens is they install a few standard out-of-the-box apps to play around and see what they’re getting, then jump in and start to extend and create their own apps to meet the true needs of what the business is asking for. They produce SAPUI5 user interfaces and SAP Gateway Open Data Protocol (OData) services, roll out the apps, and take a well-deserved victory lap for simplifying processes.

However, what those companies don’t often see is that by creating those OData services, they’ve unlocked a broad set of capabilities that go well beyond connecting to SAPUI5 in SAP Fiori. They have created standards-driven APIs that open the doors of innovation.

I encourage you to think of OData services as an investment that goes beyond powering SAP Fiori applications. There are great opportunities to flexibly integrate the SAP back end with other software and devices, including many outside of the SAP system altogether.

I discuss the following details about using OData services for integrating disparate applications:

• A primer on OData, briefly focusing on its history and key design features.
• An example of using OData to enhance office productivity through the SAP Gateway  Productivity Accelerator for Microsoft’s Excel add-in.
• An example of using OData to develop connected applications through Google Apps Script and Google Sheets.
• An example configuration scenario for integrating cloud applications through Salesforce’s Lightning Connect tool.

OData Primer

It would be easy to think of OData as just the format for the data that is sent to an application somewhere on the web, but a deeper understanding can illuminate some powerful options. The best way to appreciate OData’s capabilities is to understand its history and design principles aimed squarely at re-use, standards, and powerful data description. Using OData for interoperability between applications through the web is in its core DNA. For more details, see https://blogs.msdn.microsoft.com/odatateam/2007/07/18/welcome/.

OData’s ancestry traces back to Microsoft in 2007. A sub-team in Microsoft’s SQL Server organization started a project called Astoria. Astoria was set up to ensure that applications could expose and consume data services both within corporate networks and the broader Internet. This is a beautiful illustration of executing on a core vision, because using OData today is a reflection of that goal.

In 2010, Microsoft publicly announced OData and released software development kits (SDKs) and tools focused on generating and consuming it. For a while Microsoft published updated spec versions under the Open Specification Promise, which guaranteed that it would not sue implementers of the specifications. However, as of OData v4.0, the protocol is standardized under the Organization for the Advancement of Structured Information Standards (OASIS). The effect of that standardization is that no one company or person owns the standards, and that a number of influential companies can contribute to growing the specifications. CA, Citrix, IBM, Microsoft, Red Hat, and SAP all participate in setting OData standards. Developers can be assured that using OData will not result in lawsuits or patent disputes, and they can focus on creating applications.

OData has lots of industry support – both from organizations that have contributed to the standard like SAP and Microsoft, and from open-source developers providing libraries like Apache Olingo and datajs (at https://olingo.apache.org/ and https://datajs.codeplex.com/, respectively). Those things are immensely helpful, but there are more benefits. As a data protocol, OData has several attractive features:

?       It follows the Representational State Transfer (REST) architecture pattern. The state of an entity is passed around independent of which client device or consumer is using it, meaning that servers implementing it don’t need to focus on device-specific data features.

?       It uses web-standard formats: XML-based AtomPub and optionally JavaScript Object Notation (JSON). These two formats are the current state of the art in web application data exchange, and make the creation of tools to use OData much easier.

?       Not only do major corporations contribute to the specification, but their products actively use it. SAP Gateway, Microsoft Azure, Salesforce Lightning Connect, and others make OData feeds a core part of the application experience.

Most importantly and fundamentally, it supports many activities developers want to do in a true data service. OData:

• Represents the structure of entities and base types of properties that make up those entities.
• Addresses resources in the data. An OData record specifies its key information for subsequent retrieval.
• Specifies relationships between entities in a data service. OData entities can navigate from one to another, and this navigation is completely specified in the metadata of any given service.
• Provides a standard set of options for querying data. The standard includes descriptions of mechanisms for filtering, ordering, and restricting field lists. SQL developers will recognize concepts much the same as standard SQL “SELECT,” “WHERE,” and “ORDER BY” in OData query operations.
• Includes mechanisms whereby applications can specify single actions per HTTP request or batch up multiple create, read, update, and delete (CRUD) activities in a single HTTP POST. This is extremely handy in using HTTP connections efficiently in mobile or performance-sensitive applications.                                                                                                                      

OData’s flexibility flows right into Fiori’s flexibility. Its ability to communicate the structure and relationships of application data enables Fiori to adapt to changes. The communication of metadata is a key point: no amount of UI beauty can overcome a poorly formed pipeline of data into an application.

Use Case: Office Productivity

One of the strongest indicators that OData is meant for open connectivity is that the protocol doesn’t describe how the data should be used. For example, users might not think of their Fiori applications as being at all related to their standard office productivity applications. With SAP’s Productivity Accelerator for Microsoft Excel add-in, however, they join forces.

OData services that power Fiori applications can be easily consumed by the Excel add-in and made available to the user in a familiar spreadsheet format. Data coming from these OData services can be sliced and diced, filtered, shuffled around, and even changed and uploaded back into the SAP system from the Excel user interface.

To set up the tool, first download and install the Visual Studio Tools for Office Runtime from Microsoft at https://www.microsoft.com/en-us/download/confirmation.aspx?id=48217. Next, get the tool itself at https://support.sap.com by signing in with your SAP Service Marketplace ID. Click the Download Software button under the search bar. In the next screen, type GWM Excel add on in the search field. (GWM refers to Gateway Microsoft.) Run the setup.exe program from the download, accepting default settings.

To use the tool with OData, open Excel and click the GWM ribbon tab. Click the Bind button in the toolbar. The first configuration step is to enter a service URL. Every OData service has a different URL to attach to it. The important thing is that you put the full path up to the name of the service in the chart, and nothing more. For example, if I wanted to access the entity set MaterialSet in my service, I would put https://mygateway.company.com/sap/opu/odata/sap/ZMATERIAL_MASTER_SRV/ in the bar and click Go. The tool discovers all the entity sets in the service, including MaterialMasterSet as shown in Figure 1.

Figure 1

GWM discovers collections within the base ZMATERIAL_MASTER_SRV service

In the next wizard screen, click the entity set you want to manage and click Next.

After that, you can set up filters against this entity set. Click the Add Filter button and choose one of the available filtering fields. In this example service, Plant is used to filter the MaterialSet (Figure 2). Under the Add column you can set up the values you want to include in the filter, such as my Plant SG01.

Figure 2

Entering a value to filter the collection to records with Plant SG01

Click the Apply button to set the filter, and then click the Next button to proceed to the final wizard screen. The final screen sets up where you want to send the data when it’s retrieved. Keep the defaults to put the results at the cell you have currently selected. Note that clicking Finish in the wizard won’t actually request the data for the spreadsheet. After you are done with the wizard, click the Fetch Records button in the ribbon to retrieve the records (Figure 3).

Figure 3

After clicking Fetch Records at the top, result data comes into the spreadsheet

Once you have the data in your spreadsheet, you can use all the common, familiar Excel functionality. If the OData service is set up for changing data, you can even change values in the cells and then click the Submit Changes button to send the data back to the SAP system for processing.

Use Case: Non-SAP Development

In the previous example I showed a packaged add-in capable of consuming OData. The tool is flexible—to a certain degree—in providing access to a wide range of OData services provided by an SAP Gateway server. However, often pre-packaged software does not meet users’ needs, and opportunities for really fine-tuning and enhancing a business process have to go beyond the base set of features in the pre-packaged software.

OData shines here too, allowing developers to work against a common specification and open standards in web connectivity. If users or developers want to dig deeper and really build something exactly for their needs, there are many tools that enable them to do so.

Let’s build a simple example application for Google Sheets to bring in OData from an SAP system. In my example code you see an OData call to a service in my system. Replace that with an appropriate OData call to your SAP Gateway system.

If you don’t have a Google account, sign up at https://accounts.google.com/SignUp. Once you’re signed up, navigate to https://sheets.google.com and start a new blank worksheet. In the Tools menu, select Script Editor. Paste the code snippet shown in Figure 4, which is in JavaScript, into the Code.gs file in Figure 5, the default opened file in the Google editor.

function onOpen() {
  //Set up a new menu option in the sheet's "Add-ons" menu
  SpreadsheetApp
    .getUi()
    .createAddonMenu()
    .addItem('Retrieve Material Information', 'getMaterialData')
    .addToUi();
}


function getMaterialData(){
  //Prompt for username and password
  var ui = SpreadsheetApp.getUi();
  var username = ui.prompt('Enter SAP user name', ui.ButtonSet.OK_CANCEL);
  var password = SpreadsheetApp.getUi().prompt('Enter SAP password', ui.ButtonSet.OK_CANCEL);
  
  if(username.getSelectedButton() == ui.Button.OK && 
     password.getSelectedButton() == ui.Button.OK){
    
    //Call the SAP OData service
    var materialResult = readService(username.getResponseText(), password.getResponseText());
    
    if(materialResult.getResponseCode() == 200){
      //Put the text in the spreadsheet
      writeResults(materialResult);
    } else {
      //Display a request error
      ui.alert('Error in request: ' + materialResult.getContentText());
    }
    
  } else {
    //User cancelled out of the user/pass dialogs, show this alert
    ui.alert('Data request cancelled - no username/password provided.');
    return;
  }
}

function readService(username, password){
  //Credentials need to be base64 encoded to go with Basic authentication
  var base64Credentials = Utilities.base64Encode(username + ':' + password);
  var basicAuthString = 'Basic ' + base64Credentials;
  
  //Set up the HTTP request headers and URL
  var options = { headers: { 'Authorization': basicAuthString } }; 
  var serviceUrl = "https://demo1.mindsetconsulting.com/sap/opu/odata/sap/ZMATERIAL_MASTER_DEMO_SRV/MaterialMasterSet?$filter=Plant eq 'SG01'&$format=json";
  
  //UrlFetchApp is Sheets' way of calling out to external web services
  return UrlFetchApp.fetch(serviceUrl, options);
}

function writeResults(materialResult){
  var currentSheet = SpreadsheetApp.getActiveSheet();
  
  //Get the names of the fields from the returned data, and remove
  //the technical '__metadata' field
  var materialData = JSON.parse(materialResult.getContentText());
  var fieldNames = Object.keys(materialData.d.results[0]);
  var metadataIdx = fieldNames.indexOf('__metadata');
  fieldNames.splice(metadataIdx, 1);
  
  //Build an array to hold all the values we've pulled from SAP.
  //Doing this first makes the final setting of values in the sheet 
  //faster than setting sheet values one at a time.
  var resultData = [];
  resultData.push(fieldNames);
  
  //Move through the results and put each one into the overall data array
  for(var i = 0; i < materialData.d.results.length; i++){
    var result = materialData.d.results[i];
    var resultItem = [];
    
    for(var j = 0; j < fieldNames.length; j++){
      var value = result[fieldNames[j]];
      resultItem.push(value);
    }
    
    resultData.push(resultItem);
  }
  
  //Set the values in the sheet, using the overall array's dimensions 
  //and starting at cell 1,1 in the current sheet
  var numRows = resultData.length;
  var numColumns = resultData[0].length;
  var range = currentSheet.getRange(1,1, numRows, numColumns);
  range.setValues(resultData);
}

Figure 5

The Google Apps Script code editor, with Code.gs active and open

Click the save icon and enter a name for the project you’ve just created. Back in your spreadsheet view, reload the page to incorporate your changes. Click the Add-ons menu, select your project name, and click the Retrieve Material Information option. Shortly, you see the spreadsheet populate with the data you’ve provided in your OData service as shown in Figure 6. SAP data is retrieved to the Google Sheet.

Figure 6

Results of running the demo script in Google Sheets

The beauty of Google Sheets is that once you have the data in the spreadsheet, it’s very easy to share and collaborate with colleagues to find answers in the data. You also have access to the data in your smartphone and tablet Sheets apps. Since the scripting language is JavaScript, you have a practically infinite number of ways to enhance the experience of using sheets to manage your SAP data.

Use Case: Lightweight Integration

Because OData is an agreed-upon standard, it’s easy for software-as-a-service (SaaS) providers to enable their tools to talk to one another. I describe using OData this way as lightweight integration because its best use is in synchronous, timely information sharing for user-facing applications, not as a massive bucket to transfer multiple terabytes of data between back ends.

Salesforce supports using OData in this way. It’s relatively painless to set up one of your SAP master data objects to be shared with your Salesforce applications. Let’s look at an example of sharing Business Partner information from an SAP system to Salesforce. Setting up this integration makes your SAP Business Partner data available to all your standard and custom Salesforce applications, thereby obviating the need for the end user to know multiple systems to log in and check partner information.

I show how to connect an SAP OData service to Salesforce through the developer portal. You can sign up for free to try this portal at https://developer.salesforce.com. When you’re signed in, go to the top right side, expand the drop-down menu with your name, and choose My Developer Account. On the top right side, click the Setup link to go to the developer administration console. In the Build menu on the left side, expand the Develop menu and click the External Data Sources option as seen in Figure 7.

Figure 7

External Data Sources setup in the Salesforce developer console in the menu path below Build

Click the New External Data Source button in the main view. The system then displays the screen shown in Figure 8.

Figure 8

External Data Source settings for connecting OData service to Salesforce

Input the following:

• External Data Source - a descriptive name of what it is. You may wish to put in some descriptive text to signify where it’s coming from too if you have many SAP back-end systems as potential sources.
• Name - the actual service name
• Type - OData 2.0 (the official OData protocol goes up to version 4.0, but SAP Gateway uses 2.0)  
• URL - the URL of the service. Do not point to any individual entity on the service, just the base service endpoint.
• The URL and Format settings can be fine-tuned for performance. Note especially the connection timeout, if your data service is processing or returning a large amount of data.
• The Format must be AtomPub
• Authentication. For demonstration purposes, it’s easiest to just use a named principal and show how things work. Choose Named Principal as the Identity Type. Choose Password Authentication as the Authentication Protocol. Enter your user name and password.

Once your OData service is incorporated into Salesforce, you can include live SAP data into your Salesforce applications and reports. For your users, the magic is that they don’t have to know that the data is coming from one or another place in particular—just that their business data is accessible and ready to go.

Think Big

Your OData services give you keys to unlock your SAP back-end data to a whole world of external applications. If you keep in mind the open nature of OData and the well-documented SAP Gateway capabilities, and make your company’s infrastructure smartly and securely open to allowing external API calls, you can immediately start to open all your enterprise applications to the valuable data in your SAP systems. Next time you’re dreaming up an application to solve a business problem involving SAP data, you can be confident that incorporating it directly into your application is just a REST call away.

Paul Modderman

Paul J. Modderman is senior product architect and technology evangelist for Mindset Consulting. He has more than 10 years of software development expertise, specializing in ABAP, ABAP Objects, .NET, Python, and Java. At Mindset, Paul ideates and develops innovative software products based on SAP, often joining SAP with cutting-edge cloud software such as Google for Work. Before joining Mindset, Paul developed software internally for several large enterprises. He has experience with Fiori, SAPUI5, SAP Gateway, Transportation Management (with BOPF), SAP QIM, Workflow, and standard RICEFW objects. Paul contributes regularly to the widely read Mindset blog.

You may contact the author at paulmodderman@mindsetconsulting.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.