Interested to Buy Any Domain ? << Click Here >> for more details...
When should you use a STAR and when a SNOW-FLAKE schema?
- 6 Answers
- 50046 Views
- Accenture, I also Faced
- E-Mail Answers
Answer Posted / 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 |
Post New Answer View All Answers
What are templates in which way it is help full?
Explain what are the advantages and disadvantages of reporting directly against the database?
What is an er diagram?
Explain what are the different types of data warehousing?
What are the data types present in bo? What happens if we implement view in the designer n report?
Do you always need to copy the data before reporting on it?(Example, real-time & on-demand reporting is a requirement)?
Explain the difference between keylokup and joiner?
Explain what are non-additive facts in detail?
Where the applications and where
What are the types of dimensional modeling?
What is analytical or analytic mean?
What is cost benefit analysis (cba)?
Where do we use semi and non additive facts?
How can you import tables from a database?
Why facts table is useful in representing the data?
