String Functions: The .Properties Function, Part II - Page 2

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.

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

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.

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

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.

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.

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.