Understanding Data Warehouse Schema Types Quiz

Data Warehouse Schema

• A Data Warehouse system can have one or more fact tables, depending on the model type used to design the Data Warehouse.

Fact Table

• A fact table in a Data Warehouse system contains all the facts or business information that can be subjected to analysis and reporting activities.
• A fact table holds fields that represent direct facts and foreign fields that connect the fact table with other dimension tables.

Dimension Table

• A dimension is a collection of reference information about a measurable in the fact table.
• The primary key column of the dimension table uniquely identifies each dimension record or row.
• Dimension tables are organized with descriptive attributes.
• Examples of dimension attributes include: first and last name, birth date, gender, qualification, address, etc.

Star Schema

• A star schema is a simplest type of Data Warehouse schema, also known as Star Join Schema.
• The star schema is optimized for querying large data sets.
• The center of the star can have one fact table and a number of associated dimension tables.
• The star schema is easy to understand and provides optimal disk usage.
• The dimension tables are not normalized in a star schema.
• The schema is widely supported by BI Tools.

Characteristics of Star Schema

• Every dimension in a star schema is represented with only one dimension table.
• The dimension table should contain a set of attributes.
• The dimension table is joined to the fact table using a foreign key.
• The dimension tables are not joined to each other.
• The fact table would contain key and measure.

Steps in Designing Star Schema

• Identify the business process.
• Identify the measures or fact data.
• Identify the dimension tables related to the fact table.
• Find the lowest level of aggregation or summary analysis in the fact table.

Snowflake Schema

• A snowflake schema is an extension of the star schema where the dimension tables are connected to one or more dimensions.
• The dimension tables are normalized, which splits data into additional tables.
• The snowflake schema keeps the same fact table structure as the star schema.
• In the dimension, it has multiple levels with multiple hierarchies.
• From each hierarchy of levels, any one level can be attached to the fact table.

When to Use Snowflake Schema

• Large customer dimensions where, for example, 80 percent of the fact table measurements involve anonymous visitors.
• Financial product dimensions for banks, brokerage houses, and insurance companies.
• Multi-enterprise calendar dimensions because each organization has idiosyncratic fiscal periods, seasons, and holidays.

Characteristics of Snowflake Schema

• The main benefit of the snowflake schema is it uses smaller disk space.
• It is easier to implement a dimension added to the schema.
• Due to multiple tables, query performance is reduced.
• The primary challenge is to perform more maintenance efforts because of the more lookup tables.

Galaxy Schema

• Multiple fact tables share dimension tables in the galaxy schema.
• The galaxy schema is viewed as a collection of stars, hence called galaxy schema.
• It is also called Fact Constellation Schema.

Characteristics of Galaxy Schema

• The dimensions in this schema are separated into separate dimensions based on the various levels of hierarchy.
• For example, if geography has four levels of hierarchy like region, country, state, and city, then the galaxy schema should have four dimensions.
• It is possible to build this type of schema by splitting the one-star schema into more star schema.
• The dimensions are large in this schema which is needed to build based on the levels of hierarchy.
• This schema is helpful for aggregating fact tables for better understanding.