Non
Empty v/s NonEmpty:
Non Empty:
This will return all the columns which are not null
for all the tuples of rows axis:
SELECT
NON EMPTY
{
} ON COLUMNS
, {
} ON ROWS
FROM [Cube];
This will return all the rows which are not null for all the tuples of column axis:
SELECT
{
} ON COLUMNS
, NON EMPTY {
} ON ROWS
FROM [Cube];
Non Empty:
returns the set of tuples
that are not empty from a specified set,
SELECT
{
} ON COLUMNS
,{
NonEmpty
(
[Geography].[Country].Children
,[Measures].[Subscribers]
)
} ON ROWS
FROM [Cube];
} ON COLUMNS
,{
NonEmpty
(
[Geography].[Country].Children
,[Measures].[Subscribers]
)
} ON ROWS
FROM [Cube];
As you can see, the
NonEmpty operator takes all the rows having a not NULL value for Subscribers in
the rows
and then displays all the measures defined in the column axis.
Basically what happens internally is that
NonEmpty is evaluated when the sets
defining the axis are evaluated. So at this point of time, there is no
context
of the other axes.
SELECT
{[Date].[Month].[March]} ON COLUMNS
,{
NonEmpty([Geography].[Country].Children)
} ON ROWS
FROM [Blog Statistics]
WHERE
[Measures].[Hits];
,{
NonEmpty([Geography].[Country].Children)
} ON ROWS
FROM [Blog Statistics]
WHERE
[Measures].[Hits];
NonEmpty is evaluated
when the set defining the axis is evaluated (here, Country) and at that point
of time,
NonEmpty is evaluated for each member of the country against the
default member of the Date dimension
(which would be ALL generally).
Use NonEMPTY before
filtering the rows to improve the performance as shown below:
SELECT
{[Measures].[Hits]} ON COLUMNS
,{
Filter
(
NonEmpty
(
[Geography].[Country].Children
*
[Date].[Month].Children
,[Measures].[Hits]
)
,
[Measures].[Hits] > 30
)
} ON ROWS
FROM [Blog Statistics];
{[Measures].[Hits]} ON COLUMNS
,{
Filter
(
NonEmpty
(
[Geography].[Country].Children
*
[Date].[Month].Children
,[Measures].[Hits]
)
,
[Measures].[Hits] > 30
)
} ON ROWS
FROM [Blog Statistics];
References: http://beyondrelational.com/modules/2/blogs/65/posts/11569/mdx-non-empty-vs-nonempty.aspx
No comments:
Post a Comment