Skip navigation

May 2002 MDX Puzzle Solution


A variety of businesses use a type of analysis called dependency risk analysis. This type of analysis determines whether one group of items in your business (e.g., products) is overly dependent on just one item of another group (e.g., customers). Retailers describe an overly dependent item as at risk. For example, you might want to find out which products depend most on a single customer. To answer this question, you need to find what percentage of total store sales for each product comes from a single customer. To test yourself, find the top 10 highest risk products, and show the percentage and amount of the product's sales that are at risk.

Listing A shows a query that defines two new measures. One measure calculates the total of Store Sales for the selected product (e.g., you might want to find the total sales to that product's top customer). The other measure calculates the percentage of the product's total sales that's at risk. The MDX query in Listing A uses the PercentAtRisk measure to find the 10 products with the highest percentage of Store Sales at risk. The query then displays both the amount at risk and percentage at risk for each of the top 10 products.

TAGS: SQL Server
Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.