String Functions: The .Properties Function - Page 2

September 4, 2007

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:

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.

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.

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

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

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.

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

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.

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.