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.
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.
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:
Examine the syntax surrounding the function;
Undertake illustrative examples of the uses of the function in
Briefly discuss the results datasets we obtain in the practice
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.
Illustration 1: Member Properties Attached to the Customer
Dimension, Customer Level
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.
We will practice some uses of the .Properties function
in the section that follows.
Preparation: Access SQL Server Management Studio
To reinforce our understanding of the basics we have
covered, we will use the .Properties function within a couple of queries
that illustrate its operation, focusing, within this article, upon scenarios
where we use the basic .Properties function to meet the business
requirements of a hypothetical client. (As we have noted earlier, we examine
the use of the .Properties function with the optional TYPED
flag, within Part II of this article.) We will undertake our practice
exercises within scenarios that place the .Properties function within
the context of meeting basic requirements similar to those we might encounter
in our respective daily environments. The intent is to demonstrate the use of
the statement in a straightforward, memorable manner.
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
Procedure: Satisfy Business Requirements with MDX
Lets 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 VP - Sales
group, with whom we have worked in the past to deliver solutions to meet
various reporting and analysis needs, inform us that they have received a
request to generate datasets for several reporting tasks that have been
discussed at a recent meeting with Marketing group peers.
The analysts tell us that they wish to generate information
about the Adventure Works customer base. 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 Customer
Customer Birth Date
Customer Daily Commute Distance
Number of Children Living at Customer Home
Customer Phone Number
Customer Yearly Income
For the time being, our colleagues want simply to generate a
dataset containing the above information for each customer in the Adventure
Works cube. The end use of the information 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). Moreover, the group assures us that, once they grasp
the concepts to meet the immediate need, they will want to expand prospective
queries to bring in additional measures for the customers.
After we initially explain the use of the basic .Properties
function as a candidate for meeting the requirement, our client colleagues
state that they are interested in understanding how they might apply this
function within the context of a practical scenario such as the immediate
requirement. The basic .Properties function appears an adequate
mechanism for delivering the information requested. We discuss our reasoning
with the analyst group, and then offer to illustrate the use of the .Properties
function to meet the immediate need, both to solidify the analysts new
understanding and to assist in rounding their overall MDX vocabularies. We
then set about the assembly of our example to illustrate the basic use of .Properties.
Procedure: Use the Basic .Properties Function within Calculations
Per the request of our client colleagues, we will construct
a simple query to provide an illustration of the use of the basic .Properties
function within a common context, the definition of a calculated members
with which we will then deliver selected information stored as Customer
Our initial example will serve as an introduction to a means
of generating a dataset containing member property information, as requested
by the analysts. This will serve as a basis for meeting the business
requirement, with the intended purpose of presenting the member property
information as columns appearing alongside the individual customer member
names, which will populate the row axis.
Type (or cut
and paste) the following query into the Query pane:
-- MDX058-001 Basic.PROPERTIES Function - Practice Example 1
[Customer].[Customer Geography].PROPERTIES( "Address" )
[Customer].[Customer Geography].PROPERTIES('Birth Date')
[Customer].[Customer Geography].PROPERTIES('Commute Distance')
[Customer].[Customer Geography].PROPERTIES('Number of Children at Home')
[Customer].[Customer Geography].PROPERTIES('Yearly Income')
The Query pane appears,
with our input, as shown in Illustration 2.
Illustration 2: Our Initial Query in the Query Pane ...
The above query returns the specified member properties
for each member of the Customer dimension (Customer Geography attribute
hierarchy, Customer level) in the cube.
We alert our client colleagues to the fact that we might
insert logic to support parameterization, primarily by using the Descendants()
function, wherein we might, as an example, parameterize the level within
the function to allow control of the detail presented, among other elements of
our query, to accomplish potentially extended ends of our client colleagues. In doing so, we could set up a hierarchical
picklist within Reporting Services, whereby information consumers
might select a given Country, State-Province, City, Postal Code, and
even Customer Name, and so forth, to drive the level whose
members values are returned, among other possibilities. The obvious advantage
is that consumers can dictate the dimensional level as well as the
member(s) of that level (Reporting Services 2005 supports
multiple selection within properly constructed picklists), upon which
the focus is enacted. In some circumstances, double leverage could be
provided by modifying the query to do more within a single parameter which
might be seen as a desirable efficiency within the realm of simulated dynamic
drilldown effects and so forth.
In the calculated member definitions, we put the
basic .Properties function to work to allow the retrieval of the
respective string values. We juxtapose the individual customer member
names in our dataset through the use of the .MEMBERS function within the
row axis specification.
NOTE: For more information about,
and hands-on practice within, working examples of MDX query parameterization, see various member articles
of my MSSQL
Server Reporting Services series.
query by clicking the Execute button in the toolbar, as depicted in Illustration
Illustration 3: Click Execute to Run the Query...
The Results pane is populated by Analysis
Services, and the dataset partially shown
in Illustration 4 appears.
Illustration 4: Results Dataset (Partial View) .Properties
Function with Calculated Members
In the partial view of the returned dataset, we see that the
calculated members accomplish the intended purposes - generating the member
properties strings for the individual Customer Geography hierarchy
members that belong to Customer level.
-> Save MDXQuery1.mdx As ..., name the file MDX058-001,
and place it in a meaningful location.
Our client colleagues express satisfaction with the
contextual backdrop we have established for introducing the basic .Properties
function. We agree to next use a similar query within another such example,
to confirm understanding of the concepts. This query will provide an
illustration of the use of the .Properties function within the context
we have already seen, the definition of calculated members, which we will,
in turn, use to retrieve member property data within a dataset for
analysis and reporting. As before, we will base our example upon a local
scenario posed by the client representatives: a need to provide member
property data to support reports they plan to create in Reporting
Services 2005. Moreover, as always, they want to master concepts that can
be extended beyond the immediate example as the need arises.
The analysts tell us that they wish, once again, to generate
information from the Adventure Works database, but that this time their
focus is employee information. Specifically, they again 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 Employee
Employee Hire Year
Employee Hire Date
Employee Base Rate of Pay
Employee Pay Frequency
For the time being, our colleagues want only to generate a
list containing the above information for each employee in the Adventure
Works cube, in much the same way we generated the specified information
stored within the customer member properties in our earlier example. End
uses for the information will, again, go beyond reporting, with picklist
support and other possible utility to be explored, and with prospective queries
likely to be expanded to bring in additional measures and other data surrounding
The analysts tell us that the All
level for employees should not appear within the returned dataset, but
that every employee name is to be presented within the row axis,
juxtaposed against the selected member property information that will
again appear in columns. Per the request of our client colleagues, we will
construct a simple query to provide an illustration of the use of the basic .Properties
function, once again within the definition of calculated members,
which we will then select for retrieval into the end dataset.
combination ALT + N, to open a tab for a new query within the current Analysis
Type (or cut
and paste) the following query into the Query pane:
-- MDX058-002 Basic.PROPERTIES Function - Practice Example 2
MEMBER Measures.[Hire Year]
MEMBER Measures.[Hire Date]
MEMBER Measures.[Base Rate]
MEMBER Measures.[Pay Frequency]
, Measures.[Hire Date]
, Measures.[Base Rate]
, Measures.[Pay Frequency]}
The Query pane appears,
with our input, as depicted in Illustration 5.
Illustration 5: Our Second Practice Query in the Query
The above retrieves the calculated members that specify the
requested member properties for each member of the Employee
dimension, Employee attribute hierarchy. Within each calculated member,
we put the basic .Properties function to work to retrieve the associated
information. The same sort of logic might, of course, be applied within far larger,
more elaborate scenarios, a fact that we emphasize to our client colleagues.
Finally, and rather obviously, because we have specified
that the Employee members are to populate the rows axis, through our use
of the .CHILDREN function, we have populated the row axis with all Employee
members, while excluding the All level of the dimension (All Employees
will appear if we simply use [Employee].[Employee].MEMBERS in the row
query by clicking the Execute button in the toolbar, as shown earlier.
The Results pane is populated by Analysis
Services, and the dataset shown
in Illustration 6 appears.
Illustration 6: Results Dataset .Properties Function
within Calculated Members
In the view of the returned dataset, we see that the
calculated members accomplish their intended purposes generating the
specified member properties strings for the individual employee
-> Save MDXQuery2.mdx As ..., name the file MDX058-002,
and place it in a meaningful location.
In the returned dataset, we see that the query appears to
meet the business requirements outlined by the client representatives. We have
delivered member properties by putting the basic .Properties
function to work within simple calculated members, which we can then
select within Reporting Services, or within any other application that
can successfully pass similar MDX queries to an Analysis Services 2005
database. We can, of course, use the member properties data that we
retrieve for reporting and analysis purposes, as well as to drive report
attributes, among a host of other possible uses.
client representatives confirm that the immediate goal of the practice example
has been met: the creation of calculated members whose employment of the .Properties
function provides a vehicle for retrieving string values from selected member
properties all in a manner that lends itself to the parameterization
opportunities that are expected to arise at the Reporting layer.
Moreover, they state that the illustration we have provided can be easily
extrapolated to other scenarios where they need to perform an action, or to
present a value, based upon the data contained within the member properties
residing at various levels within the dimensional structures of their Analysis
Services 2005 data sources.
-> Exit to leave the SQL Server Management Studio, when ready.
this, the first half of a twopart article, we explored the basic MDX .Properties
function, which can be called upon in activities that range from generating
simple lists and supporting parameter picklists, to supplying data contained
within the member properties of our dimensional structures to meet
myriad analysis and reporting needs within our local business environments. We
introduced the basic .Properties function, commenting upon its operation,
and touching upon the data strings we can deliver through its use.
examined the syntax involved with .Properties, and then undertook
illustrative practice examples of business uses for the basic function,
generating queries that capitalized upon its capabilities. Throughout our
practice session, we briefly discussed the results datasets we obtained from
each of the queries we constructed, as well as extending our discussion to other
possible options and uses for the concepts we exposed.
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.