mandag den 15. april 2013

Exists, the MDX variant of 'Cogito ergo sum'

While reviewing or maintaning SSRS 2008/2012 cascading parameters reports based on SSAS olap cubes, I face quite often the lack of 'narrowing' of the values of param n, based on the values of the preceding 1...n-1 parameters.
Scenario : The reporting solution deals with customer sales on time perspective.
When selecting dates from a YMD hierarchy for instance, only customer with sales within the selected period of time must be retrieved and nothing else.
And if there are no customers, the user has to stop her instead of continuing to select values for the next parameters and experience an Empty dataset message at last.
Using the cascading parameters appropriately, can save your users a lot of frustrations.

The MDX template for defining the parameters may look like :

WITH MEMBER [Measures].[ParameterCaption] AS <<[Dimension].[attribute]>>.CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS <<[Dimension].[attribute]>>.CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS <<[Dimension].[attribute]>>.CURRENTMEMBER.LEVEL.ORDINAL

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,
<<[Dimension].[attribute]>>.Members ON ROWS FROM
( SELECT ( STRTOSET(@param1, CONSTRAINED) ) ON COLUMNS FROM
( SELECT ( STRTOSET(@param2, CONSTRAINED) ) ON COLUMNS FROM
( SELECT ( STRTOSET(@param3, CONSTRAINED) ) ON COLUMNS FROM
......................
( SELECT ( STRTOSET(@param(n-1), CONSTRAINED) ) ON COLUMNS
 FROM   <<Cube>>))))... )


That will only work IRL, if @param1 ... @param(n) are sourced from attributes from the same dimension due to the 'autoexists' feature.  
As soon as at least one paramter is based on another dimension, the 'narrowing' effect is gone. 
To reinforce it, you may replace the line :
<<[Dimension].[attribute]>>.Members ON ROWS with an Exists construction, that at a basic level may look like :
Exists ( <<[Dimension].[attribute]>>.ALLMEMBERS , <<[Other Dimension].[Other attribute]>>.AllMembers , <<Measure Group related to the two dimensions>>  )

The Members in the set { <<[Dimension].[attribute]>>.ALLMEMBERS } must be related to the members in the set { <<[Other Dimension].[Other attribute]>>.AllMembers } in the measure group <<Measure Group related to the two dimensions>> .

Ingen kommentarer:

Send en kommentar