Knowledge Hub

Dimensional Data Modelling is an analytical modelling approach that structures data into facts (quantitative measures) and dimensions (descriptive attributes) to support reporting, dashboards, and business intelligence. It provides a simplified, performance-oriented representation of organizational data, making analytics accessible to both technical and business users.

The primary goal of dimensional modelling is to deliver a business-friendly and performance-optimized structure that enables fast and intuitive access to information. Rather than focusing on eliminating redundancy at all costs, dimensional modelling focuses on making data easy to query, aggregate, and understand.

Dimensional models are organized around business processes. A fact table captures measurable events, such as loan approvals, repayments, or transactions. Surrounding the fact table are dimension tables, which provide context, such as customer attributes, officer details, product types, and dates. Together, they answer two fundamental questions:

The Star Schema is a dimensional modelling design in which a central fact table connects directly to multiple surrounding dimension tables, forming a star-like structure. The fact table stores quantitative measures of a business process, while the dimension tables provide descriptive context for analysis.

The primary goal of the star schema is to deliver a simple, high-performance, and business-friendly structure for analytics. It is designed specifically for reporting, dashboards, and self-service business intelligence, where clarity and speed are critical.

In a star schema, the fact table captures measurable events such as LoanAmount, InterestRate, ApprovalCount, or RepaymentAmount. Each row represents a clearly defined grain, such as one loan approval or one repayment transaction. The fact table contains foreign keys that link to dimension tables.

The dimension tables contain descriptive attributes used for filtering, grouping, and slicing data. Examples include:

The Snowflake Schema is a dimensional modelling design in which one or more dimension tables are normalized into related sub-dimensions, forming a snowflake-like structure around a central fact table. While the fact table remains the analytical core, dimension tables are decomposed to reduce redundancy and improve governance.

The primary goal of the snowflake schema is to improve data quality, storage efficiency, and reusability, especially for large, shared dimensions, while still preserving dimensional modelling semantics for analytics.

A snowflake schema typically begins as a classic star schema (Fact + Dimensions). Over time, wide dimension tables may be normalized into smaller, related sub-dimensions. For example:

• Dim_Customer may be split into:
  • Customer (core identity attributes)
  • Demographics (age band, income band)
  • Employment (industry, employer category)
  • Geography (city, region, country)

Do We Really Need Data Modelling?

In recent years, a recurring question has surfaced in data conversations: Do we really need data modelling anymore? With cloud-native platforms, data lakes, AI pipelines, and schema-on-read architectures dominating the narrative, some assume modelling is optional, or even obsolete.

The reality is far simpler. There is no such thing as data without a model. Whether designed consciously or not, every system that stores data follows a structure. That structure, explicit or implicit, is a data model.

When we build operational applications, we design backend databases. These typically follow normalization principles, often in Third Normal Form (3NF). Customer data is stored separately from orders, orders from payments, and relationships are maintained through keys and constraints. This structure prevents redundancy, preserves integrity, and ensures consistency. That is data modelling in action.

I am sure most of us know this term but not sure how accurate you are in terms of its importance.

‘The granularity of data means, the levels of data breakdown.’

Generally, people think having all relevant details about an entity is Granularity, but the question is what level of details?

For example, details can be name, email, and contact number, date of birth, color, blood group, home address, company, role, salary, father name, mother name and so. Is this the lower level of granularity? NO, when we say the lowest level of granularity, we can break down.

• Name into First Name, Middle Name and Last Name
• Address into Apartment Number, Building Name, Street, Area, City, District, Region, Country, Postal Code etc.

In OLTP systems, granularity must go as low as possible and in OLAP systems, it can depend on the business use case.

Now, when we have established what the Granularity is, let's discuss when it's needed. Data Wrangling, Data Mining, Data Science etc., all use raw data for analysis and analytics. And when we say RAW, the lower the level of granularity can be, the better the model result will be for DSS.

