String Functions: The .Properties Function

Tuesday Sep 4th 2007 by William Pearson
Share:

Business Intelligence Architect Bill Pearson introduces the basic .Properties function, within the first of a two-part article surrounding this important member of our MDX toolsets.

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 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:

  • 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

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.

Discussion

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.

Let’s look at some syntax illustrations to further clarify the operation of the .Properties function.

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

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, let’s 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:

[Customer].[Customer Geography].PROPERTIES('Phone')

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.

Practice

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:

Prepare MSSQL Server Management Studio to Query Analysis Services

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 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 member properties:

  • Customer Address
  • 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 member properties.

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.

1.  Type (or cut and paste) the following query into the Query pane:

-- MDX058-001 Basic.PROPERTIES Function - Practice Example 1

WITH 
MEMBER Measures.CustomerAddress 
AS 
   [Customer].[Customer Geography].PROPERTIES( "Address" )
   
MEMBER Measures.CustomerBirthday 
AS
   [Customer].[Customer Geography].PROPERTIES('Birth Date')
   
MEMBER Measures.CustomerCommute 
AS 
   [Customer].[Customer Geography].PROPERTIES('Commute Distance')
   
MEMBER Measures.CustomerHomeChildCount 
AS
   [Customer].[Customer Geography].PROPERTIES('Number of Children at Home')
   
MEMBER Measures.CustomerPhone 
AS 
   [Customer].[Customer Geography].PROPERTIES('Phone')
   
MEMBER Measures.CustomerAnnualIncome 
AS 
   [Customer].[Customer Geography].PROPERTIES('Yearly Income')
   
SELECT 
   {Measures.CustomerAddress
   , Measures.CustomerBirthday
   , Measures.CustomerCommute
   , Measures.CustomerHomeChildCount
   , Measures.CustomerPhone
   , Measures.CustomerAnnualIncome}
      ON AXIS(0),
      
 {[Customer].[Customer Geography].[Customer].MEMBERS}
      ON AXIS(1)  
FROM 
         [Adventure Works]

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.

2.  Execute the query by clicking the Execute button in the toolbar, as depicted in Illustration 3.


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.

3.  Select File -> 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 member properties:

  • 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 employees.

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.

4.  Press key combination ALT + N, to open a tab for a new query within the current Analysis Server connection.

5.  Type (or cut and paste) the following query into the Query pane:

-- MDX058-002 Basic.PROPERTIES Function - Practice Example 2

WITH 
MEMBER Measures.[Hire Year] 
AS 
   [Employee].[Employee].PROPERTIES("Hire Year")
   
MEMBER Measures.[Hire Date] 
AS 
   [Employee].[Employee].PROPERTIES("Hire Date")
   
MEMBER Measures.[Base Rate] 
AS 
   [Employee].[Employee].PROPERTIES("Base Rate")

MEMBER Measures.[Pay Frequency] 
AS 
   [Employee].[Employee].PROPERTIES("Pay Frequency")
   
SELECT 
   {Measures.[Hire Year]
   , Measures.[Hire Date]
   , Measures.[Base Rate]
   , Measures.[Pay Frequency]}
      ON AXIS(0),
      
 {[Employee].[Employee].CHILDREN}
      ON AXIS(1)  
FROM 
   [Adventure Works]

The Query pane appears, with our input, as depicted in Illustration 5.


Illustration 5: Our Second Practice Query in the Query Pane ...

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 specification.)

NOTE: For information on the .CHILDREN function, see my article MDX Member Functions: The “Family" Functions, within the Database Journal MDX Essentials series.

6.  Execute the 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 members.

7.  Select File -> 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.

The 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.

8.  Select File -> Exit to leave the SQL Server Management Studio, when ready.

Summary ...

In this, the first half of a two–part 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.

We 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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved