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 Compensation for Consistency
Sales Objectives Clarity
Sales Material logistics and Distribution
Lead marketing Database Quality
Sales Leads Management System
Read more...
  Customer Relationship Management
Customer Segmentation Actions
Customer Service and Support - Strategic Role
Customer Segmentation Parameters
Customer Knowledge and Organizational Knowledge
Customer Satisfaction & Retention- Data Management
Read more...
  Human Resources & Leadership
Lead Change
Business and Financial Acumen
Fitting leadership dimension in employee performance
Competencies Definitions
Roles and Level based Competency Segregation
Read more...
 
 
Business Performance & Planning
Scorecards need manual finish
For important KPIs- Install first & Fix later
Dashboard Health Checklist
Strategic Planning leadership commitment
Strategy Map to Strategic theme
Read more...
  Business Intelligence & Data Quality
Data Correction Checklist
Impact of Bad Data Quality
Metadata standards
Aligning rewards to strategy
Individual Impact and Usage of BI
Read more...
  IT Vendors & Tools Management
Metadata Tool administration Security
OLAP Dimensional Model Tuning
Business Intelligence Vendor Evaluation
Security Technical Evaluation
Data Profiling and Monitoring
Read more...