String Functions: The .Properties Function
September 4, 2007
About the Series ...
This article is a member of the series, MDX Essentials. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively adding features designed to meet specific real-world needs.
For more information about the series in general, as well as the software and systems requirements for getting the most out of the lessons included, please see my first article, MDX at First Glance: Introduction to MDX Essentials.
Note: Current updates are assumed for MSSQL Server, MSSQL Server Analysis Services, and the related Books Online and Samples.
In this, the first half of a two-part article, we will expose another useful function within the MDX toolset, the .Properties function. The general purpose of the .Properties function is to return a member property value. Enhancements to the .Properties function allow it go beyond returning a string containing the member property value (the capability that the function had in Analysis Services 2000): .Properties can now return a strongly typed result, through the use of a special flag incorporated within Analysis Services 2005 MDX for that purpose. In this article, we will introduce the basic .Properties function, which returns a string in every case. We will explore the use .Properties with the TYPED flag, whereby the property value is returned in its internal data type, in Part II of this article.
The .Properties function can be leveraged in many activities to present member properties in our analysis and reporting efforts. We will introduce the basic function, commenting upon its operation and what we can employ it to deliver. As a part of our discussion, we will:
The .Properties Function
According to the Analysis Services Books Online, the .Properties function returns a string, or a strongly-typed value, that contains a member property value. .Properties returns the value of the specified member for the specified member property (a member can, of course, have multiple properties), as we shall see. The member property can be any of the intrinsic member properties, such as NAME, ID, KEY, MEMBER_VALUE, or CAPTION, or it can be a user-defined member property.
By default, the value, regardless of its actual type, is coerced to be a string when we extract it using the .Properties function. If we make use of the TYPED flag within the function, the return value is strongly typed, as we shall see in Part II of this article. Here, we will consider the default behavior, where a string is returned.
We will examine the syntax for the basic .Properties function after a brief discussion in the next section. We will then explore some of the possibilities .Properties offers the knowledgeable user, within practice examples constructed to support hypothetical business needs. This will allow us to activate what we explore in the Discussion and Syntax sections, where we will get some hands-on exposure in creating expressions that employ the .Properties function.
To restate our initial explanation of its operation, the basic .Properties function, when acting upon a member expression, returns a string reflecting the value of the member property name that we specify for the member expression. The .Properties function is appended to the member expression to which we wish to apply it with the period (.) delimiter. Depending upon the degree to which we leverage member properties within our cube(s), as well as the extent to which we put those properties to work to support information consumers within the organizations we serve, .Properties can be used to retrieve stored member properties for many sophisticated uses especially when used in conjunction with a reporting application as sophisticated as MSSQL Server 2005 Reporting Services, or similar applications for which MDX and the .Properties function are fully exposed. Moreover, as is often the case with MDX functions in general, when we couple it with other functions, we can leverage .Properties to deliver a wide range of analysis and reporting utility.
Lets look at some syntax illustrations to further clarify the operation of the .Properties function.
Syntactically, in using the basic .Properties function to return the associated member property string value, the member upon which we seek to apply the function is specified to the left of .Properties. The function takes the member expression (a valid MDX expression that returns a member) to which it is appended, together with the Property Name (a valid string expression of a member property name) we specify in parentheses at its right - as its arguments, and returns a string representing the member property specified. The general syntax is shown in the following string:
<<Member_Expression>>.Properties(Property_Name [, TYPED])
NOTE: The above syntax contains the TYPED flag, whereby we can specify our instructions that the return value is a typed value, based upon the original type of the member or the type of the return value of the .Value function, as applied to the member (depending upon whether the property type is intrinsic or user defined, respectively). We will expand upon the use of the TYPED flag within Part II of this article.
Putting .Properties to work is straightforward. When using the function to return a desired property value of the member with which we intend it to work, we simply append the function to the right of the targeted member, enclosing a string containing the name of the specific property for which we intend to return a representative string value. As an illustration, lets take a look at a member property defined within the sample Adventure Works cube.
The Customer dimension, Customer level members, (among many other dimensional levels within the sample cube), contain numerous member properties. One of these properties is named Phone, as depicted in Illustration 1.
As an example, within a query executed against the cube, the information stored in the Phone member property could be retrieved with the following pseudo-expression:
As is probably obvious, the .Properties function can often be best leveraged by combining it with other functions, particularly relative functions, to generate lists of names, and so forth, as we shall see in short order.
NOTE: For information on several of the relative functions, see my article MDX Member Functions: "Relative" Member Functions, within the Database Journal MDX Essentials series.
We will practice some uses of the .Properties function in the section that follows.