MxBlog

Using external reporting tools with Mendix

Using external reporting tools with Mendix

Here's a short example how you use data stored in a Mendix application in a 3rd party reporting tool. And not just any tool. I'll show you how to use R, one of the most popular tools used by Data Scientists, used for statistical analysis, but also for Big Data machine learning. The bonus is that this popular tool is actually open source and free.

Lets see how we can start doing machine learning with the data in your Mendix application.

CSV Rest service

First step is to get access to the data in your Mendix application. You can easily download everything in an excel spreadsheet, but having real time access to the data makes it a bit easier.

In combination with R i find it easiest to provide a REST endpoint that exposes the data in comma separated value format. Using a Request Handler you can define a REST http endpoint that does this. (Or you could use the REST module and export the data in json format, which is also supported by R.)

Connecting Mendix with R using csv request handler

Here's how you can use such an endpoint with curl:

C:\> curl -X GET https://demo_user:sQIGwSVv83@orderapp101.mendixcloud.com/ws-doc/Orders/ProductLabels
Id,LabelId,Label
3096224743817217,1,"Car"
3096224743817218,2,"Truck"
3096224743817219,3,"Motorbike"
3096224743817220,4,"Bicycle"
3096224743817317,5,"4x4"

The basics of using Request Handlers in Mendix is explained here: Request Handlers at your service. The basic idea is to provide module and entity name in the url, query all records from this entity and return in csv format.

Parsing the url, asuming the simple situation /{module_name}/{entity_name}:

String pathInfo = iMxRuntimeRequest.getHttpServletRequest().getPathInfo().replace("/" + CsvExportHandler.serviceRoot, "");
logger.info("pathInfo: " + pathInfo);
String[] path = pathInfo.split("[\\/\\?\\#]");
for (int i = 0; i < path.length; i++) {
    logger.info("path, " + i + " " + path[i]);
}

Set content type:

Writer writer = iMxRuntimeResponse.getWriter();
...
iMxRuntimeResponse.setContentType("text/csv");
entityToCsv(context, writer, path[0], path[1]);

Loop through all records:

private void entityToCsv(IContext context, Writer writer, String moduleName, String entityName) throws CoreException, IOException {
    int recordIdx = 0;
    int pageSize = 10;
    int offset = 0;
    long lastId = 0;

    String xpath = "//" + moduleName + "." + entityName;
    /*
     * Determine number of records to fetch
     */

    long recordCount = Core.retrieveXPathQueryAggregate(context, "count(" + xpath + ")");
    LinkedHashMap<String, String> sortIdAsc = new LinkedHashMap<String, String>();
    sortIdAsc.put("ID", "Asc");

    /*
     * number of records may have changed during last fetch, check if have retrieved full pageSize of records
     * otherwise we have already fetched all records
     */
    while ((recordIdx < recordCount) && (recordIdx % pageSize == 0)) {
        /*
         * Retrieve small set of records to avoid running out of memory
         */
        String offsetXpath = xpath;
        if (lastId > 0) {
            offsetXpath += "[ID > 'ID_" + lastId + "']";
        }
        List<IMendixObject> objects = Core.retrieveXPathQuery(context, offsetXpath, pageSize, 0, sortIdAsc, 2);
        /*
         * write one line per object, comma separated values
         */
        for (IMendixObject obj : objects) {
            Map<String, ? extends IMendixObjectMember<?>> members = obj.getMembers(context);
            int memberCount = members.size();
            /*
             * Before first record, print header names
             */
            if (recordIdx == 0) {
                String prefix = "Id,";
                for (IMendixObjectMember objMember : members.values()) {
                    /*
                     * Include name of attribute. In case of association, remove module name
                     */
                    writer.write(prefix +
                            (objMember.getName().indexOf(".") > -1
                                    ? objMember.getName().split("[.]")[1]
                                    : objMember.getName()));
                    prefix = ",";
                }
                writer.write("\n");
            }
            /*
             * Output all attributes
             */
            String prefix = "";
            writer.write(String.format("%d,", obj.getId().toLong()));
            for (IMendixObjectMember objMember : members.values()) {
                writeAttributeValue(context, writer, prefix, objMember);
                prefix = ",";
            }
            writer.write("\n");
            recordIdx++;
            lastId = obj.getId().toLong();
        }
        offset += pageSize;
    }
}

