JasperReports & Infor PM OLAP

I’ve been working with Infor PM OLAP Server for almost four years now, I knew it when it was known as MIS Alea, before Infor took over MIS AG. It is one of those products you don’t select, a client has it and you have to work with it. I’ve always used Infor products, like Infor Office Plus and Infor Application Studio, in order to access to this OLAP database, but, last week, I tried to do it using my favourite reporting platform: JasperReports Server, so I’d like to share my findings.

First of all, i’d like to point out that Jaspersoft hasn’t certified this OLAP DB  as a source of data. It is obvious, but I must say that I just want to tell you my experience, so I am not responsible for any misfunction caused, if you try to do what I did.

Infor PM OLAP Server (10.1.7 version, in my case) is tightly coupled to Microsoft Windows. Therefore, the interaction with JasperReports has to run through a standard, in this case XML for Analysis (XMLA). It is difficult to find information about Infor PM OLAP products on the Internet, so I relied on the documentation of the DVD which has the software, specially the “Provider for Infor PM OLAP” manual.

The default installation of Infor had the XML interface up and running. I just had to change the name of the server in the file C:\Program Files\Infor PM\OLAP\XMLA\DataSources.xml, I replaced the default host, localhost, by the name of my server (for this post inforserver), I also enabled the Basic Authentication on the site InforPMOLAPXmla. After I restarted IIS, I browsed through the URL of the interface: http://inforserver/InforPMOlapXMLA/XmlaWebService.asmx

Later on, I started to work with iReport by creating an OLAP connection (option XMLA Server of the datasources dialog). I could test the connection and get the metadata I needed: datasource, catalog and cube. The problems began when I tried to execute a MDX query through iReport, I didn’t get an error but nothing happened.  I searched for a solution on the Internet and I realized that you have to perform the fields mappings manually, the JasperReports Ultimate Guide was very helpful at this point. After an effort, I had my first test report and, of course, it didn’t run:

Error filling print... XML/A fault: The server did not recognize the value of the header HTTP SOAPAction: "urn:schemas-microsoft-com:xml-analysis:Execute".; Code: Client;net.sf.jasperreports.engine.JRRuntimeException: XML/A fault: The server did not recognize the value of the header HTTP SOAPAction: "urn:schemas-microsoft-com:xml-analysis:Execute".; Code: Client;at net.sf.jasperreports.olap.xmla.JRXmlaQueryExecuter.handleResultFault(JRXmlaQueryExecuter.java:494)at net.sf.jasperreports.olap.xmla.JRXmlaQueryExecuter.parseResult(JRXmlaQueryExecuter.java:335)at net.sf.jasperreports.olap.xmla.JRXmlaQueryExecuter.createDatasource(JRXmlaQueryExecuter.java:130)at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1112)at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:689)at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1281)at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:900)at net.sf.jasperreports.engine.fill.JRFiller.fill(JRFiller.java:152)at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:464)at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:300)at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:757)

I researched the issue and, finally, I had to modify the source code (version 5.2.0) of the class net.sf.jasperreports.olap.xmla.JRXmlaQueryExecuter I changed the line 222 of the method createQueryMessage:

mh.setHeader("SOAPAction", "\"urn:schemas-microsoft-com:xml-analysis:Execute\"");

to

mh.setHeader("SOAPAction", "urn:schemas-microsoft-com:xml-analysis:Execute");

This is the great advantage of the open source! I compiled the source code, took the file jasperreports-5.2.0 and changed the one shipped with iReport. I tried a preview of my report and, ufs, another exception:

net.sf.jasperreports.engine.JRRuntimeException: Could not find dimension "MONTH" on axis 1.at net.sf.jasperreports.olap.JROlapDataSource.getDimensionIndex(JROlapDataSource.java:451)at net.sf.jasperreports.olap.mapping.MappingParser.getDimensionIndex(MappingParser.java:75)at net.sf.jasperreports.olap.mapping.MappingParser.tuplePosition(MappingParser.java:363)at net.sf.jasperreports.olap.mapping.MappingParser.member(MappingParser.java:263)at net.sf.jasperreports.olap.mapping.MappingParser.memberMapping(MappingParser.java:163)at net.sf.jasperreports.olap.mapping.MappingParser.mapping(MappingParser.java:133)at net.sf.jasperreports.olap.JROlapDataSource.init(JROlapDataSource.java:322)at net.sf.jasperreports.olap.JROlapDataSource.(JROlapDataSource.java:122)at net.sf.jasperreports.olap.xmla.JRXmlaQueryExecuter.createDatasource(JRXmlaQueryExecuter.java:141)at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1112)at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:689)at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1281)at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:900)at net.sf.jasperreports.engine.fill.JRFiller.fill(JRFiller.java:152)at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:464)at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:300)at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:757)

This time it was a bit more difficult to find the error, I had to debug and I found that Infor PM return the dimension name between [], so I modified the class net.sf.jasperreports.olap.JROlapDataSource, changing the line 431 of the method getDimensionIndex

if (dimension.equals(hierarchy.getDimensionName()))

to

if (dimension.equals(hierarchy.getDimensionName()) ||     (("[" + dimension + "]").equals(hierarchy.getDimensionName())))

Once I compiled and changed the file on iReport, I could run the report properly.

The next step was to move the report to JasperServer. The first task here was to replace the default jasperreports-5.2.0.jar  by the one I modified. After that, I restarted JasperReports Server. When I tried to run the report, I got the following exception:

net.sf.jasperreports.engine.JRRuntimeException: Message-Call failed. at net.sf.jasperreports.olap.xmla.JRXmlaQueryExecuter.executeQuery(JRXmlaQueryExecuter.java:310) at net.sf.jasperreports.olap.xmla.JRXmlaQueryExecuter.createDatasource(JRXmlaQueryExecuter.java:127) ...Caused by: com.sun.xml.internal.messaging.saaj.SOAPExceptionImpl: java.security.PrivilegedActionException: com.sun.xml.internal.messaging.saaj.SOAPExceptionImpl: Bad response: (401Unauthorized at com.sun.xml.internal.messaging.saaj.client.p2p.HttpSOAPConnection.call(HttpSOAPConnection.java:157) at net.sf.jasperreports.olap.xmla.JRXmlaQueryExecuter.executeQuery(JRXmlaQueryExecuter.java:306) ... 16 more

It was exasperating! According to this forum post, the cause rely on the JVM, so I adapted me to the circumstances and revert the first change I made on the source code (“\”urn:schemas-microsoft-com:xml-analysis:Execute\””). I know have two different versions of the file jasperreports-5.2.0.jar: one for iReport and one for JasperServer.