Data Modelling - A Primer

You've undoubtedly heard the term, but what does it mean?

There is a lot of information out there, and in this article, we're going to bed down the basics so you can begin your data modelling journey and progress into that beautiful data modelling swan you're meant to be. If you're trying to resolve some data to give you some number, there is a data modelling technique that will provide it.

Your job is to identify and apply the correct modelling technique to the data.

If you find yourself reinventing the wheel, stop, turn around and backtrack.

So, what exactly is a data model?

In simple terms, a data model is a technical representation or plan that describes how data is organised and accessed.

It identifies what information is needed, how it is related, and how it can be grouped or categorised.

It's like a map for how all of your data fits together and can come in the form of a simple diagram or a fully-fledged blueprint.

This helps anyone using the data, from database administrators to developers to end users, understand and work with the data in their business.

And what does a data model do?

A data model performs several vital functions:

1. Overall Organisation of Data: It systematically organises data elements and outlines how they relate to each other. This allows for easy retrieval, manipulation, and analysis of data.
2. Standardisation: It provides a standardised system to define and format data, which helps maintain data integrity and consistency.
3. Enables Understanding: A data model allows technical and non-technical stakeholders to actually understand the data structure, its interrelationships, and business rules.
4. Uncovers Integration Possibilities: It helps integrate data from various sources by providing a unified view of the data. This allows us to conceive how data will integrate between systems/platforms.
5. Supports Business Requirements: A data model helps ensure that the data needed to support business processes and decision-making is available, correctly structured, and reliably implemented.

What are the types of data models?

The beginning of learning data modelling is to understand that there are three types of data models.

Conceptual

Conceptual models represent the things in the business and how they relate to each other.

Think of this model as something you can scribble on the back of a napkin: e.g. customers and sales are linked, sales and products are linked, products and storage locations are linked, etc.

Logical

A Logical Data Model (LDM) represents the organisation's data, organised in terms of entities, attributes, and relationships but not taking into account physical storage considerations.

It is essentially an abstraction that outlines how data elements relate to each other and how they can be logically grouped, including restrictions.

Physical

A Physical Data Model (PDM) represents a data design that considers the technical specifications and infrastructure of a specific database management system (DBMS).

It includes detailed specifications about how data is stored and accessed, including table structures, column names, data types, indexes, keys, and any constraints on the data.

In brief, a physical data model is a blueprint of how the data will be physically stored in a database.

What are relationships and cardinality?

The next part of learning data modelling is relationships and cardinality.

Relationships are connections between tables that allow us to analyse data across them.

For instance, connecting a Customers table to an Orders table on a 'CustomerID' field lets us see total orders per customer. Power BI supports one-to-one, one-to-many, and many-to-one relationships and, with some careful modelling, can effectively handle many-to-many relationships too.

Cardinality defines the uniqueness of data in columns being related.

It is crucial to determine the nature of the relationship and how data will be aggregated and visualised. The cardinality of a relationship between tables can be one-to-one (1:1), one-to-many (1:N), many-to-one (N:1), or many-to-many (N:N).

In Power BI, there are also filtering direction considerations when modelling.

If I have issues with my data, is it usually in the data model?

Short answer, yes.

While Power BI is a powerful tool for visualising and analysing data, it sometimes does its job too well. If your numbers are out or not quite right, start with your data model. Poorly designed data models can lead to performance issues, data integrity problems, and misleading reports. Understanding and appropriately implementing data models within Power BI, paying close attention to relationships and cardinality, is essential to leveraging its full potential. It's worth remembering that data modelling isn't merely a technical exercise but an ongoing process that requires business insight, technical acumen, and constant iteration.

You could do it today, and it could change tomorrow.

Ultimately, the success of any business intelligence initiative lies in the foundation of a well-designed and maintained data model.

While we can't sum it up in this article, this should give you a start to carry forward your journey to becoming a gun data modeller.

Go get modelling.