THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

How to work with Dimension Leaves

Dimension leaves is one of the fundamental concepts in OLAP. Dimension leaves are used in variety of situations – from defining calculations on the leaves to aggregate up, and to different UI icons for leaves vs. non-leaves. But while it may seem that this concept is simple, it is actually more complicated. Most of the confusion comes from the fact, that there are two distinct concepts, both of which are commonly called leaves:

 

  • Hierarchy leaves
  • Measure group leaves

In this article we will explain the differences between the two and discuss the Analysis Services and MDX support for both of these concepts.

 

Hierarchy Leaves

 

Hierarchy leaves refer to the hierarchy members which don’t have visible children. It is important to recognize hierarchy leaves in UI, to give the user indication that such member cannot be drilled down further.

 

Measure group Leaves

 

Measure group leaves (also called sometimes fact leaves, fact granularity etc) refer to the measure group granularity, i.e. the lowest level at which data is loaded into the partitions. It is important to recognize measure group leaves for non-allocated writeback (because it can only done at leaves), for calculations such as currency conversion which need to modify the data at the lowest granularity etc.

 

Sometimes (in simple cubes and dimensions), the same member can seen as both hierarchy leaf and measure group leaf. However, there are important differences which are summarized in the following table:

 

Feature

Measure group leaves

Hierarchy Leaves

Defined on

Attribute

Hierarchy

Current measure

Affects

Doesn’t affect

Multiple hierarchies

Doesn’t affect

Affects

Dimension security

Doesn’t affect

Affects

Hidden members

Doesn’t affect

Affects

Unbalanced hierarchies

Doesn’t affect

Affects

 

 

Current measure

 

Whether or not the attribute member is a measure group leaf depends on the current measure group, which, in turn is determined by the current measure. Therefore, same attribute can be measure group leaf for one measure and non leaf for another measure – depending on the granularity of the respective measure groups.

 

Multiple hierarchies

 

Member which is hierarchy leaf in one hierarchy might not be a leaf in another hierarchy, but for the measure group member is either always a leaf or always isn’t.

For example, if we have two hierarchies in the Customer dimension – Country/State/City/Name and Country/State, then WA is not a leaf in the first hierarchy but is a leaf in the second hierarchy.

 

Dimension security

 

If all the children of a member are secured, then this member becomes hierarchy leaf in that hierarchy. Dimension security doesn’t affect measure group leaves.

 

Hidden members

 

If all the children of a member are hidden, then it becomes hierarchy leaf. Members which can be hidden are UnknownMember, HideMemberIf (when MDX Compatibility Mode is 2) etc. However, it is important to note, that in the SCOPE statement or in the left hand side of assignment, that hidden members are treated as if they were visible.

 

Unbalanced hierarchies

 

If the member didn’t have any relating members in the dimension table, then the natural hierarchy will be unbalanced. Such member is hierarchy leaf, but still not measure group leaf.

 

Other considerations

 

There are also several related issues such as

 

-         Parent Child Hierarchies

-         Difference between Data Members and Leaf members

-         Placeholder Members

 

However, we will not discuss them here, perhaps in another article.

 

Now let’s look into different MDX functions and other functionality that AS provides to deal with leaves.

 

Retrieve hierarchy leaves

 

The MDX function to get the hierarchy leaves is Descendants(member,,LEAVES). It can be used to get all the leaves under the current position in the hierarchy by using

 

Descendants(hierarchy.CurrentMember,,LEAVES)

 

Or it can be used to get unconditionally all the hierarchy leaves by using

 

Descendants(hierarchy.Levels(0).Members,,LEAVES)

 

This form of call to Descendants function uses set as its first argument. This is to account for hierarchies with non aggregatable first level, i.e. when the first level is not ‘All’, but contains multiple members. Note, that while this is indeed the most general form, it will raise an error when used in the SCOPE statement. However, as we will see later, usually we won’t want to use Descendats(,,LEAVES) inside SCOPE anyway.

