July 2002 MDX Puzzle Solution

Sometimes, you need to create a cube that has only calculated measures visible because 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. 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.

The following query returns the visible measures you're after:

SELECT \{\[Product\].\[All Products\]\} ON COLUMNS,
  AddCalculatedMembers( \{\[Measures\].DefaultMember\} ) ON ROWS
FROM Sales

SQL Server 2000 Analysis Manager lets you choose which calculated measure is the default measure. You can make a set with the default measure, then use the AddCalculatedMembers() function to add the rest of the calculated measures to the rows. If you deal with more than a handful of cubes, you'll run into a situation in which all measures are calculated. Being able to display visible measures without referencing them is handy in those situations because it lets you write MDX that doesn't need to change when the measures' dimension changes.

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.