Interested to Buy Any Domain ? << Click Here >> for more details...
in which situations do u go for starflake schema ?
- 6 Answers
- 19546 Views
- TCS, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / nagaraju bhatraju
Star Schemas
The star schema is the simplest data warehouse schema. It
is called a star schema because the diagram resembles a
star, with points radiating from a center. The center of
the star consists of one or more fact tables and the points
of the star are the dimension tables
Star schema contains the dimesion tables mapped around one
or more fact tables.
It is a denormalised model.
No need to use complicated joins.
Queries results fastly.
Snowflake schema
It is the normalised form of Star schema.
contains indepth joins ,bcas the tbales r splitted in to
many pieces.We can easily do modification directly in the
tables.
We hav to use comlicated joins ,since we hav more tables .
There will be some delay in processing the Query .
1.Star Schema contains denormalized Dimensions. Snowflake
contains one or
more Normalized Dimensions.
2. Snowflake Schema give Less performance b'cos, giving
single result it
needs more joins. that is performance speed less in
Snowflake.
The snowflake schema is a variation of the star schema used
in a data warehouse.
The snowflake schema (sometimes callled snowflake join
schema) is a more complex schema than the star schema
because the tables which describe the dimensions are
normalized.
Flips of "snowflaking"
- In a data warehouse, the fact table in which data values
(and its associated indexes) are stored, is typically
responsible for 90% or more of the storage requirements, so
the benefit here is normally insignificant.
- Normalization of the dimension tables ("snowflaking") can
impair the performance of a data warehouse. Whereas
conventional databases can be tuned to match the regular
pattern of usage, such patterns rarely exist in a data
warehouse. Snowflaking will increase the time taken to
perform a query, and the design goals of many data
warehouse projects is to minimize these response times.
Benefits of "snowflaking"
- 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 snowflaking 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 normalised. 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 organisation
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 snowflaking would typically be required to
permit simple query tools such as Cognos Powerplay to form
such a query, especially if provision for these forms of
query weren't anticpated when the data warehouse was first
designed.
In practice, many data warehouses will normalize some
dimensions and not others, and hence use a combination of
snowflake and classic star schema.
| Is This Answer Correct ? | 11 Yes | 1 No |
Answer / venugopal
mainly in real time ...when we want to use existing data
warehousing as source we will go for snow flake schema
| Is This Answer Correct ? | 7 Yes | 2 No |
Answer / prabakaran
when we have single or multiple facts and not bothered
about query performance.
When we dont have disk storage limitations
| Is This Answer Correct ? | 6 Yes | 3 No |
Answer / vidyanand
Mostly preferable is Star schema where query performance
will be high comparing to snowflake Schema.
Snowflake schema is used only where performance issue will
not come into matter.
So, better to use star schema rather than snowflake schema.
| Is This Answer Correct ? | 2 Yes | 0 No |
Answer / r.lakshmi
to design a DW, starflake schema is an effective method to
process the business instead of queries. provides speedy
retrival of information than snowflake schema
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / padmavathi
When we bother about query performance we will go for
starschema modelling because in this modelling data
redundancy will be there while writing queries to retrive
data need not use more join conditions.
| Is This Answer Correct ? | 1 Yes | 1 No |
Hello all, In SOA architecture i.e 8.6 , What is mean by node exactly?
what is the complex transformation you used in informatica
What are the Differences between connected and unconnected lookup?
What is decode in static cache?
how to identify new and old values in source qualifier or any other transformations using informatica
Calculate sum of salaries department wise. Then the sum will be repeat for all columns in each department. Develop a mapping for this.
What is the need of an ETL tool?
tell me the datawarehouse defination,and it is maintaing hystorical data meaning,what is the use
How to view and Generate Metadata Reports?
IN SCD1, insource we have 10 billion records and in the first day its uploaded successfully and in the second day its taking time to upload because some records it might get update or insert new records. As a developer what will be the better solution for this??
What is the exact difference b/w IN and EXIST in Oracle..?
what is the size of u r source(like file or table)?
Teradata 
