Schema type comparisons

One way to achieve a balance of the various trade-offs in your schema design is to use a variety of schema types in your physical warehouse schema. One hierarchy can be highly normalized while another can be highly denormalized. You can even use different schema types within the same hierarchy. The table below compares the different schema types.

Schema Type

Lookup Table Structure

Advantages

Disadvantages

Highly normalized schema

Attribute ID
Attribute description column
ID column of parent

Minimal storage space and minimal data redundancy which makes updating data less intensive than for the other schema types

Requires numerous joins to retrieve information from higher-level lookup tables

Moderately normalized schema

Attribute ID
Attribute description column
ID column of parent
ID column of grandparents

Greatly reduces the number of joins necessary to relate an attribute to its grandparents as compared to a highly normalized schema

Requires some redundant storage

Highly denormalized schema

Attribute ID
Attribute description column
ID column of parent
description column of parent
ID column of grandparents
description column of grandparents

Further reduces joins necessary to retrieve attribute descriptions as compared to a moderately normalized schema

Requires the most storage space and redundant data requires a more intensive process to update

Star schema

Consolidates an entire hierarchy into a single lookup table
Further reduces joins necessary to retrieve attribute descriptions as compared to a moderately normalized schema
Requires less storage space and data redundancy than a highly denormalized schema and thus data is easier to update

Large lookup tables can negatively affect query performance when searching tables and requiring DISTINCT operations to be performed

Now that you have gained an understanding of data modeling and the roles of facts and attributes, you can learn about these same schema objects in terms of how they exist in the MicroStrategy environment. As facts and attributes are the cornerstones of the reports you intend to create using MicroStrategy, it is essential to understand the structure of each of these schema objects before creating a project.