in which situations do u go for starflake schema ?
- 6 Answers
- 17461 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
What is dimensional table?
I want my deployment group to refer an external configuration file, while i deploy in the production environment. How can i achieve it.
Why update strategy and union transformations are active?
To import the flat file definition into the designer where should the flat file be placed?
What are the modules in Power Center
What are junk dimensions?
How to call shell scripts from informatica?
What is dynamic cache?
What are active transformations.
Can one use mapping parameter or variables created in one mapping into any other reusable transformation?
How you prepared reports for OLAP?
How to generate sequence numbers using expression transformation?
In development project what is the process to follow for an etl developer from day1
How can you generate reports in informatica?
What is the difference between router and filter?
