MDX very slow looking for alternative syntax

Below is an example of MDX syntax used on our DTS, but I observed that it takes ages to complete, I want alternative way of doing it.

Any idea or can you link me up with a user group where I can post the challenge?

Note the name 'Loss Sales Stock' is the name of this syntax(measure) itself  defined in the analysis manger; while the other measures are QoH and arws. the dimesions is products 

 -- restrict this calculation to LEAF (lowest level) items - ie sku

iif

( isleaf([products].currentmember),

-- show only items with qoh less than arws

([products].currentmember.children ,[Loss Sales Stock]) )

Using a SCOPE assignment instead of double IIF's should speed this up. Give that a shot.

Try Adam sugestion.

Btw, are you running in a 32 bit server? I've had an performance issue in one cube because of the detailed needed. My solution was to calculate that in my fact table and just add the measure into the cube.

But now, with a 64 bit server, I have done a lot of measures with IIF and scope and ain't no performance issue...yet. It's a huge difference.

Thanks Adam,

I will appreciate your input in getting the equivatent syntax using 'scope' for I have tried but no success.

Thanks.

Patrick

Hi Patrick,

Not sure of the specifics of your user-defined Products hierarchy but you might be able to do something like this in your cube's MDX script.

CREATE MEMBER CurrentCube.[Measures].[Level Specific Calculated Member] AS NULL;

//Overwrite SKU level
SCOPE([Measures].[Level Specific Calculated Member]
 , Descendants ([Product Dim].[Product Hierarchy]
   ,[Product Dim].[Product Hierarchy].[SKU]
   ,SELF)
);

  this = iif([Measures].[QoH]-[Measures].[arws]<0,([Measures].[QoH]-[Measures].[arws]),NULL);

END SCOPE;

//Overwrite all levels above SKU
SCOPE([Measures].[Level Specific Calculated Member]
 , Descendants ([Product Dim].[Product Hierarchy]
   ,[Product Dim].[Product Hierarchy].[SKU]
   ,BEFORE)
);

  this = sum([Product Dim].[Product Hierarchy].currentmember.children ,[Measures].[Loss Sales Stock]) ;

END SCOPE;

Hope this help.  Sorry for the delayed response.  I don't check this forum enough.

Regards,

Luke