Calculated Members slow to display in Excel PivotTable

Hi,

I have created an Analysis Services 2005 cube, and am viewing it using an Excel PivotTable. When using Excel 2003, normal members display instantly, but when I place a calculated member in the PivotTable's data area it sits for several minutes before displaying. The calculated member (Gross Margin Percentage) was created with the expression:

[Measures].[Gross Margin]/[Measures].[Net Sale]

Net Sale is a data field from the Data Source View, while Gross Margin is a named calculation in the Data Source View (with the expression NetSale - Cost). When I use Excel 2000 the calculated member processes quickly. I cannot see any other option to give a percentage measure in the PivotTable, as I'm assuming that if I specify Gross Margin Percentage in the Data Source View it will not be aggregated correctly. Does anyone have any idea why Excel 2003 would give the slow performance, and whether there is any alternative way around it?

Thanks, Matt

Actually I prefer calculating the fields in a query and creating the cube using that query. Still slow? Create an index for that query Still slow? create a table. :) Hope it is MOLAP.|||

Hi, thanks for your response. The cube is fairly standard, being based on a few tables in my data warehouse. It is MOLAP. The reason I don't wish to calculate the Percentage field in a query is that the percentages will be aggregated incorrectly. For example if I had two records in the query as below:

Sales Margin MarginPercentage

10 5 0.5

20 5 0.25

Total 30 10 0.75

So as you can see the total margin percentage will give 0.75 when it should give 0.33. This is why I am using a calculated member in the cube rather than calculating the percentage within a query. I am wanting to know why this calculated member processes the same as all other members in Excel 2000, but is slow in Excel 2003. And also if there is anything I can do to get around the problem?

Thanks in advance, Matt

|||Nothing I can say. Ask the developpers of Excel 2003. :)|||

Did anyone tried with Excel 2007 ?

Philippe

|||

Could you please use Profiler to trace the MDX queries executed by Excel 2000, respectively Excel 2003, to understand the different between the queries?

Thank you

|||

A couple of thoughts:

i. Have you configured Non Empty Behaviour for the measure?

ii. I've had some problems with Excel 2003 hanging waiting for a query off a pivot table to execute. If I run the profiler and capture the MDX it still runs in a few seconds from BIDS. I'm guessing this particular issue is in the data transport layer because I get the same issue with ProClarity.

NWC