Data Warehousing Schemas- part 5

Ø  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.

Ø  Schema Types

            There are many schema types used in data warehouses but the following are most commonly                used  ones:

4   Star Schema

4   Snowflake schema

Star Schema

4   A single fact table and for each dimension one dimension table

4   Typically, most of the fact tables in a star schema are in database third normal form, while dimensional tables are de-normalized

           Each dimension table is joined to the fact table using a primary key to foreign key join, but the                           dimension tables are not joined to each other.

Snowflake schema

Ø  Normalized form of Star Schema

Ø  Represent dimensional hierarchy directly by normalizing tables.

Ø  Reduces the width of dimension table

Ø  Easy to maintain and saves storage

Why use Snowflake….

General rule of thumb is to avoid the snowflake as it goes against the goals of a data warehouse. There are however situations where the snowflake schema makes sense.

Ø  A dimension contains 50 attributes but 80% of the times a few columns are never used. So we can snowflake the dimension here.

Ø  To improve performance as smaller tables are joined

Fact Table

Ø  Is the primary table in a dimensional model.

Ø  Facts are numeric measurements (values) that represent a specific business aspect or activity.

Ø  Facts can be computed or derived at run-time (metrics).

Ø  Have two or more foreign keys(FK) that connect to the dimension table’s primary keys.

4   Satisfy referential integrity.

Ø  Generally has own primary key(called a composite or concatenated key) made up of a subset of the foreign keys.

Ø  Express the many-to-many relationships between dimensions.

Dimension Tables

Ø  Are integral companions to a fact table

Ø  Contain the textual descriptors of the business

Ø  Have many columns or attributes

Ø  Defined single primary key(PK)

Ø  We strive to minimize the use of codes in our dimension tables by replacing them with more verbose textual attributes

►      Operational codes often have intelligence embedded in them

Ø  Typically are highly denormalized

Ø  Typically are geometrically smaller than fact tables, improving storage efficiency by normalizing or snowflaking

►      Snowflake

•          Brand description and category description replace by brand code and create brand table