在jPivot中运行MDX查询需要很长时间

Question

我正在做一个商业智能项目。我使用的是mondian olap服务器和jPivot。我在模式文件中使用星型模型。当我单击向下钻取的最后一个加号按钮(来自右侧)时，mdx查询永远不会运行。在数据库中，只有大约5000条记录。我使用的是oracle数据库。第一个mdx查询是

SELECT NON EMPTY {[Measures].[Revenue]} ON COLUMNS,
NON EMPTY  ({([Stream].[All Stream],[Portfolio].[All Portfolio],[Serviceline].[All Serviceline], [Year].[All Year], [Month].[All Month], [Department].[All Department])}) ON ROWS
FROM [RevenueBudget]
WHERE ([Time].[201404] : [Time].[201508])

我已经尝试在schema work中执行查询(第四级)。几乎不需要40秒。去执行。我还使用java profiler检查了后台sql查询。但是大约在一分钟左右。那么，如果我是对的，那么为什么jpivot需要这么长时间才能显示记录，这就是jpivot的问题。任何帮助都被寄予厚望。

从JPivot MDX编辑器获得的第四级MDX查询如下

select NON EMPTY {[Measures].[Revenue]} ON COLUMNS,
NON EMPTY Hierarchize(Crossjoin({[Stream].[All Stream]}, Union(Crossjoin({[Portfolio].[All Portfolio]}, Union(Crossjoin({[Serviceline].[All Serviceline]}, Union(Crossjoin({[Year].[All Year]}, Union(Crossjoin({[Month].[All Month]}, {[Department].[All Department]}), Crossjoin({[Month].[All Month]}, [Department].[All Department].Children))), Crossjoin({[Year].[All Year]}, Union(Crossjoin([Month].[All Month].Children, {[Department].[All Department]}), Crossjoin([Month].[All Month].Children, [Department].[All Department].Children))))), Crossjoin({[Serviceline].[All Serviceline]}, Union(Crossjoin([Year].[All Year].Children, Union(Crossjoin({[Month].[All Month]}, {[Department].[All Department]}), Crossjoin({[Month].[All Month]}, [Department].[All Department].Children))), Crossjoin([Year].[All Year].Children, Union(Crossjoin([Month].[All Month].Children, {[Department].[All Department]}), Crossjoin([Month].[All Month].Children, [Department].[All Department].Children))))))), Crossjoin({[Portfolio].[All Portfolio]}, Union(Crossjoin([Serviceline].[All Serviceline].Children, Union(Crossjoin({[Year].[All Year]}, Union(Crossjoin({[Month].[All Month]}, {[Department].[All Department]}), Crossjoin({[Month].[All Month]}, [Department].[All Department].Children))), Crossjoin({[Year].[All Year]}, Union(Crossjoin([Month].[All Month].Children, {[Department].[All Department]}), Crossjoin([Month].[All Month].Children, [Department].[All Department].Children))))), Crossjoin([Serviceline].[All Serviceline].Children, Union(Crossjoin([Year].[All Year].Children, Union(Crossjoin({[Month].[All Month]}, {[Department].[All Department]}), Crossjoin({[Month].[All Month]}, [Department].[All Department].Children))), Crossjoin([Year].[All Year].Children, Union(Crossjoin([Month].[All Month].Children, {[Department].[All Department]}), Crossjoin([Month].[All Month].Children, [Department].[All Department].Children)))))))))) ON ROWS
from [RevenueBudget]

其中(Time.201304 : Time.201508)

模式XML文件是

<Schema name="RevenueBudget">
 <Cube name="RevenueBudget" cache="true" enabled="true">     
<Table name="MVW_DIMENSION_TRANSACTIONS">    </Table>   
 <Dimension name="Time" type="TimeDimension" foreignKey="DIMENSION_TRANSACTION_ID">  
 <Hierarchy hasAll="false" primaryKey="DIMENSION_TRANSACTION_ID">
  <Table name="MVW_DIMENSION_TRANSACTIONS"/>
  <Level name="YEAR_MONTH" column="YEAR_MONTH" type="Numeric" uniqueMembers="false"
      levelType="TimeYears"/>    
 </Hierarchy> 
 </Dimension> 
 <Dimension type="StandardDimension" foreignKey="STREAM_MASTER_ID" name="Stream">
  <Hierarchy  allMemberName="All Stream"  defaultMember="All Stream" hasAll="true" primaryKey="STREAM_MASTER_ID">
    <Table name="MVW_STREAM_MASTERS">
    </Table>
    <Level name="StrName" column="STREAM_DESCRIPTION" keyColumn="STREAM_MASTER_ID" nameColumn="STREAM_DESCRIPTION" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    </Level>
  </Hierarchy>
</Dimension>    
  <Dimension type="StandardDimension" foreignKey="PORTFOLIO_MASTER_ID" name="Portfolio">
  <Hierarchy allMemberName="All Portfolio"  defaultMember="All Portfolio" hasAll="true" primaryKey="PORTFOLIO_MASTER_ID">
    <Table name="MVW_PORTFOLIO_MASTERS">
    </Table>
    <Level name="PortfolioName" column="PORTFOLIO_DESCRIPTION" keyColumn="PORTFOLIO_MASTER_ID" nameColumn="PORTFOLIO_DESCRIPTION" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    </Level>
  </Hierarchy>
</Dimension>
   <Dimension type="StandardDimension" foreignKey="SERVICELINE_MASTER_ID" name="Serviceline">
  <Hierarchy allMemberName="All Serviceline" defaultMember="All Serviceline" hasAll="true" primaryKey="SERVICELINE_MASTER_ID">
    <Table name="MVW_SERVICELINE_MASTERS">
    </Table>
    <Level name="ServicelineName" column="SERVICELINE_DESCRIPTION" keyColumn="SERVICELINE_MASTER_ID" nameColumn="SERVICELINE_DESCRIPTION" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    </Level>
  </Hierarchy>
</Dimension>
<Dimension type="StandardDimension" foreignKey="YEAR_ID" name="Year">
  <Hierarchy allMemberName="All Year" defaultMember="All Year" hasAll="true" primaryKey="YEAR_ID">
    <Table name="MVW_YEAR"></Table>       
    <Level name="YearId" column="YEAR"  keyColumn="YEAR_ID" nameColumn="YEAR" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    </Level>
  </Hierarchy>
</Dimension>
   <Dimension type="StandardDimension" foreignKey="MONTH_ID" name="Month">
  <Hierarchy allMemberName="All Month" defaultMember="All Month" hasAll="true" primaryKey="MONTH_ID">
    <Table name="MVW_MONTH">
    </Table>
    <Level name="MonthName" column="MONTH" keyColumn="MONTH_ID" nameColumn="MONTH" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    </Level>
  </Hierarchy>
</Dimension>    
<Dimension type="StandardDimension" foreignKey="DEPARTMENT_MASTER_ID" name="Department">
  <Hierarchy allMemberName="All Department" defaultMember="All Department" hasAll="true" primaryKey="DEPARTMENT_MASTER_ID">
    <Table name="MVW_DEPARTMENT_MASTERS">
    </Table>
    <Level name="DepartmentName" column="DEPARTMENT_DESCRIPTION" keyColumn="DEPARTMENT_MASTER_ID" nameColumn="DEPARTMENT_DESCRIPTION" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    </Level>
  </Hierarchy>
</Dimension>
<Measure name="Revenue" column="REVENUE_AMOUNT" datatype="Numeric" aggregator="sum" visible="true"></Measure> 
</Cube>
</Schema>

Answer 1

我想知道将集合移动到WITH子句是否有帮助？

WITH 
  SET [TargetSet] AS 
      [Stream].[All Stream]*
      [Portfolio].[All Portfolio]*
      [Serviceline].[All Serviceline]*
      [Year].[All Year]*
      [Month].[All Month]*
      [Department].[All Department] 
SELECT 
  NON EMPTY 
    {[Measures].[Revenue]} ON COLUMNS
 ,NON EMPTY 
    [TargetSet] ON ROWS
FROM [RevenueBudget]
WHERE 
  [Time].[201404] : [Time].[201508];