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