String Functions: The .Properties Function, Part II

Monday Oct 8th 2007 by William Pearson
Share:

Business Intelligence Architect Bill Pearson continues his hands-on introduction to the .Properties function. In this article, we examine the use of the TYPED flag within the .Properties function to deliver a strongly typed value using .Properties.

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.

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

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



Procedure: Use the .Properties Function with TYPED Flag within a Calculation

Per the request of our client colleagues, we will construct a simple query to provide an illustration of the use of the .Properties function within a common context, the definition of a calculated members with which we will then deliver selected information stored as Date member properties. As a part of the query, we will define a calculated member containing the TYPED flag, as well, to demonstrate its operation among our basic uses of .Properties within the other calculated members in the same query.

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 member property information surrounding a given date (July 1, 2003). We will retrieve the member property data via calculated measures, as we have done in many articles in the past, with one of the calculations providing an example of the use of the TYPED flag in combination with the .Properties function.



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


-- MDX059: .PROPERTIES Function with TYPED Flag in Place

WITH 

MEMBER [Measures].[MemberName] 

AS 

    [Date].[Calendar].[July 1, 2003].PROPERTIES('Name') 

MEMBER [Measures].[MemberVal] 

AS 

    [Date].[Calendar].[July 1, 2003]. PROPERTIES ('Member_Value') 

MEMBER [Measures].[MemberKey] 

AS 

    [Date].[Calendar].[July 1, 2003]. PROPERTIES ('Key') 

MEMBER [Measures].[MemberID]

AS 

    [Date].[Calendar].[July 1, 2003]. PROPERTIES ('ID') 

MEMBER [Measures].[MemberCaption] 

AS 

    [Date].[Calendar].[July 1, 2003]. PROPERTIES ('Caption') 

MEMBER [Measures].[DayName]

AS 

    [Date].[Calendar].[July 1, 2003]. PROPERTIES ('Day Name', TYPED) 

MEMBER [Measures].[DayNameTyped]

AS 

   [Date].[Calendar].[July 1, 2003]. PROPERTIES ('Day Name') 

MEMBER [Measures].[DayofWeek] 

AS 

   [Date].[Calendar].[July 1, 2003]. PROPERTIES ('Day of Week') 

MEMBER [Measures].[DayofMonth]

AS 

   [Date].[Calendar].[July 1, 2003]. PROPERTIES ('Day of Month') 

MEMBER [Measures].[DayofYear] 

AS 

    [Date].[Calendar].[July 1, 2003]. PROPERTIES ('Day of Year') 

SELECT 

    {[Measures].[MemberName] , [Measures].[MemberVal], 

        [Measures].[MemberKey] , [Measures].[MemberID], [Measures].[MemberCaption], 

           [Measures].[DayName], [Measures].[DayNameTyped], [Measures].[DayofWeek], 

        [Measures].[DayofMonth], [Measures].[DayofYear]} 

    ON AXIS (0) 

FROM 

    [Adventure Works]

The Query pane appears, with our input, as shown in Illustration 1.


Illustration 1: Our Initial Query in the Query Pane ...

The above query is crafted to return the specified member properties for the stated member (July 1, 2003) of the Date dimension (Calendar Date attribute hierarchy, Date level) in the cube.

We alert our client colleagues to the fact that we might insert logic to support parameterization within their reports. As an illustration, we might employ the Descendants() function, through which means we might, as an example, parameterize the level within the function to allow control of the detail presented, among other elements of our query. (We might likewise insert other functions 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 Date Hierarchy (Calendar or Fiscal), Year, Quarter, Month, Date, 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. Moreover, we use the TYPED flag within the calculated member [Measures].[DayName], whose definition is as follows:


MEMBER [Measures].[DayName]

AS 

    [Date].[Calendar].[July 1, 2003]. PROPERTIES ('Day Name', TYPED) 

This means that the value returned is strongly typed (versus a simple string), based upon the data contained within the member property.

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


Illustration 2: Click Execute to Run the Query...

The Results pane is populated by Analysis Services, and the dataset shown in Illustration 3 appears.


Illustration 3: Results Dataset – .Properties Function with Calculated Members

In the view of the returned dataset, we see that the calculated members accomplish the intended purposes - generating the member properties strings for the specified Date Calendar hierarchy member within the Date level. Moreover, we see that the typed value for Day Name (actually a number) is also delivered as expected.

3.  Select File -> Save MDXQuery1.mdx As ..., name the file MDX059-001, and place it in a meaningful location.

Our client colleagues express satisfaction with the contextual backdrop we have established for employing the basic .Properties function, along with the instance of the extended function where we put the TYPED flag to work. Moreover, they 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, together with an example of the “focus” item within our current article, the use of the TYPED flag – all in a manner that lends itself to the parameterization opportunities that are expected to arise at the Reporting layer. They assure us that they understand how 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.

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

Summary ...

In this, the second half of a two-part article, we continued 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 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 delved further into the .Properties function, having covered its basic use in Part I. We explored the use of .Properties with the TYPED flag, whereby the property value is returned in its internal data type, introducing the enhanced function, and commenting upon its operation and what we can employ it to deliver. We reviewed the syntax involved with basic .Properties, and then focused upon the use of the TYPED flag to extend the function to obtain a strongly typed result.

We next undertook an illustrative practice example that included business uses for the basic function, as well as an instance of the .Properties function in combination with the optional TYPED flag we had introduced in the earlier sections, working with a query that capitalized upon the general capabilities of .Properties. As a part of our practice exercise, we briefly discussed the results dataset we obtained from the query 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