15 ways to enhance your MDX arsenal
The most frequent request that I receive from readers is for more information about MDX. They particularly want more MDX examples. In this column, I offer 15 MDX queries that you can test in SQL Server 2000 Analysis Services' FoodMart 2000 sample cubes. The following real business questions focus on the problem of sales analysis.
1. What product brands does the company sell in each state in the United States? The query in Listing 1 creates a set called SoldInUSA, which determines the product brands sold in the United States by eliminating brands that have an empty Unit Sales value. The query defines a calculated member that determines whether the current product is sold in the current state. This member returns Yes if the product is sold in the state and No if the product isn't sold in the state. The query displays the states on the cube's columns and the products on the rows. The cell values are either Yes or No, depending on the corresponding product-state combination.
You might notice that this query's result returns the same brand name more than once. The Sales cube divides product categories into brands. Therefore, if one brand makes products in more than one category, that brand will appear multiple times in the hierarchy. At first glance, the repetition might appear to be a problem, but you probably want to treat multiple occurrences of the same brand as different brands. For example, suppose one company produces luggage and clothes. As an analyst, you'd treat these product categories as different brands because you wouldn't want the luggage brand's failure to affect your decision to carry the clothes brand.
2. What are the top product categories across all stores? Listing 2's straightforward query uses the TopCount() function to determine the top 10 product categories based on unit sales.
3. What are all the food and beverage brands that were sold in the United States during any of the past three quarters? The query in Listing 3 demonstrates how to define time-dynamic sets—a valuable technique. A time-dynamic set moves forward in time as the cube fills with data. The LastQuarter set determines the time dimension's most recent quarter that contains data. The Last3Quarters set builds on LastQuarter by using the Range() function—denoted with a colon (:)—to select the three consecutive quarters that end with LastQuarter. I didn't use the Tail() function in the LastQuarter definition to retrieve the past three quarters because doing so might return three nonconsecutive quarters. An empty quarter might occur between full quarters, and the Filter() function would eliminate the empty quarter. The Lag() function, combined with the Range() function, guarantees that the quarters are consecutive.
In this query, the item(0).item(0) function retrieves a set's first member. Because a set is technically a group of tuples (i.e., lists of members from different dimensions), you must use the first Item() function to select a tuple in the set and the second Item() function to select a member in the tuple.
4. What are the recent sales trends for the 10 best-selling product brands? The query in Listing 4 uses TopCount() to find the top-selling product brands, then uses Listing 3's time-dynamic set technique to determine unit sales for the past 6 months. The query then places the top brands in the rows, along with their unit sales for the past 6 months. You might use this query in a line chart for monitoring product-brand performance.
5. Which product brands make up the top 80 percent of the company's sales? TopPercent() is similar to the TopCount() function but returns the smallest number of items that make up 80 percent of the total unit sales. (In other words, these are the items that have the largest numbers of unit sales.) Listing 5's query displays the product brands on the query result's rows, along with their total Unit Sales value.
6. Which product brands make up the bottom 20 percent of volume? The query in Listing 6 uses BottomPercent() to find the largest number of items that make up only 20 percent of the total unit sales. (In other words, these are the items that have the smallest numbers of unit sales.) The query uses the Non Empty keyword to eliminate any brands that have no unit sales. Using Non Empty is slightly different from using the combined Filter() and IsEmpty() functions (which you see in Listing 3) because Non Empty works on all items on an axis (e.g., rows).
7. Which are the top five stores, and who are each store's top five customers? The query in Listing 7 demonstrates the useful but complex Generate() function. If you have programming experience, you'll find the Generate() function similar to the For Each statement in Visual Basic (VB) or C#. The Generate() function calculates the second parameter for each item in the first parameter.
In the following example
Generate( {Miami, Atlanta}, Customers.CurrentMember.Parent)
Generate() calculates the second parameter, in this case Customers.CurrentMember.Parent, for each item in the first parameter—{Miami, Atlanta}. In this example, the second parameter's MDX expression returns the parent of the current item, so the final result of this Generate() function—{Florida, Georgia}—would be a set of the parent of each item in the first parameter.
This query also uses the Generate() function to perform a nested rank. The function determines the top five customers for each of the top five stores. Then, Generate() unions the sets of customers together to create a list of the 25 store-customer combinations.
8. For each product brand, what are the two top-selling products and what percentage of total sales do they make up? To answer this question, Listing 8's relatively complicated query uses a combination of a calculated member and the Generate() function. The calculated member determines the percentage of the brand's total unit sales that a brand's top two products make up. The Generate() function searches the list of all brands and returns a set of each brand's top two products and the percent of total value that those products account for.
Prev. page
[1]
2
next page 
