Data Warehousing Schemas

A schema is a collection of database objects, including tables, views, indexes, and
synonyms. You can arrange schema objects in the schema models designed for data
warehousing in a variety of ways. Most data warehouses use a dimensional model.

The model of your source data and the requirements of your users help you design the
data warehouse schema. You can sometimes get the source model from your
company's enterprise data model and reverse-engineer the logical data model for the
data warehouse from this. The physical implementation of the logical data warehouse
model may require some changes to adapt it to your system parameters—size of
machine, number of users, storage capacity, type of network, and software.

Star Schemas
The star schema is the simplest data warehouse schema. It is called a star schema
because the diagram resembles a star, with points radiating from a center. The center
of the star consists of one or more fact tables and the points of the star are the
dimension tables, as shown in Figure



The most natural way to model a data warehouse is as a star schema, where only one
join establishes the relationship between the fact table and any one of the dimension
tables.
A star schema optimizes performance by keeping queries simple and providing fast
response time. All the information about each level is stored in one row.


Other Schemas
Some schemas in data warehousing environments use third normal form rather than
star schemas. Another schema that is sometimes useful is the snowflake schema,
which is a star schema with normalized dimensions in a tree structure.

Posted in: Data Warehouse