MDX - large crossjoin with nonempty - how to optimize performance

Question

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

• Time
• Entity
• Cost center
• Account
• Intercompany party
• Project
• Activity
• Amount (this is the value)

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:

• Dimensions are pretty huge: 400 accounts, 6000+ activities, 50 entities, 500 CostCenters
• Dimensions Activity and Project are very flat (almost no structure)
• There are only 50.000 amounts, so data is very very sparse

Any suggestion or hint how to solve this?

Answer 1

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