Ø 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
Great Article
ReplyDeleteData Mining Projects
Python Training in Chennai
Project Centers in Chennai
Python Training in Chennai
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
ReplyDeleteo Thank you for helping people get the information they need. Great stuff as usual. Keep up the great work!!!
ReplyDeletepmp certification course training in Guwahati