In simple scenarios Descendants(,,LEAVES) is equivalent to returning the members from the last level of the hierarchy, i.e. hierarchy.Levels.Members(hierarchy.Levels.Count-1), however, unlike this expression, it works correctly for situations like parent child, ragged hierarchies, dimension security, MDX Compatibility Modes etc.

Another important case is Descendats(member, level, LEAVES), which is useful for the parent child hierarchies – it will return the descendants from the specified level, plus all the leaves above this level.

 

Checking whether the member is an hierarchy leaf

 

There are many ways in which people check whether member is an hierarchy leaf. We will start with the most correct way first, and then look into other way and understand why they are not correct.

From MDX, the best way to check for hierarchy leaf, is simply to call the IsLeaf(member) function. This function is optimized to work in the most efficient way, for example for the non ragged, non parent child hierarchy without dimension security, it will simply check the level of the member, for parent child hierarchy it will check in efficient way for data members etc.

Other methods that I have seen people using are:

 

member.Children.Count = 0

 

This practically always returns the same result as IsLeaf(member), after all it literally implements the definition of leaf – the member which doesn’t have visible children. However, it has worse performance then IsLeaf, because it performs more operations – constructs the set of children, takes count of tuples in it etc. It also cannot use the optimizations mentioned above.

 

IsGeneration(member, 0)

 

Checking for first (zero’th) generation is much less efficient then IsLeaf

 

member.Level.Ordinal = member.Hierarchy.Levels.Count-1

 

As we mentioned above, comparing the level of the current member with total number of levels in the hierarchy will only work correctly for regular, non ragged and non parent child hierarchies without dimension security etc.

 

Sometimes, it is desirable to know whether the member is hierarchy leaf or not without creating additional calculations for the query. It is useful, when the leaf/no leaf decision is not fed into other calculations, but when it is needed purely for UI purposes to mark all the members on the query result axes. It is not advisable to create special calculated member for that, since, as we know from the caching whitepaper, creating query calculated members prevent calculation caching at the session and cube levels.

To figure out leaf/no leaf without additional calculations, people tried to use the builtin member property DISPLAY_INFO. Indeed, two bytes out of that four byte structure are dedicated to CHILDREN_CARDINALITY. While two bytes is too low to hold the actual number of children for AS2005 (but it was enough for AS2000), it still seems OK, since the only important information here is 0 or non 0. However, OLEDB for OLAP spec states that the CHILDREN_CARDINALITY is only an estimate, and therefore sometimes it won’t be accurate. Unfortunatelly, in the current version of AS2005, it is indeed only an estimate, and sometimes the true leaf will have non zero, and non leaf will have zero. Therefore, application which needs reliable way of checking for leaf, cannot rely on DISPLAY_INFO.

 

There is also an additional builtin member property CHILDREN_CARDINALITY, which can be specified in the DIMENSION PROPERTIES clause of the axis definition in the MDX SELECT query. However, in the current version of AS2005 it is mostly useless, as it only has values of 1000 or 0, and even there cannot be used to determine whether the member is really leaf or not. We hope, that this member property will be fixed in the future versions.

 

 

Position on measure group leaves

 

In AS2000 there was no easy way to get to the granularity of facts in MDX automatically. The writer of MDX was supposed to know which levels were disabled (i.e. excluded from the granularity) and use <>.MEMBERS on the lowest non-disabled level. The client applications could discover the lowest non-disabled levels for each measure group (or non virtual cube in AS2000 terms) by means of looking at MDSCHEMA_MEASURES schema rowset, which contained comma separated list of lowest non disabled levels in the LEVELS_LIST column. But this was very cumbersome way, which had to be adjusted for parent child and ragged hierarchies, and I don’t know any customer who used this method.

Therefore, in AS2005, we provided easy way to get to the measure group leaves, by the means of Leaves function. Leaves function can be used in one of the two forms:

 

  1. Leaves() – positions on the granularity of the measure group by all of the dimensions related to that measure group
  2. Leaves(dim) – positions on the granularity of the measure group only in the specified dimension.

 

