Interested to Buy Any Domain ? << Click Here >> for more details...
in which situations do u go for starflake schema ?
- 6 Answers
- 19765 Views
- TCS, I also Faced
- E-Mail Answers
Answer Posted / 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 |
Post New Answer View All Answers
As union transformation gives UNION ALL output, how you will get the UNION output?
How do you load more than 1 max sal in each department through informatica or write sql query in oracle?
What is enterprise data warehousing?
how lookup transformation is made active in new versions... When to use connected and when to use unconnected lookup and why? which is good for session performance. How to make lookup persistent and how to remove stale data from that lookup. how commit works - when we stop or abort data. Explain in both cases. What is factless fact table and have you ever used it in real time scenarios.
Design time, run time. If you don't create parameter what will happen
What is informatica etl tool?
What is the difference between a repository server and a powerhouse?
How can we store previous session logs?
What is the scenario which compels informatica server to reject files?
SOURCE DATA IS DISPLAY IN THIS FORMATE IN TARGET . WHAT BUSINESS LOGIC WE CAN APPLY. source table target table ------------ ------------ c1 c2 c3 c1 c2 c3 -- -- -- -- -- -- 1 A J 1 2 B K 2 A 3 C L 3 B J 4 D M 4 C K 5 E N 5 E L F M N
where to store informatica rejected data? How to extract the informatica rejected data?
update strategy transformation in informatica
What is union transformation in informatica?
Explain the informatica workflow?
What is a transaction control transformation?
