MicroStrategy ONE
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.