THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

Typed member properties and MDX .Properties function

After my previous post I got some questions about whether it will be possible to use .Properties MDX function in AS2005 in order to get true data type of the property and not the string. While it is easy to give a simple answer to this question (yes, it is possible, see details below), this question raises many other questions and interesting discussions, and I hope that one day I will cover this subject in depth. For now, I will just give a quick preview of what are the issues.

First of all, we need to look into all possible ways to access member properties:

  1. .Properties MDX function, i.e. [Store].CurrentMember.Properties("Store Sqft")
  2. DIMENSION PROPERTIES clause in the MDX SELECT statement, i.e.
    SELECT [Store].[Store Name].MEMBERS DIMENSION PROPERTIES [Store].[Store Name].[Store Sqft] ON 0
  3. MDSCHEMA_PROPERTIES schema rowset.
  4. In ADOMD.NET, Member object has MemberProperties collection (which is implemented using one of the three methods listed above)
  5. Some of the intrinsic properties can be accessed directly as MDX properties, for example .Name, .UniqueName, .Level etc.
  6. In AS2005 member properties are modeled as attributes which have their own hierarchies, therefore they can be addressed in MDX as real members, i.e. [Store].[Store Sqft].CurrentMember

Each one of those methods may work differently with respect to data types. Depending on the context, the data type may be MDX data type, .NET data type, VARIANT data type, OLEDB data type etc. To complicate things even further, member property can have more then one data type, i.e. it has name (which is always a string), but also a key (which has its native type and type of the binding), the key can be composite (where every component could have its own data type) and AS2005 introduces yet another binding for the “value“ of member (which can have completely different data type).

So by now you hopefully appreciate the the issue here is somewhat complex, but AS2005 has nice and elegant solution, which I hope to describe in one of the future blogs.Going back to the original question of how to get .Properties function to return strongly typed result and not always string like in AS2K - we simply added new flag to that function. I.e.
[Store].CurrentMember.Properties("Store Sqft",TYPED)
does it.

Published Saturday, November 13, 2004 8:43 PM by mosha
Anonymous comments are disabled
Privacy Statement