The data model is the work. The chart is the last day.

People who order a dashboard are usually thinking about the charts. That makes sense, because the charts are the only thing you see.

But in almost every project I do, ninety percent of the work sits in the layer underneath. And if that layer is not right, you get beautiful charts nobody trusts.

What a data model is

Put briefly: the way you hang data from different sources together, so you can ask it questions none of those sources could answer on its own.

An example. You want to know which product group performed best in which month in which location. For that you need sales lines, an article list with product groups, a list of locations and a calendar. Four things that live in three different systems and that you have to be able to join without anything dropping out.

That joining is the data model. Not the chart on top of it.

Why a calendar exists as its own table

This is the part people trip over most often, so let me be concrete.

You need a separate table with every day in it. Not the dates that happen to appear in your sales, but every day, including the days without revenue.

Why? Because otherwise you can never see that nothing was sold on a given day. That day simply does not exist in your report and your average is quietly distorted. And because you can hang things on a calendar like that which live nowhere else: school holidays, public holidays, opening Sundays, weeks in which you ran a promotion.

A drop in sales in a week with two public holidays is not a drop in sales. Without a calendar table your dashboard does not know that.

Where it usually goes wrong

Three things, in order of how often I run into them.

No unique keys. Two sources you have to join on name instead of on number. You will always get a result, but all sorts of things quietly fall away and nobody knows how much.

Different levels of granularity mixed together. Your sales sit per line, your budget per month, your footfall per hour. You cannot just put those side by side. You have to decide explicitly at what level you bring them together, and what happens to whatever does not fit.

And amounts in different forms. Including or excluding VAT, in different currencies, before or after discount. If those run together in one table, no total is correct, and the error is small enough to stay unnoticed for a long time.

When the source itself is not right

There is one case in which no data model is going to save you, and that is when the source is in a state where nothing is reliable.

At a contractor we worked with, everything sat neatly in their invoicing package. Invoices, quotes, deliveries. At first sight a complete source.

Until you establish four things. Open invoices were never reconciled against the bank statements, so open only meant that nobody had marked it as paid. Rejected quotes simply stayed in the system, so the order book was partly fiction. The planning was not recorded anywhere, so there was no connection at all between what had been sold and when it was carried out. And delivery notes were not checked against what had been ordered, so you did not know whether what was invoiced matched what was delivered.

Each of those four makes one specific question unanswerable. Together they make almost every question a business owner would ask unanswerable.

The treacherous part is that you can build a perfectly fine model on this. The numbers come out, they add up, they are defensible against the source. And they are wrong, because the source does not describe what actually happened.

That is why there should be a step before the modeling work that almost nobody plans in: checking whether the processes that feed your data are actually carried out. Not whether the system can do it, whether it happens. Is reconciliation done. Are files closed. Is anything checked.

If the answer is no, then that is your first project and not your model. That is an unpleasant message to deliver and it is the only honest one.

Why this has to come first

Because every chart you build on a bad model has to be redone once you repair the model. And because a mistake in the model multiplies: it sits in every report that leans on it, and you usually only notice when someone recognizes a number that is not right.

That is also the moment a dashboard loses its credibility, and you do not get that back easily. One wrong total in a meeting and people go back to their own Excel.

How to tell whether it is right

A few checks you can do yourself, without technical knowledge.

Take a total you know from another source, for example your revenue for last month according to your accounting, and see whether your dashboard says the same. If not, someone has to be able to explain why, and "rounding difference" is not an answer when the gap is more than one percent.

Filter on something you know should be empty and see whether there really is nothing there. Often a category called "unknown" shows up, and that is exactly what fell away in your join.

Count the number of rows in your source and in your model. Those two should match, or the difference should be explainable.

How long it takes

At an SME with two or three sources you spend a few days on the model, provided the definitions are settled. If they are not, it takes as long as that conversation takes, and that is usually the real lead time of a project.

The charts are a matter of hours after that. That is the part that looks impressive and is the least work.

The claim

If someone offers you a dashboard and the conversation is about colors and chart types before it has been about your sources and your definitions, you are buying a shop window with no shop behind it.