Dear all,

I would like to know whether there are some methods to improve the speed of query in Excel. Coz I spent a few second to get the result using the following MDX statement. However, it took about 15 mins to get the same result in Excel.


MDX statement:
WITH
MEMBER [Measures].[Net Amount] AS
'[Measures].[Gross Amount] - [Measures].[Discount Amount]',
SOLVE_ORDER = 1
MEMBER [Measures].[Unit Price] AS
'[Measures].[Net Amount] / [Measures].[Qty]',
SOLVE_ORDER = 2
select
{ crossjoin({[Branch].[Store A],[Branch].[Store B], [Branch].[Store C],
[Branch].[Store D], [Branch].[Store E], [Branch].[Store F],
[Branch].[Store G]},
{[Measures].[Qty], [Measures].[Unit Price],[Measures].[Net Amount]})}
on columns,
non empty{ nonemptycrossjoin({[Supplier].[Suppcname].members}, {[Item Category].[Chicken].children})} on rows
from
[Receiving Datawarehouse]
where
([Delivery Date].[2004].[June], [Section].[All Section].[Kitchen] )

Please help me to improve the speed! Thanks in advance!

Best Wishes,
Polly

Presumably Excel is not generating NonEmptyCrossJoin() for the rows,
causing the MDX query to take longer. If both the calculated measures:
[Net Amoount] and [Unit Price] should be empty when [Gross Amount] is,
then setting their Non Empty Behavior Property to [Gross Amount] should
help:

http://groups.google.com/groups?hl=e...cLMNk%24DHA.14
52%40TK2MSFTNGP09.phx.gbl
Subject: Re: Problems with Calculated member -- Correction
View: Complete Thread (4 articles)
Original Format
Newsgroups: microsoft.public.sqlserver.olap
Date: 2004-02-28 13:40:56 PST


Hi Subhash,

This is the best [MS] documentation that I found:

http://support.microsoft.com/default...b;en-us;304137
Keyword

View products that this article applies to.

This article was previously published under Q304137
SUMMARY

In some cases, a query slows down considerably when you use both the NON
EMPTY keyword on an axis of a Multidimensional Expression together with
a calculated member.

This article describes how you can optimize a query, by using the Non
Empty Behavior property for the calculated member.

MORE INFORMATION

Use of the NON EMPTY keyword on a MDX statement causes the calculated
member to evaluate the calculated member's expression for each member to
determine whether or not the member is empty. The extra time taken for
the evaluation is what causes the NON EMPTY keyword to slow down the MDX
query.

To optimize a query that uses the NON EMPTY keyword, set the Non Empty
Behavior property to a base measure of the cube, so that if the base
measure is empty the calculated member's value is considered empty and
the expression is never evaluated, which increases the query
performance.

How to Enable the Non Empty Behavior Property

To enable the Non Empty Behavior property, use these steps:
Right-click the Sales cube, and then click Edit.

In the Cube Editor, scroll down to the Calculated Members folder.

By default, the Calculated Members folder is expanded. Select the
calculated member Sales Average, and then click Properties to open the
Properties pane for the Sales Average calculated member.

In the list of properties, locate the Non Empty Behavior drop-down list
box and click Store Count.
Save the cube.

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Dear Deepak Puri,

Thanks for your reply.

Using Non Empty Behavior Property can improve the speed of getting "Measures" information in Excel. However, when I dragged "Supplier" and "Item Category" to row, the speed was still slow. Is there any way to improve the speed of this part? Thanks!

Best Wishes,
Polly

Polly,

Defining the "Non Empty Behavior" Property for ALL the calculated
measures included in a pivot table usually results in significant
savings in query time, WITH multiple dimensions on rows (ie.
cross-joins). Did you find no noticeable difference in query time after
doing so?


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!