05-04-2012, 04:33 PM
Snow Flakes,Fact Constellations
EDU-ygkncEUh4wmDvLu.docx (Size: 207.34 KB / Downloads: 27)
Stars, snowakes, and fact constellations: schemas for multidimensional databases
The entity-relationship data model is commonly used in the design of relational databases, where a database schema consists of a set of entities or objects, and the relationships between them. Such a data model is appropriate for online transaction processing. Data warehouses, however, require a concise, subject-oriented schema which facilitates on-line data analysis.
The most popular data model for data warehouses is a multidimensional model. This model can exist in the form of a star schema, a snowake schema, or a fact constellation schema. Let's have a look at each of these schema types.
Fact constellation schema of a data warehouse for sales and shipping.
Example 2.3 An example of a fact constellation schema. This schema specifies two fact tables, sales and shipping. The sales table definition is identical to that of the star schema
The shipping table has five dimensions, or keys: time key, item key, shipper key, from location, and to location,and two measures: dollars cost and units shipped. A fact constellation schema allows dimension tables to be shared between fact tables. For example, the dimensions tables for time, item, and location, are shared between both the sales and shipping fact tables.
In data warehousing, there is a distinction between a data warehouse and a data mart. A data warehouse collects information about subjects that span the entire organization, such as customers, items, sales, assets, and personnel, and thus its scope is enterprise-wide. For data warehouses, the fact constellation schema is commonly used since it can model multiple, interrelated subjects. A data mart, on the other hand, is a department subset of the data warehouse that focuses on selected subjects, and thus its scope is department-wide. For data marts, the star or snowake schema are popular since each are geared towards modeling single subjects.