Hitachi Consulting BI Blog

Real Hierarchies Interact in Unusual Ways

In an earlier post, I got effusive about real hierarchies in AS2005. Multiple hierarchies in a dimension have a lot of very good consequences, but there are also some implications that are, at least in the current build—how shall we say it?—counter-intuitive.

Imagine a customer dimension, with a Geography multi-level hierarchy consisting of Country, State, and City levels, and also with separate hierarchies for Country, State, and City. Now imagine a report that has the Geography hierarchy on rows—expanded to show all levels—and the Country hierarchy as a slicer (page filter). What do you expect to have happen to the rows for the Mexico portion of the Geography hierarchy when you slice by Country = USA?

I half-expected all the Mexico rows to disappear (because of the new interaction between hierarchies in a dimension), but they don’t. OK. I can live with that. But I really expected all the Mexico rows to be empty, because the slicer limits the data to USA. They are not empty. In fact, the USA slicer is completely ignored. In fact, even if the rows axis shows only the City level of the Geography hierarchy (so that you can’t see the country on the rows, the USA slicer is still ignored.)

I do understand the logic behind this. When a row shows Guadalajara, that member implies that the country is Mexico. When MDX figures out the value for a cell, it needs one member from each hierarchy. By the time it gets to the slicer, the Country has already been filled in.

The MDX behavior hasn’t changed from previous versions—and it shouldn’t. In MDX, an “inner” member always overrides an “outer” member. It has to do this, or set functions wouldn’t work. The only thing that has changed is that you can now put members from the same dimension on two different axes, provided that they are in different hierarchies. (You could always put different hierarchies of the same dimension on different axes, but in AS2K different hierarchies were really different dimensions, so that doesn’t count.)

My vote is for AS2005 to disallow slicers with attributes that are implied by levels on another axis. That would be consistent with AS2K behavior and would avoid the counter-intuitive results. But maybe the brain trust in Redmond will come up with a better solution by the time the product ships.