I'm have a MDX quey that should return about 60,000 rows with 83 columns. Mostly consisting of calculated measures

I have the following nonemptycrossjoi

NonEmptyCrossJoin
[DepartureDate].[Standard].[Date Value].Members --1829 member
,[ProcessDate].[Standard].[Date Value].Members -- 1829 member
,[Flight].[Standard].[Flight Number].Members -- 760 member
,[Aircraft].[Standard].[Iataaircraft Code].Members --7 member
,[DaysPrior].[Standard].Members --339 member
,{[Measures].[Authorization Level]
, 5

This performs extremely slow. I have a T-SQL based solution that takes about 30 minutes to calculate all column
and populate a table. The server is a 2way p3 2ghz with 2gb of ra

The MDX solution runs for about 3hrs before I cancel it

I have not done much in the way of optimizing the MDX server confi

Is there anything I can do to tune the query? or am I hitting a HW limitation
Any suggestions?

You might try creating a separate identical cube with only a single measure
(distinct count) that is a unique key for your data set. Ideally
aggregations should be at the top level only, but it works at the lowest
level ... just a little longer for queries. Create a new virtual cube over
the 2 cubes using the distant count cube for the totals with the values in
the original cube. This works fantastic on a similar set of aviation data I
manage with 17 million records. The distinct count cube has 58,000 unique
keys and takes about 15 min to process on a dual Pentium server. To create
the unique key I concatenate the combined values of various dimension
members into a long string that forms a unique key in a separate table. I
use an identity field as the index and create a new dimension from this
table that can now be used as a distinct count measure in the new cube. If
you have more than 64,000 unique keys you need to establish an interim level
of clustered keys to get around the analysis server limitations.





"Jason" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Calculated members cannot be used with the NonEmptyCrossJoin function. If
[Measures].[Authorization Level] is a simple formula, {[1]+[2]}, you could
rewrite the {[Measures].[Authorization Level]} as
{[Measures].[1],[Measures].[2]}. If it's not, your may have to use the
Filter function instead.

Jason" <anonymous (AT) discussions (DOT) microsoft.com> wrote

[Measures].[Authorization Level
is a normal measure so that is not an issue in this case; but good to know for future use

The cube processes fairly quickly, It is the query response that is the main proble
It runs in about 4 secs if I filter it down to just one day. It is when i try and query for all 338 processdates that it goes off into a deep sleep.

Since the query runs in only 4 secs for a single [DaysPrior] member,
which works out to 23 mins for 339 members, maybe nesting the cross-join
will speed it up?

NonEmptyCrossJoin(
[DepartureDate].[Standard].[Date Value].Members,
[ProcessDate].[Standard].[Date Value].Members,
[Flight].[Standard].[Flight Number].Members,
[Aircraft].[Standard].[Iataaircraft Code].Members,
{[DaysPrior].CurrentMember},
{[Measures].[Authorization Level]},5))


- Deepak

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