Connecting Microsoft SQL Server Analytic Services (MS SSAS) to JasperReports Server's Ad-hoc

Why?

You've developed some cubes in MS SSAS and you want to consume them in Jaspersoft. This has always been possible from iReport to create static or parametrized reports and publish them to the server, in version 5.0 the ability to connect to JasperReports Server through the ad-hoc interface is enabled.

Preparation

You should first set up your Microsoft SQL Server to accept XML/A connections through IIS - this is not trivial and is largely outside of the scope of this article. One of the most useful sources of information was this article:

You should have 2 types of authentification Enabled: Windows Authentifiaction (to have ability to connect from Visual Studio) and Basic Authentification - Jaspersoft uses this to send user/password to the MS-SSAS server

In iReport, create a new connection of type 'XMLA Server' and input your URL and password. The URL if you set the server up correctly should be in this form: http://my-web-srv01/OLAP/msmdpump.dll Once you do this then press the "Get metadata" button. If all goes well you'll see a screen like this:

Only after this is working, move to the next step!

Making the Connection from the Server

After you've tested the connection with iReport, move on to the server. Find the folder you want to create the connection in, then right click on it and press "OLAP Client Connection"

The next screen should be filled out as follows, note that the red letters correspond to the letters from the iReport screenshot above, this was very helpful in discovering the information needed to fill out this screen

NOTE: the Data Source (in this case WIN-B3GSCF9OA47) should map to the IP address of the server.

Test this ahead of time by trying to ping it. If this doesn't work then add the host to your hosts file

Now save the connection and attempt to create an ad-hoc view from it, the tool should read the catalogs:

Troubleshooting

• Absolutely make sure that you can ping the server using it's fully qualified domain name (not it's IP).
• Make sure Basic authentication is enabled on MS SSAS
• Make sure you can reach your server in a web browser, you'll get an error but it should not be a 404 error or a time out (http://my-web-srv01/OLAP/msmdpump.dll)
• What does the log of MS SSAS say? Is there a failed connection attempt?

• Anything in the jasperserver.log? If not you can try to add:

  com.jaspersoft.jasperserver.api.metadata.olap.service.impl.OlapConnectionServiceImpl=DEBUG
      and
  com.jaspersoft.ji.adhoc.action.tree.OlapConnectionTreeProvider=DEBUG
  

  to log4j.properties to get more logging on the JasperReports Server side

• Be patient if you're on a remote connection, XML/A is not very fast and transfers lots of information over the wire, latency between Jaspersoft and Microsoft is relevant
• You can disable cache or change some related settings in the file ../WEB-INF/applicationContext-olap-connection.xml, this example effectively disables cache: 

<!-- olap4j caching config -->
<!-- see javadoc here http://www.olap4j.org/api/org/olap4j/driver/xmla/cache/XmlaOlap4jNamedMemoryCache.html -->
<property name="OLAP4J_CACHE" value="org.olap4j.driver.xmla.cache.XmlaOlap4jNamedMemoryCache" />
<property name="OLAP4J_CACHE_NAME" value="JasperServerXMLACache" />
<property name="OLAP4J_CACHE_MODE" value="LFU" />
<property name="OLAP4J_CACHE_TIMEOUT" value="1" />
<property name="OLAP4J_CACHE_SIZE" value="1" />

See Also

• JasperReports Server OLAP User Guide, Working with XML/A Connections
• http://www.olap4j.org/api/org/olap4j/driver/xmla/cache/XmlaOlap4jNamedMemoryCache.html