MicroStrategy ONE
Highly normalized schema: Minimal storage space
The following diagram is an example of a highly normalized schema. In highly normalized schemas, lookup tables contain unique developer-designed attribute keys, such as Call_Ctr_id
, Dist_Ctr_id
, and Region_id
, as shown in the figure below. They also contain attribute description columns, such as Call_Ctr_desc
, Dist_Ctr_desc
, and Region_desc
. Also, the lookup table for an attribute contains the ID column of the parent attribute, such as Dist_Ctr_id
in the Lookup_Call_Ctr
table.
The following diagram shows what physical lookup tables look like in the warehouse:
One benefit of using a highly normalized schema is that it requires minimal storage space in the warehouse because of it uses smaller lookup tables than the other schema types.
However, there is a drawback to using only small tables in the data warehouse. When accessing higher-level lookup tables such as Lookup_Region
in the example above, numerous joins are required to retrieve information about the higher-level tables. This is because each table contains only a small amount of information about a given attribute; therefore, multiple tables must be joined until the required column is found.