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:

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.

MDX Essentials Series
The LEVEL_NUMBER Member Property
The LEVEL_UNIQUE_NAME Intrinsic Member Property
Intrinsic Member Properties: The HIERARCHY_UNIQUE_NAME Property
Intrinsic Member Properties: The DIMENSION_UNIQUE_NAME Property
Further Combination of BottomCount() with Other MDX Functions
Combine BottomCount() with Other MDX Functions to Add Sophistication
Basic Set Functions: The BottomCount() Function, Part I
Intrinsic Member Properties: The MEMBER_VALUE Property
Intrinsic Member Properties: The MEMBER_UNIQUE_NAME Property
Intrinsic Member Properties: The MEMBER_NAME Property
Intrinsic Member Properties: The MEMBER_KEY Property
Intrinsic Member Properties: The MEMBER_CAPTION Property
Set Functions: The StripCalculatedMembers() Function
Set Functions: The AddCalculatedMembers() Function
MDX Numeric Functions: The Min() Function
MDX Numeric Functions: The Max() Function
Set Functions: The .AllMembers Function
MDX Essentials: Set Functions: The MeasureGroupMeasures() Function
String Functions: The .Properties Function, Part II
String Functions: The .Properties Function
Logical Functions: IsGeneration(): Conditional Logic within Filter Expressions
MDX Scripting Statements: Introducing the Simple CASE Statement
Logical Functions: IsGeneration(): Conditional Logic within Calculations
Logical Functions: IsAncestor(): Conditional Logic within Filter Expressions
MDX Clauses and Keywords: Use HAVING to Filter an Axis
Logical Functions: IsAncestor(): Conditional Logic within Calculations
Logical Functions: IsSibling(): Conditional Logic within Filter Expressions
Logical Functions: IsSibling(): Conditional Logic within Calculations
MDX Operators: The IsLeaf() Operator: Conditional Logic within Filter Expressions
MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations
MDX Numeric Functions: The .Ordinal Function
Other MDX Entities: Perspectives
MDX Operators: The IS Operator
MDX Set Functions: The Distinct() Function
MDX Set Functions: The ToggleDrillState() Function
Set Functions: The DrillUpLevel() Function
Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions
MDX Set Functions: DrillDownLevel()
MDX Set Functions: The DRILLUPMEMBER() Function
MDX Essentials: Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions
MDX Essentials : Set Functions: The DRILLDOWNMEMBER() Function
MDX Essentials: Drilling Through with MDX: The DRILLTHROUGH Statement
MDX Essentials: String Functions: The .UniqueName Function
MDX Essentials: String Functions: The .Name Function
MDX Essentials: String / Numeric Functions: The CoalesceEmpty() Function
MDX Essentials: Basic Set Functions: The TopCount() Function, Part II
MDX Essentials: Basic Set Functions: The TopCount() Function, Part I
MDX Essentials: Enhancing CROSSJOIN() with Calculated Members
MDX Essentials: Set and String Functions: The GENERATE() Function
MDX Essentials: The CROSSJOIN() Function: Breaking Bottlenecks
MDX Essentials: String / Numeric Functions: More on the IIF() Function
MDX Essentials: String / Numeric Functions: Introducing the IIF() Function
MDX Essentials: Logical Functions: The IsEmpty() Function
MDX Essentials: Basic Set Functions: The EXTRACT() Function
MDX Essentials: Numeric Functions: Introduction to the AVG() Function
MDX Essentials: Basic Member Functions: The .Item() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Subset() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Tail() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Head() Function
MDX Essentials: Basic Set Functions: The CrossJoin() Function
MDX Essentials: Basic Numeric Functions: The Count() Function
MDX Essentials: Basic Set Functions: The Filter() Function
MDX Essentials: Basic Set Functions: The EXCEPT() Function
MDX Essentials: Basic Set Functions: The Intersect() Function
MDX Essentials: Basic Set Functions: The Union() Function
MDX Essentials: Basic Set Functions: The Order() Function
MDX Essentials - MDX Time Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions
MDX Time Series Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions
MDX Essentials - MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions
MDX Essentials: MDX Member Functions: "Relative" Member Functions
MDX Member Functions: The Cousin () Function
MDX Essentials: Member Functions: More "Family" Functions
MDX Member Functions: The "Family" Functions
MDX Essentials: MDX Members: Introducing Members and Member
MDX Essentials : MDX Operators: The Basics
MDX Essentials: Structure of the MDX Data Model
MDX at First Glance: Introduction to SQL Server MDX Essentials








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers