Tuesday, 5 December 2006

Dimension Data Modelling

Dimensional data model is most often used in data warehousing systems. This is different from the 3rd normal form, commonly used for transactional (OLTP) type systems. As you can imagine, the same data would then be stored differently in a dimensional model than in a 3rd normal form model.

To understand dimensional data modeling, let's define some of the terms commonly used in this type of modeling:

Dimension: A category of information. For example, the time dimension.

Attribute: A unique level within a dimension. For example, Month is an attribute in the Time Dimension.

Hierarchy: The specification of levels that represents relationship between different attributes within a dimension. For example, one possible hierarchy in the Time dimension is Year → Quarter → Month → Day.

Fact Table: A fact table is a table that contains the measures of interest. For example, sales amount would be such a measure. This measure is stored in the fact table with the appropriate granularity. For example, it can be sales amount by store by day. In this case, the fact table would contain three columns: A date column, a store column, and a sales amount column.

Lookup Table: The lookup table provides the detailed information about the attributes. For example, the lookup table for the Quarter attribute would include a list of all of the quarters available in the data warehouse. Each row (each quarter) may have several fields, one for the unique ID that identifies the quarter, and one or more additional fields that specifies how that particular quarter is represented on a report (for example, first quarter of 2001 may be represented as "Q1 2001" or "2001 Q1").

A dimensional model includes fact tables and lookup tables. Fact tables connect to one or more lookup tables, but fact tables do not have direct relationships to one another. Dimensions and hierarchies are represented by lookup tables. Attributes are the non-key columns in the lookup tables.

In designing data models for data warehouses / data marts, the most commonly used schema types are Star Schema and Snowflake Schema.

Star Schema: In the star schema design, a single object (the fact table) sits in the middle and is radially connected to other surrounding objects (dimension lookup tables) like a star. A star schema can be simple or complex. A simple star consists of one fact table; a complex star can have more than one fact table.

Snowflake Schema: The snowflake schema is an extension of the star schema, where each point of the star explodes into more points. The main advantage of the snowflake schema is the improvement in query performance due to minimized disk storage requirements and joining smaller lookup tables. The main disadvantage of the snowflake schema is the additional maintenance efforts needed due to the increase number of lookup tables.

Whether one uses a star or a snowflake largely depends on personal preference and business needs.

5 comments:

Anonymous said...

top [url=http://www.c-online-casino.co.uk/]uk online casino[/url] check the latest [url=http://www.realcazinoz.com/]online casino[/url] unshackled no deposit hand-out at the foremost [url=http://www.baywatchcasino.com/]casino games
[/url].

ninest123 said...

ninest123 16.03
ray ban sunglasses, cheap oakley sunglasses, replica watches, michael kors outlet, oakley sunglasses, jordan shoes, prada handbags, nike outlet, nike air max, nike free, louis vuitton, tiffany and co, ugg boots, louboutin outlet, ray ban sunglasses, louis vuitton outlet, burberry outlet online, michael kors outlet, chanel handbags, uggs on sale, tiffany jewelry, louis vuitton outlet, michael kors outlet, oakley sunglasses, oakley sunglasses, louboutin shoes, prada outlet, ugg boots, ray ban sunglasses, ugg boots, burberry, replica watches, michael kors outlet, tory burch outlet, longchamp outlet, michael kors outlet, louis vuitton, michael kors, polo ralph lauren outlet, longchamp outlet, louis vuitton, polo ralph lauren outlet, louboutin, oakley sunglasses, gucci outlet, ugg boots, christian louboutin outlet, nike air max, longchamp

ninest123 said...

north face, hogan, coach outlet, air force, hollister pas cher, michael kors, coach outlet, lululemon, burberry, sac guess, nike roshe, nike free, true religion jeans, air jordan pas cher, lacoste pas cher, ralph lauren pas cher, michael kors, kate spade outlet, ralph lauren uk, michael kors, new balance pas cher, true religion jeans, ray ban uk, nike roshe run, converse pas cher, michael kors, hermes, mulberry, vans pas cher, louboutin pas cher, true religion outlet, ray ban pas cher, nike air max, oakley pas cher, air max, timberland, nike free run uk, kate spade handbags, nike air max, hollister, tn pas cher, replica handbags, nike air max, vanessa bruno, abercrombie and fitch, sac longchamp, true religion jeans, nike blazer, coach purses, longchamp pas cher, north face

ninest123 said...

ralph lauren, babyliss, bottega veneta, ipad cases, louboutin, baseball bats, ferragamo shoes, iphone 6 plus cases, mac cosmetics, iphone 6s plus cases, longchamp, lululemon, herve leger, chi flat iron, nfl jerseys, iphone cases, valentino shoes, hollister, new balance, nike trainers, hollister, beats by dre, iphone 5s cases, iphone 6 cases, celine handbags, north face outlet, oakley, mcm handbags, abercrombie and fitch, wedding dresses, soccer jerseys, insanity workout, soccer shoes, nike air max, vans shoes, instyler, nike huarache, nike roshe, north face outlet, p90x workout, ghd, hollister, iphone 6s cases, nike air max, birkin bag, timberland boots, jimmy choo shoes, s5 cases, reebok shoes, giuseppe zanotti, mont blanc, asics running shoes

ninest123 said...

ugg,uggs,uggs canada, canada goose, ugg,ugg australia,ugg italia, canada goose, hollister, wedding dresses, moncler, louis vuitton, canada goose outlet, swarovski, pandora charms, pandora jewelry, moncler outlet, moncler, sac louis vuitton pas cher, replica watches, louis vuitton, marc jacobs, louis vuitton, vans, moncler, swarovski crystal, pandora jewelry, toms shoes, moncler, juicy couture outlet, doudoune canada goose, karen millen, converse, lancel, canada goose outlet, coach outlet, gucci, ray ban, thomas sabo, moncler, supra shoes, canada goose uk, juicy couture outlet, links of london, barbour jackets, louis vuitton, canada goose, barbour, ugg boots uk, pandora charms, bottes ugg, converse outlet, moncler, montre pas cher, canada goose, ugg pas cher, moncler
ninest123 16.03