When should you use a STAR and when a SNOW-FLAKE schema?
- 6 Answers
- 47614 Views
- Accenture, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / tanmay kumar meher
The snowflake and star schema are methods of storing data
which are multidimensional in nature (i.e. which can be
analysed by any or all of a number of independent factors)
in a relational database.
The snowflake schema (sometimes called snowflake join
schema) is a more complex schema than the star schema
because the tables which describe the dimensions are
normalized.
Snowflake schema is nothing but one dimension table will be
connected to another dimension table and so on.
------------
Snowflake
------------
? If a dimension is very sparse (i.e. most of the
possible values for the dimension have no data) and/or a
dimension has a very long list of attributes which may be
used in a query, the dimension table may occupy a
significant proportion of the database and snow flaking may
be appropriate.
? A multidimensional view is sometimes added to an
existing transactional database to aid reporting. In this
case, the tables which describe the dimensions will already
exist and will typically be normalized. A snowflake schema
will hence be easier to implement.
? A snowflake schema can sometimes reflect the way in
which users think about data. Users may prefer to generate
queries using a star schema in some cases, although this
may or may not be reflected in the underlying organization
of the database.
? Some users may wish to submit queries to the
database which, using conventional multidimensional
reporting tools, cannot be expressed within a simple star
schema. This is particularly common in data mining of
customer databases, where a common requirement is to locate
common factors between customers who bought products
meeting complex criteria. Some snow flaking would typically
be required to permit simple query tools such as Cognos
Power play to form such a query, especially if provision
for these forms of query weren't anticipated when the data
warehouse was first designed.
---------
Star
----------
The star schema (sometimes referenced as star join schema)
is the simplest data warehouse schema, consisting of a
single "fact table" with a compound primary key, with one
segment for each "dimension" and with additional columns of
additive, numeric facts.
The star schema makes multi-dimensional database (MDDB)
functionality possible using a traditional relational
database. Because relational databases are the most common
data management system in organizations today, implementing
multi-dimensional views of data using a relational database
is very appealing. Even if you are using a specific MDDB
solution, its sources likely are relational databases.
Another reason for using star schema is its ease of
understanding. Fact tables in star schema are mostly in
third normal form (3NF), but dimensional tables are in de-
normalized second normal form (2NF). If you want to
normalize dimensional tables, they look like snowflakes
(see snowflake schema) and the same problems of relational
databases arise - you need complex queries and business
users cannot easily understand the meaning of data.
Although query performance may be improved by advanced DBMS
technology and hardware, highly normalized tables make
reporting difficult and applications complex.
| Is This Answer Correct ? | 40 Yes | 2 No |
STAR SCHEMA:-if PERFORMANCE is the priority than go for
star schema,since here dimension tables are DE-NORMALIZED
SNOW-FLAKE SCHEMA:-if MEMORY SPACE is the priority than go
for snoflake schema,since here dimension tables are
NORMALIZED
| Is This Answer Correct ? | 36 Yes | 5 No |
Answer / nethaji
We use star schema when the query involves few joins and for
better performance.here data is denormalized.
Snow-flake schema is an extension of star where the
dimensions are divided into hierarchy of dimensions.eg: time
dimension.
time->year->month->day
For complex joins we go for snow-flake.performance is little
bit slower due to no. of joins.Here data is normalized.
| Is This Answer Correct ? | 17 Yes | 4 No |
Answer / shan
The snowflake and star schema are methods of storing data
which are multidimensional in nature (i.e. which can be
analysed by any or all of a number of independent factors)
in a relational database.
The snowflake schema (sometimes called snowflake join
schema) is a more complex schema than the star schema
because the tables which describe the dimensions are
normalized.
Snowflake schema is nothing but one dimension table will be
connected to another dimension table and so on.
------------
Snowflake
------------
? If a dimension is very sparse (i.e. most of the
possible values for the dimension have no data) and/or a
dimension has a very long list of attributes which may be
used in a query, the dimension table may occupy a
significant proportion of the database and snow flaking may
be appropriate.
? A multidimensional view is sometimes added to an
existing transactional database to aid reporting. In this
case, the tables which describe the dimensions will already
exist and will typically be normalized. A snowflake schema
will hence be easier to implement.
? A snowflake schema can sometimes reflect the way in
which users think about data. Users may prefer to generate
queries using a star schema in some cases, although this
may or may not be reflected in the underlying organization
of the database.
? Some users may wish to submit queries to the
database which, using conventional multidimensional
reporting tools, cannot be expressed within a simple star
schema. This is particularly common in data mining of
customer databases, where a common requirement is to locate
common factors between customers who bought products
meeting complex criteria. Some snow flaking would typically
be required to permit simple query tools such as Cognos
Power play to form such a query, especially if provision
for these forms of query weren't anticipated when the data
warehouse was first designed.
---------
Star
----------
The star schema (sometimes referenced as star join schema)
is the simplest data warehouse schema, consisting of a
single "fact table" with a compound primary key, with one
segment for each "dimension" and with additional columns of
additive, numeric facts.
The star schema makes multi-dimensional database (MDDB)
functionality possible using a traditional relational
database. Because relational databases are the most common
data management system in organizations today, implementing
multi-dimensional views of data using a relational database
is very appealing. Even if you are using a specific MDDB
solution, its sources likely are relational databases.
Another reason for using star schema is its ease of
understanding. Fact tables in star schema are mostly in
third normal form (3NF), but dimensional tables are in de-
normalized second normal form (2NF). If you want to
normalize dimensional tables, they look like snowflakes
(see snowflake schema) and the same problems of relational
databases arise - you need complex queries and business
users cannot easily understand the meaning of data.
Although query performance may be improved by advanced DBMS
technology and hardware, highly normalized tables make
reporting difficult and applications complex.
| Is This Answer Correct ? | 10 Yes | 2 No |
Answer / freak.abinitio
Snowflake schema architecture is a more complex variation of a star schema design. The main difference is that dimensional tables in a snowflake schema are normalized, so they have a typical relational database design.
Snowflake schemas are generally used when a dimensional table becomes very big and when a star schema can’t represent the complexity of a data structure. For example if a PRODUCT dimension table contains millions of rows, the use of snowflake schemas should significantly improve performance by moving out some data to other table (with BRANDS for instance).
The problem is that the more normalized the dimension table is, the more complicated SQL joins must be issued to query them. This is because in order for a query to be answered, many tables need to be joined and aggregates generated.
A dimensional modelling technique in which a detail fact table is linked to dimension tables.
The data in data warehouses and data marts is accessed by end-users. The information contained in the data warehouse/data mart must be easy for the end-user to use and access. Denormalized designs are easier for end-users to use than highly normalized designs, however these designs are more difficult to design and maintain.
The Star Schema diagram graphically models the end-user's view (i.e., the denormalized view) of how the information is accessed.
Components of a Star Schema Diagram
The diagram has three main components:
· Fact Table and its contents: metric attributes and the foreign keys necessary to join to the dimension tables,
· Dimension Tables and their contents: reference attributes, hierarchical attributes, and metric attributes. The dimension tables are highly denormalized,
· the lines that link the Dimension Tables to the Fact Table.
| Is This Answer Correct ? | 3 Yes | 2 No |
Answer / malli
usually star schema is the best option for end users due to
its simple design and navigation.
snow flaking is the process of normalizing the dimension
table.For example the customer dimension table usally will
have millons of rows.The updations will take palce
concerned to demographic information(city,place,..).By
snowflaking, these attribute can be seperated and put in
another dimension table, so the updations would be quite
easier.
| Is This Answer Correct ? | 11 Yes | 14 No |
what are different types of partitioning in oracle ?
How to reduce warnings?
how much memory space occupied real time project (roughly)?
What is real-time datawarehousing?
Explain the importance of data warehouse?
Explain the difference between data ware house & data mart?
What is data analysis? Where it will be used?
What are snapshots? What are materialized views & where do we use them?
What is data validation strategies for data mart v?
What is the working process for application.cfm?
What is difference between a connected look up and unconnected look up?
5.Explain ODS ?
Teradata 
