Data Warehousing Schemas- part 5 - ETL- iNFORMATICA DEVELOPER

Sunday, May 19, 2019

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


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


  1. Be that as it may, it might include complex joining over the long haul if its structure and arranging were not endeavor wide Data Analytics Courses

  2. o Thank you for helping people get the information they need. Great stuff as usual. Keep up the great work!!!
    pmp certification course training in Guwahati