in which situations do u go for starflake schema ?
- 6 Answers
- 17523 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 |
In a scenario I have col1, col2, col3, under that 1,x,y, and 2,a,b and I want in this form col1, col2 and 1,x and 1,y and 2,a and 2,b, what is the procedure?
Is it necessary to maintain the primary-foreign key relation ship between the targets in informatica while loading using constarint based loading or it is required only at database level ??
What are the differences between a connected lookup and unconnected lookup?
What are the different options used to configure the sequential batches?
can u explain me banking domain projects ? and in banking domain projects how many dimension tables and how many fact tables and how to load source and targets in mapping level plese expain give me one example?
1 Answers Accenture, Cognizant, HCL, IBM, iGate, L&T, TCS, Virtusa,
replace multiple spaces with single space.
I have the source like col1 col2 a l b p a m a n b q x y How to get the target data like below col1 col2 a l,m,n b p,q x y
How can we use mapping variables in informatica? Where do we use them?
write a query to retrieve the latest records from the table sorted by version(scd)
i want excute multiple sessions in one workflow using the "start task".what do i need to do?
Where can we find the throughput option in informatica?
How do you handle two sessions in Informatica
Teradata 
