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

Washington (1) Level State

Redmond (1) Seattle (2)

Level City

Edward (1) Irina (2) Sasha (3) Py (4)

Level Customer

FIGURE 20.9 Order of members in a hierarchy depends not only on a position of the member
in an attribute, but also on the ordinal of member's parent.

Each record of the set store can be translated to a data structure called the path, which
contains the DataID s of all the ancestors of a member and the DataID of the member
itself.

NOTE
The path is very important data structure. Paths are widely used in Analysis Services
architecture—a large number of internal APIs operate with paths and many internal
requests are eventually converted to paths.

Let's look at how Analysis Services builds the path of the member. For example, you have
the Customer hierarchy with the levels State-City Customer , and this hierarchy has the
members shown in Figure 20.10. Each member has a DataID assigned to it.

When Analysis Services builds a set store for the Customer level, it enumerates DataID s
starting from the top level— State —and finishing on the current level. For member Py ,
Analysis Services would construct the path (1, 1, 3), and for member Edward , (1, 2, 4).

As you can see, the set store enables efficient access to the DataID of a member and any of
its ancestors by the level index of the member on a given level.

Order Store of a Hierarchy
Sometimes Analysis Services needs to locate a member in the hierarchy by its DataID . To
enable quick linking between attribute members and level members, Analysis Services uses
an order store. The order store has only one column, which contains the level index of
each member.

Decoding Table
Analysis Services often has to find out the DataID values of members from higher levels of
the hierarchy by the DataID of a member from the lower level of the same hierarchy. This
process is called decoding. To efficiently perform decoding, Analysis Services builds a
special data structure—a decoding table—during dimension processing.