Flattening Algorithm

OLE DB Programmer's Reference

OLE DB for OLAP defines the following algorithm for flattening a dataset:

1. The components of each tuple on the COLUMNS axis are concatenated with a period (.) separator to yield compound names. Each component consists of a unique name. Call this set of names X.
2. For every non-X axis dimension D_i:
   • Let k be the lowermost level on the axis from this dimension.
   • Let L_i be the set of unique names of levels above (and including) k up to (but not including) the ALL level. If this hierarchy does not have an ALL level, include the unique name of the root level in L_i. L_i must contain unique names.

     This rule is meaningful only when the MDX statement for D_i results in members from a single hierarchy. If members from multiple hierarchies are included, the set of levels in L_i is provider-dependent. For example, suppose that the Geography dimension has two hierarchies—Political and Physical. If the members Geography.Political.China and Geography.Physical.USA are both included as a result of the MDX statement for D_i, the set of levels in L_i is provider-dependent.

     Case:

     If there is a DIMENSION PROPERTIES clause in the MDX statement that created the dataset, let P_i be the list of properties specified in this clause, enclosed in [ ]. (Therefore, if DIMENSION PROPERTIES MEMBER_UNIQUE_NAME is specified, the P_i value contains the string "[MEMBER_UNIQUE_NAME]".)

     Otherwise, let P_i contain the string "[MEMBER_CAPTION]".

     For each element l of L_i and each element p of P_i, concatenate l and p together, separated by a period (.). Let C_i be the resulting set of compound names.

3. Let C represent the union of all C_i values obtained in step 2.
4. The flattened dataset has the following columns:
   • Each name in C is a column.
   • The levels from the highest numbered axis come first, followed by lower numbered axes.
   • Within the levels of each axis, the levels of the outermost dimension come first.
   • Within the levels of each dimension, the lower numbered levels (that is, levels at a higher degree of aggregation) come first, followed by higher numbered levels (that is, levels with a lower degree of aggregation).
5. Among the properties for a given level, the order is as specified in the DIMENSION PROPERTIES clause.
6. If there is no DIMENSION PROPERTIES clause, the column name is just the unique name of the level. (See step 2—C_i is the same as L_i.) The CAPTION property is returned as the value of this column.

   Note   The main idea behind a flattened rowset is that it is a quick and easy method for rowset-based consumers to display multidimensional data. Therefore, the default (without the DIMENSION PROPERTIES clause) is to return the display caption.

7. Each name in X is a column.
   • These columns appear after the columns from L.
   • Within X, the columns are in tuple order.
   • The names in X are always the unique names, whether or not the DIMENSION PROPERTIES clause exists. Consequently, any properties specified in this clause are ignored.
8. If a hierarchy has an ALL member and that member appears on an axis, this is represented in the flattened rowset by a row that has a NULL in every column that corresponds to a level below it.

1998-2001 Microsoft Corporation. All rights reserved.