MQT Dimension Design - Options

Virtual Dimensions

Define the dimensionality of the OLAP cube the base fact belongs to, and include all of these dimensions to each of the family of MQTs that implements the cube. For the purposes of aggregation, you will find that there are logical (virtual) dimensions involved that are simple attributes in the basic dimensions, or that there are multiple hierarchies implemented in one dimension. For example, a location dimension may have store as its base level and store type, or store size be attributes. If you want to report sales at the district, region and division levels by store type or store size, then these two attributes should be viewed as independent dimensions for aggregation purposes.

Levels to Include

For each base dimension, we can define all functional dependencies involving all the attributes of the dimension. This way we form an FD tree with the base dimension key being the only independent attribute with all other attributes directly or indirectly dependant on it. The core FDs should be among the identifiers of each "level" of the dimension, e.g. the dependencies among identifiers for Stores, Districts, Regions, Divisions etc. Each description or name of the respective name shall depend on the respective ID of its level, e.g. the name of a district depends on the district ID. Additional attributes, not clearly part of the hierarchy should have a dependency to the highest level that makes sense. For example, store size should be functionally dependent to store, while Primary Distribution Center of a Store may depend on the district or region. In building an MQT, only core IDs should be included in its key.

Redundant Flattened Keys

When building an MQT at the district level, should we also include the Region and Division IDs? Flattening helps with de facto integrity testing, ensuring that the hierarchy is consistent by inspecting the actual MQT rows. There may be a performance advantage in exploiting flattened keys particularly in MDC organized tables where any higher level ID block indexes come almost free. Finally, one should consider the recasting implications of including or not including redundant higher level IDs in the MQT.