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

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.

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

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

Correct results

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.

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.

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