MDX query takes forever to run in jPivot

Question

I am working on a BI project. I am using mondian olap server and jPivot. I am using star model in schema file. The mdx query takes forever to run when I click on the last plus button for drilldown (coming from the right). In the DB there are only around 5000 records. I am using oracle database. The first mdx query is

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

I have tried executing the query(fourth level) in schema work bench. It hardly takes 40 sec. to execute. I have also checked the background sql queries using java profiler. But its well around one min. So why it is taking so long for jpivot to display the records, if I am right its the problem of jpivot. Any help is highly expected.

The fourth level MDX query got from JPivot MDX editor is below

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]

where ([Time].[201304] : [Time].[201508])

The schema XML file is

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

whytheq whytheq · Accepted Answer · 2015-09-04T09:33:11

I wonder if moving the set to the WITH clause helps?

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

MDX query takes forever to run in jPivot

1 Answers