Note, that it is really exceptional case, where in MDX you can use dimension. Everywhere else, MDX accepts hierarchy. But since granularities are defined in terms of the attributes rather then hierarchies, using dimension here makes sense.

 

So what does Leaves function returns ? The answer is that it returns a subcube which can be used inside SCOPE statement or at the left-hand side of the assignment. It does not return a set ! Again, this makes sense, since subcubes are defined in terms of attributes, but sets are defined by hierarchies. This is one of the reasons why Leaves function is not safe to be used in the SELECT statement, or inside named sets or in other MDX expressions. While Analysis Services may convert subcube returned by Leaves function to set (by choosing attribute hierarchies corresponding to the attributes), it is not supported usage.

 

Another important question which needs to be answered is, since we said that the Leaves function operates on measure group leaves, how does it know which measure group to use ? The answer lies again in the usage of Leaves inside SCOPEs or assignments. Leaves function checks the current SCOPE and looks whether it is constrained to a single measure group (or multiple measure groups, but with exactly same granularity). If the current SCOPE has multiple measure groups with different granularities, Leaves function will raise an error, since it doesn’t know for which measure group should it calculate the granularity. This is another reason why Leaves is not safe to be used in SELECT or in MDX expressions. It may work for simple cubes with single measure group, but it won’t work for more complex cubes.

Below is a typical example of how Leaves function is used (taken from this article):

 

SCOPE [Measures].[Sales];
 SCOPE (Leaves([Date]), [Seller].[Country].[Country]);
  [Measures].[Sales] = [Measures].[Sales]*[Measures].[Rate];
 END SCOPE;
END SCOPE;

 

Note, that by restricting the SCOPE to a single measure, we obviously restricted it to the single measure group.

 

Checking whether a member is a measure group leaf

 

While positioning on measure group leaves is easy with the Leaves function, checking the measure group leaf is more difficult. One typical scenario when this is needed, is for the applications which support writeback. Since non-allocated writeback in AS is only allowed at measure group granularity, the application would like to check it first, and not allow the user to modify the cell which cannot be modified.

The best way to do that in AS2000 was to use UPDATEABLE cell property. It is a bitmask which tells how the cell can be updated, and if it cannot be updated, then the mask contains all kinds of reasons why not. It could be because one of the cell’s coordinates is calculated member, it can be because the cell is not at measure group granularity, or it can be because there is a cell security on that cell etc.

The important values for checking measure group granularity are

 

DBPROP_CELL_UPDATE_ENABLED – cell can be updated through IRowsetUpdate, i.e. it is on measure group granularity

DBPROP_CELL_UPDATE_ENABLED_WITH_UPDATE – cell can be updated with UPDATE CUBE statement, i.e. it is above measure group granularity

 

Unfortunatelly, in AS2005, this cell property always returns DBPROP_CELL_UPDATE_ENABLED. The reason for that is that AS2005 unlike AS2000 supports IRowsetUpdate::Update not just on the leaf cells, since behind the scenes it performs equal allocation over non-leaf cells. However, this also means that the UPDATEABLE cell property cannot be used with AS2005 to determine measure group granularity.

 

Now, what if the knowledge whether the member (or cell) is a measure group leaf or not is needed inside a calculation ? Frankly, we didn’t think that this would be an interesting scenario, and therefore AS2005 doesn’t have MDX function to check it. Recently, however, we have run into a couple of customers who had such a requirement. In order to solve it, the following workaround can be used:

CREATE IsLeafInsideFoo = false;
SCOPE (MeasureGroupMeasures("foo"),Leaves());
    IsLeafInsideFoo = true;
END SCOPE;

Basically, this creates a calculated measure, which is False everywhere. Then, the SCOPE positions on the leaves measure group and changes the calculated measure to be True there. Since calculated members are not aggregated up, the values above leaves will stay False. Now, the calculated measure IsLeafInsideFoo can be used to determine whether a member or a cell is at measure group granularity or not.

Published Saturday, April 29, 2006 1:56 PM by mosha
Filed under:
Anonymous comments are disabled
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement