Using MS SSAS as OLAP View in JasperReports

The Microsoft ® SQL Server Analysis Services (SSAS MS) provide the OLAP service of Microsoft SQL Server ® since version 2000 (Shiloh) and also in its later versions 2005 (Yukon) and 2008 (Katmai). Though the next version is available by SQL Server 2012 (Denali), SQL Server 2008 R2 (Kilimanjaro) is still the up-to-date version that is used in this evaluation.

An ongoing project had the demand of representing the OLAP cube from SSAS 2008 R2 in JasperReports server. This could be evaluated positively with the JasperServer Pro version 4.5. According to the provided documents,  Jasper focuses on Mondrian OLAP connections. However, XML/A connections are explicitly provided as a connection option by the vendor. XML/A connections can be provided by MS SSAS using “HTTP Access to SQL Server Analysis Services on IIS 7.0″ by providing the msmdpump.dll DLL as an ISAPI extension (Internet Server Application Programming Interface). Instructions on how to provide this service can be found on the Microsoft Developer Network (MSDN) at http://msdn.microsoft.com/en-us/library/gg492140.aspx.

The integration in iReport is no problem with the help of XML/A connections.

Unfortunately, it can create reports that can be run by iReport, but not be uploaded to the Jasperserver. The attempt of uploading results in the error message:
“Unsupported language: You are trying to upload a report on JasperServer XMLA Which uses the mdx query language. This language is not supported by JasperServer and it is outdated. You should use the language mdx instead. ”

The iReport with its repository navigator can upload XML/A connection to the JasperReports Server. This process runs without error, but the resulting repository entries of type “XML-A-Verbindung” or “XML-A connection” are of no use for subsequent OLAP queries, since the entries for the catalog and data source are erroneous. It leads to the error message:
“com.jaspersoft.jasperserver.api.JSException: com.tonbeller.jpivot.olap.model.OlapException: Malformed data source given for determining XML/A provider”

The solution is to suggest the “SQL Server Analysis Services” as Mondrian data source to JasperReports. The theoretical correct data source (e.g., provider = MSOLAP.4; Data Source = localhost; Initial Catalog = AdventureWorksDW2008R2) is not recognized and raises the error message:
“com.jaspersoft.jasperserver.api.JSException: com.tonbeller.jpivot.olap.model.OlapException: Unexpected data source determining XML/A provider”is acknowledged. Instead, a Mondrian data source (e.g., “Provider = Mondrian; DataSource = Adventure Works DW 2008R2″) with the URI of the SSAS service (e.g., http://localhost/olap/msmdpump.dll) that is provided by MSMDPUMP.dll leads to the desired result. A working XML/A connection is shown in the following screenshot:

As an example, I provide an OLAP cube that is navigable in JasperReports. It uses an example MDX query of the AdventureWorks sample data:
SELECT
{ [Measures].[sales Amount],
[Measures].[Tax Amount] } ON COLUMNS,
{ [Date].[Fiscal].[Fiscal Year].&[2008],
[Date].[Fiscal].[Fiscal Year].&[2009] } ON ROWS
FROM [Adventure Works]
WHERE ( [sales Territory].[southwest] )

http://blog.itransparent.de/wp-content/uploads/2012/05/OLAP.png

Using this workaround, MS SSAS can be used as an OLAP data source by JasperReports.

Here are some pitfalls that occurred during this evaluation:
com.jaspersoft.jasperserver.api.JSException: com.tonbeller.jpivot.olap.model.OlapException: java.lang.Exception: Schwerwiegender Fehler beim Parsen von MDX: Couldn’t repair and continue parseUngültiges Symbol “FROM”
This error might raise, if the parser of the fields provider cannot process the MDX query. This might also happen with MDX queries that can be run in the Microsoft SQL Server Management Studio without errors.