Insert value of an attribute:

private void writeAttributeValue(IContext context, Writer writer, String prefix, IMendixObjectMember objMember) throws IOException {
    Object value = objMember.getValue(context);
    String csvValue = "";
    if (value == null) {
        csvValue = "";
    } else {
        logger.info("value: " + value + "," + value.getClass());
        if (value.getClass().equals(Date.class)) {
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ssXXX");
            csvValue = dateFormat.format(value);
        } else if (value.getClass().equals(String.class)) {
            csvValue = String.format("\"%s\"", value);
        } else if (value.getClass().equals(ArrayList.class)) {
            csvValue = "";
            logger.info("arrayList.size: " + ((ArrayList) value).size());
            ListIterator iter = ((ArrayList) value).listIterator();
            String arrayPrefix = "";
            while (iter.hasNext()) {
                MendixIdentifier o = (MendixIdentifier) iter.next();
                logger.info(" - arraylist: " + o);
                csvValue += arrayPrefix + o.toLong();
                arrayPrefix = ";";
            }
            csvValue += "";
        } else if (value.getClass().equals(Integer.class)
                || value.getClass().equals(Long.class)
                ) {
            logger.info("int/long = " + value);
            csvValue = String.format("%d", value);
        } else if (value.getClass().equals(MendixIdentifier.class)) {
            logger.info("mxid: " + ((MendixIdentifier) value).toLong());
            csvValue = String.format("%d", ((MendixIdentifier) value).toLong());
        } else {
            csvValue = String.format("%g", value);
        }
    }
    writer.write(prefix + csvValue);
}

We also need to add some security. This code just validates that the data is accessed using valid application credentials. You could also check for an application Role that allows REST access. What data you can see is determined by the access rules on your entities.

/*
 * Validate credentials
 */
IContext context = validateCredentials(iMxRuntimeRequest.getHttpServletRequest());
if (context == null) {
    iMxRuntimeResponse.setStatus(401);
    return;
}


/*
 * Validate mendix username password using basic authentication
 */
private IContext validateCredentials(HttpServletRequest httpServletRequest) {
    boolean hasCredentials = false;
    String authHeader = httpServletRequest.getHeader("Authorization");
    String username = null;
    String password = null;
    ISession session = null;
    IContext context = null;
    logger.info("authorization: " + authHeader);
    if (authHeader != null && authHeader.trim().startsWith("Basic")) {
        String base64Credentials = authHeader.trim().substring("Basic".length()).trim();
        String base64 = new String(javax.xml.bind.DatatypeConverter.parseBase64Binary(base64Credentials));
        String[] parts = base64.split(":");
        username = parts[0];
        password = parts[1];
    }
    logger.info("username: " + username + "," + password);
    if (username != null) {
        try {
            session = Core.login(username, password);
            context = session.createContext();
        } catch (Exception e) {
        }
    }
    return context;
}

Demo using curl from the command line

You now have an easy way to access the data in your application. Here's an example how you can use this from the command line using curl:

C:\Users\ako\projects\mendix\OrderApp-main_2\r-scripts> curl -X GET https://demo_user:sQIGwSVv83@orderapp101.mendixcloud.com/ws-doc/Orders/ProductLabels > out.csv
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 3700k    0 3700k    0     0   201k      0 --:--:--  0:00:18 --:--:--  212k

Use in R

library('httr')
secret <- RCurl::base64(paste('demo_user', 'sQIGwSVv83', sep = ":"));
req <- GET("https://orderapp101.mendixcloud.com/ws-doc/Orders/Address",config(httpheader = c("Authorization" = paste("Basic",secret))))
addresses <- content(req)
addresses

Using r-fiddle you can run this r script online.

Lets show the addresses on a map

Another interesting R package is sqldf. This enables you to run sql queries on your R dataset. You can use this to retrieve objects from entities in different Mendix applications, join the datasets and query the results.