Archive

Posts Tagged ‘PROPERTIES’

MDX #35 – Properties() function with TYPED flag

December 27, 2013 Leave a comment

 

Reader Query

My publisher recently forwarded me a question from a reader about the “Using the PROPERTIES() function to retrieve data from attribute relationships” section in Chapter 1 of the book “MDX with SSAS 2012 Cookbook”.

“The following code seems to always return true for [Is Numeric], no matter I use the 2nd parameter ‘typed’ or not.

Can you please explain why?”

clip_image001

clip_image002

In the reader’s query, the second parameter TYPED was commented out. Indeed, the [Is Numeric] is returning 1 (true).

I went back and read the section in the book, and I can see why the IsNumeric function used in the query is causing a bit of confusion.

IsNumeric Function Does not Return Data Type

IsNumeric function is one of the Visual Basic for Applications (VBA) library functions that extend the functionality of MDX statements. It returns a Boolean value indicating whether an expression can be evaluated as a numeric value. It does not tell if the property is a numeric data type or a string data type.

This explains why in the proceeding query, the calculated measure [Is Numeric] always returns 1. The value of Total Children can be evaluated as a numeric value.

In this sense, the IsNumeric function behaves consistently with the IsNumeric function in Transact-SQL.

You can try these two simple Transact-SQL queries in SSMS. The second SELECT will always return 1 (true) even when the parameter is a string data type.

clip_image003

Parameter TYPED is useful in sorting, filtering and calculations

If the property value can be evaluated as a number, and you intend to manipulate the property value arithmetically, then adding the TYPED parameter is the safest way to guarantee the accuracy of the sorting, filtering and calculation results.

Let’s use the FILTER function in the following three queries and compare the results.

  • 1. No TYPED parameter, and FILTER by [Total Children] = 3
  • 2. No TYPED parameter, and FILTER by [Total Children] = ‘3’
  • 3. With TYPED parameter, and FILTER by [Total Children] = 3

The first query should produce wrong results (no result set) because [Total Children] is a string value and [Total Children] = 3 cannot be found.

The second and the third queries should procedure correct results because the FILTER function used the correct data type in the search.

Query 1: No TYPED parameter, and FILTER by [Total Children] = 3

Incorrect results: no result set

clip_image005

Query 2: No TYPED parameter, and FILTER by [Total Children] = ‘3’

Correct results

clip_image007

Now try another query with TYPED parameter, and FILTER by [Total Children] = 3. You should see the same results as from the second query.

Properties() function with TYPED flag returns the data type of the property that was defined in the data source

So what exactly is the data type returned when the TYPED parameter is provided? It is the data type that is defined in the data source.

If you open the Data Source View in Adventure Works DW and expand the DimCustomer table, you will see that TotalChildren is defined as System.Byte.

clip_image008

clip_image009

In the relational data warehouse, this filed is defined as tinyint. Another data type you might be interested in is the data type of the Dimension Attribute Total Children. UnsignedTinyInt is an 8-bit unsigned integer with values that range from 0 to 255.

clip_image010

A bit more info

Another reason that you should include the TYPED parameter for the Properties function is for your PivotTable users. Very often, PivotTable users need to manipulate numeric values arithmetically. Data type conversion in PivotTable can create a lot of frustration for users.

Past blogs that explored the Properties function:

MDX #11 – How to get number of cars each customer owned using Properties() function?

MDX #12 – Do not forget the TYPED flag in the Properties() function

MDX #26 – SSN can only be referenced as a member property in MDX

Categories: MDX Challenges Tags: , ,

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: