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.