December MDX Puzzle Solution Revealed - 03 Dec 1999

You can use calculated members to aggregate a custom group of dimension members. For example, you can create a calculated dimension member that sums a group of California cities.

CREATE MEMBER \[Sales\].\[Customers\].\[All
   Customers\].\[USA\].\[CA\].\[City Group\] AS

After you execute this statement, you can use \[City Group\] as a shortcut to the sum of the three California cities. How would you rewrite the above statement so that \[City Group\] is a shortcut to the same set of three California cities? This time you don't want the sum; you want the results to list all three cities every time you use the name \[City Group\].

The answer is

CREATE SET \[Sales\].\[City Group\] AS

A set is a shorthand notation for a group of members. You can specify this group of members as any MDX set expression. Beyond the obvious benefit of less typing, sets can also help performance. If you have a single query or a set of queries that use the same set of members repeatedly, you might want to create a set. OLAP Services can resolve the set expression once and cache it for multiple uses.

MDX stores sets differently from the way it stores calculated members. A calculated member appears in a dimension hierarchy, whereas a set is stored at the cube level. Notice in the above example that the cube name \[Sales\] is the prefix for \[City Group\]. If you store calculated members this way, then the result set can include members from different dimensions.

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.