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
   Dimensional Model Schemas- Star, Snow-Flake and Constellation Foundation & Conformed Dimensions and Facts in Data Warehouse Dimensional Model  

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

Dimensional Modeling vs. Relational Modeling

Dimensional modeling is different from the OLTP normalized modeling to enable analysis and querying through massive and unpredicted queries. Something which is a relational model is ill-equipped to handle.


How Dimensional model is different from an E-R diagram?

  • An E-R diagram (used in OLTP or transactional system) has highly normalized model (Even at a logical level), whereas dimensional model aggregates most of the attributes and hierarchies of a dimension into a single entity.
  • An E-R diagram is a complex maze of hundreds of entities linked with each other, whereas the Dimensional model has logical grouped set of star-schemas.
  • The E-R diagram is split as per the entities. A dimension model is split as per the dimensions and facts.
  • In an E-R diagram all attributes for an entity including textual as well as numeric, belong to the entity table. Whereas a 'dimension' entity in dimension model has mostly the textual attributes, and the 'fact' entity has mostly numeric attributes.

Dimensional modeling is a better approach for Data warehouse compared to standard Data Model.

The dimensional model has a number of important data warehouse advantages that the ER model lacks.

First advantage of the dimensional model is that there are standard type of joins and framework. All dimensions can be thought of as symmetrically equal entry points into the fact table. The logical design can be done independent of expected query patterns. The user interfaces are symmetrical, the query strategies are symmetrical, and the SQL generated against the dimensional model is symmetrical. In other words,

  • You will never find attributes in fact tables and facts in dimension tables.
  • If you see a non-fact field in the fact table, you can assume that it is a key to a dimension table

Second advantage of the dimensional model is that it is smoothly extensible to accommodate unexpected new data elements and new design decisions. First, all existing tables (both fact and dimension) can be changed in place by simply adding new data rows in the table. Data should not have to be reloaded. Typically, No query tool OR reporting tool needs to be reprogrammed to accommodate the change. All old applications continue to run without yielding different results. You can, respectively, make the following graceful changes to the design after the data warehouse is up and running by:

  • Adding new unanticipated facts (that is, new additive numeric fields in the fact table), as long as they are consistent with the fundamental grain of the existing fact table.
  • Adding completely new dimensions, as long as there is a single value of that dimension defined for each existing fact record
  • Adding new, unanticipated dimensional attributes.
  • Breaking existing dimension records down to a lower level of granularity from a certain point in time forward.

Third advantage of the dimensional model is that there is a body of standard approaches for handling common modeling situations in the business world. Each of these situations has a well-understood set of alternatives that can be specifically programmed in report writers, query tools, and other user interfaces. These modeling situations include:

  • Slowly changing dimensions, where a 'constant' dimension such as Product OR Customer actually evolves slowly and asynchronously. Dimensional modeling provides specific techniques for handling slowly changing dimensions, depending on the business environment.
  • Heterogeneous products, where a business such as a bank needs to:
    • track a number of different lines of business together within a single common set of attributes and facts, but at the same time..
    • it needs to describe and measure the individual lines of business in highly idiosyncratic ways using incompatible measures.
 

   Dimensional Model Schemas- Star, Snow-Flake and Constellation Foundation & Conformed Dimensions and Facts in Data Warehouse Dimensional Model  
 
 
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
Schemas- Star, Snow-Flake and Constellation
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 force density
Sales product Mix Profitability
Sales Leads Generation through Events
Variable Sales Cost
Sales Leads Management System
Read more...
  Customer Relationship Management
Exit barriers for Customer Retention
Customer Value and Profitability Tips and Actions
Customer Service and Support - Strategic Role
Customer Value and Profitability- BI
Customer Value and Profitability-Overview
Read more...
  Human Resources & Leadership
Fostering Innovation
Roles and Level based Competency Segregation
Developing Leaders- Few Leadership Traits
Empower Front-line Employees
Develop Self and Others
Read more...
 
 
Business Performance & Planning
Strategic Planning leadership commitment
Scorecards need manual finish
A KPI should be simple -but it depends
Business Objectives Drill Down
Financial Business Plan
Read more...
  Business Intelligence & Data Quality
Data Warehouse Project Initiation Phase
Trusted first, Respected later & Loved last
Master-Data-Management CDI Objectives
Data Warehouse Testing Categories
Data Warehouse Data Quality assurance
Read more...
  IT Vendors & Tools Management
Cascade standards & guidelines
Data Quality Tools Wizards
Report objects for Enterprise Reporting
Vendor Management strength Evaluation
Metadata Tool Architecture Features
Read more...