Another term, if data modelers are not aware and if data model doesn’t take care via proper Data Cardinality check and balance, data analysts are hit by Chasm and Fan Traps.

We know what Data Modelling is. We know what Data Cardinality is. Now, it’s time to understand Cartesian data which give nightmares to Finance department when they manually know the total is USD100k but when they run query, they see USD200 k 😊.

‘Cartesian Data is about writing a query on tables with ‘Many to Many (M:M)’ relationship.’

If there are two tables with ‘Many to Many’ relationship, it’s time for data modeler to introduce an intermediary table which can have unique records and then can have ‘One to Many’ relationship with previously used two tables.

If the Sales table is joined with Target tables directly based on Employee ID then surely there will Cartesian data as both Sales and Target tables can have more than 1 employee ID. But hang-on, even having intermediary table, if query is written wrongly then still data analyst can hit with Cartesian data know as Chasm Trap.

Data Hierarchy is a known term and most of us are aware of it but sometimes, it’s confused with Data Taxonomy & Data Ontology.

Data Taxonomy and Data Ontology are metadata for the purpose of Data Governance whereas Data Hierarchies presents actual data. A very well-known and famous example is the Organizational Chart where hierarchy starts from CEO and goes down to Database Administrator in data domain.

• ‘Data Taxonomies apply to identified objects or phenomena.’’
• ‘Data Ontologies apply to terms, concepts, or categories.’
• ‘Data Hierarchy is a chain of relationship in actually data.’

Family tree is another simple example.

Data can be grouped in a way where data exploration and representation become easy. For example, at database level, hierarchy can be Database 🡪 Files/ Tables 🡪 Records 🡪 Fields/ Columns 🡪 Bytes 🡪 Bits.

Same line Data Taxonomy, Data Hierarchy also have single parent and multiple child relationship among the rows of dataset.

This topic is very close to many experienced data modelers because it is surprisingly misunderstood. The confusion usually begins with one assumption: if both systems use Third Normal Form (3NF), then they must be conceptually the same. They are not.

In data architecture, the Data Warehouse concept popularized by Bill Inmon embraces the use of 3NF. Since 3NF is also widely used in OLTP systems, many architects assume the modeling philosophy and operational behavior are identical. The structure may look similar, but the intent, behavior, and lifecycle of data are fundamentally different.

OLTP 3NF: Designed for Transactional Efficiency

In an OLTP (Online Transaction Processing) system, the objective is operational efficiency. The system must support high-volume, real-time transactions such as order placement, payments, inventory updates, and customer modifications.

The primary goals are:

• Fast inserts, updates, and deletes
• Concurrency control
• Storage optimization
• Data consistency for current state

As discussed in the topic i.e., Data Warehouse or Data Mart, Star schema and Snowflake Schema both refer to Ralph Kimball Data Warehousing approach.

As per Ralph Kimball, we MUST use Dimensional Modelling for Data Warehousing and Data Marts consisting of Star and Snowflake Schemas.

In Star Schema, the model has a Fact table in the middle and all the Dimensions are joined with the Fact. For example, there is a Fact table called Sales and there can be many dimensions like Time, Location, Product, Categories etc. The Primary Key of the Fact table consists of all the foreign key columns coming from Dimensions. This way when a report or a query is created, there is always one direct join with any Dimension.

As discussed in topic i.e., Data Warehouse or Data Mart that Star schema and Snowflake Schema both refer to Ralph Kimball Data Warehousing approach.

As per Ralph Kimball, we MUST use Dimensional Modelling for Data Warehousing and Data Marts consisting of Star and Snowflake Schemas.

In Snowflake Schema, the model also has a Fact table in the middle and Dimensions around it but here Dimensions can be normalized further in multiple Sub-dimensions which changes the query to create a report where there will be multiple joins to reach a conclusive result. For example, there is a Fact table called Sales and a Dimension i.e., Location, this location Dimension can be normalized further in State, City, and Postal Code etc.