Share via

Flattening Algorithm

  • Article

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 Di:

    • Let k be the lowermost level on the axis from this dimension.

    • Let Li 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 Li. Li must contain unique names.

      This rule is meaningful only when the MDX statement for Di results in members from a single hierarchy. If members from multiple hierarchies are included, the set of levels in Li 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 Di, the set of levels in Li is provider-dependent.

      Case:

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

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

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

  3. Let C represent the union of all Ci 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 ? Ci is the same as Li.) 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.