MDX Operators: The IS Operator(2)

Procedure: Satisfy Business Requirements with MDX 数据挖掘研究院

Let′s assume, for purposes of our practice example, that we have received a request from representatives of our client, the Adventure Works organization. As we have noted in other articles of the series, the Reporting department, a group of client-facing authors and developers, often requests assistance such as this: our relationship with Adventure Works is such that we provide on-site augmentation for business requirements gathering and training, as well as workshops, in many cases, that illustrate approaches to meeting specific needs.

To establish the context of the latest request for assistance, the authors and developers in the group inform us that they wish to leverage the IS operator, where possible, to improve performance within queries, both in reports and other applications, where comparisons take place. They state that they have recently become aware, from entries that they have encountered within blogs where general MDX considerations are addressed, that the IS operator often affords more efficient comparisons between objects than using the equals ("=") operator. For example, they relate an example recently noted, where the blog author states something like the following:

Our colleagues tell us that the author argued that performing the comparison with the IS operator results in more rapid query execution. His reasoning, they recall, was that the conversion of "members" to strings, implicit in the use of the equals operator, is more time consuming and simply "doesn′t need to be done."

数据挖掘研究院

The Reporting department group informs us that they wish to test the assertions that the author seemed to be making. (They indicate that, after the manner of many "bloggers," the author attempted to "make his case" in unclear and imprecise terms, using mangled English within a "stream of consciousness" approach worthy of James Joyce.) The authors / developers predict that the use of the IS operator to perform comparisons will be more optimal, in most cases, within those of their own queries which rely upon the equals operator. They ask us to illustrate the use of the IS operator within a couple of working examples, so as to arm themselves with the familiarity needed to modify the respective queries to use IS. 数据挖掘研究院

After gaining an understanding of the need, we explain to the developers the basics surrounding the IS operator, as we have done in the introductory sections above, and then set about the assembly of examples to illustrate its use. 数据挖掘研究院

Procedure: Use the IS Operator to Perform Comparisons within a Calculated Member 数据挖掘研究院

Let′s construct a simple query to provide an illustration of the use of the IS operator within a common context, the definition of a calculated member based upon a comparison. As an example, we will work within a local scenario described by the client representatives.

数据挖掘研究院

The developers / authors cite the following example as useful. They would like to create a basic query that returns the Customer Count for the respective month, quarter, half-year and annual levels for Calendar Year 2004. In addition, they are interested in seeing a simple 3-month Rolling Average Customer Count, but they wish for this calculated measure to appear only at the month level, and for a null to appear at the quarter, half-year and annual levels, of the Date hierarchy. They further specify that they wish to see the calculated measure rounded to two decimal places. They prefer to present the Date hierarchy levels in the columns and the measures in the rows of the returned dataset.

-- MDX045-001-1 IS Operator in Definition of a Calculated Member
 
WITH
 
MEMBER
 
   [Measures].[3-Mo Rolling Avg Customer Count]
 
AS
 
   ′IIF(
     
      [Date].[Calendar].CURRENTMEMBER.LEVEL IS [Date].[Calendar].[Month],
 
         AVG(LASTPERIODS(3, [Date].[Calendar].CURRENTMEMBER), 
         
            [Measures].[Customer Count]),
    
               NULL
    
     )′, FORMAT_STRING = "#,###.00" 
 
SELECT 
    
   DESCENDANTS(
 
      [Date].[Calendar].[Calendar Year].[CY 2004],[Date].[Calendar].[Month],
 
         SELF_AND_BEFORE) ON AXIS(0),
    
   CROSSJOIN(
    
      {[Product].[Product Categories].[Category].[Bikes].CHILDREN },
    
         {[Measures].[Customer Count],
 
             [Measures].[3-Mo Rolling Avg Customer Count]}
    
                ) ON AXIS(1)
    
FROM
 
   [Adventure Works]
 
数据挖掘实验室
 

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

数据挖掘研究院

The above query supplies the simple rolling average that the developer group has requested alongside the monthly counts, thus providing, to some extent, a quick means of "reasonability testing" of the accuracy of the calculated member. 数据挖掘研究院

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

