Hitachi Consulting BI Blog

Hierarchies Become Real

In Analysis Services 2000, there was no real distinction between a dimension and a hierarchy. The notion of "hierarchy" was simply a syntactic convention to group dimensions by a prefix. The two "hierarchies" Time.Calendar and Time.Fiscal, were really two independent dimensions.

is completely different. A dimension consists of a primary (granularity) key, plus as many other "attributes" as you want to add. Each attribute can (and, by default, does) form its own little mini-hierarchy. For example, a customer dimension with Age, Gender, and Nationality attributes would, by default, contain separate hierarchies for Age, Gender, and Nationality. In AS2K, you could manually create these three "hierarchies", but they would really be separate dimensions. The difference becomes obvious if you nest the hierarchies on a single axis.

In AS2K, if you nest Age, Gender, and Nationality, you would get a list with all nationalities repeated for all genders (uh, I mean, for both genders), and that combination repeated for all age brackets. Even if you didn't have any 83-year-old female customers in Lithuania, that combination would still appear; the row would just be empty. You could use NON EMPTY on the rows to get rid of the non-existent combination, but for large, sparse, combinations, assembling all the possibilities and then discarding the ones with no data is a very slow operation.

In AS2005, if you nest Age, Gender, and Nationality—which now really are hierarchies of a single dimension—you get a list of only those combinations for which a real customer exists. So if you still don't have any 83-year-old female Lithuanian customers, that combination will not even show up on the axis—whether you use NON EMPTY or not. Conversely, if you do have a 14-year-old Lithuanian boy as a customer who has never actually bought anything, that combination does show up, but the data cells are empty.

In AS2K, an empty row can appear for two different reasons: 1) the combination of attributes does not exist in the dimension, or 2) the combination of attributes exists in the dimension, but does not appear in the current slice of the facts. In AS2005, only the latter will produce an empty row; the former will never happen. The implication may seem subtle, but it can have a profound impact on query performance—or, more precisely, on the ability to create more flexible reporting structures that still give superb performance.