Andyblg's Blog

July 21, 2009

Data model for a data warehouse

Filed under: DW — andyblg @ 01:30
Tags: ,

The most popular data model for a data warehouse is a multidimensional model. Such a model can exist in the form of a star schema, a snowflake schema, or a galaxy schema (fact constellation schema). Term “normalized schema” and term “combining schemas” are used too.

Let’s look at each of these schema types.

Normalized schema

An OLAP cube can use a fully normalized relational database as its schema. Normalization generally results in more tables and more joins. Usually, this will cause poor performance for the OLAP solution, but as data warehousing becomes more cost-effective and easier to deploy, there is an increased demand for small-scale solutions that do not require high performance and do not have the overhead of schema conversion. Conversely, you might also use normalization on very large systems, where storage space is at a premium.

Star schema

The most common modeling paradigm is the star schema, in which the data warehouse contains (1) a large central table (fact table) containing the bulk of the data, with no redundancy, and (2) a set of smaller attendant tables (dimension tables), one for each dimension. The schema graph resembles a starburst, with the dimension tables displayed in a radial pattern around the central fact table.

Snowflake schema

The snowflake schema is a variant of the star schema model, where some dimension tables are normalized, thereby further splitting the data into additional tables. The resulting schema graph forms a shape similar to a snowflake.

The major difference between the snowflake and star schema models is that the dimension tables of the snowflake model may be kept in normalized form to reduce redundancies. Such a table is easy to maintain and saves storage space. However, this saving of space is negligible in comparison to the typical magnitude of the fact table. Furthermore, the snowflake structure can reduce the effectiveness of browsing, since more joins will be needed to execute a query. Consequently, the system performance may be adversely impacted. Hence, although the snowflake schema reduces redundancy, it is not as popular as the star schema in data warehouse design.

Combining schemas

You can successfully implement a combination of star and snowflake schemas, and this is the most common OLAP design. Where a dimension would benefit from reduced redundancy and referential integrity, you can use a snowflake schema and continue to use a star schema for all other dimensions.

Fact constellation

Sophisticated applications may require multiple fact tables to share dimension tables. This kind of schema can be viewed as a collection of stars, and hence is called a galaxy schema or a fact constellation.

Consideration

I think what a normalized schema  may be considered as case of a snowflake schema. A combining schemas may be considered as case of a galaxy schema.

or

Snowflake schema is general or common schema for one fact table.

Galaxy schema is general or common schema for multiple fact tables.

If you have any considerations you may comment =)

Advertisements

3 Comments »

  1. Spot on with this write-up, I seriously believe this site needs a great deal more attention. I’ll probably be returning to see more, thanks for the
    advice!

    Comment by collier anti aboiement avis vétérinaire — September 4, 2014 @ 06:45 | Reply

  2. I feel this is among the such a lot important information for me.
    And i am happy reading your article. But wanna statement on some common issues,
    The web site style is perfect, the articles is really nice : D.
    Just right process, cheers

    Comment by Hemoroide photo — August 19, 2014 @ 02:16 | Reply

  3. Magnificent goods from you, man. I’ve understand your stuff previous to and you’re just too excellent.
    I really like what you’ve acquired here, really like what you’re stating and the
    way in which you say it. You make it enjoyable and you
    still care for to keep it smart. I can not wait to read much more
    from you. This is actually a wonderful website.

    Comment by hemorroides externes — August 17, 2014 @ 08:51 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: