Count Query Performance

I have a query that seems simple but apparently is not.Any thoughts on performance optimization would be helpful.The following query works, but it takes 20 minutes to run.

WITH

MEMBER [Measures].[90-100] AS

'COUNT( FILTER( EXISTING [Student].[Student].[Student].Members, [Measures].[Avg Attendance] >.90)

)'

MEMBER [Measures].[80-90] AS

'COUNT(FILTER( EXISTING [Student].[Student].[Student].Members, [Measures].[Avg Attendance] >.80

AND [Measures].[Avg Attendance] <.90)

)'

SELECT

{[Measures].[90-100], [Measures].[80-90]} ON COLUMNS,

NON EMPTY EXISTING [School].[By District Area].[School].MEMBERS ON ROWS

FROM [Student Engagement]

There has got to be a better way. My guess is that somehow I need to limit the resulting set of schools * students that get counted to only the nonempties, but I’m not sure how to formulate the MDX.

Any ideas?

Chris

Instead of EXISTING in the calculated measures, which isn't needed unless other attributes of the [Student] dimension are placed on query axes, you could try NonEmpty(), like:

WITH

MEMBER [Measures].[90-100] AS

COUNT( FILTER( NonEmpty([Student].[Student].[Student].Members,

{[Measures].[AttendFactCount]}),

[Measures].[Avg Attendance] >.90)

)

MEMBER [Measures].[80-90] AS

COUNT(FILTER( NonEmpty([Student].[Student].[Student].Members,

{[Measures].[AttendFactCount]}),

[Measures].[Avg Attendance] >.80

AND [Measures].[Avg Attendance] <.90)

)

where [AttendFactCount] is some cube measure from the relevant measure group (assuming that [Avg Attendance] is a calculated measure like: [PresentSum] / [AttendFactCount]).|||

Deepak-

Thank you! Inserting the “NonEmpty” worked. The query returned in 5 seconds.

The question is why.Given, that I had “Non Empty” in the select statement and the NonEmptyBehavior(NEB) set to [Measures].[Absence] for the calculated member [Measures].[Avg Attendance] (Avg Attendance = Absence/ Enrollment) in the initial query,I would have assumed that the query engine would suppress the non empties automatically based on the NEB.Can you help me understand?

Chris

|||

Chris,

The “Non Empty” in the select statement wouldn't help because, if the 2 calculated measures are simply defined as counts, they could be 0, but never empty. Regarding the NEB, I'm not sure; but it could be that the query optimizer doesn't infer from the condition: [Avg Attendance] > 0.9 that [Avg Attendance] isn't empty. You could check whether changing the condition to: Not IsEmpty([Avg Attendance]) And [Avg Attendance] > 0.9 speeds up the query.