CODEWITHSUNDEEP
performancemdxiccube
Arthur
Arthur

Reputation: 1704

MDX - large crossjoin with nonempty - how to optimize performance

I have built a model in icCube on top of a General Ledger codeblock, which has the following dimensions (not limitative):

With this model loaded in a tool Planning, there is a performance problem when you have more than 3 dimensions on the x-axes collapsed to the bottom level.

I was trying to check if icCube can handle this better, but the statement with 3 dimensions took me over 1700 seconds:

select [Dec] on 0
, non empty { Descendants([Account].[Account].[Total],,leaves) }
     * { Descendants([Activity].[Activity].[Total],,leaves) }
     * { Descendants([CostCenter].[CostCenter].[Total],,leaves) } on 1
from finance

The reason to have multiple dimensions on the rows is that users want to see as much details of the codeblock as possible, preferably the complete codeblock.

I was challenged by the fact that other tools can handle this kind of thing very easily since it does not have an OLAP database underlying but it queries directly on the data cells using hierarchies. Same performance is obtained when querying an extract of the the data in Excel (there are not so much rows of data).

Info on the data:

Any suggestion or hint how to solve this?

Upvotes: 5

Views: 2814

Answers (1)

ic3
ic3

Reputation: 7680

It's the classical problem in MDX, worth creating MDX antipatterns and putting it as the number 1.

The crossjoin you're calculating will produce 400x60000x500 = 12000000000 (12X10^9) tuples and we're asking to evaluate each of them. That makes a lot of evaluations per second.

Looks like a 'strange' way doing a drillthrough. I'd go for a drillthrough but let's try to solve this in MDX :

The solution is trying to reduce the number of tuples generated by performing a nonempty as soon as possible. So :

 noempty( noempty(A) x noempty(B) ) x noempty(C)
   or 
 noempty(A) x noempty( noempty(B)  x noempty(C) )

Using the first version with a few less nonempty :

select 
[Dec] on 0, 
nonempty( 
  nonempty( 
      Descendants([Account].[Account].[Total],,leaves)
    * nonempty( Descendants([Activity].[Activity].[Total],,leaves) , [DEC] )
  , [DEC] )
  * { Descendants([CostCenter].[CostCenter].[Total],,leaves) } 
, [DEC] )
on 1
from [finance]

In icCube you would create a Function that performs this operation to simplify the syntax :

 Function megaCrossjoin1(A,B,C,M) as nonempty( nonempty(A,M) * nonempty(B,M), M) * nonempty(C,M)

and use it

megaCrossjoin1( 
   Descendants([Account].[Account].[Total],,leaves) ,
   Descendants([Activity].[Activity].[Total],,leaves) ,
   Descendants([CostCenter].[CostCenter].[Total],,leaves) ,
   [Dec])

hope it helps

Upvotes: 4

Related Questions