Archive

Posts Tagged ‘Query Editor’

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.

MDX+SSRS #31– Query Designer in SSRS only allows the Measures dimension in the first axis

September 28, 2013 Leave a comment

In Chapter 1 of the book MDX with SSAS 2012 Cookbook, in the first recipe “Putting data on x and y axes”, I have given a simple example to show how easy it is to use the CROSSJOIN function to "combine" more than one hierarchy into the COLUMNS and ROWS axes.

SQL Server Reporting Services is a report design and information delivery tool, and has been adopted by many companies for their Business Intelligence reporting needs. However, building reports in SSRS accessing OLAP cubes in Analysis Services is not without frustration.

The graphical MDX Query Designer in Reporting Services allows you to retrieve data from any BI semantic model through the technique of drag and drop, without you actually needing to understand and write MDX queries.

If you have some experience building reports in SSRS with Analysis Services data, you have already discovered that the graphical MDX Query Designer serves its purpose very well to  graphically generate well-formed and efficient MDX queries. Very quickly, however, you also will find that the you will need to cross a bridge. This bridge will lead you to the generic MDX query editor where you can edit the MDX query that is built by the graphical designer.

The following is a screenshot of the Query Designer and the toggle button for switching between the graphical designer and the editor.

image

Don’t expect the MDX query editor in SSRS works the same way as the MDX query editor in SSMS.

This MDX query simply puts two measures on the COLUMNS, and the CROSSJOIN (all possible combinations) of the sales territory country and product category on ROWS.

SELECT   
    { [Measures].[Internet Sales Amount],
      [Measures].[Internet Gross Profit]
    } ON 0,
    { [Sales Territory].[Sales Territory Country].[Sales Territory Country] *
      [Product].[Product Categories].[Category]
    } ON 1
FROM   
    [Adventure Works]

In SSMS, the previous query will produce the following result.

image

If we copy the same query to the query editor in SSRS, we do get the same number of rows back, and the measures match perfectly for every combination of the sales territory country and product category. However, we see some noticeable differences, comparing the previous screenshot with the following screenshot.

image

1. The measures are not formatted in the query editor in SSRS.

2. We get four columns in the query editor in SSRS. In addition to the two measures that we put on the X axis, the two hierarchies from our CROSSJOIN function on the Y axis have also appeared as two separate columns.

Now, let’s change the previous MDX query slightly, by switching the measures to the ROWS, and the CROSSJOIN of the sales territory country and product category to COLUMNS.

SELECT   
    { [Sales Territory].[Sales Territory Country].[Sales Territory Country] *
      [Product].[Product Categories].[Category]
    } ON 0,
    { [Measures].[Internet Sales Amount],
      [Measures].[Internet Gross Profit]
    } ON 1
FROM   
    [Adventure Works]

 

In SSMS, we would expect to see the following results.

image

Let’s copy the same query to the query editor in SSRS. This time we would get an error.

image

The message is actually very clear. I’d translate the error message into the following two rules. The query editor (and the graphical Query Designer) in SSRS:

1. does not allow CROSSJOIN in the COLUMNS (or 0-axis)

2. only allows the Measures dimension in the COLUMNS (or 0-axis).

What we have put on the COLUMNS clearly violated both of the rules.

     { [Sales Territory].[Sales Territory Country].[Sales Territory Country] *
      [Product].[Product Categories].[Category]
    } ON 0

 

In my experience, knowing what to expect from the graphical MDX Query Designer and the MDX query editor in SSRS will put you half way through the learning curve.

%d bloggers like this: