Hello all,

The below MDX almost does what is needed - it provides data to an Excel OWC
report in a format that mimics the output of a tsql "group by with rollup"
statement.

The trouble is that rather than the <5 second performance needed, this is
taking multple 10's of seconds on a new, fast multi-proc machine.

i would really appreciate any tips or suggestions as to what in the MDX
below is taking so long, and what can be used instead.

VisualTotals seems much faster, but the resuting data output isn't correct
(so far).

Thanks,

Larry Clark

========================

WITH

SET [Dates] AS
'{[Sales_Date_Dim].[Dat Date].[Sep 30 2005],[Sales_Date_Dim].[Dat Date].[Sep
29 2005],[Sales_Date_Dim].[Dat Date].[Sep 28 2005],[Sales_Date_Dim].[Dat
Date].[Sep 27 2005],[Sales_Date_Dim].[Dat Date].[Sep 26
2005],[Sales_Date_Dim].[Dat Date].[Sep 25 2005],[Sales_Date_Dim].[Dat
Date].[Sep 24 2005],[Sales_Date_Dim].[Dat Date].[Sep 23
2005],[Sales_Date_Dim].[Dat Date].[Sep 22 2005]}'

MEMBER [Sales_Date_Dim].[Date Total] AS 'AGGREGATE([Dates])'

SET [Locations] AS
'{[Sales_Location].[Location Name].[Oklahoma City,
OK],[Sales_Location].[Location Name].[Pampanga],[Sales_Location].[Location
Name].[Panama City, Panama],[Sales_Location].[Location Name].[Pasig City,
Philippines],[Sales_Location].[Location Name].[Roseburg,
OR],[Sales_Location].[Location Name].[Round Rock,
TX],[Sales_Location].[Location Name].[San Salvador, El Salvador]}'

MEMBER [Sales_Location].[Location Total] AS 'AGGREGATE([Locations])'


SET [Queues] AS
'{[Sales_Queue].[Queue Id].[OS],[Sales_Queue].[Queue
Id].[SQ],[Sales_Queue].[Queue Id].[SQO],[Sales_Queue].[Queue Id].[SQOB]}'

MEMBER [Sales_Queue].[Queue Total] AS 'AGGREGATE([Queues])'


SET [SubQueues] AS
'{[Sales_SubQueue].[Sub Queue Desc].[Outbound Super
Queue],[Sales_SubQueue].[Sub Queue Desc].[Sales Help
Desk],[Sales_SubQueue].[Sub Queue Desc].[Sales Manager],[Sales_SubQueue].[Sub
Queue Desc].[SALES QUALITY],[Sales_SubQueue].[Sub Queue Desc].[Segmentation -
XPS]}'

MEMBER [Sales_SubQueue].[SubQueue Total] AS 'AGGREGATE([SubQueues])'


SET [Area Managers] AS
'{[Sales_Area_Manager].[AreaManager Name].[Woods,
Tiger],[Sales_Area_Manager].[AreaManager Name].[Couples,
Fred],[Sales_Area_Manager].[AreaManager Name].[Gates, Bill],
[Sales_Area_Manager].[AreaManager Name].[King,
Steven],[Sales_Area_Manager].[AreaManager Name].[Ballmer,
Steve],[Sales_Area_Manager].[AreaManager Name].[Williams,
Vanessa],[Sales_Area_Manager].[AreaManager Name].[Walken,
Chris],[Sales_Area_Manager].[AreaManager Name].[Fuentes, Daisy]}'

MEMBER [Sales_Area_Manager].[Area Manager Total] AS 'AGGREGATE([Area
Managers])'


SELECT
{[Measures].[Calls],[Measures].[Hold Time],[Measures].[W
Time],[Measures].[Adj
D/Hr],[Measures].[T],[Measures].[RegRate],[Measures].[RegRate],[Measures].[Reg1Plus],[Measures].[Complaince
%],[Measures].[Complaince D%],[Measures].[Complaint
Hours],[Measures].[ComplianceHit],[Measures].[ComplianceMiss],[Measures].[Complaint
Hours],[Measures].[Dead Duck %],[Measures].[Dead Aux Duck
%],[Measures].[FooTime%],[Measures].[FooTimeMins],[Measures].[Efficiency
%],[Measures].[HoursWorked],[Measures].[IN
%],[Measures].[LCTime%],[Measures].[LDCalls],[Measures].[LDTime%],[Measures].[LDTimeMins],[Measures].[LD/D%],[Measures].[OUT
%],[Measures].[Productive D%],[Measures].[Productive
Hours],[Measures].[PcsSold],[Measures].[Queue%],[Measures].[Scheduled
%],[Measures].[Scheduled D Hours],[Measures].[Signed
Pct],[Measures].[SignedHrs],[Measures].[Staff Hours],[Measures].[Talk
Time],[Measures].[Utility
%],[Measures].[LocalCalls],[Measures].[FoolCalls],[Measures].[Reg],[Measures].[Reg3Plus],[Measures].[Reg2Plus]
} on columns

, non empty {HIERARCHIZE
{{[Dates]}
*
{[Queues]}
*
{[SubQueues]}
*
{[Area Managers],[Sales_Area_Manager].[Area Manager Total]}
}+
{
{[Dates]}
*
{[Queues]}
*
{[Sales_SubQueue].[SubQueue Total]}
*
{[Sales_Area_Manager].[Area Manager Total]}
}+
{
{[Dates]}
*
{[Sales_Queue].[Queue Total]}
*
{[Sales_SubQueue].[SubQueue Total]}
*
{[Sales_Area_Manager].[Area Manager Total]}
}+
{
{[Sales_Date_Dim].[Date Total]}
*
{[Sales_Queue].[Queue Total]}
*
{[Sales_SubQueue].[SubQueue Total]}
*
{[Sales_Area_Manager].[Area Manager Total]}
}
)
} on rows

FROM Sales_AgentCombined_Cube

WHERE ([Sales_Location].[Location Total])

CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

If the rows are sparse, has the Non Empty Behavior Property been set for
all the calculated measures (or can it be)?

http://support.microsoft.com/default...b;en-us;304137
Keyword
...
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.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***