Dimensional modeling (DM) is the name of a set of techniques and concepts used in data warehouse design. According to Ralph Kimball, its goal is to deliver data that’s understandable to the business users and fast query performance.
Dimensional modeling is widely accepted as the preferred technique for presenting analytic data because it addresses two simultaneous requirements:
- Deliver data that’s understandable to the business users.
- Deliver fast query performance.
It is considered to be different from entity-relationship modeling (ER). Dimensional Modeling does not necessarily involve a relational database. The same modeling approach, at the logical level, can be used for any physical form, such as multidimensional database or even flat files. According to data warehousing consultant Ralph Kimball, DM is a design technique for databases intended to support end-user queries in a data warehouse. It is oriented around understandability and performance. According to him, although transaction-oriented ER is very useful for the transaction capture, it should be avoided for end-user delivery.
Dimensional modeling always uses the concepts of facts (measures), and dimensions (context). Facts are typically (but not always) numeric values that can be aggregated, and dimensions are groups of hierarchies and descriptors that define the facts.
Fundamental concepts for data-modeling
- Gather Business Requirements and Data Realities - before launching a dimensional modeling effort, the team needs to understand the business and the realities of the underlying source data. Understanding the objectives based on key performance indicators and decision-making processes.
- Collaborative Dimensional Modeling Workshops - it should be designed with subject matter experts and data governance specialists Collaboration is critical.
Four-Step Dimensional Design Process
- Select the business process - the operational activities performed by an organization.
- Declare the grain - exactly what a single fact table row represents.
- Identify the dimensions - “who, what, where, when, why, and how” context surrounding a business process event
- Identify the facts - the measurements the result from a business process event
Example
Sales amount is a fact; timestamp, product, register#, store#, etc. are elements of dimensions. Dimensional models are built by business process area, e.g. store sales, inventory, claims, etc. Because the different business process areas share some but not all dimensions, efficiency in design, operation, and consistency, is achieved using conformed dimensions, i.e. using one copy of the shared dimension across subject areas.
Links
