The Baker's Dozen: 13 Tips for Querying OLAP Databases with MDX : Page 3

Tip 4: "Filter, Wherefore Art Thou?"
So how do you use MDX to filter based on specific conditions? For example, you may want to filter on all products where the gross profit per unit is greater than $500.00, where the product contains the word 'BIKE' in the description.

Use FILTER inside a CROSSJOIN—not the other way around.

MDX provides a FILTER function that takes on two parameters: the SET definition, and the filter expression. Here is an example:

   SET [FilteredOrderedSales] AS FILTER(
   ORDER(
        ([Customer].[State-Province].CHILDREN,
         [Product].[Subcategory].CHILDREN ),
         [GrossProfitPerUnit],BDESC),
         [GrossProfitPerUnit] > 500
   AND INSTR(
         [Product].[SubCategory].
            CURRENTMEMBER.Name,'BIKE') > 0)

Notice that the code uses the INSTR function and must evaluate each member using the CurrentMember.Name convention.

Tip 5: Name, "RANK", and Serial Number
SQL Server 2005 provides a terrific new function to RANK result sets (i.e., the new ROW_NUMBER OVER syntax). MDX provides functionality to RANK result sets as well, with the RANK function.

Suppose you want to rank sales by product, according the sales amount descending (so that the top-selling product is ranked #1, etc.) In the simplest form, the RANK function takes on two parameters: which member you are ranking on (SubCategory.CurrentMember) and the ordered set against which each subcategory CurrentMember is evaluated:

If you want to rank a result set based on an order, first create an ordered set and then create a calculated member that applies the RANK function against the ordered set. This will usually provide maximum performance.

   WITH SET [SalesRank] AS
   ORDER( 
      [Product].[Subcategory].CHILDREN ,
      [Measures].[Internet Sales Amount], BDESC )
   MEMBER [Measures].[SalesRank] AS 
      RANK([Product].[SubCategory].CurrentMember, 
         [SalesRank])
   SELECT 
      {[Measures].[SalesRank], 
       [Measures].[Internet Sales Amount]} on 0,
       [SalesRank] on 1
   FROM [Adventure Works] 

That covers a basic example. But suppose you want to rank product sales within customers? Or suppose you want to rank product sales across all products and customers? Tip 7 will cover this in more detail.

Tip 6: Can You "TOP" This?
You can easily perform the equivalent of a SELECT TOP 10 by using the TOPCOUNT function. TOPCOUNT takes three parameters: the set definition, the number, and the measure on which to base the top order logic:

   -- Get the top 10 states based on sales
   SELECT  
      [Measures].[Internet Sales Amount] on 0,
      TOPCOUNT( [Customer].[State-Province].CHILDREN,
      10,[Internet Sales Amount]) ON 1
   FROM [Adventure Works]

If you want to retrieve the states that make up the top ten percent of sales, you can use the TOPPERCENT function:

   -- Get the States that make up 
   -- the top 10% of total sales
   SELECT  
     [Measures].[Internet Sales Amount] on 0,
     TOPERCENT( [Customer].[State-Province].CHILDREN,
     10,[Internet Sales Amount]) ON 1
   FROM [Adventure Works]

Finally, if you want to retrieve the states that make up the top ten million dollars in sales, you can use the TOPSUM function:

   -- Get the States that represent 
   -- the top 10 million dollars
   SELECT  
      [Measures].[Internet Sales Amount] on 0,
      TOPSUM( [Customer].[State-Province].CHILDREN,
      10000000,[Internet Sales Amount]) ON 1
   FROM [Adventure Works]

Previous Page

Next Page