Mendix apps encourage the application of a services-oriented architecture, with multiple smaller services providing APIs and user interfaces for a specific set of data and logic. Enterprises build up complete solutions by assembling these services.
One important aspect of services is that all access to data and logic is handled by the service operations. Direct access to databases used for storing the service data is discouraged, because this would bypass the business rules and security handled by the service. This creates a challenge for generic reporting, data warehousing, and ETL tooling.
A new standard called OData being adopted more and more, as this enables generic data access within a services-oriented architecture. OData is “an open protocol to allow the creation and consumption of queryable and interoperable Restful APIs in a simple and standard way.“. In other words, it enables tools to use any REST/OData service by providing metadata that describes the data being provided and by standardizing the messages exchanged with the OData services.
A reporting tool like Tableau or Excel can discover what data and functionality is available in an OData service and provide a generic way for users to build new queries for the data.
This how-to will teach you how to do the following:
- Create a published OData service with resources
- Add the OData server to Tableau and Excel
- Combine resources in Tableau
- Create custom queries
Before starting this how-to, make sure you have completed the following prerequisites:
- Open the Company Expenses sample app from the Mendix App Store in the Modeler
- Add some expense reports to the app so that you have data to work with in this how-to
- Install Excel 2013
- Download and install Tableau (you can use the trial version for 14 days)
3 Creating a Published OData Service
A published OData service can be used by third-party applications to read data from a Mendix application. In this chapter, you will create and configure such a service.
- Open the Modeler and add a new folder to the Expenses folder called OData Services.
Right-click the OData Service folder and select Add > Published services > Published OData service:
Enter the name Expenses and click OK.
Go to the Resources tab and click Add published resource:
In the Edit published resource window, you can select an Entity as the data source for the OData service. Security in OData is managed by the project security settings and the entity level access rules; therefore, if you have already configured access rules in your app, you don’t have to configure it separately for OData.
In the Select Entity window, select the Expense entity and click Select:
In the Edit published resource window, do the following:
- Change the Exposed entity name to Expenses:
- Click Select for Exposed attributes and associations
In the Select members for entity window, select what values are exposed in the OData service, then click OK to save the resource:
Repeat steps 4–8 for the ExpenseType and Employee entities:
Click OK to save the OData service.
Restart the application. The OData service is now ready to be consumed.
4 Working with Mendix Data in Excel 2013
Open Excel and create a new blank workbook:
Open the DATA tab and select From Other Sources > From OData Data Feed:
On the Data Connection Wizard dialog box, do the following:
http://localhost:8080/odata/Published_OData_service/Expensesfor the Location of the data feed
- Select Use this name and password and enter the MxAdmin credentials
- Click Next
Select Expenses in the tables selector and click Finish:
On the Import Data dialog box, select Table as the import method and then click OK:
The data of the Mendix application should now be imported in Excel:
5 Working with Mendix Data in Tableau
To visualize data from the Company Expenses app in Tableau, follow these steps:
Open Tableau and select More Servers… > OData:
On the Server Connection dialog box, do the following:
http://localhost:8080/odata/Published_OData_service/Expensesfor the Server address
- Select Use a specific username and password and enter the MxAdmin credentials
- Click OK to save the server connection
Click OK to save the server connection. You should now see the data source details:
Click the name of the server connection and change it to Expenses for readability:
Repeat step 1–4 to add a server connection for
Open Sheet1 and drag Expense_Employee and Expense_ExpenseType from Measures to Dimensions:
Select Data > Edit Relationships… in order to define the relation between the different data sources:
On the Relationships window, do the following:
- Select Expenses for the Primary data source (1)
- Select Employees for the Secondary data source (2)
- Switch to Custom mapping (3)
- Remove any default mappings (4)
- Click Add… to configure a field mapping (5)
In the Add/Edit Field Mapping window, select Expense_Employee for the Primary data source field and ID for the Secondary data source field, then click OK to save the field mapping:
In the Relationships window, do the following:
- Select ExpenseTypes for the Secondary data source
- Switch to Custom mapping
- Remove the default mappings
- Click Add… to configure a field mapping
In the Add/Edit Field Mapping window, select Expense_ExpenseType for the Primary data source field and ID for the Secondary data source field, then click OK to save the field mapping:
In the Relationships window, click OK to save the relationships:
Select Expenses for the data source and drag the Amount attribute from the Measures section to Rows:
Select Employees for the data source, drag the FullName attribute from the Dimensions section to Columns, and click the icon next to the ID attribute to use ID as the linking field:
Select ExpenseTypes for the data source, drag the Name attribute from the Dimensions section to Color, and click the icon next to the ID attribute to use ID as the linking field:
You should now see a stacked bar chart with data combined over multiple data sources:
6 Filtering Data With Query Parameters
By default all the expenses are retrieved by Tableau, but Mendix allows you to add filters to the query so only the desired data is being retrieved.
To filter data with query parameters, follow these steps:
Right-click the Expenses data source and select Edit Data Source…:
Click the OData URL to change the connection settings:
?$top=3to the server URL (in order to only retrieve the first three expenses) and click OK:
Open sheet1, where you should now see a stacked bar chart with only the data of the three expenses:
You can combine filters by using the
&character. Repeat steps 1–4, but now use
http://localhost:8080/odata/Expenses/Expenses?$skip=3&$top=3as the server URL. You should now see a stacked bar chart showing the data of expenses four to six.
These are some other query examples:
7 Read More
- How to Consume a Complex Web Service
- How to Consume a Simple Web Service
- How to Export XML Documents
- How to Import Excel Documents
- How to Expose a Web Service
- How to Enable Selenium Support
- How to Synchronize User Accounts Using the LDAP Module
- How to Import XML Documents
- How to Consume a REST Service
- How to Expose Data to BI Tools Using OData
- Publishing OData Services
- OData Representation
- OData Query Options