Slow NonEmpty query with 3 Dimensions

Question

Slow NonEmpty query with 3 Dimensions

Mossy-552515

SSCommitted

Points: 1921
More actions
February 25, 2008 at 10:45 pm

#120101

Hi,
Running the following query in AS - using 3 dimensions in the nonempty statement
select [Measures].[Appointed FTE] on 0,
NONEMPTY(
{[Employee].[Employee].[Employee].AllMembers
*[Position].[Position].[Position].AllMembers*[Paypoint].[Paypoint].&[20MMOI2 01]}
,[Measures].[Appointed FTE]) on 1
FROM [Employee Position]
WHERE ([Period].[Fiscal].[Fiscal Year].&[2007])
The query takes about 10 hours to complete in production. Number of members in the selected dimensions is as follows:
•Employee (160,000)
•Position (40,000)
•Paypoint (200)
Fact table has about 8 million rows.
Have tried splitting the query into 2 smaller queries and using the exists function which works quite well, however the afore mentioned query is generated by a reporting package (Panorama) used by end users and not really alterable. If 1 of the dimensions are removed to the filter it does run quicker as well (using 2 dimensions in the query is fine) but the users wish to report on the 3 dimensions as shown. Does anyone know of a way to speed up the nonempty query?? Is there a switch or property that can be specified?
Thanks in advance

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Pete Brown SSC Eights! Points: 984 More actions · Answer 1

Is the measure 'Appointed FTE' a calculated measure?

If so then the cube is doing the calculation and then checking to see if it is empty and it is doing this for all the combinations of the selected dimension members.

We have found by setting the option 'Nonempty behavior' for the calculated measure to be one of the base measures from the fact table that are used in the calculation improves the performance immensely.(e.g. a 10 minute query reduced to a sub second response) It would appear that the cube merely reads the base measure and if it is empty discards it rather than carrying out all the calculations.

Hope this may be of help.

Mossy-552515 SSCommitted Points: 1921 More actions · Answer 2

Thanks Pete,

I checked with the users and apparently none of the measures in the query are calculated measures. I have tried a few things with the non_empty_behavior property but unsuccessfully. I think you are right that speed would be fine if they were calculated - I will see what I can do to work around it.Thanks for the help - muchly appreciated mate.

Scott