数据挖掘研究院

In the partial view of the returned dataset, we see that the calculated member accomplishes the intended purpose – generating the simple moving average at the month level, while returning null at the quarterly, bi-annual and annual levels of the Date hierarchy. 数据挖掘研究院

Our developer / author colleagues express satisfaction with the contextual backdrop we have established for introducing the IS operator. We will undertake using the operator outside of a calculated member in our next steps, within a "fresh" query we will construct to meet another illustrative business need.

Procedure: Use the IS Operator to Perform Comparisons within the FILTER() Function 数据挖掘研究院

The report authors / developers have an additional request which we can help them to meet with the IS operator. This time, they wish to use the Filter() function within a query which will allow them to focus upon Internet Sales at a specific geographical level. While our colleagues have asked that we craft the query to return the Internet Sales measure for customers within a single U. S. state, Georgia, for Fiscal Year 2004, we confirm their understanding that, given the core query, the capability to perform ad hoc queries, where the information consumer is prompted at runtime for a state or states (or members at other geographic levels, for that matter) becomes a simple matter of parameterizing the unique name for the state, etc., within the Filter() function we will place within the rows-axis specification of the query. Because we have demonstrated to the developers, that parameterization of this sort becomes easily attainable within Reporting Services, assuming that sufficiently sophisticated queries are put into place to support it (within either the Reporting Services or Analysis Services layers), the core query is deemed valuable to the team. 数据挖掘研究院

Having obtained consensus on the proposed target dataset, we are ready to begin constructing the query.

数据挖掘研究院

A new tab, with a connection to the Adventure Works cube (we can see it listed in the selector of the Metadata pane, once again) appears in the Query pane. 数据挖掘研究院

-- MDX045-002-1 IS Operator within a Filter() Function
 
SELECT 
 
{[Measures].[Internet Sales Amount]} ON AXIS(0),
 
{FILTER (
 
   [Customer].[Customer Geography].MEMBERS,
    
      [Customer].[Customer Geography].CURRENTMEMBER.PARENT 
    
         IS [Customer].[Customer Geography].[State-Province].[Georgia]
   
      ) 
 
   } ON AXIS(1)
   
FROM 
 
   [Adventure Works]
 
WHERE
 
   ([Date].[Fiscal].[Fiscal Year].[FY 2004])
 
数据挖掘研究院
 

The Query pane appears, with our input, as shown in Illustration 12. 数据挖掘研究院

The Results pane is, once again, populated by Analysis Services. This time, the dataset depicted in Illustration 12 appears. 数据挖掘实验室

In the returned dataset, we see that the query appears to deliver the intended results: only those members of Customer Geography whose parent is the State-Province of Georgia (the cities of Georgia) are displayed. 数据挖掘研究院

The effect, as we see, has been to filter the results dataset to return only those members that "pass" the conditional test enacted by the IS operator within the Filter() function. That is, only those members who return a "true" are included within the final results. 数据挖掘研究院

The client representatives inform us that their immediate goals have been met, and that the examples we have shared have illustrated the principles of operation behind IS operator. We discuss related matters, such as parameterization within Reporting Services, and make an appointment to return to the client site at a future date to demonstrate the concepts. 数据挖掘实验室

Summary ...

In this article, we shifted our examination from the MDX functions, as we have upon occasion in the past, to concentrate upon an operator. We introduced the IS operator, noting that, like other logical operators, it evaluates values and returns a Boolean value. We explained that the IS operator′s utility lies in its capability to perform comparisons between objects within Analysis Services.

After discussing the straightforward purpose of the IS operator, to ascertain the equivalence of two object expressions, we examined the manner in which IS manages to do this, and ways we can leverage the operator to perform efficient comparisons to meet various business needs in our own environments. Next, we examined the syntax with which we employ IS. Finally, we undertook illustrative examples whereby we put the IS operator to work, both within the definition of a calculated member and, in conjunction with the MDX Filter() function, within an axis specification of a SELECT statement, to gain some hands-on practice in its use. Throughout our practice session, we briefly discussed the results datasets we obtained from each of the queries we constructed.

数据挖掘研究院