I have a query which returns some summary values for last Month, the
last 14 days, and a to date summary based on a specified dat, which is
always the previous saturday from when the query was run. This query is

ainfully slow and I realy do not know where to start in speeding it up.

It is hitting a cube which is partitioned at the month level and each
partition points to a seperate fact table.


WITH MEMBER [Time].[YMD].[To Date] AS ''AGGREGATE({[Time].[YMD].[All
Time].[2000].[January].[1]:[Ti*me].[YMD].[All
Time].[2005].[August].[6]})''
SELECT
{[Time].[YMD].[All Time].[2005].[July],[Time].[YM*D].[All
Time].[2005].[July].[24]:[Time*].[YMD].[All
Time].[2005].[August].[6],[Tim*e].[YMD].[To Date]} ON COLUMNS,
NON EMPTY NEST (
NEST (

[Product.DP].Members,[Product.*Title].Members
)
, {
[Measures].[Launches]
,[Measures].[Games Played]
,[Measures].[Players]
,[Measures].[Time Played]
,[Measures].[Avg Time per Player]
}
) ON ROWS
FROM [GameStats Activity]

Not knowing your cube design, my first question would be why are you doing a
NEST of a NON EMPTY NEST in your "on rows" statement? My first suggestion
here would be to do a NONEMPTYCROSSJOIN on rows. NONEMPTYCROSSJOIN is much
more effecient.

In other words, try the following for on rows:

NONEMPTYCROSSJOIN (
{[Product.DP].Members,[Product.Â*Title].Members}

, {
[Measures].[Launches]
,[Measures].[Games Played]
,[Measures].[Players]
,[Measures].[Time Played]
,[Measures].[Avg Time per Player]
}
) ON ROWS


The next question would be do you have aggretations in your cube based on
what you're asking for? Here it looks like you're going down to the day
level and the product dp and product title level in their respective
hierarchies. Do you have an aggregation for this combination of dims/levels?
If not, you might want to consider adding it to the partitions, if possible.

My next question would be why is [ToDate] at the day level? Does it have to
be there? The reason I ask, is that it might be (if there are aggregations
for it) faster to sum up all complete quarters and months (or whatever the
higher levels are in your time hierarchy) and just the remainder of days. In
other words, aggregate quarters 1 and 2, and then July, and then the first 6
days of August. I'm going on the premise of a normal calendar, but I think
you get my point.

Just a few suggestions.

Good luck.

- Phil


"Ledridge" wrote: