Hierarchical Functions in MDX

One useful feature of OLAP cubes is their ability to use hierarchies to retrieve result sets by selecting a specific dimension member (a specific market, product group, or time element such as a quarter or month) and drilling down (or up) to see all the child (or parent) data. After you’ve established hierarchies in your OLAP dimensions, you can use several MDX functions to navigate these hierarchies. Let’s take a look at some examples.

For starters, you can use the MDX Children function to retrieve all records for the next level down in a hierarchy, based on a specific dimension member. For example, you can retrieve sales for all the subcategories under the category of Bikes with the following query. The results are ...

Get Smart Business Intelligence Solutions with Microsoft® SQL Server® 2008 now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.