Expose your data via OData
PreviousConsuming your OData source with Excel 2016NextUsing OData to transfer data between runtimes
Last updated
Last updated
WEM supports OData V3. OData is an open protocol that allows you to consume or expose data through simple web requests. It builds upon the ideas of REST. Introduced by Microsoft in 2007, it is adopted by OASIS as a standard and supported by a large number of applications.
OData is comparable with ODBC and JDBC, as it allows you to communicate with an (often relational) database in a similar manner. It allows you to query, insert, edit or delete data, all through simple HTTP requests. An OData service shares the schema of the exposed data, which makes it possible for the client application to discover all the table and column definitions.
With WEM, you can both consume external OData sources and expose the database of your application as an OData service. In this blog post I will show you how to do the latter.
For this article we created a simple WEM application that stores product information about laptops, tablets and smartphones. This application is accessible via https://odata-demo.live.wem.io.
OData Demo Application:
Base URL: https://odata-demo.live.wem.io/odata/
Metadata: https://odata-demo.live.wem.io/odata/$metadata
Username:demo
Password: Demo1234!
Enabling OData for your WEM application is as simple as creating an OData Account for accessing your Project's Data. You can do this on the Project Settings page, in the Security Panel. Here you can create a new OData Login. Make sure that the password is secure, because these login credentials allows anyone with the information direct access to the data of your WEM application.
On the next step you can determine the level of access:
Full access: these credentials will allow Read, Update, Insert and Delete on all datalists in your project;
Restricted access: Define the proper access to specific lists and for specific actions
To be able to test the OData connection to your application via the Staging or Live URLs, you have to Publish your application to make the OData Login available in the Runtime.
After you have created the OData Login and published your application, you can test the OData service in the browser. Type in the URL of your application followed by /odata/. The browser will prompt you for the user credentials.
You should see an XML document that describes which collections (persistent lists) are available in your WEM application. You can also view the detailed schema information by using the Metadata URL (just append $metadata to the Base URL).
If you want to learn more about using OData directly from your browser, you can read the official documentation.
if you want to use Excel 2016, please read the article that deals with Excel 2016 specifically! The steps below don't work for Excel 2016.
Excel can connect to any OData source. This allows you to directly view the data, create pivot tables, generate charts, etc. Excel can not modify the exposed data, Microsoft never implemented that feature.
To connect to an OData source from Excel, go to the DATA tab, and choose From Other Sources and then From OData Data Feed. This will start the data connection wizard. The first step asks for the URL and user credentials of the OData connection.
The second step allows you to select which tables you want to connect to. For this example, I just select the Products table.
Excel stores the OData connection settings in an external .odc file. Afterwards you can reuse this connection via the toolbar icon Existing Connections. In the next step you can change the name and location of this file. You can also choose to store the login credentials in the file. For this example, I leave everything unchanged and click on Finish.
The final step allows you to configure how you want to show the data in Excel. For this example, I just choose Table. This will show all the rows that are stored in database of your WEM application.
When this is done, you should see your data in Excel. You can now sort and filter the data using the standard Excel features. Note that Excel won't automatically synchronize the data. To see all the updates since the last time the data was imported, you can click on the button Refresh All (found in the DATA tab).
