Sales Management Customer Relationship Human Resources Business Performance BI & Data Quality IT Tools & Vendors

Sign-in   Register
Establishing 'Making it Happen' as a 'Formal & Predictable' Discipline
   Data Warehouse Dimensional Model Components Concept Dimensional Modeling vs. Relational Modeling  

ENCYCLOPEDIA→   Enterprise Intelligence  →   -  Data-Warehouse/Mart  →   -  DW Dimensional Modeling Concepts  → 

Dimensional Model Schemas- Star, Snow-Flake and Constellation

Dimensional model can be organized in star-schema or snow-flaked schema.

Dimensional Model Star Schema using Star Query

The star schema is perhaps the simplest data warehouse schema. It is called a star schema because the entity-relationship diagram of this schema resembles a star, with points radiating from a central table. The center of the star consists of a large fact table and the points of the star are the dimension tables.

A star schema is characterized by one OR more very large fact tables that contain the primary information in the data warehouse, and a number of much smaller dimension tables (OR lookup tables), each of which contains information about the entries for a particular attribute in the fact table.

A star query is a join between a fact table and a number of dimension tables. Each dimension table is joined to the fact table using a primary key to foreign key join, but the dimension tables are not joined to each other. The cost-based optimizer recognizes star queries and generates efficient execution plans for them.

A typical fact table contains keys and measures. For example, in the sample schema, the fact table, sales, contain the measures quantity_sold, amount, and average, and the keys time_key, item-key, branch_key, and location_key. The dimension tables are time, branch, item and location.

A star join is a primary key to foreign key join of the dimension tables to a fact table.

The main advantages of star schemas are that they:

  • Provide a direct and intuitive mapping between the business entities being analyzed by end users and the schema design.
  • Provide highly optimized performance for typical star queries.
  • Are widely supported by a large number of business intelligence tools, which may anticipate OR even require that the data-warehouse schema contains dimension tables

Snow-Flake Schema in Dimensional Modeling

The snowflake schema is a more complex data warehouse model than a star schema, and is a type of star schema. It is called a snowflake schema because the diagram of the schema resembles a snowflake.

Snowflake schemas normalize dimensions to eliminate redundancy. That is, the dimension data has been grouped into multiple tables instead of one large table. For example, a location dimension table in a star schema might be normalized into a location table and city table in a snowflake schema. While this saves space, it increases the number of dimension tables and requires more foreign key joins. The result is more complex queries and reduced query performance. Figure above presents a graphical representation of a snowflake schema.

Fact Constellation Schema

This Schema is used mainly for the aggregate fact tables, OR where we want to split a fact table for better comprehension. The split of fact table is done only when we want to focus on aggregation over few facts & dimensions.

 

   Data Warehouse Dimensional Model Components Concept Dimensional Modeling vs. Relational Modeling  
 
 
Relevant Links to this page
Practice Tools → Dimensional Model Completion Checklist → 
All Topics in: "DW Dimensional Modeling Concepts" Chapter
 Dimensional Model Components Concept | Fact Table | Dimension Table →  Dimensional Model Schemas- Star, snow-Flake and Constellation →  Dimensional Modeling vs. Relational Modeling →  foundation conformed Dimensions facts in Data Warehouse Dimensional Model →  slowly changing Dimensions SCD in Dimensional Modeling → 
 
 
More on DW Dimensional Model Concepts
DW Dimensional Model Components Concept
Dimensional Modeling vs. Relational Modeling
Foundation & Conformed Dimensions and Facts
Slowly Changing Dimensions SCD
BUY BI & Data Management Vendors & Tools Evaluation Kit
Read more...
BUY largest on-line Data-Quality Management Kit
Read more...
Additional Channels
Principles & Rules
Free Templates
Glossary
Key Performance Indicators



Most Popular Zones with list of pages crossing 25000 hits  →→→ 
Maximising Sales Performance
Sales geographic expansion
Sales Compensation Structure Decision
Lead marketing Database Quality
Sales Channel Retention, Support and Engagement
Sales ticket Size Mix
Read more...
  Customer Relationship Management
Customer Knowledge and Organizational Knowledge
Customer Value and Profitability Tips and Actions
Supply Chain for Customer Service and Support
Customer Value and Profitability-Overview
Customer Service and Support - Strategic Role
Read more...
  Human Resources & Leadership
Strategic Business Plan
Setting Strategic Intent and Alignment
Developing Leaders- Few Leadership Traits
Act with Decisiveness
Give feedback closer to the observation
Read more...
 
 
Business Performance & Planning
SWOT Assessment Report
Business Objectives Drill Down
Dashboard Health Checklist
Strategic Vision and Mission
External Info Assessment Report
Read more...
  Business Intelligence & Data Quality
Metadata Repository Transformation Design
Two tier Data Warehouse Architecture
Data Warehouse is beyond Analytics
Customer Data Correction and Techniques
Business Metadata for IT
Read more...
  IT Vendors & Tools Management
Report Viewer Feature
Vendor Delivery Support Model
OLAP Server write backs
Data Cleansing and Augmentation
OLAP Dimensional Model Change Management
Read more...