Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
#SQLServer 46
SQL Server 44
SSRS EXPERT 41
BI 36
#BI 33
brh 28
SSIS BEST PRACTICES 23
TSQL 19
MS BI STACKS 18
SSRS 17

Archive · View All
March 2011 24
July 2011 17
February 2011 15
April 2010 13
June 2011 11
May 2011 11
April 2011 11
July 2010 11
March 2010 8
December 2011 7

MDX #35 - Properties() function with TYPED flag

Dec 26 2013 9:37PM by Sherry Li   

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

Tags: 


Sherry Li
14 · 12% · 3827
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]