Hiring

What to ask a data consultant before you hire one

The questions most people ask a data consultant — years of experience, which tools they know, can we see a case study — don't predict whether the engagement will work. A different, shorter set of questions does.

Here is a pattern we see from the other side of the table, when we're brought in to fix something someone else built. A regional equipment rental company with twelve branch locations hires a consultant to build a reporting layer on top of their rental and maintenance systems. The engagement runs fourteen weeks. It produces a Power BI model with around forty measures, a handful of dashboards, and an invoice that gets paid. Everyone is satisfied at the handoff meeting.

Nine months later, the person who commissioned it has left, the dashboards are subtly wrong in ways nobody can diagnose, and the one employee who half-understands the model spends six hours a week nursing it instead of doing her actual job. Nobody documented the business logic. Nobody at the company can open the data model and explain what a given measure does. The consultant is unreachable, or available again only at a day rate for "maintenance."

Nothing in that story involves incompetence. The model probably worked fine on day one. The failure is structural, and it is almost always set up during hiring, not during the build.

Why the wrong questions get asked

Data consulting has an information asymmetry built into it that most professional services don't have to the same degree. You can read a lawyer's brief or sit in on an audit review and form some judgment about the work. You generally cannot open someone else's semantic model, star schema, or orchestration pipeline and tell in twenty minutes whether it's sound. The output looks the same whether it's built well or badly: a dashboard that loads and shows numbers.

So buyers default to proxies — years in business, logos of tools used, a portfolio of screenshots. None of those proxies predict the thing that actually determines whether the engagement helps you two years from now, which is whether you can operate, modify, and eventually replace what gets built without the original consultant in the room.

The handoff is the product, not an afterthought to it

Most statements of work describe the deliverable as a dashboard, a data warehouse, or a model. That framing is backwards. The dashboard is disposable — requirements change, tools get replaced, someone redesigns it in eighteen months regardless of how good it is. What survives is the data model underneath it: the definitions, the joins, the business logic encoded in the transformation layer. That is the actual asset you're paying for, and it's the part almost never discussed before the contract is signed.

A consultant who is confident in the work will talk about documentation and handoff unprompted, because they expect the thing they built to hold up under someone else's hands. A consultant who avoids the topic, or treats it as a separate add-on billed later, is telling you something about how the engagement is likely to end.

What to ask, and what the answer tells you

1. Who can open this and change it after you're gone? The honest answer names a role, not a person — "whoever on your team knows SQL and Power BI" — and includes a plan to document the business logic in plain language, not just in code comments. A vague answer here means you're buying a dependency, not a capability.
2. What do you do differently for a twelve-location rental company versus a single-site business? This isn't a trick question about your industry. It's about whether they think in terms of scale and data volume at all, or whether they have one architecture they apply everywhere regardless of fit. A consultant who proposes the same warehouse-and-pipeline build for a company with 40,000 rows a month as for one with 40 million either hasn't asked, or doesn't care.
3. What would make you tell me not to do this project? Anyone willing to say "your transaction volume doesn't justify a warehouse, Power BI connected directly to QuickBooks will get you there in a tenth of the time" is telling you they'll say the same thing mid-project if scope balloons past what's useful. Anyone who can't name a single scenario where the answer is less work is optimizing for the invoice, not the outcome.
4. What's the first thing you'll deliver, and when? A fourteen-week engagement with nothing to show until week fourteen is a bet, not a project. The right answer carves out a working, if narrow, piece of the problem in the first two to three weeks — one report, one model, one pipeline — so you can judge the work before you've committed the full budget to it.
5. What happens if I want to stop paying you after this phase? This is the most uncomfortable question to ask and the most revealing one to have answered. If stopping means the dashboards break, the pipeline stops refreshing, or nobody internal can touch the model, you haven't hired a consultant. You've acquired a recurring cost disguised as a project.

None of these questions are about technical skill, and that's deliberate. Technical skill is close to a commodity at this point — most consultants who've survived a few years can build a working data model in whatever tool you ask for. What varies enormously is whether they structure the engagement so you still own the result when the contract ends.

What good answers sound like together

Going back to the rental company: a consultant answering these questions well would have proposed starting with branch-level utilization reporting — one question, answerable in three weeks, built on the systems already in place — before touching maintenance costs or fleet forecasting. They'd have written down, in a page anyone at the company could read, what "utilization" meant and how it was calculated, before building a single visual. And they'd have said plainly that if the twelve branches were already on one rental platform with decent reporting, a chunk of the fourteen-week scope wasn't needed at all.

That version of the engagement costs less, finishes with something the company can run without them, and gives the company a real decision point at week three instead of a sunk cost at week fourteen. The difference between that outcome and the one that actually happened was not the quality of the DAX. It was five questions, asked before the first invoice.

Platform

Fabric, Synapse or Databricks for a mid-size company, and when the answer is none of them

Picking a data platform by brand name is how a 340 GB reporting problem ends up running on infrastructure built for volumes you will never reach. The right first question is not which of the three to choose. It is whether you need one of them at all.

Here is a situation we see often enough to recognize it immediately. A mid-size company — a regional building-materials distributor doing about $190 million a year across fourteen branches, in the case we'll use here — gets a demo from a platform vendor or a systems integrator. The pitch is some version of: your reporting is slow, your data is scattered across the ERP, EDI feeds from forty suppliers and branch-level spreadsheets, and the fix is to move everything onto a modern lakehouse. Fabric, Synapse, Databricks — the name varies, the pitch doesn't.

Nobody in that meeting asks how much data the company actually has. In this case it was about 340 GB after removing duplicates and old exports, growing by roughly 40 GB a year. Eleven people write reports, all of them fluent in SQL, none of them writing Python or Spark. The single worst problem was a nightly consolidation job that pulled branch inventory and sales into one finance view, took six hours, and failed about once a week.

None of that is a reason to adopt a distributed compute platform. It's a reason to find out why a six-hour job is six hours long.

Three platforms, three different problems

Synapse, Databricks and Fabric are not three versions of the same product. They were built to solve different problems, and the differences matter more than the marketing suggests.

Databricks exists for heavy, iterative data engineering and machine learning at genuinely large scale — teams writing Spark jobs against data measured in terabytes, with workloads that benefit from distributed compute because a single machine couldn't do the transformation in reasonable time regardless of how well it's written. It assumes a team comfortable in code, not just SQL.

Synapse was Microsoft's attempt to put a data warehouse and big-data processing under one roof. Its own roadmap has been steering customers toward Fabric for a while now, which tells you something about how settled that product's future is.

Fabric is Microsoft's newer and more opinionated answer: one SaaS surface covering ingestion, a lakehouse, a warehouse and Power BI, billed as a single consumption pool. It's a genuinely good fit for an organization already standardized on Power BI that wants one security model and one bill instead of five. But underneath the unified surface it's still a lakehouse architecture with distributed storage and consumption-based compute — built around the same assumption as the other two, that you have enough data and enough workload variety to justify it.

What was actually wrong with the six-hour job

We looked at the distributor's nightly job before anyone talked about platforms. It did a full reload of every branch's transaction history every night, instead of pulling only what changed since the previous run. It joined fourteen branch tables with no useful indexes, inside a database sized for a much smaller workload than it had grown into. None of that is a volume problem. It's a design problem, and it shows up at 340 GB exactly as it would at 34 GB.

Move that same job, unmodified, onto Databricks or Fabric and one of two things happens. Either it still takes hours, because distributing badly-designed work across more compute doesn't fix the design — or it runs faster because the new platform's compute is simply bigger than what it replaced, and you've spent a migration to buy brute force you could have gotten by rewriting the job to load incrementally and adding three indexes.

We rebuilt the pipeline to load incrementally and fixed the indexing. The job now runs in under twenty minutes, on the same SQL database the company already owned. That is the fix that was actually needed, and it required no platform decision at all.

A framework for the decision that should come first

1. Measure your actual data volume and its growth rate. Not the number in the vendor's slide — the number from your own systems, after removing duplicates, stale exports and anything nobody has queried in a year. Most mid-size companies are surprised how small this number is.
2. Inventory the skills you actually have. A team of SQL analysts with no engineer on staff will struggle to maintain a Spark-based platform regardless of how well it's implemented at the start. The platform has to match the people who will run it after the consultants leave.
3. Diagnose the current slowness before blaming the platform. Full reloads instead of incremental ones, missing indexes, and transformation logic buried in spreadsheets account for most of the "our reporting is too slow" complaints we see. Fix those first, cheaply, and see what's left.
4. Only then evaluate Fabric, Synapse or Databricks on fit. If you're already standardized on Power BI and your data has genuinely outgrown a single database, Fabric is a reasonable default. If your workload is heavy transformation or machine learning at real scale, Databricks earns its complexity. If neither is true, don't buy either.

None of this is an argument against these platforms. Databricks is the right tool when the workload actually needs distributed compute. Fabric is a sensible consolidation for a Microsoft-standardized shop with real lakehouse needs. The argument is against choosing one because it was pitched well, before anyone measured what the company actually has.

Power BI

Direct Lake, Import and DirectQuery — choosing without guessing

Most teams pick a storage mode because a blog post called it "the fast one" or "the real-time one." Neither description is wrong. Neither is a reason.

Take a regional equipment rental company we'll call the setup for this piece: twelve branches, roughly 40,000 contracts a year, a transaction fact table sitting at about 2.1 million rows. They wanted two things from one semantic model: branch managers checking which equipment is out and which is sitting idle, updated through the day, and finance running historical utilization and margin reports going back three years.

Someone had heard DirectQuery was "real time" and built the model on it, straight against the production SQL Server that also runs the rental counter software. By mid-morning on a busy day, the dashboard was timing out. Nobody had done anything wrong exactly. They had just answered a question—"do we want fresh data?"—that the storage mode doesn't actually ask.

What each mode is actually doing, underneath

Import copies your data into Power BI's in-memory columnar engine, VertiPaq, on a schedule. Once loaded, queries never touch your source system at all—they run against a heavily compressed copy sitting in memory, which is why Import models are almost always the fastest to interact with. The cost is staleness: the data is only as current as the last refresh, and refresh time grows with row count and transformation complexity, not with how badly you need it to finish quickly.

DirectQuery sends no copy anywhere. Every click, filter and slicer change gets translated into a query that runs live against your source database. That's genuinely real time. It's also completely dependent on your source database's ability to answer ad hoc aggregate queries fast, under concurrent load, while that same database is doing its actual job. A query that takes 200 milliseconds for one branch manager starts queuing when eight branch managers open the report at the same time, because now the production database is serving rental transactions and analytical aggregations off the same tables.

Direct Lake is the newest of the three and gets described as having the best of both, which is close but not quite right. It reads delta tables directly out of OneLake into the same engine Import uses, without a separate copy step—so you get Import-like query speed without a refresh pipeline duplicating the data. But it only works inside Fabric capacity, it only works on data already landed as delta tables, and under memory pressure it silently falls back to row-by-row DirectQuery behavior for whatever got evicted. It is not a setting you flip on an existing import model. It is a different architecture that assumes a lakehouse already exists underneath it.

What this looked like with the real numbers

For the rental company, the honest answer was neither extreme. The utilization view needed to be current within fifteen or twenty minutes, not instant to the second—a branch manager deciding whether to send a forklift across town doesn't need sub-second data, they need data from this morning, not last week. The historical margin reports needed to be correct and fast, with no requirement for freshness tighter than overnight.

The fix was an Import model refreshed every fifteen minutes against a replica, not the production database, with a separate nightly full Import for the historical reports. No DirectQuery anywhere. The branch managers got data that was never more than fifteen minutes stale, which was fast enough to change what they did with it, and the production database stopped seeing analytical load at all.

Where Direct Lake actually earns its place

Direct Lake makes sense when a company already has, or is already building, a lakehouse in Fabric—data landing in OneLake as delta tables for reasons that have nothing to do with this one report. In that case, pointing a semantic model at those tables directly avoids building and maintaining a separate Import pipeline that just re-copies data that's already sitting there in the right format. It is a genuine win in that specific situation.

It is not a reason to build a lakehouse. We've seen teams adopt Fabric and move straight to Direct Lake because it's the newest option, without a pipeline that reliably produces clean, well-partitioned delta tables underneath it. When that discipline isn't there, Direct Lake doesn't fail loudly—it falls back to DirectQuery-style row scanning for the parts of the model under memory pressure, and you get inconsistent performance that's much harder to diagnose than a plain DirectQuery model, because half your queries are fast and half aren't, for reasons that aren't visible from the report.

How we actually decide

1. Write down the real freshness requirement, in minutes, not adjectives. "Real time" usually means "fast enough that the number doesn't look obviously wrong." Pin it to an actual interval before picking a mode around it.
2. Find out what the source system can tolerate. DirectQuery only works if the underlying database can serve analytical queries without degrading its primary job. If that database is also running your operations, assume it can't, and check before building anything.
3. Check whether a lakehouse already exists for other reasons. If delta tables in OneLake are already being maintained, Direct Lake removes a redundant copy step. If they're not, building one solely to enable Direct Lake for one report is usually more infrastructure than the problem warrants.
4. Default to Import unless something specific rules it out. It is the least exotic option, the easiest to debug, and for most reporting—including most dashboards people call "real time" when they mean "pretty current"—it is also the right one.

Strategy

When not to build a data warehouse

Every firm that builds data warehouses, including us, has an incentive to tell you that you need one. Here is the honest version of when you don't, using a case where we said no to the work.

A regional auto parts distributor, twelve branches, about $42 million in revenue, came to us wanting a data warehouse. The board had asked for branch-level profitability ahead of a planning meeting six weeks out, and the CFO had read that a warehouse was how companies answer questions like that.

It might become true, eventually. It wasn't true yet. The board wanted one number, once, before one meeting. A warehouse is infrastructure you build because a question keeps coming back. It is not something you build to answer a question a single time. Building one for a one-time board request would have taken longer than the six weeks available, and the meeting would have come and gone before the first dashboard loaded.

The test is whether the question repeats

Branch profitability for a board meeting is a one-off. Branch profitability every quarter, consumed by finance, ops, and the regional managers, each expecting the same numbers to mean the same thing — that's a recurring question, and recurring questions justify building something durable underneath them. The distinction isn't about how important the question is. The board's question mattered a great deal. It just wasn't going to be asked again in the same form, so there was nothing to amortize the build cost against.

When one system already holds the answer

We checked what the board actually needed: revenue and cost of goods by branch. Both lived entirely inside the distributor's ERP. No join to the point-of-sale systems, no join to anything finance kept by hand. A saved search and a pivot table answered it in three days. Building a warehouse to replicate numbers that already sat in one place, cleanly, would have produced a second copy of the same data with its own refresh schedule and its own chance to drift out of sync with the source — a new failure mode, purchased at real cost, for zero new capability.

This is the most common reason a warehouse project turns out to be unnecessary. The data isn't actually scattered. It's one system and a reporting gap, and the fix is a better query or a semantic layer on top of the system you already have, not a second system underneath it.

When the real problem is upstream, not architectural

Three of the twelve branches had been added through an acquisition and still used a different branch coding scheme in the point-of-sale system than in the ERP. Nobody had reconciled the codes because nothing downstream had ever needed both systems at once. The board's question, answerable from the ERP alone, never touched that mismatch.

But a warehouse would have touched it immediately, because a warehouse's entire job is joining systems on shared keys. Build one before fixing the branch codes and you get a tool that merges two branches into one row, or silently drops a third, and reports a confident, wrong number every quarter until a branch manager notices their revenue looks nothing like what they wrote on a whiteboard that morning. Warehouses don't clean up a mismatch like that. They encode it, repeat it on schedule, and make it harder to find because it's now buried under a pipeline instead of sitting in a spreadsheet where someone might have spotted it.

What justified the real build

The genuine warehouse need showed up three months later, from finance, asking a different question: branch profitability after vendor rebates. Rebates were tiered by purchase volume, tracked in a spreadsheet finance maintained by hand, and they needed to be matched against ERP cost data and POS sell-through by branch, every quarter, for every branch, indefinitely. Three systems, a question asked on a fixed schedule by more than one team, and source systems that weren't going anywhere. That combination is what a warehouse is for.

Same company, same twelve branches, two questions that looked similar on the surface. One took three days and a pivot table. The other justified a proper build, because it recurred, it spanned systems that would still exist two years out, and more than one team needed the same number to mean the same thing at the same time.

What to check before you commission one

1. Count the systems the question actually touches. If the honest answer is one, you need a better report against that system, not a warehouse underneath it.
2. Count how many times you expect to ask it. A question you'll ask once should get a one-off answer, built fast and thrown away. A question you'll ask every month or quarter is a candidate for infrastructure.
3. Check whether the source systems agree with each other first. If two systems use different keys for the same entity, fix that before you build anything that joins them, not after.
4. Confirm the systems underneath will still be in place in two years. A warehouse built against a system mid-migration gets rebuilt the moment that migration finishes, which means you paid for it twice.

None of this is an argument against warehouses. It's an argument against building one as a reflex. The distributor eventually needed exactly the thing they first asked for. They just didn't need it yet, and building it on the wrong question would have cost them the board meeting and left the actual problem, the branch code mismatch, undiscovered underneath a system too new for anyone to question.

Security

Row-level security done properly, and the mistakes that leak one customer's data to another

Row-level security is treated like a checkbox: turn it on, test it once with your own login, ship it. It is actually a chain of five separate things that all have to hold, and the failures are silent by design.

Take a regional equipment rental company with sixty commercial accounts — general contractors, municipal fleets, a couple of large property managers. They built a customer portal on top of Power BI: each account logs in and sees its own invoices, equipment on rent, and service history. Nobody outside Arka built it. It works. It has worked for a long time. And three months after launch, a property manager's finance team calls to ask why they can see invoice totals for a different company on the same report.

Nobody had touched the security rules. Nothing had thrown an error. The report simply returned rows it should not have, because one link in the chain quietly broke.

Row-level security is a chain, not a switch

The phrase "row-level security" describes a single feature in a menu, which makes people think of it as one thing you turn on. It is really five separate things, each of which has to work for the whole to hold:

An identity has to be established — who is actually logged in. That identity has to be mapped to an entitlement — which customer account this person belongs to. The entitlement has to propagate through every relationship in the model, not just the table it was applied to. The filter has to be enforced at every path a user can reach the data through — the report canvas, but also export, drillthrough, live connection, and any embedded or mobile view. And someone has to test all of that, not just the happy path with a demo login.

Miss any one of those five and the result is not an error message. It is a report that renders perfectly and quietly shows the wrong rows to the wrong person.

What actually happened, in numbers

In the rental company's case, the mapping table linking login to account had one column doing two jobs. Fifty-seven of sixty accounts had a clean one-to-one mapping between a user's login and a single customer ID. Three accounts were subsidiaries of the same parent company and had been given the parent's customer ID as a fallback, on the assumption that a subsidiary should see the parent's data too.

That assumption was correct for those three accounts. It was not applied as a rule anyone wrote down — it was a value someone typed into a spreadsheet that fed the mapping table. When a fourth, unrelated account was onboarded later and its setup was copied from an existing row instead of built fresh, it inherited that same parent customer ID by accident. From that point on, one property management company had visibility into roughly $2.3 million a year of another company's invoice history, equipment locations, and payment terms.

The filter logic in the report was correct. The DAX was correct. The mapping table — the one artifact nobody thought of as "security" — was wrong, and everything downstream trusted it without question.

The access paths people forget to test

Most row-level security testing stops at the report canvas: log in as a test user, confirm the numbers look right, move on. That covers exactly one of the ways a person can actually reach the data.

Every row in that table that says "no" or "depends" is a place we have found leaks in the wild. Embedded reporting is the worst offender, because the whole point of an app-owns-data setup is that the report authenticates as a service account, not as the end user — which means the app itself has to enforce the filter, and if that logic lives in one overlooked line of code, the model's security rules never even get consulted.

How we build it so it holds

1. Put the entitlement in its own governed table, not a spreadsheet column. The mapping from login to account needs an owner, a change log, and a rule for what happens when an account is a subsidiary, a shared login, or a departing employee. If it can be edited by copy-paste, it will eventually be wrong.
2. Enforce the filter at the data layer, not the presentation layer. A visual-level filter or a report-page filter can be removed by anyone with edit access or bypassed through export. The rule has to live in the semantic model or the database itself, applied through actual relationships, so there is no path around it.
3. Test every access path with a real second account, not a demo login. Log in as account A and account B and try to see the other's data through export, drillthrough, and any live connection — not just the default view. If the only test ever performed is "does the main report look right," the export path and the embedded path are unverified.
4. Audit who can see the mapping table itself. The entitlement table is more sensitive than any single report, because getting a row wrong there exposes everything downstream of it. Restrict who can edit it and log every change.

None of this requires exotic tooling. It requires treating the mapping between a person and what they are allowed to see as a piece of infrastructure with its own owner, rather than a setup step you do once during launch and never revisit.

Job costing

Why your job cost report is always a few weeks behind the job

Your project managers can tell you a job is running hot before the accounting system shows a single dollar of overrun. That is not a reporting delay you can fix by running the report more often. It is a structural gap between two systems that measure cost differently.

Take a mid-size commercial electrical contractor, forty active jobs, roughly $12 million a year in revenue. One job on the board is a $340,000 tenant buildout, budgeted at twelve weeks. Pull the job cost report from the accounting system on a Friday and it says the job is 64% spent. Ask the superintendent the same day and he says the crew is nearly done with the rough-in and the job feels tight on labor. Those two answers are not in conflict. They are answers to different questions.

What the accounting system actually records

QuickBooks, or whatever general ledger sits under your job costing, records a cost when a transaction is entered — a bill, a check, a payroll run. It attaches that transaction to a job through a class or a job-cost field, which works fine as far as it goes. The problem is timing. A subcontractor does $18,000 of duct work in week six. His invoice arrives in week nine. Your bookkeeper enters it in week ten. Until that entry happens, the job cost report has no idea that cost exists. The ledger is not wrong. It is honest about what has been recorded, which is not the same as what has been incurred.

The same lag shows up in materials, in equipment rental, in every cost category that depends on a vendor sending a bill before the accounting system can see it. On a job with a twelve-week schedule and a typical four-to-six week billing lag from subs and suppliers, the accounting system is often reporting the state of the job as it stood three or four weeks ago, not today.

What the field system actually records

The field side runs on a different clock. Daily time sheets, whether on paper, in a scheduling app, or in a dedicated field service tool, capture labor hours the day they happen. A foreman logging eight hours of two electricians on the buildout today shows up in the field system today. Equipment logs, material pulls from the yard, and daily progress notes work the same way — recorded at the point of use, not at the point of billing.

What the field system is missing is the accounting logic. It knows an electrician worked eight hours on the job. It usually does not know his fully burdened cost — base wage plus payroll tax, workers' comp, and benefits — because that math lives in payroll, not in the field tool. So the field system is fast and granular but financially incomplete, and the accounting system is financially correct but weeks behind. Job costing needs both, and almost no contractor's software stack joins them automatically.

What that gap looks like on one job

Here is the same $340,000 buildout, budget versus what the accounting system shows billed to date versus what the field logs show actually incurred to date, three weeks apart:

By the accounting system's own numbers, this job has $124,000 of budget left and looks comfortable. By the field logs, it has $35,000 left against work that is not yet finished, and the labor line alone is already at 94% of its budgeted total with the buildout not done. The project manager who only trusts the accounting report finds out about the overrun when the sub finally invoices, which is exactly the point at which there is nothing left to do about it. The one who checks the field logs sees it three weeks earlier, while there is still time to add a change order, adjust the schedule, or have the pricing conversation with the client.

Why this is not a data entry problem

The obvious-sounding fix is "enter costs faster." That helps at the margin but does not close the gap, because some of it is structural rather than a matter of speed. Committed cost — a signed subcontract, a purchase order, a change order verbally approved on site and not yet paperworked — is real cost the job will incur, and it exists in neither system cleanly. The field knows the work is happening. The accounting system will not know the number until an invoice lands. A job cost report that only reflects what has been billed is, by construction, an optimistic report on every job that is still in motion.

This is also why buying a nicer job costing module inside your accounting software does not solve it. A better report layered on top of the ledger still only knows what the ledger knows. The fix has to bring in the field data — daily labor hours, committed subcontract and PO amounts, equipment logs — and reconcile it against the accounting system on a shared job key, on a schedule that matches how often decisions actually get made on the job, not how often the books get closed.

What a working version looks like

1. Use committed cost, not just billed cost. A signed subcontract or approved PO is a near-certain future cost. Job cost reports that ignore it understate exposure on every job with outstanding commitments, which on a typical mid-size job is most of them.
2. Pull labor from the field, cost it from payroll. Hours worked come from daily time entry. Fully burdened rate comes from payroll. Neither system alone gets you a defensible labor cost per job; the join does.
3. Reconcile weekly, not at invoice arrival. The point of field data is that it is available before the paperwork is. A weekly job cost run that blends committed, billed, and field-actual cost catches overruns while there is still a decision to make.
4. Pick one job type to prove it on first. Tenant buildouts, service calls, and new construction have different cost patterns and different billing lags. Build the join for the job type that bleeds the most margin, not for everything at once.

None of this replaces a good project manager's instincts. It gives those instincts a number to check itself against, three or four weeks before the accounting system would otherwise let anyone know there was a problem.

Performance

Why your Power BI report is slow, in order of likelihood

Almost nobody has a Power BI performance problem. What they have is a data model problem that Power BI is faithfully rendering slowly, and the two get treated the same way — with more capacity, more RAM, more patience — when only one of those is fixable with money.

A mid-size industrial distributor came to us with a familiar complaint. Their sales performance report — the one the regional managers open every Monday — took 22 seconds to load and another 8 to 10 seconds every time someone changed a filter. Nobody was accusing the report of being wrong. They were accusing it of being slow, and the working theory was that Power BI itself, or the company's Pro licenses, or the laptops in the field, were the bottleneck.

None of those were the problem. The report was built on a single query pulled straight out of the ERP: one table, 2.3 million rows, 42 columns, covering three years of order lines. Every visual on the page was scanning that whole table every time. That is not a licensing problem. That is a table that was never supposed to be a data model.

The order that actually explains most slow reports

We've looked at enough of these to know the causes cluster, and they cluster in a fairly stable order. Here is that order, roughly by how often each one turns out to be the culprit.

Look at where the weight sits. Three-quarters of slow reports trace back to how the data is shaped before a single visual is drawn. The thing people reach for last — more capacity — is the thing that actually explains the problem least often.

One flat table is the default, and it's the wrong one

Every export from an ERP or a CRM comes out as one wide table, because that's how the source system stores it. It's tempting to load that table straight into Power BI and start building, especially when the report is due Friday. It also works, at first, when the table has ten thousand rows.

It stops working once the table has real volume, because a single flat table forces every visual to scan every column of every row, every time. A slicer on region has to look across the whole 2.3 million rows to find the distinct values. A card showing total revenue has to sum the whole column. There is no smaller structure underneath for the engine to lean on.

The distributor's fix was not exotic. We split the one table into a fact table of order lines — quantity, price, cost, about 260,000 rows once duplicated header data was removed — and three dimension tables: product, customer, date. The fact table now holds only the numbers and the keys that point to the dimensions. Filtering by region means the engine touches a customer dimension with a few thousand rows, not a fact table with millions. Page load went from 22 seconds to under 2.

Calculated columns are the second most common tax

A calculated column gets computed once per row, for every row, and stored in the model — which sounds efficient until you realize it also gets recomputed on every refresh and adds real weight to the file, whether or not anyone ever uses it in a visual. A measure, by contrast, computes only what's needed for the specific combination of filters on screen, on demand.

The distributor's model had eleven calculated columns doing things like concatenating a "sales rep — region" label, or flagging whether a row was "large order" with a hardcoded threshold. Every one of those belonged in Power Query, computed once at refresh, or as a measure computed on demand — not baked into the model as a column recalculated across 2.3 million rows on every open.

What we actually check, in order

1. Look at the model before the visuals. Open the model view. Count the tables. If there's one table with forty-plus columns, that's very likely the whole answer, and no visual-level fix will matter until it's resolved.
2. Separate what's stored from what's computed. Every calculated column gets a hard look: does this need to exist as a column, or can it be a measure, or can it be pushed upstream into the query itself.
3. Count visuals per page and check cardinality. A table visual with a column that has 40,000 distinct values will be slow no matter how good the model underneath it is. Aggregate before you visualize, not after.
4. Confirm import versus DirectQuery is a deliberate choice, not a default. DirectQuery has real uses — near-real-time data, sources too large to import — but it means every interaction sends a live query to the source. If nothing forces that tradeoff, import mode is almost always faster.
5. Only then look at capacity. If the model is genuinely lean and the report is still slow, that's when a hardware or licensing conversation is the right next step — not the first one.

None of this requires rebuilding the report from scratch. It requires rebuilding what's underneath it, which the users never see and the report author usually never touches after the first draft. That's exactly why it accumulates — it's invisible until someone times it with a stopwatch.

Reporting

Reporting questions QuickBooks and Xero structurally cannot answer

Every so often a report request comes back as "we can't build that in QuickBooks." That's usually true, and it usually has nothing to do with how the file was set up.

Take a regional facilities maintenance contractor running five branches across three states, about $9.4 million a year in revenue, tracked in QuickBooks Online with each branch mapped to a location tag. The owner wants one thing: which branch is actually more profitable, North or South. It sounds like a five-minute report. It isn't answerable as asked, and the reason is structural, not a matter of better bookkeeping.

A location field holds one value. A business has several.

In QuickBooks and Xero, a class or location tag is a single attribute on a single transaction line. One line, one location. That works fine when every dollar of revenue and cost belongs cleanly to one branch and stays there.

It stops working the moment a crew crosses branch lines, which in this business happens most weeks. Crews get dispatched wherever there's capacity. Payroll codes a crew's hours to whichever branch was active on the pay period cutoff — not to the branch where the job was actually performed. Materials get billed to the ordering branch, not the consuming one. Every one of these is a correct entry by the accounting rules. None of them reflects where the work happened.

The ledger has no way to hold "this labor belongs 60% to North and 40% to South, on these three jobs." It wasn't built to. Classes and locations are tags bolted onto a flat transaction list, not a relational structure that can express one crew serving two branches in the same week.

What that gap looks like in numbers

Pull the location report for North and South and it looks decisive:

The location report says South is the better-run branch. Once labor and materials are reattributed to the job they actually served, using the field service dispatch records rather than the payroll coding shortcut, the answer flips. North is the stronger branch. South has been absorbing costs generated by North's overflow crews for over a year, and the location tags never had anywhere to record that.

This isn't an argument for tagging more carefully. Better tagging discipline helps at the margin, but the underlying structure — one tag, one value, per line — cannot represent a many-to-many relationship between crews, branches, and jobs no matter how conscientious the bookkeeper is. The fix has to happen outside the ledger, by joining it to the dispatch and job-costing system that already records which crew did which job where.

The other structural gap: no version of the past

The second limit shows up less often but does more damage when it does. QuickBooks and Xero keep one version of history. When a transaction gets edited — a reclass, a corrected bill, a cleanup entry six weeks after close — the system doesn't keep the old value alongside the new one. It just changes the past.

The board saw Q1 margin reported at 21%. Six weeks later, a bookkeeper reclassifies $40,000 of misfiled expense out of overhead and into a job cost, fixing a genuine error. Pull the Q1 report today and it shows 24%. Nobody decided Q1 improved. There is no record it was ever reported at 21%, because the ledger doesn't version anything — it only ever shows you the current state of all history, restated silently every time someone touches it.

For a single correction, this is harmless. Over a year of routine bookkeeping cleanup, the numbers a company reported at the time and the numbers that ledger shows today for the same period can diverge meaningfully, and nothing surfaces the drift. The audit log records that each edit happened, but nobody rebuilds a published report from it. If anyone ever needs to answer "what did we actually tell the bank in Q1," the ledger cannot answer that question. It was never designed to remember what it used to say.

What to do about it

1. Identify which dimension the ledger is missing. Usually it's the thing that moves across your tagging boundaries — crews across branches, projects across cost centers, subscriptions across product lines. The operational system that dispatches, schedules, or fulfills the work almost always already records the real relationship.
2. Build a bridge, not a better tag. A many-to-many join between crews and branches, or jobs and locations, lives outside QuickBooks in a small relational structure that can hold "this labor split 60/40." The ledger stays the system of record for cash and tax. The bridge becomes the system of record for allocation.
3. Snapshot before you let anyone edit. If a report goes to a bank, a board, or an owner, capture a copy of the numbers as reported, separate from the live ledger, before the next round of bookkeeping cleanup touches the period. That's the only way to answer "what did we say then" months later.
4. Recompute on a schedule, not on request. Once the bridge exists, rerunning the branch or crew allocation is a scheduled job, not a special project every time someone asks the question.

None of this replaces QuickBooks or Xero. Both are doing exactly what a general ledger is supposed to do. The questions that stall are the ones that need a relationship the ledger was never built to hold, or a memory of the past the ledger was never built to keep.

Reporting

Two reports disagree. Here's what's actually wrong.

Sales says the number is one thing. Finance says it's another. Twenty minutes of a leadership meeting disappears into which spreadsheet is right — and the honest answer is usually that both are.

Every business we walk into has a version of this. It's treated as a data quality problem, and people go looking for the bad number. That's rarely where it lives.

The problem is almost always that the same word means different things in different systems, and nobody ever wrote down which one is the company's answer.

One word, four legitimate meanings

Take "revenue." In a business with a CRM, an order system and an accounting package, that single word can honestly mean any of the following:

Four numbers. Four correct answers. They will never match, and no amount of reconciliation will make them, because they are measuring four different things.

Now add the smaller decisions nobody documented. Does revenue include shipping charged to the customer? Are refunds subtracted in the month of the sale or the month of the refund? Do intercompany transfers count? Is a discount a reduction in revenue or a cost? Each of those is a defensible choice, and each one is being made differently in two places right now.

Why it gets worse rather than better

The usual response is to have someone reconcile the two reports. That works once. It doesn't survive the next month, because the definitions still live in two people's heads and in the formulas of two spreadsheets.

Then a third report appears, built by someone who copied the second one and changed a filter. Then somebody leaves and takes the reasoning with them. Within a year the organization has several numbers for the same thing and a quiet, corrosive habit of not fully trusting any of them.

That last part is the real cost. It isn't the wasted meeting time. It's that people stop making decisions from the data and start making them from instinct, while still paying for the reporting.

How we go about fixing it

The fix is not a better dashboard. It's putting the definition somewhere that isn't a person or a spreadsheet.

In practice that means building a semantic layer between your source systems and every report — one model that holds the calculation for each metric, once. "Net revenue" is defined in a single place, in code, and every report that shows net revenue reads that definition rather than reimplementing it. When the definition changes, it changes everywhere, in one edit.

Getting there is usually less about technology than about a conversation. We sit with the people who own each number and force the ambiguity into the open: what exactly does this include, what does it exclude, and which team's version wins. That conversation is uncomfortable for about an hour and then it's done forever.

The technical part — modeling it properly, making it fast, and locking down who can see which rows — is the straightforward half.

What you can do before calling anyone

1. Take one disputed number and find every version of it. Not all your metrics — one. List every report that shows it and who built each. The list is usually longer than anyone expects, and the exercise alone often reveals the answer.
2. Write the definition down in a sentence. "Net revenue is invoiced amounts excluding shipping and tax, less credits issued in the same period." If two people can't agree on that sentence, no reporting tool will save you — that's the actual problem, and it's a business decision, not a technical one.
3. Decide who owns it. Every metric needs one person who gets to settle disputes about what it means. Without that, the definitions drift again within a quarter.

Do those three things and you'll have fixed most of it yourself. What's left is making the definition live in the system rather than in a document nobody opens — which is where we usually come in.

Operations

What a hand-built monthly report actually costs you

Somebody in your business spends the first few days of every month assembling the same report. It feels like a small annoyance. Written down as a number, it usually isn't.

You know the one. Export a few files, paste them into a workbook, match the columns, fix the ones that didn't match, rebuild the summary, notice something looks wrong, chase it down, send it out. Then do it again in four weeks.

Nobody has ever costed it, because the time is buried in a salary that's being paid anyway. So it survives for years.

The arithmetic

Here's a typical one — a monthly operations and margin pack, assembled by an office manager or a controller:

Eight hours a month is 96 hours a year — about two and a half working weeks. At a fully loaded cost of $45 an hour that's roughly $4,300 a year, every year, to produce a document that already existed last month in a slightly different form.

And that's the cheap part.

The cost nobody counts

A report built by hand in the first week of the month describes a month that ended a week ago. By the time anyone acts on it, the information is five or six weeks old on average.

So the real question isn't what the eight hours cost. It's what you'd have done differently if you'd known three weeks earlier — that a product started losing money, that a customer stopped ordering, that a job was running over. Most businesses can name a decision from the last year that would have gone differently with two weeks' notice, and the value of that one decision usually dwarfs the labor.

There's a third cost too, quieter than both. The person doing it is usually one of your more capable people, and this is the least valuable thing they do all month.

Why it never gets fixed

Not because it's hard. Because it belongs to nobody. It's not big enough to be a project, the person who builds it is too busy building it to automate it, and the software that would replace it looks like a bigger commitment than the annoyance justifies.

So it stays. We've seen the same report assembled by hand for nine years.

How we go about it

Usually in about a week, and usually starting with the exports that already exist rather than anything new.

The work is: connect directly to the systems the exports come from, rebuild the matching and cleaning steps as a pipeline that runs on a schedule instead of a human, model the numbers once so the definitions are fixed, and then deliver the result the way the audience already consumes it — a live dashboard for the people who want to explore, and a scheduled PDF or spreadsheet in the inbox for the people who genuinely just want the file on the first of the month.

That last point matters more than it sounds. Plenty of automation projects fail because they hand people a portal when what they wanted was the attachment they've always had, arriving without anyone having to make it.

Worth doing yourself first

1. Time it honestly for one cycle. Not an estimate — have whoever builds it note the actual hours, including the chasing and the fixing. It's almost always more than the estimate.
2. Ask who reads it, and what they do next. Occasionally the honest answer is nobody and nothing, and the right fix is to stop producing it. That's a free win and we'd rather you found it than paid us to.
3. Check whether the source systems have APIs. If the data can be pulled programmatically — most modern accounting, commerce and field systems can — automation is a week of work rather than a project.

Analytics

Why your P&L can't tell you which products make money

Your income statement is correct. It is also, by design, incapable of answering the question most owners actually want answered — and no amount of better reporting on top of it will change that.

Here is a situation we see constantly. A business is doing well. The P&L shows a healthy gross margin — say 67%. Revenue is growing. And yet cash is tighter than it should be, and nobody can say precisely why.

The instinct is to look harder at the reports. Usually that's the wrong move, because the answer isn't hiding in the reports. It's absent from them.

A chart of accounts has no product dimension

An income statement is organized by account — revenue, cost of goods sold, advertising, shipping, merchant fees. That structure is exactly right for tax filing and for understanding the business as a whole.

But notice what it doesn't have: any notion of which product caused which cost. Advertising is one line. Shipping is one line. Payment processing is one line. Each is a company-wide total sitting beneath the gross margin, and none of them is attached to the thing that generated it.

So when you ask "which products actually make money," the ledger can only answer with revenue minus cost of goods. Everything else is invisible at the product level — not because the bookkeeping is wrong, but because the structure has nowhere to put it.

What that gap looks like in numbers

Take a consumer goods business turning over about $815,000 a year across fourteen products. The P&L shows gross profit of $548,000 — a 67.3% margin. Genuinely healthy.

Now push the four company-wide cost lines down to the products that caused them:

Sixty-seven percent becomes thirty-eight. That's not an accounting error — it's the same money, just attributed rather than pooled. And the aggregate number isn't even the interesting part.

When those costs land on individual products, three of the fourteen turn out to be sold at a loss, together destroying about $14,000 a year. On the P&L all three show gross margins between 52% and 57%. They look like winners.

The reasons are specific and, once visible, obvious. A limited-edition item absorbs a disproportionate share of ad spend because it needs promotion to move. A bulky product costs more to ship than it earns in gross profit. A fragile one comes back on nearly one order in five, and the returned units can't be resold.

None of that is knowable from the ledger. All of it is knowable from the ledger joined to the operational systems that recorded it — the order platform, the payment processor, the ad account.

Why better reporting tools don't solve this

There is a healthy market of reporting products that connect to QuickBooks or Xero and produce polished dashboards. They're good at what they do. But almost all of them are ledger-in, report-out: they read the accounting system and nothing else.

Which means they inherit the ledger's structure, including the missing product dimension. A prettier rendering of an income statement is still an income statement. The moment the question becomes "margin by SKU after fees and ad spend" — or "profit per job after labor and materials," or "revenue per billable technician" — the ledger alone cannot answer it, and neither can anything reading only the ledger.

The answer requires joining data across systems that were never designed to talk to each other. That's an engineering problem, not a reporting one, and it's the reason this analysis is rare despite being valuable.

What to do about it

1. Pick one question that would change a decision. Not "show me everything" — one question. Which products lose money. Which jobs run over. Which customers cost more to serve than they pay. A narrow question is answerable in days; a broad one becomes a project.
2. Find the system that holds the missing dimension. Whatever your ledger can't see is usually recorded somewhere else already — the e-commerce platform knows SKUs, the field service system knows jobs, payroll knows hours. You rarely need new data collection. You need the join.
3. Allocate honestly, and write down how. Shipping by billable weight, fees at actual transaction cost rather than a blended rate, advertising by campaign where a campaign maps to a product. The method matters less than being explicit about it, because the first thing anyone will ask is how a number was arrived at.

Contribution margin isn't net profit — it excludes overhead, salaries and anything else that doesn't vary by product. It answers one question well: which parts of the business earn their keep. For most owners that turns out to be the question they were trying to ask all along.