Flattening Algorithm

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

1. The components of each tuple on the COLUMNS axis are concatenated together 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:

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

   b. Let Li be the set of unique names of levels above (and including) k up to (but not including) the root level. If D_i has multiple members at the root level, add the unique name of the root level to L_i. L_i must contain unique names.

   c. Case:

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

   ii. 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:

   a. Each name in C is a column.

   b. The levels from the highest numbered axis come first, followed by lower numbered axes.

   c. Within the levels of each axis, the levels of the outermost dimension come first.

   d. 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, then 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.

   a. These columns appear after the columns from L.

   b. Within X, the columns are in tuple order.

   c. The names in X are always the unique names, regardless of whether the DIMENSION PROPERTIES clause exists or not. Consequently, any properties specified in this clause are ignored.

8. If a dimension has a single member at the root level, and that member appears on an axis, then this is represented in the flattened rowset by a row that has a NULL in every column that corresponds to a level below it.