MDX - Great Crossbreeding with Nonempty - How to Optimize Performance

Question

I built the model in icCube on top of the General Ledger code block, which has the following sizes (non-limiting):

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

With this model, loaded into the Planning tool, there is a performance problem when you have more than three dimensions on the x axis, folded to the lower level.

I tried to check if icCube could handle this better, but a statement with 3 dimensions took me more than 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 for having multiple dimensions in rows is because users want to see as many details of the code block as possible, preferably a complete code block.

I was questioned by the fact that other tools handle this very easily, since it does not have an OLAP database, but it directly queries data cells using hierarchies. The same performance is obtained when querying data retrieval in Excel (not many rows of data).

Data Information:

• The sizes are quite huge: 400 accounts, 6000+ operations, 50 objects, 500 CostCenters
• Dimensions Activity and project are very flat (almost no structure)
• Only 50,000 amounts, so the data is very sparse.

Any suggestion or hint how to solve this?

Answer 1

This is a classic problem in MDX, it is worth creating MDX antiprocessors and putting it at number 1.

The cross join that you are calculating will produce tuples of size 400x60000x500 = 12000000000 (12X10 ^ 9), and we ask you to evaluate each of them. This makes a lot of estimates per second.

Looks like a “weird” way of doing exercises. I would choose drillthrough , but try to solve this problem in MDX:

The solution attempts to reduce the number of tuples generated using 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 non-empty:

 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 will 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 this helps