Scribd
Upload
26 views3 pages

Data Mining Practical 10

The document outlines an experiment focused on designing and creating a cube using a snowflake schema, which consists of a fact table surrounded by normalized dimension tables. It explains the characteristics of snowflake schema, its advantages and disadvantages compared to star schema, and provides an example of its structure. The exercise includes tasks such as differentiating between star and snowflake schemas, providing examples, and explaining cube generation using SQL Server 2000.

Uploaded by

akhilpapa303
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
26 views3 pages

Data Mining Practical 10

The document outlines an experiment focused on designing and creating a cube using a snowflake schema, which consists of a fact table surrounded by normalized dimension tables. It explains the characteristics of snowflake schema, its advantages and disadvantages compared to star schema, and provides an example of its structure. The exercise includes tasks such as differentiating between star and snowflake schemas, providing examples, and explaining cube generation using SQL Server 2000.

Uploaded by

akhilpapa303
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
You are on page 1/ 3

Shree Swaminarayan Institute of Technology CE DEPT.

(VI SEMESTER)

Date:
Student Name:
Student Enrollment No:

EXPERIMENT NO: 10

TITLE: Design & create cube by identifying measures & dimensions for snowflake schema.

OBJECTIVE:On completion of this exercise student will able to know about…

 What is snowflake scheme?


 How to create cube by using snowflake schema?

THEORY:

What is Snowflake schema?

Snowflake schema consists of a fact table surrounded by multiple dimension tables which can be
connected to other dimension tables via many-to-one relationship. Snowflake schema is a kind of star
schema however it is more complex than a star schema in term of data model. This schema resembles a
snowflake therefore it is called snowflake schema.

Snowflake schema is designed from star schema by further normalizing dimension tables to eliminate
data redundancy. Therefore in snowflake schema, instead of having big dimension tables connected to a
fact table, we have a group of multiple dimension tables. In snowflake schema, dimension tables are
normally in the third normal form (3NF). The snowflake schema helps save storage however it increases
the number of dimension tables.

Snowflake schema example

The figure below shows an example of snowflake schema that is a snowflaked version of a star schema
demonstrated in the star schema article.

Student Name (Enrollment No) Page no


Shree Swaminarayan Institute of Technology CE DEPT. (VI SEMESTER)

Figure 1 Star Schema Example

Figure 2 Snowflake Schema Example

Let’s examine the snowflake schema above in a greater detail:

Student Name (Enrollment No) Page no


Shree Swaminarayan Institute of Technology CE DEPT. (VI SEMESTER)

 DIM_STORE dimension table is normalized to add one more dimension table called
DIM_GEOGRAPHY
 DIM_PRODUCT dimension table is normalized to add 2 more dimension tables called
DIM_BRAND and DIM_PRODUCT_CATEGORY
 DIM_DATE dimension table is now connecting with three other dimension tables:
DIM_DAY_OF_WEEK, DIM_MONTH and DIM_QUARTER.
 Fact table remains the same as star schema.

Useful snowflake schema notes

 The normalization of dimension tables tends to increase number of dimension tables or sub-
dimension table that require more foreign key joins when querying the data therefore reduce the
query performance.
 The query of snowflake schema is more complex than query of star schema due to multiple joins
from dimension table to sub-dimension tables.
 Snowflake schema help to save space by normalizing dimension tables.
 It is more difficult for business users who use data warehouse system using snowflake schema
because they have to work with more tables than star schema.

EXCERSICE:

1) Write down the difference between star schema and Snowflake schema.
2) Give the example of star schema and Snowflake schema.
3) Explain the cube generation in snowflake schema using SQL server 2000 take relevant screen
shots?

EVALUATION:

Observation &
Timely completion Viva Total
Implementation
4 2 4 10

Subject In-charge Name & Signature: ____________


Date: ________________

Student Name (Enrollment No) Page no

You might also like