title: MDX - large crossjoin with nonempty - how to optimize performance
body: <p>I have built a model in icCube on top of a General Ledger codeblock, which has the following dimensions (not limitative):</p>
  
  <ul>
  <li>Time</li>
  <li>Entity</li>
  <li>Cost center</li>
  <li>Account</li>
  <li>Intercompany party</li>
  <li>Project</li>
  <li>Activity</li>
  <li>Amount (this is the value)</li>
  </ul>
  
  <p>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.</p>
  
  <p>I was trying to check if icCube can handle this better, but the statement with 3 dimensions took me over 1700 seconds:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>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).</p>
  
  <p>Info on the data:</p>
  
  <ul>
  <li>Dimensions are pretty huge: 400 accounts, 6000+ activities, 50 entities, 500 CostCenters</li>
  <li>Dimensions Activity and Project are very flat (almost no structure)</li>
  <li>There are only 50.000 amounts, so data is very very sparse</li>
  </ul>
  
  <p>Any suggestion or hint how to solve this? </p>