Querying dimensions in MDX
In this article we will discuss and evaluate different methods Analysis Services 2005 provides for accessing dimension members. Requirements, flexibility and performance tradeoffs will be discussed.
In the OLAP system, the primary object for storing data is the cube. MDX as a language was designed in the first place for accessing cubes. Yet, dimensions are important for accessing data as well. There are plenty of scenarios where dimension rather then cube should be queried
- Hierarchy browser - all client apps have those
- UI for setting more sophisticated filters and/or axes then traditional drill up/down navigation
- Reports and analysis on dimensions (i.e. "how many products weight more then certain threshold", "show all orders placed in last two weeks" etc)
- UI for dimension writeback
- etc
There is an old argument in the OLAP world - whether dimension members (more precisely attribute members) represent metadata or data. From one hand attribute members are really just coordinates in the multidimensional space to navigate to cells which contain the data. And without doubt, members of the Measures dimension are considered to be metadata by everybody. Some OLAP products treat all dimension members as metadata, for example in older versions of Essbase, the cube designer was expected to enter all dimension members by hand in the outline editor (I don't know whether it is true with the Essbase 7 'Ukraine'). If the designer wanted to automatically load prepared list of members from flat file, it had to be done with additional HIS tool, so clearly this scenario was treated as not mainline. Probably this approach works fine with small models typical for financial and planning cubes. But in sales and marketing applications, the cardinality of dimension can easily reach hundreds of thousands up to tens of millions members. This is why Analysis Services treats attribute members as data, which is loaded from the database. Only instead of fact table, dimension members are loaded from dimension table (of course, degenerate dimensions are loaded from fact table as well). Therefore, most of the methods for accessing dimension members are very similar to the accessing cube data, i.e. through MDX.
Tags: mdx