String Functions: The .Properties Function, Part II

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.

Overview

In
this, the second half of a two-part article, we will continue to expose another
useful function within the MDX toolset, the .Properties function. As we noted in The
.Properties Function, Part I, the general purpose of the .Properties function is to
return a member property value. We also discussed enhancements to the .Properties
function that allows 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 delve further into the .Properties function, having
covered its basic use in
Part I. We will explore the use of .Properties
with the TYPED flag, whereby the property value is returned in
its internal data type. We will introduce the newly extended function,
commenting upon its operation and what we can employ it to deliver. As a part
of our discussion, we will:

• Examine the syntax surrounding the function;
• Undertake illustrative examples of the uses of the function in
  practice exercises;
• Briefly discuss the results datasets we obtain in the practice
  examples.

The .Properties Function

Introduction

In The .Properties Function, Part I, we introduced the .Properties function, which, according to the Analysis Services Books Online,
“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 this article. (In Part
I, we considered the default
behavior, where a string is always returned.) We will examine the syntax for the .Properties
function in conjunction with the TYPED
flag, 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.

Discussion

As we learned in The .Properties Function, Part I, 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 leveraged to retrieve stored member properties for many
sophisticated uses – especially when we employ the function 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.

Let’s review some syntax illustrations to further clarify
the operation of the .Properties function, before expanding our
examination to focus upon the use of the optional TYPED flag.

Syntax

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])

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 in this
article, getting some hands-on practice within the section that follows.

As we noted in The .Properties Function, Part I, putting .Properties
to work in its most basic form 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 value. By default, the returned value is
coerced to be a string. As we noted earlier, if we specify TYPED, the
returned value is strongly typed. If the property type is intrinsic,
the function returns the original type of the member. If the property
type is user-defined, the type of the returned value is
identical to the type of the returned value of the .MemberValue
function.

As is almost always the case
with MDX functions, the .Properties function (whether we leverage the TYPED
flag or not) 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.

We will practice some uses of the .Properties function
in the section that follows.

Practice

Preparation: Access SQL Server Management Studio

To reinforce our understanding of the basics we have
covered, we will use the .Properties function, extended with the TYPED
flag, within a couple of queries that illustrate its operation. (As we have
noted earlier, we examined the use of the basic .Properties function,
without the optional TYPED flag, within Part I of
this article.) We will undertake our practice exercises within a scenario used
within the Books Online to illustrate the general retrieval of member
properties within a single, multifaceted query. The intent is to demonstrate
the use of the .Properties function in a straightforward, memorable
manner that efficiently illustrates a number of concepts surrounding member
properties and their associated types.

We will turn to the SQL Server Management Studio as a
platform from which to construct and execute the MDX we examine, and to view
the results datasets we obtain. If you do not know how to access the SQL
Server Management Studio in preparation for using it to query an Analysis
Services cube (we will be using the sample Adventure Works cube in
the Adventure Works DW Analysis Services database), please
perform the steps of the following procedure, located in the References
section of my articles index:

This procedure will take us through opening a new Query
pane, upon which we will create our first query within the section that
follows.

Procedure: Satisfy Business Requirements with MDX

Let’s assume, for purposes of our practice example, that we
have received a request for assistance from representatives of our client, the Adventure
Works organization. Analysts and report writers within the Controllers’
group, with whom we have worked in the past to deliver solutions to meet
various reporting and analysis needs, inform us that they would like a short
training session on the use of the .Properties function, together with a
demonstration surrounding the retrieval of member properties within a
context where types come into play.

The analysts tell us that they wish to generate information containing
variously typed member properties, which they know to exist within their
cube, the Adventure Works sample cube, to support various aspects of the
Time / Date dimension.. Specifically, they wish to create a dataset
upon which they can build tabular and graphical reports, to include the
following information that they know to be stored in the cube as Date member
properties (with examples for each):

• Date Member Name (July 1, 2003)
• Date Member Value (7/1/2003)
• Date Member Key (731)
• Date Member ID (732)
• Date Member Caption (July 1, 2003)
• Date Member Day Name (3 – TYPED)
• Date Member Day Name (Tuesday)
• Date Member Day of Week (3)
• Date Member Day of Month (1)
• Date Member Day of Year (182)

For the time being, our colleagues want simply to generate a
dataset containing the above information for a sample date member in the Adventure
Works cube. The ultimate use of information prospectively retrieved in
this way will go beyond reporting, and perhaps be used to support picklists and
the like (as we have demonstrated in many of the articles within my MDX Essentials, and other Database Journal,
series’). Naturally, the group assures us that, once they grasp the concepts
to meet the immediate need, they will want to create prospective queries to
retrieve member properties for other dimensions.

We expand upon the explanation of the basic .Properties function
we developed in The .Properties Function, Part I, illuminating
our client colleagues about how we might use
the .Properties function, in combination with the optional TYPED
flag, as a candidate for meeting the requirement. We next offer to illustrate
the use of the .Properties function / TYPED flag combination to
meet the immediate need, both to solidify our colleagues’ new understanding and
to assist in rounding their overall MDX “vocabularies.” We then set about the
assembly of our example to illustrate the use of .Properties with the TYPED
flag.