Highly denormalized schema: Enhanced query performance

The following diagram is an example of a highly denormalized schema. A highly denormalized schema has the same basic structure as the other two schema types. With this type, not only are higher-level attribute ID columns present within all related tables, but the description columns are present as well. For example, Region_desc is included in the Lookup_Call_Ctr table.

Using a highly denormalized schema further reduces the joins necessary to retrieve attribute descriptions. For example, you can include the descriptions of Call Center, Distribution Center, and Region along with Sales Dollars in the same report while only having to join the Lookup_Call_CTR and Fact_Sales tables. This is possible because Lookup_Call_CTR contains all information (including description data) for Call Center as well as for Distribution Center and Region.

However, this schema type requires the largest amount of storage space within the warehouse because of its large lookup tables. High denormalized schemas also cause the highest level of data redundancy.

Star schema: Consolidating lookup tables

When using the highly denormalized schema, it is possible to eliminate most of the lookup tables and leave just a few, as shown below. Arranging the warehouse schema this way produces a star schema. In this type of schema, the lookup tables are consolidated so that every attribute ID and description column for a given hierarchy exists in one table.

Recall that in a highly denormalized schema, each hierarchy (for example, geography) consists of several lookup tables. In a star schema, however, only one lookup table is used to contain all of the attribute IDs and description columns for a given hierarchy, as shown below:

As with any schema type model there are advantages and disadvantages to using a star schema. As with a highly denormalized schema type, the amount of join operations are reduced by using a star schema. A star schema can also reduce the amount of storage space necessary in a highly denormalized schema. However, star schemas can often require large lookup tables that can take a more time to search than the smaller tables that are used in the other schema types.