Intrinsic Member Properties: The MEMBER_VALUE Property - Page 4

Preparation within Analysis Services – SQL Server Management Studio

To reinforce our understanding of the basics we have covered so far, we will use the MEMBER_VALUE property in a couple of examples that illustrate its operation. We will do so in simple scenarios that place MEMBER_VALUE within the context of meeting business requirements similar to those we might encounter in our respective daily environments. The intent, of course, is to demonstrate the operation of the MEMBER_VALUE property 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 can create our first query within the section that follows.

Procedure: Satisfy Business Requirements with MDX

As a basis for our practice example, we will continue to work within our foregoing assumption that we have received a call from the Reporting department of our client, the Adventure Works organization, requesting our assistance in meeting a specific report presentation need. The client has implemented the integrated Microsoft BI solution - in addition to using Analysis Services as an OLAP data source, they use Reporting Services as an enterprise reporting solution. The MDX we explore together, we are told, will thus be adapted and extended for ultimate use within Reporting Services, in multiple parameterized reports.

Much as they have in past events, recorded in earlier articles of this series, a group of report authors want to display the Names of the Customer Geography Cities (the City level members of the Customer Geography hierarchy of the Customer dimension), alongside the respective “MDX Qualified Names” (their term for the Unique Names within Analysis Services), to provide an index, or map, for a developer who needs the Unique (“MDX”) Names, alongside the total Internet Sales Amount for each, for a reporting project he has undertaken. In addition, and unlike previous requests in this arena, our colleagues tell us that they also want to present the newly added (in the preparation section above) customer Postal Code information that has now been added via the Member Value property of each City attribute member. They state that they would like to present the Postal Code between the Unique (“MDX”) Names and the total Internet Sales Amount for each customer within the returned dataset. Moreover, they state that they may choose to parameterize Postal Code within the reports at a later time.

This represents a simple, yet practical, need that we can readily satisfy using the associated intrinsic member properties. Just as we did with the MEMBER_UNIQUE_NAME property (as well as others) in earlier sessions, we will employ MEMBER_VALUE in conjunction with a relative function, .CurrentMember. (We accomplished similar objectives using both the MEMBER_NAME and MEMBER_UNIQUE_NAME properties in earlier scenarios, so our example will also serve, to a small extent, as a review of what we covered in Intrinsic Member Properties: The MEMBER_NAME Property and Intrinsic Member Properties: The MEMBER_UNIQUE_NAME Property.) We will create a basic query that returns the City names for each U.S. City in which we have customers (whether we have conducted Internet Sales with them or not), together with the Names, Unique Names and Values (the Postal Code) for each respective U.S. City. Some of the Names, Unique Names and Values we generate with the query will ultimately find their way into the Dataset definition of reports that the developer intends to construct within Reporting Services – and any of these values can be used in axes, slicers, and so forth, within queries against the Analysis Services cube under consideration.

The requests relayed by the client representatives evidence a need to present multidimensional data in a manner that we think might best be served with the MEMBER_NAME, MEMBER_UNIQUE_NAME, and (the primary focus of this article) MEMBER_VALUE properties. Once our colleagues provide an overview of the business requirements, and we conclude that MEMBER_VALUE is likely to be a key component of the option we offer, we provide the details about the function and its use, much as we have done in the earlier sections of this article. We convince the authors that they might best become familiar with the MEMBER_VALUE property (as well as confirm their previous understanding of the MEMBER_NAME and MEMBER_UNIQUE_NAME properties) by examining an introductory example, where our objective is to generate a straightforward list of City member Names, Unique Names, and Values (Postal Codes), together with corresponding Internet Sales Amounts, in a results dataset.

Procedure: Use the MEMBER_VALUE Property within the Generation of a Simple List of Members with a Measure in a Results Dataset

Let’s construct a simple query, therefore, to return the requested Customer City information, presenting the Names, Unique Names, Values (Postal Codes) and Internet Sales Amount in four, side-by-side columns, with the corresponding City member names as rows.

-- MDX070-01 Using MEMBER_NAME, MEMBER_UNIQUE_NAME and MEMBER_VALUE
--   to generate a name / unique name / postal code list within the data grid
    
WITH
MEMBER
  [Measures].[Customer Geography - Name]
AS
  '[Customer].[Customer Geography].CurrentMember.MEMBER_NAME'
 
MEMBER
   [Measures].[Customer Geography - MDX Qual Name]
AS
   '[Customer].[Customer Geography].CurrentMember.MEMBER_UNIQUE_NAME'
   
MEMBER
   [Measures].[Customer Geography - Postal Code]
AS
   '[Customer].[Customer Geography].CurrentMember.MEMBER_VALUE'
         
SELECT
   {[Measures].[Customer Geography - Name], 
      [Measures].[Customer Geography - MDX Qual Name],
         [Measures].[Customer Geography - Postal Code],
         [Measures].[Internet Sales Amount]} 
      ON AXIS(0),
   
   {DESCENDANTS( 
      [Customer].[Customer Geography].[Country].&[United States], 
         [Customer].[Customer Geography].[City]
      )} 
   ON AXIS(1)
 
FROM    
   [Adventure Works]  

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

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

We see Customer Geography City names, the output of the Customer Geography - Name calculated member, populating the first data column. The respective Customer Geography City Unique Name (a “qualified” MDX name that can, itself, be used within a query against the Adventure Works cube) for each City occupies the second data column (which we populate via the Customer Geography - MDX Qual Name calculated member in the query). The Postal Codes, the output of the Customer Geography – Postal Code calculated member, populate the third data column, which appears to the immediate left of the fourth column, containing the corresponding Internet Sales Amount measure. The Customer Geography City members themselves occupy the row axis, as the client has requested.

The Customer Geography – Postal Code calculated member employs the MEMBER_VALUE, property (the focus of this article), in conjunction with the “relative” .CurrentMember function. The calculated members Customer Geography - Name and Customer Geography - MDX Qual Name employ the MEMBER_NAME property and the MEMBER_UNIQUE_NAME property, respectively, each also in conjunction with the “relative” .CurrentMember function. As we can easily see from our practical example, these three calculated members are then used together to return, via the dataset we see above, a combination list of the names (which might be used as captions / labels within a given report layout), the qualified names (which could be passed from the reporting layer to Analysis Services via parameterization at report runtime), and the postal codes (which we could parameterize, display, or both, within the report) of the members that we specify in our row axis.

Our client colleagues express satisfaction with our initial solution, and state that it satisfactorily displays the Postal Code values they want, alongside the respective Names and qualified / Unique Names of the Customer Geography Cities, together with the associated Internet Sales Amount measure, within Analysis Services. They state that they expect this approach to provide the desired index for the developer who needs the Names, Unique (“MDX”) Names, and Postal Code values, alongside the total Internet Sales Amount, for each of the Customer Geography Cities, and that this “map” will equip him to complete the reporting project he has undertaken.