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

  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
BUY largest on-line Data-Quality Management Kit
Additional Channels
Principles & Rules
Free Templates
Key Performance Indicators

Most Popular Zones with list of pages crossing 25000 hits  →→→ 
Maximising Sales Performance
Sales Compensation System
Sales force density
Sales representation and experience
Sales Compensation administration
Sales Compensation Structure Decision
  Customer Relationship Management
Customer Service and Support Overview
Customer Segmentation Data Management
Customer Value and Profitability Tips and Actions
Supply Chain for Customer Service and Support
Customer-Centric product-service management
  Human Resources & Leadership
Develop Self and Others
Feedback does not mean only negative feedback
Be straight and blunt, till you team gets used to it
What is Leadership?
Maximize the output first and then the potential
Business Performance & Planning
Internal Info Assessment Report
Strategic Planning Business Themes
External Info Assessment Report
Strategic Planning leadership commitment
For important KPIs- Install first & Fix later
  Business Intelligence & Data Quality
Data Warehouse Project Initiation
Null foreign Keys in fact tables
Data Filtration Analysis
Data Warehouse ETL Loading
Data Mapping and Assessment
  IT Vendors & Tools Management
Collaboration and Administration Support
Metadata Tool Architecture Features
Technical Architecture Evaluation
Report Viewer Feature
Vendor Credentials and Track-Record Evaluation