Turn Data into Insight with Mendix OData Support

Mendix now offers a fast and simple way to enable access to the data in your Mendix applications that is compatible with a large number of third-party tools. Within one minute, you can get your Mendix data into Microsoft Excel, Tableau, and more.

What

OData is an industry standard that offers REST access to data. The way the data is presented, and can be queried, is standardized so all tools automatically know how to work with your data. In addition, OData REST services provide a standardized way to describe the data available. The result is that all tools can work with the service out of the box. OData is an open standard maintained by the OASIS group.

How

With the Mendix DX release, Mendix enables you to create OData services for persistent entities so you can read and query the data available in the entity. We’ve implemented a number of query options so you can specify what objects you want to retrieve from the entity.

In the Business Modeler, you can create an OData service and add a number of entities. You can specify the URI where the OData entities can be accessed. We will automatically ensure all the security and access rules you have defined on your entities are also applied when data is queried through the OData endpoint. In addition to the access rules, you can specify which role a user needs in order to access the data (for example, a reporting role).

As stated earlier, OData offers a number of standard ways to indicate what data you want to retrieve. You can specify that you want to retrieve all objects of an entity, one specific object, or a number of objects that match a filter.

Some use cases

There is a growing number of tools that support OData. The possibilities that this creates range from simple ad-hoc querying, to business intelligence, to statistical analysis, to big data science. Below we list a few examples.

Data analysis on small data sets

These days there is much ado about big data. However, there are still many cases where you want to do reporting or analysis on smaller amounts of data. There are a number of interesting tools that load a limited amount of data into the tool itself, and enable you to generate charts on this set of data.

How much data you can work with depends on the tool and the size of the machine running the tool: Microsoft Excel is limited to tens of thousands of records; Tableau can do more than 100,000 records; and analysis tools like R can handle even millions of records on a normal desktop PC. Using OData, you can retrieve all objects of an entity or limit the number of records by filtering on specific attribute values.

Some tools that support OData:

• Tableau – This tool comes in a number of versions. Tableau Public is a free desktop application that includes OData support. More options are available in Tableau Desktop. For larger datasets, Tableau Server can be used. You can publish Tableau dashboards online so you can use them in your online applications. You can also define OData dataset in Tableau Desktop and publish them to Tableau Online. Data will be fetched from Mendix into the Online environment of Tableau where you can build and view online dashboards based on up to date data from Mendix applications.
• Microsoft – As the original creator of oData, Microsoft has a large number of tools supporting oData. You can use oData datasets in Excel, automatically refresh this data, and generate graphs on the data. Microsoft Power BI is a data visualization tool similar to Tableau.
• Tibco Spotfire – Another interesting Business Intelligence tool is Tibco Spotfire. Its support for OData is quite mature. As illustrated in the picture below, Tibco Spotfire offers powerful relation management where you can specify how entities are related.

Statistical analysis on smaller data sets

One of the most widely used tools among data scientist is R. This tools has excellent facilities for statistical analysis and machine learning. It has a large library of open source plugins including functionality to load and use oData datasets. You can use the libraries httr and XML to retrieve and parse an OData dataset, as illustrated in the following screenshot. For more info on this, please check out our blogpost on using R with Mendix oData services, which will be published in the near future.

Updating a central data warehouse or reporting database

When you are working with large datasets, or very active reporting users, it’s best to offload the reporting to a dedicated reporting database or data warehouse. This can be achieved using existing ETL tools with support for OData or REST, or by creating an ETL script.

One example is SnapLogic. This is a cloud integration platform, where you can retrieve data from different sources, modify the data, and send it to a destination. SnapLogic offers a “REST Snap,” which you can configure to read data from a Mendix OData service. You can then load the data into a data warehouse, for example Amazon RedShift using the “Redshift insert Snap.”

Use of Mendix data in native mobile applications

OData services are also a great way to provide data for native mobile applications. Through OData, the application has a flexible query facility to retrieve the data required from a Mendix server into the application. And since OData is just REST, all mobile platforms can take advantage. If you also want to provide ways to modify data from a mobile client, you can create a number of services using the REST module or via webservices.

Conclusions

I hope by now you are as excited about this new Mendix feature as we are. It opens up limitless possibilities to do more with the data in your Mendix applications. We are very curious to know how you will use it in your solutions and what insight it will provide your users!