Any suggestions would be greatly appreciated..
The below query returns results in less than 3 sec.'s.
However, when the application substitues the FuncUnitList set with '{[FuncUnit].&[All]}' the results take over a minute to come back.
This appears to be related to the filtering of SUM({Axis(0)},[Measures].[Activity Count])> 0)
Any help would be greatly appreciated..
-- MDX Query from sampleapp
WITH
SET [DateRangeSpan] AS
'{[DueDate].[YQMD].[All Dates].[2004].[Quarter 1].[February].[26] :[DueDate].[YQMD].[All Dates].[2004].[Quarter 1].[March].[25]}'
SET [FuncUnitList] AS
'{[FuncUnit].&[Merchandise/Media]}'
SET [ActivityList] AS
'{[Activity].[Activity].Members}'
SELECT
{[OrgUnit].&[4-040], [OrgUnit].&[4-040].CHILDREN} DIMENSION PROPERTIES [OrgUnit].[Org Type] ON COLUMNS,
Subset (
FiLTER (
CROSSJOIN (
{EXTRACT ( NONEMPTYCROSSJOIN ( [ActivityList], [DateRangeSpan] , [FuncUnitList] ), Activity) },
{[Measures].[Stores Included],[Measures].[Stores Complete],[Measures].[% Complete]})
, SUM({Axis(0)},[Measures].[Activity Count])> 0)
, 0, 150)
DIMENSION PROPERTIES [Activity].[Activity].[Event ID], [Activity].[Activity].[Process ID], [Activity].[Activity].[Activity ID],
[Activity].[Activity].[Event Name], [Activity].[Activity].[Process Name],
[Activity].[Activity].[Activity Due Date], [Activity].[Activity].[FuncUnit Name] ON ROWS
FROM [CompletionMonitor]
The below query returns results in less than 3 sec.'s.
However, when the application substitues the FuncUnitList set with '{[FuncUnit].&[All]}' the results take over a minute to come back.
This appears to be related to the filtering of SUM({Axis(0)},[Measures].[Activity Count])> 0)
Any help would be greatly appreciated..
-- MDX Query from sampleapp
WITH
SET [DateRangeSpan] AS
'{[DueDate].[YQMD].[All Dates].[2004].[Quarter 1].[February].[26] :[DueDate].[YQMD].[All Dates].[2004].[Quarter 1].[March].[25]}'
SET [FuncUnitList] AS
'{[FuncUnit].&[Merchandise/Media]}'
SET [ActivityList] AS
'{[Activity].[Activity].Members}'
SELECT
{[OrgUnit].&[4-040], [OrgUnit].&[4-040].CHILDREN} DIMENSION PROPERTIES [OrgUnit].[Org Type] ON COLUMNS,
Subset (
FiLTER (
CROSSJOIN (
{EXTRACT ( NONEMPTYCROSSJOIN ( [ActivityList], [DateRangeSpan] , [FuncUnitList] ), Activity) },
{[Measures].[Stores Included],[Measures].[Stores Complete],[Measures].[% Complete]})
, SUM({Axis(0)},[Measures].[Activity Count])> 0)
, 0, 150)
DIMENSION PROPERTIES [Activity].[Activity].[Event ID], [Activity].[Activity].[Process ID], [Activity].[Activity].[Activity ID],
[Activity].[Activity].[Event Name], [Activity].[Activity].[Process Name],
[Activity].[Activity].[Activity Due Date], [Activity].[Activity].[FuncUnit Name] ON ROWS
FROM [CompletionMonitor]