Archive

Archive for November, 2013

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”.

MDX+SSRS #33 – Retrieve manager’s key and fully-qualified member name

November 8, 2013 Leave a comment

A co-worker recently worked on a SSRS report and wanted to use a specific manager’s team for testing. The Manager is a filter for the SSRS report and can take a fully-qualified member name as the default value.

Using the Employee dimension in Adventure Works cube as an example, a fully-qualified member name can take two forms, key-based and name-based:

  • [Employee].[Employees].&[77]: key-based
  • [Employee].[Employees].[Douglas B. Hite]: name-based

The Manager parameter in the SSRS report is designed to take the key-based fully-qualified member name as input, and the member name as the label.

It is common for us to know only the partial spelling of names. So what would be the quickest way to get a member’s key-based fully-qualified member name (I’ll also refer it as member unique name) when we only know the partial spelling of names?

Retrieve member properties not as data in the result set

Member unique name is one of the many member properties exposed by dimensions in SSAS. I went ahead and showed my co-worker how to write a query using the CurrentMember function to return member properties as data in a result set.

On the second thought, I should have showed him another easier way. That is to use the PROPERTIES keyword to retrieve member properties, including the member unique name. Properties returned this way will not have the property data in the result set.

In SSMS, you can run this query to return each employee’s unique name, i.e. the key-based fully-qualified member name.

SELECT    {} on 0,
        [Employee].[Employees].MEMBERS
        DIMENSION PROPERTIES
             MEMBER_CAPTION,
             UNIQUE_NAME,
             LEVEL_UNIQUE_NAME,
             MEMBER_KEY
             ON 1
FROM    [Adventure Works]

This is part of the results.

image

Now double-click on an employee name Amy E. Alberts. Here is the Member Properties window you will see.

clip_image002

You notice that the unique name appeared twice. As a matter of fact, by default SSMS query result will return the member properties that are shown in the above window, from Caption to ChildCount.

So by simply running the following query without the PROPERTIES keyword you will still be able to get the member’s unique name by double-clicking on the employee name.

SELECT    {} on 0,
        [Employee].[Employees].MEMBERS  ON 1
FROM    [Adventure Works]

The Copy button is also can be very convenient. It will copy the entire Property and Value list to the clipboard.

Query Editor in SSRS will not return UNIQUE_NAME property by default

Let’s just first run this simple query in the Query Editor (in the text mode rather than the graphical mode) in SSRS to see what it will return.

SELECT    {} on 0,
        [Employee].[Employees].MEMBERS  ON 1
FROM    [Adventure Works]

clip_image003

We see employee names returned in the result set. It actually only returns the member property MEMBER_CAPTION by default.

The Query Editor in SSRS behaves quite differently. It will not return member properties other than MEMBER_CAPTION by default. If you want to return more than just the MEMBER_CAPTION, you will need to use the PROPERTIES keyword.

SELECT    {} on 0,
        [Employee].[Employees].MEMBERS
        DIMENSION PROPERTIES
             MEMBER_CAPTION,
             UNIQUE_NAME,
             LEVEL_UNIQUE_NAME,
             MEMBER_KEY
             ON 1
FROM    [Adventure Works]

clip_image005

Interestingly, if you don’t include the property MEMBER_CAPTION, the first column is now empty.

clip_image007

Another different behavior from the query editor in SSMS is that it actually includes the member properties as data in the result set.

Here is a short list of the behaviors of the Query Editor in SSRS when using the PROPERTIES keyword.

  • It only returns MEMBER_CAPTION property by default (default means without using the PROPERTIES keyword).
  • It includes the member properties as data in the result set.
  • When using the PROPERTIES keyword, the MEMBER_CAPTION should be included in the property list (but does not need to be the first one in the list).

Conclusion

Using the PROPERTIES keyword is a simple way to get members’ unique names in the Query Editor in both SSMS and SSRS. We also see that the Query Editor in SSRS behaves quite differently from how the Query Editor behaves in SSMS.

We need to be careful when retrieving the entire members from a very large dimension without filtering. It might cause performance issue.

This might not work very well either when retrieving the entire members from a hierarchy that has multiple levels. It will be very difficult to look for what you want to see without proper sorting.

These member properties, MEMBER_CAPTION, UNIQUE_NAME, LEVEL_UNIQUE_NAME, MEMBER_KEY, are only part of the intrinsic member properties exposed by SSAS. To see all the intrinsic member properties, check out this link on SQL Server Books Online for more details:

Using Member Properties (MDX)

In the query below, I am including quite a long list of intrinsic member properties.

SELECT    {} on 0,
        [Employee].[Employees].MEMBERS
        DIMENSION PROPERTIES
             MEMBER_CAPTION,
             UNIQUE_NAME,
             LEVEL_UNIQUE_NAME,
             MEMBER_KEY,
             CATALOG_NAME,
             CHILDREN_CARDINALITY,
             CUSTOM_ROLLUP,
             CUSTOM_ROLLUP_PROPERTIES,
             DESCRIPTION,
             DIMENSION_UNIQUE_NAME,
             HIERARCHY_UNIQUE_NAME,
             IS_DATAMEMBER,
             IS_PLACEHOLDERMEMBER,
             KEY0,
             LCID,
             LEVEL_NUMBER,
             MEMBER_NAME,
             MEMBER_TYPE,
             MEMBER_UNIQUE_NAME,
             MEMBER_VALUE,
             PARENT_COUNT,
             PARENT_LEVEL,
             PARENT_UNIQUE_NAME,
             SKIPPED_LEVELS,
             UNARY_OPERATOR
             ON 1
FROM    [Adventure Works]

In all the above queries, I used an empty set on the COLUMNS axis. For more details on this technique, please see the recipe “Skipping axis“ in Chapter 1 of the book “MDX with SSAS 2012 Cookbook”).

Notes: all the examples and screenshots are from 2012 version of the SQL Server platform.

%d bloggers like this: