Most businesses don’t have a data problem. They have a data-in-too-many-places problem. Sales live in the POS, costs in the accounting system, labor in payroll, and reconciling them eats hours every month. Large companies solve this with data warehouses and teams of data engineers. Small and midsize businesses tend to assume that’s out of reach, so they keep reconciling spreadsheets.
It doesn’t have to be that way. In my fractional FP&A work, much of it with restaurant companies, I build right-sized data warehouses using tools most businesses already own. This piece walks through the core ideas: what a data warehouse is, how Power Query gets your data into one, and how dimensions tie it all together, along with a few practical tips I’ve picked up along the way.
A data warehouse, right-sized
Your POS, accounting system, and payroll software each do their own job well. But none of them can answer questions that span all three, like how labor cost per customer served varies by store and by shift. That’s what a data warehouse is for. It’s a central database that pulls data from every system you run, standardizes it, keeps the history, and organizes it for analysis. Think of it as the difference between the systems that run your business and the one that explains it.
Despite the name, it doesn’t have to be a massive enterprise project. For most small and midsize businesses, a right-sized warehouse is affordable, quick to build, and pays for itself in the hours no longer spent reconciling spreadsheets.
Most data warehouses run on a cloud-based SQL database, but there are more down-to-earth options that many businesses already have. I serve most of my clients using Microsoft Access, which in many ways is a “baby SQL” database. Data pros will be quick to point out that Access is desktop-based rather than cloud-native and has a 2 GB file-size limit. Those limits are real, but most SMBs just getting started won’t run into them for quite a while. And when they do, the tables and relationships you’ve designed carry over to a cloud database.
Get to know Power Query
The process of getting data into a warehouse is called ETL: extract data from each system, transform it into one consistent structure, and load it into the warehouse, ideally on a refresh that runs without anyone rebuilding it by hand.
One of the most effective ETL tools is also one of the most accessible: Power Query. It’s built into both Excel and Power BI, yet many Excel users don’t know it’s there or what it can do. I’ve been an Excel power user for a couple of decades, waited far too long to start using it, and now use it daily. The good news for Power Query neophytes is that AI tools like Claude and Copilot can walk you through almost any step, which shortens the learning curve dramatically.
In my own workflow, Power Query handles the extract and transform steps, and its output lands in an Excel table. The load step is then a simple import of that table into the Access warehouse. It isn’t fully automated, but it’s quick and repeatable, and it gives me a natural checkpoint to review the numbers before they go in.
Pro tip
When I capture monthly data from the accounting system, which is almost always the keystone source, I do three things to make sure the numbers go into the warehouse right and stay right. Take a monthly P&L with 85 lines.
- Keep only data, not subtotals. If a line represents a single account, it’s data and we collect it. Lines like Gross Profit or Operating Income are subtotals, and we skip them.
- Sign every line so it adds up to net income. Most source reports show both revenue and expenses as positive numbers. Before anything goes into the warehouse, revenue and other income go in positive; expenses and contra-revenue items like discounts and comps go in negative. That way, the sum of all the data points equals the Net Income on the report.
- Load a control total. I load the month’s Net Income into a separate table, which gives me a top-down check on the bottom-up detail. If the two don’t tie, I know right away that a line was missed or carries the wrong sign.
Get to know dimensions
Every number in your business has context: when it happened, where, and what it was for. In a data warehouse, that context is organized into dimensions, such as time, location, product, and customer. Dimensions are what let you connect data from different sources properly, and ask questions like “How did labor cost per customer served trend by store over the last six months?” and get an answer in seconds. Because every report shares the same dimensions, your numbers line up across the board: “last quarter” and “the downtown location” mean the same thing everywhere, every time.
Identifying the right dimensions takes some work, and it depends on how many data sources you have and how granular their data is. I work with a lot of restaurant companies, and even if you’re in a different business, their model is familiar to most of us. The accounting system produces sales figures, but the point-of-sale (POS) system’s data is far richer. The accounting data totals everything by month, while the POS shows sales by day, even by hour, as well as by menu item, sales channel, and more.
To keep sources like these straight, I add a dimension I call Term, which labels the time granularity of each record. POS data is tagged Daily; the accounting system’s default is Monthly. That one column prevents a common mistake: accidentally adding daily and monthly versions of the same sales together.
I rarely store annual data. If I need an annual number, it’s easy to aggregate the monthly data I already have, and storing both invites double counting. The exception is a source that only comes annually, such as a yearly budget or insurance premium, and that’s exactly where the Term tag earns its keep.
The calendar table: your most important dimension
A business calendar table is vitally important. When you combine data from different sources, you almost always need to link them by date, and often along other dimensions too.
As essential as a calendar table is, AI can now write one for you. I asked Claude to “write me a basic Power Query calendar table,” and it produced a solid version in about 30 seconds.
Pro tip
When I run a daily data source like my POS through Power Query, I add a Month-End date column to every record (one step, using Date.EndOfMonth). All 30 daily sales records for September also carry September 30 in that column. Since many of the other sources you’ll connect to are monthly, such as budgets, P&Ls, and payroll summaries, keying those to the month-end date as well makes linking daily and monthly data much simpler.
Where to start
You don’t need to build it all at once. Start with your keystone source, usually the monthly P&L: load it with signed data and a control total, and add a calendar table. Then bring in one more source, such as POS or payroll, and connect it through shared dimensions. Each source you add makes the whole more valuable, and before long the questions that used to take an afternoon of spreadsheet work take seconds.
If you’d like help getting started, or a second set of eyes on a warehouse you’re already building, I’d be glad to talk.