olap4j. MDX (XMLA) query takes too long

Question

I have simple MDX query. It takes less than 1 second to execute it:

SELECT
NON EMPTY {  } ON COLUMNS,
NON EMPTY
  {[Z_OP].[LEVEL01].MEMBERS *
   [Z_NV].[LEVEL01].MEMBERS *
   [Z_DT].[LEVEL01].MEMBERS }
ON ROWS
FROM
  [Z_TEST/Z_TEST_REQ]

However when I use olap4j libraries it takes longer than 30 second to execute it. Here is my code:

Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");
final String query = "[query]";
final Connection connection =
    DriverManager.getConnection(
        "jdbc:xmla:Server=[URL]?sap-client=200;catalog=[catalog]",
        "[login]",
        "[password]");

final OlapConnection olapConnection = connection.unwrap(OlapConnection.class);

final CellSet cellSet = olapConnection
    .createStatement()
    .executeOlapQuery(query);

I found out it makes an additional 300 'discover' requests for obtaining metadata (for each value member/level/property etc.). Is it possible to disable or minimize the amount of these requests?

Answer 1

The three hierarchies may be large - if you have some measure that you could use along with the function NonEmpty then maybe you could add a WITH clause to make the sets smaller before hitting the SELECT:

WITH 
   SET [OP] AS NONEMPTY([Z_OP].[LEVEL01].MEMBERS, [MEASURES].[EssentialMeasureInCube])
   SET [NV] AS NONEMPTY([Z_NV].[LEVEL01].MEMBERS, [MEASURES].[EssentialMeasureInCube])
   SET [DT] AS NONEMPTY([Z_DT].[LEVEL01].MEMBERS, [MEASURES].[EssentialMeasureInCube])
SELECT
  NON EMPTY {  } ON 0,
  NON EMPTY [OP] * [NV] * [DT] ON 1
FROM
  [Z_TEST/Z_TEST_REQ];