I’m running into severe performance issues when using calculated fields and not setting the NON EMPTY behavior property.

My cube consists of the following dimensions—customer (this is the largest at 3000 members), product, item, company, sales rep, time (split hierarchy -- calendar/fiscal)

The base measures coming from the fact table are budget volume, actual volume, budget gross revenue, actual gross revenue.

I’ve created a fiscal year to date actual volume calculated field that consists of the following MDX:
Sum(PeriodsToDate([Time].[Fiscal].[Year]), [ActualVolume])

My cube is very sparse. I can’t utilize the NON EMPTY behavior property because if it is set to Actual Volume for the FYTD Actual Volume calculated field, it will evaluate the FTYD Actual volume calculated field as empty if the Actual Volume field is empty. This results in data being excluded in situations where the Actual Volume is null, but the Year to Date Volume is not null.

When the NON EMPTY behavior property is turned off, the query doesn’t return for a long period of time (30 plus minutes). Performance decreases as more members are displayed.

We are using Crystal Analysis off of a MSAS cube.

What are my options in this situation?