semenchikus
semenchikus

Reputation: 760

Pentaho reports with OLAP data source and SAP BW - poor performance

I have few BEx queries (OLAP cubes for third-party client) and .prpt reports in Pentaho that uses these BEx via OLAP data source (OLAP4J, MDX). When I execute these BEx in SAP BW or even directly as SOAP service (XML/A execute command via SoapUI), I'm getting result very fast, just 2-3 seconds.

But when I try to execute the same BEx queries in Pentaho I'm getting results after 1-2 minutes. It doesn't matter if I execute reports that are already published on Pentaho BI server or if I execute these reports in Report Designer. Caching is enabled.

MDX-query is a simple select.

As captured with Wireshark, Pentaho does multiple HTTP requests to get metadata about all existing cubes (and their measures too!) in current catalogue.

Capture of network packets in attachments. There are more than 400 HTTP requests that perform by Pentaho to execute just one MDX select statement.

It is OK that Pentaho tries to get metadata information by specified cube, but executing MDX query twice and getting metadata of all other cubes from catalogue is very unefficient.

Previously I read about similar problem with SSAS. But this information doesn't help with SAP XML/A provider. Changes in OLAP4J driver (in org.olap4j.driver.xmla.XmlaOlap4jCellSet and org.olap4j.driver.xmla.XmlaOlap4jCube) cause unstable rendering of reports.

So the main question is: how to improve performance?

[EDIT: link to image is broken]: "XML/A requests capture" https://docs.zoho.com/file/fc9kr673074aa4f7d409b8328d372cebd0f26

Upvotes: 2

Views: 231

Answers (0)

Related Questions