One of my other favourite MDX functions is Root(). It's a really easy way to get all the filters off a dimension. I often use it in calculations defined in the cube.
However the Root() function does not support multi-select and you might get this error message.
#Error Query (6, 30) The MDX function ROOT failed because the coordinate for the 'Region' attribute contains a set.
Fortunately, there is a workaround. Unfortunately, it means not using the Root() function and going back to the .[All] member of all the attributes (or all the ones being used in the query) of the dimension.
For example, the following query will hit the error.
WITH
MEMBER [Client Host].Region.[AfricaAsia] AS
AGGREGATE({[Client Host].Region.&[Africa]
,[Client Host].Region.&[Asia]})
MEMBER Measures.[HitsPercent] as
[Measures].[Request Count]/(ROOT([Client Host]), [Measures].[Request Count])
SELECT
{Measures.[HitsPercent]}
ON COLUMNS
,[Date].[Month].[Month]
ON ROWS
FROM [EasternMining]
WHERE
([Client Host].Region.[AfricaAsia])
The fix is simply to change Root([Client Host]) to [Client Host].Region.[All]. ie.
WITH
MEMBER [Client Host].Region.[AfricaAsia] AS
AGGREGATE({[Client Host].Region.&[Africa]
,[Client Host].Region.&[Asia]})
MEMBER Measures.[HitsPercent] as
// [Measures].[Request Count]/(ROOT([Client Host]), [Measures].[Request Count])
[Measures].[Request Count]/([Client Host].Region.[All], [Measures].[Request Count])
,format_string="#,#0.00%"
,non_empty_behavior="measures.[Request Count]"
SELECT
{Measures.[HitsPercent]}
ON COLUMNS
,[Date].[Month].[Month]
ON ROWS
FROM [EasternMining]
WHERE
([Client Host].Region.[AfricaAsia])
Note, I have only put the .[All] member in for the Region attribute, whereas you really need to add in a .[All] member for all the dimension attributes to be equivalent to the Root() function.
By the way, I don't know why the Root() function doesn't support multi-select. One would imagine that it should.
Because of this limitation, I might be using the Root() function less liberally in calculations stored in the cube.
Hi Richard,
ReplyDeletethe ROOT is also not so good for the performance (see Mosha's answer here http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/3b724376-aa6d-4bcc-afcb-a2ac07c94df5).
Michael.