The Physical Data Model - Microsoft SQL Server 2008 Analysis Services - page 356

Data Structures of Hierarchies

Users access data stored in Analysis Services by the navigation paths called hierarchies. To
achieve fast access to the members of the dimension through hierarchies, Analysis Services
builds special data structures. These data structures are built for each level of the natural
hierarchy (as discussed in Chapter 5). Unnatural hierarchies are first broken down into
natural hierarchies, and then the data structures are built for those natural hierarchies.
Each level of a hierarchy has three data structures, in which <level ID> represents the
identifier of a level:

. A structure store, a compressed store that contains information about the children

and parents of level members, named <level ID>.lstore

. A set store, a compressed store that contains information about all ancestors of each

level member, named <level ID>.sstore

. An order store, a compressed store that contains the position of each member on a

level by DataID , named <level ID>.ostore

Structure Store of a Hierarchy
As its name implies, a hierarchy enables you to access data in a hierarchical view. Each
hierarchy contains levels, and each level has references to members. This means that the
most important information about the member in a hierarchy is the level index (informa-
tion about member's position on the level among its siblings), the parent of the member,
and the children of the member. All this data is stored in a data structure called a structure
store.

The records of the structure store correspond to members of the level. However, the
members are ordered not according to their DataID , but according to their level index. The
structure store has three columns: the level index of the member's parent, the level index
of its first child, and the number of children for that member. Therefore, Analysis Services
can easily retrieve the level index of the member's parent and the level indexes of its chil-
dren. Figure 20.8 shows a diagram of a structure store.

The position of the member in a level—the level index—depends on the settings specified
in the definition of the attribute from which this level is built. For example, you can order
members by their names or keys. However, the ordering is hierarchical, so the ordinal
position of the member is determined first by the ordinal of its parent and then by the
position of the member among its siblings. Analysis Services starts to order members from
the top level of the hierarchy, and because members of this level don't have parents, it
orders the members in it by comparing the members to each other. Analysis Services then
moves recursively to lower levels of the hierarchy and orders members on those levels,
taking into consideration the ordinal of the parents.

Figure 20.9 shows an example that uses hierarchical ordering. Members of all the levels
of the hierarchies are supposed to be ordered by their names. But, because they also are
ordered according to the position of the parent, the ordinal of the member correspond-