July 2002 MDX Puzzle

The only measures useful in some types of analysis are measures that are derived from multiple loaded measures (i.e., those from the fact table) or from some calculation that uses other dimensions. In such cases, you need to create a cube that has only calculated measures visible. Creating such a cube is easy, but using MDX to query such a cube can present a challenge.

Use the Cube Wizard to create a new cube from scratch in the FoodMart 2000 database. In the Cube Wizard, choose the sales_fact_1997 fact table, then select two measures from the fact table (e.g., Store Cost and Store Sales). In the Cube Editor, create a calculated measure whose value is the result when you subtract one of the selected measures from the other, then set the Visible property of the first two measures to false. Finally, save and process the cube. When you're done, the cube should have only the calculated measure visible.

Now, write a query that displays the visible measures without directly referencing them. Here's a hint: \[Measures\].\[Measures Level\].Members doesn't help because it returns an empty set.

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.