olap4j。MDX (XMLA)查询花费的时间太长

Question

我有一个简单的MDX查询。执行它所需的时间不到1秒：

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]

但是，当我使用olap4j库时，执行它的时间超过30秒。下面是我的代码：

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);

我发现它发出了额外的300个'discover‘请求来获取元数据(对于每个值成员/级别/属性等)。是否可以禁用或最小化这些请求的数量？

Answer 1

这三个层次结构可能很大-如果您有一些度量可以与函数NonEmpty一起使用，那么也许可以添加一个WITH子句，使集合在命中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];