Archive

Posts Tagged ‘NON EMPTY’

When a calculated measure is added NON EMPTY stopped working

March 24, 2016 Leave a comment

We use NON EMPTY key word on the rows axis to remove rows with NULL values. It works only when all the measures are NULL. If any one of the measures has value, NON EMPTY key word will not be able to remove rows.

NON EMPTY on rows axis worked as expected

A co-worker recently worked on getting 30+ measures from multiple measure groups out from our production cube. The NON EMPTY key word on the rows axis worked as expected.

SELECT
{ [Measures].[measure 1],
....
{[Measures].[measure 30] } ON COLUMNS,
NON EMPTY
[Employee].[Employee Name].[Employee Name].MEMBERS
ON ROWS
FROM
[REPORTING_CUBE]
WHERE
( [Date].[Date View].[Month].&[201601])

The month on the WHERE clause is hardcoded in this blog, but this query is supposed to be executed in an OPENQUERTY with a dynamic month value. In order to attach a period key value to the month, my co-worker decided to also get the month period key in the same query. (Let’s put aside why the OPENQUERTY is used and whether the month period key is needed or not and let’s focus on the NON EMPTY.)

NON EMPTY stopped working when a calculated Period Key is added

In order to get the hidden month period key, he used the WITH MEMBER to create a query-scope measure:

WITH MEMBER [Measures].[Period Key] AS LEFT([Date].[Date View].CurrentMember.Member_Key, 6)

This [Measures].[Period Key] is then added to the columns axis.

SELECT
{ [Measures].[Period Key],
[Measures].[measure 1],
....
{[Measures].[measure 30] } ON COLUMNS,

The query at the point returns rows that more than doubled what it returned before with many of them showing all measures NULL, except the Period Key.

The NON EMPTY is obviously not working at this point. When you think about it, this actually makes sense. In the context of the query, the Period Key has value and should be the same for all the employees whether those measures are NULL or not. This explains why NON EMPTY cannot remove the rows even when all the measures are NULL, since [Measures].[Period Key] has value.

Below is a made-up example. It shows that we get rows back even when all measures are NULL.

 

Period Key Measure 1 Measure 2 Measure 3
Austin, D 201601 (null) (null) (null)
Avalos, K 201601 (null) (null) (null)
Baker, B 201601 (null) 1,158.78 (null)
Brooks, W 201601 85.55% 291.38 1
Brown, L 201601 (null) (null) (null)
Cruz, P 201601 (null) (null) (null)
Cruz, S 201601 (null) (null) (null)

NONEMPTY() function is a lot more flexible

The NONEMPTY() function is a lot more flexible and should work in this case. But the usual form of it, where one measure is given as the second parameter, would not work in this case.

NONEMPTY(
[Employee].[Employee Name].[Employee Name].MEMBERS,
[Measures].[measure 1]
)

Use a named set as the second parameter for the NONEMPTY() function

We need to check for all the 30+ measures. The solution here is to use a named set as the second parameter.

NONEMPTY(
[Employee].[Employee Name].[Employee Name].MEMBERS,
[The Set]
)

We will define the named set as the following to combine all 30+ measure together into a set:

WITH
SET [The Set] AS
{ [Measures].[measure 1],
...
[Measures].[measure 30] }

Putting all together, here is the final query that will only return the employees who have values in those 30+ measures.

WITH
SET [The Set] AS
{ [Measures].[measure 1],
...
[Measures].[measure 30] }
SELECT
{ [Measures].[Period Key],
[The Set] } ON COLUMNS,
NONEMPTY(
[Employee].[Employee Name].[Employee Name].MEMBERS,
[The Set]
) ON ROWS
FROM
[REPORTING_CUBE]
WHERE
( [Date].[Date View].[Month].&[201601])

In my made-up example, only the two rows that are in red and bold will be returned, Baker, B and Brooks, W.

Always provide set_expression2 in NonEmpty() function

On MSDN the NonEmpty() Function page shows the syntax as this:

NONEMPTY(set_expression1 [,set_expression2])

It implies that the second parameter is optional. Check out my old blog to understand why we should always provide set_expression2 in the NonEmpty() function.

https://bisherryli.com/2012/01/20/mdx-1-why-should-we-always-provide-set_expression2-in-nonempty-function/

Categories: MDX Challenges Tags: , ,

MDX #41–Remove employees with less than $100,000 sales

May 30, 2014 Leave a comment

Table of contents

Need to remove employees who have zero sales amount
Create a calculated measure with the IIF() function
The NON EMPTY keyword will take care of the removal
Ordering results with both numeric and string expression
IIF() statement VS. Scope() statement in MDX Cookbook 2012

Need to remove employees who have zero sales amount

I recently helped a co-worker with a simple MDX query. All she wanted to do is to remove employees from the return set who has no sales or who has zero $ sales. She was using the NON EMPTY keyword on both the X and Y axis. That removed employees who have no sales, but the employees who have zero sales amount are still showing in the data set.

A quick and simple way to remove those employees with zero sales amount is to create a calculated measure in the query. This new calculated measure will simply use the IIF function to turn the sales amount into a NULL value, when the sales amount is zero. The NON EMPTY keyword on the Y axis will automatically take care of the removal of those employees with zero sales amount.

Create a calculated measure with the IIF() function

Suppose that this is our initial MDX query, where all employees (at leaf level of the Employees hierarchy) who have sales are returned.

 

image

Now you would like to only see employees who have more than $100,000 sales. In the following modified MDX query, I added this calculated measure. 

[Measures].[Reseller Sales Amount > 100,000]

This measure is simply identical to [Measures].[Reseller Sales Amount], except that it is set by the IIF function to be NULL, when it is less than 100,000.

The NON EMPTY keyword will take care of the removal

The NON EMPTY keyword on the Y axis will automatically take care of the removal of those employees with sales less than 100,000.

 

image

Ordering results with both numeric and string expression

To be sure that we get only the employee who have more than 100,00 sales amount, we can sort the results by the sales amount using the order() function with this numeric expression.

[Measures].[Reseller Sales Amount > 100,000]

 

image

 

If you insist to sort the results by the employees’ name, use the employee names as a string expression in the order() function.

[Measures].[Employee name]

 

image

IIF() statement VS. Scope() statement in MDX Cookbook 2012

In the book MDX Cookbook 2012, we have quite extensive examples on how to use the query-based IIF() statement, as well as the the cube-based Scope() statement.

You can check out the recipe Detecting a particular member of a hierarchy while you are using the book.

Categories: MDX Challenges Tags: , ,

MDX+SSRS #34 – Query not returning results: there are good reasons

November 10, 2013 Leave a comment

When we are learning MDX, we unavoidably have to deal with the tools we use. Adding confusions about the behaviors of the different tools to our still fragile understanding of the MDX language itself can create huge frustration for learners.

Here is one of them. In developing SSRS reports, we frequently need to create report parameters using members from dimensions as choices for users to pick (they are referred to as pick lists).

Empty Measure (COLUMNS) + NON EMPTY keyword (ROWS) = no results

If you already know how to use the Query Designer in SSRS in graphical mode to create basic MDX queries, you might want to venture further to see if you can create a pick list by yourself. Let’s use Adventure Works cube as our example, and assume that you want to create a product pick list by retrieving all the members from the Product dimension. Very soon, you will realize that your dataset in SSRS will not return any product at all.

Here is what you might have done. In the Query Designer in SSRS, you just simply find the Product dimension and also the Product attribute hierarchy, and drag it to the report area. There is no product returned.

image

As a matter of fact of fact, there would be no data returned at all no matter which dimension you choose.

Now let’s click on the toggle button on the upper right corner and switch to the query editing mode.

image

We see a couple of things in this query.

  • There is an empty set on the COLUMNS axis: { } ON COLUMNS
  • There is NON EMPTY keyword on both the COLUMNS axis and the ROWS axis.

The combination of the above two creates an empty result set. Let’s remove the NON EMPTY keyword on the ROWS axis, and click on the red execution icon on the top. A list of products came up.

image

At this point, you night wonder why SSRS even bothered to use the NON EMPTY keyword by default if it is causing the query not returning any results.

There is a good reason why it does it. Cube space can be very large, and typically it is very sparse. When writing MDX, we spend a lot of effort trying to remove the empty cells from our results to improve both the query time and the data rendering time. SSRS is no exception. By adding NON EMPTY keyword, it assumes that we do not want to include any products that do not have any fact data. Unfortunately in this case, you didn’t get a chance to specify what the fact data (measure) is; SSRS added an empty set on the first axis by default. We get a combination of Empty Measure (COLUMNS) + NON EMPTY keyword (ROWS) which produces no results .

[ALLMEMBERS] = no result

I have seen another good reason why a query would not return the pick list.

you can try to run the following query. It would not return any results.

SELECT  {} on 0,
        [Product].[Product].[ALLMEMBERS] ON 1
FROM    [Adventure Works]

Now remove the brackets around ALLMEMBERS, you will see the result set.

SELECT  {} on 0,
        [Product].[Product].[ALLMEMBERS] ON 1
FROM    [Adventure Works]

In SSMS, by removing the brackets around ALLMEMBERS, ALLMEMBERS’s color turned from black to maroon. 

image

You might have already guessed the reason.

  • ALLMEMBERS is a MDX built-in function: [Product].[Product].ALLMEMBERS will evaluate the product hierarchy in the product dimension and returns a set that contains all members of the product hierarchy, which includes the top level member [All Products], all products, and all calculated members if any. See http://technet.microsoft.com/en-us/library/ms144768.aspx for details on the ALLMEMBERS function.
  • [ALLMEMBERS] is a member with the name of ALLMEMBERS. [Product].[Product].[ALLMEMBERS] represents a member with name ALLMEMBERS that does not exist in the product hierarchy.

More Information on the NonEmpty() function and the Non Empty keyword

In MDX, both the NonEmpty() function and the Non Empty keyword can be used to remove empty rows and columns. The difference between the NonEmpty() function and the Non Empty keyword is well explained in the recipe “Knowing the difference between NONEMPTY() function and the NON EMPTY keyword” in Chapter 1 of the book “MDX with SSAS 2012 Cookbook”.

%d bloggers like this: