Data Insights — Data & Tech
Single Source of Truth
Why a data model ends the spreadsheet debate
It's Monday morning. Management meeting. The sales lead opens his spreadsheet and presents 12% revenue growth. The financial controller opens hers — and arrives at 8%. The rest of the meeting isn't about opportunities or direction, but about the question: whose numbers are right?
This scenario is instantly recognisable for most SME directors. It's not a matter of incompetence or bad intent. It's the direct consequence of lacking a Single Source of Truth.
Why do the numbers never add up?
A growing company accumulates systems. Xero or QuickBooks for accounting. HubSpot or Salesforce for customers. A WMS for the warehouse, a planning sheet for production. Each system does what it needs to do — but they don't talk to each other.
To create reports, data gets manually exported to Excel. And that's where the problem begins. As soon as two people interpret the same data slightly differently, definition gaps appear:
- Does Sales count an order at the time of signing? Or does Finance only count it at invoicing?
- Are returns already processed in the revenue figures?
- Is the customer classification from Sales the same as Marketing's?
The result is a house of cards made of spreadsheets. As soon as one formula breaks or one column shifts, confidence in the numbers collapses. And without central rules, you end up comparing apples with oranges.
From spreadsheet chaos to one truth
The difference between working with scattered spreadsheets and a central data model is fundamental. Not in technology — but in trust.
- Three versions of the same revenue report in Dropbox
- Two days every month spent on month-end closing in Excel
- Sales and Finance report different revenue figures
- Nobody dares base decisions on the reports
- One dashboard that refreshes automatically from the sources
- Month-end closing in minutes — the data is ready
- One shared definition of revenue, margin and costs
- Management meetings focus on actions, not on numbers
How does a data model work? The Star Schema explained
A data model is the blueprint of your business operations, translated into data. In Power BI, we build this model to bring all separate data streams together and — more importantly — to define the relationships between them.
The most widely used structure for this is the Star Schema. The concept is surprisingly simple: imagine a star with a core and points.
The core: fact tables. At the centre of the star sit the hard numbers — the transactions that occur in your business. A sales invoice, a logged hour, an inventory movement. These tables are long (many rows) and contain numbers you sum, average or count.
The points: dimension tables. Around the core sit the dimensions that provide context for your numbers. They answer the questions who, what, where and when. The customer table (name, region, segment), the product table (SKU, group, cost price), the date table (month, quarter, year, working day).
The power of the relationship. The Star Schema establishes relationships between facts and dimensions. Instead of Sales and Finance each maintaining their own customer list — where one writes "Jansen Ltd" and the other "Jansen Limited" — they both reference the same unique customer from the dimension table.
When you filter on "Customer Group A" in Power BI, the system automatically filters all associated revenue, costs and hours from the fact tables. Flawlessly and instantly.
The result: the definition of gross margin is defined once in a DAX formula. Nobody can accidentally put their own spin on it. When the data refreshes, everyone — from the intern to the managing director — looks at exactly the same numbers.
Why Excel falls short
Excel is an excellent tool, but it lacks the structure needed for reliable reporting. You can type a date in cell A1 and text in cell A2 — Excel is perfectly happy with it. A data model in Power BI is stricter: a date is a date, and a customer is a customer.
That strictness is precisely what you need. A Power BI data model forces you to define your metrics: what is our definition of gross margin? Which product groups do we use? How do we calculate inventory turnover? By defining these rules once in the model, nobody needs to apply them manually again.
Microsoft now calls this model a semantic model — a term that emphasises that the model doesn't just store data, but captures its meaning. The definitions, the relationships, the security rules: everything sits in one governed layer.
How do you build a Single Source of Truth in Power BI?
Moving from spreadsheets to a central data model doesn't have to be a major IT project. For most SMEs, four steps are sufficient:
What does a Single Source of Truth deliver?
The value of a central data model isn't in the technology — it's in the decisions it enables.
End of number debates. Everyone looks at the same dashboard. When revenue is £100,000, that's the number for everyone. The meeting shifts from "are the numbers right?" to "what do we do with these numbers?"
Time saved. No more hours spent copying, pasting and checking data in Excel at month-end. The data model refreshes itself — the monthly report is ready when you walk in on Monday morning.
Scalability. Today you have 1,000 transactions, next year 100,000. A well-built data model grows effortlessly. Power BI compresses data so efficiently that even millions of rows are analysed in seconds.
Deeper insight. Because your data is clean and structured, you can ask more complex questions. "What is the margin per product group per region in Q3 compared to last year?" In a good data model, that's one click. In Excel, it's a day project.
Want to know more about the measurable impact of data analytics? Read the article on the ROI of data analytics for SMEs.
A Single Source of Truth isn't software you buy — it's a way of working. It requires the discipline to define your metrics, take your data seriously, and structure it in a data model that everyone trusts.
Think of it as laying the foundation of a house. Without a foundation, you can't build. But once the foundation is in place, you can build as high as you want — additional KPIs, new data sources, deeper analyses. Everything starts with that one reliable model.
The question isn't whether your business needs a Single Source of Truth. The question is how long you can afford to operate without one.
Last updated: April 2026
