➜~cpchander@dev:~$
cpchander@dev:~/blog$ cat navision-power-bi-analytics-maturity.mdx
➜ ~ cat navision-power-bi-analytics-maturity.mdx
# published May 23, 2026

Navision + Power BI: the analytics maturity journey from Excel exports to executive dashboards

How a 1,500-employee company graduated from ad-hoc Excel reports to a real BI layer — Microsoft Dynamics NAV (Navision) as the system of record, Power BI for executive dashboards across CRM, HRMS, ERP, and call-center CDR.

Every growing company hits the same wall around 300–500 employees: the data exists, but nobody can answer the simplest leadership question without three days of Excel. "What was our gross margin per account last quarter?" "Which agents are below 70% utilization?" "Where are receivables aging?" Three questions, three people pulling exports, three days, and the answers don't reconcile with each other.

This post is the playbook we used at Virtual Employee to grow out of that — over several years — using Microsoft Dynamics NAV (Navision, now Business Central) as the system of financial truth and Power BI as the executive analytics layer on top.

Stage 0 — Excel everywhere (50–200 employees)

The starting state in almost every company:

  • Finance keeps the books in QuickBooks or Tally
  • Sales tracks pipeline in a spreadsheet
  • Operations tracks utilization in another spreadsheet
  • Leadership asks for reports; analysts spend Friday building them

This works. Don't migrate too early. The cost of building an analytics layer at under 200 employees rarely pays back. The signal that you've outgrown Excel is not "we have a lot of spreadsheets" — it's "we make decisions on numbers that don't agree."

Stage 1 — System of record: Navision (Dynamics NAV / Business Central)

When the finance department can't reconcile the books in under three weeks at month-end, you need a real ERP. We chose Microsoft Navision (Dynamics NAV at the time, now Business Central). The selection criteria that mattered:

  • Multi-currency, multi-company — we billed clients in USD, GBP, AUD, EUR and paid vendors in INR
  • Integrated finance + inventory + procurement — one source of truth, not three
  • Native Microsoft integration — connected cleanly to Active Directory, Exchange, and (later) Power BI
  • Customizable without forking — extensions and C/AL (now AL) language for clean customizations
  • A real partner ecosystem in India — implementation cost was a fraction of SAP or Oracle for our scale

The Navision implementation playbook

I owned this end-to-end. Twelve months from RFP to go-live, then another six months of stabilization. The non-obvious things:

1. Run fit-gap before contract signing. Walk through every business process with the implementation partner and document where Navision's standard behavior matches your operation, where it needs configuration, and where it needs customization. The cost of customization is typically 3–5× the cost of configuration. If a customization is "nice to have," kill it.

2. Map your chart of accounts before migration. This is the boring step that decides whether your reports will work. A bad COA at go-live is a multi-year regret. Take a quarter to design it right with your CFO.

3. Data migration is the killer. Migrating master data (customers, vendors, items, GL balances) is straightforward. Migrating open transactions (open invoices, outstanding POs, accrued expenses) is where 80% of project risk lives. We did it in stages — historical data into an archive company, current open transactions into the live company at cutover, then one trial close before go-live.

4. Training is half the project. We trained finance, procurement, sales operations, and inventory teams. We also trained internal IT (me) deeply enough to operate the system without external consultants. That last piece saved seven figures over the next decade.

5. Customize against your process, not your tradition. A common failure mode: companies customize the ERP to match their old way of working. Don't. The point of an ERP is the discipline of working in the way the ERP expects, with customization only where your business truly differs.

What Navision is genuinely good at

  • Financial integrity. Closes are predictable. The audit trail is solid. ISO 27001 evidence chain falls out of it.
  • Inventory + procurement integration. AP, PO, GR, AR all reconcile.
  • Modifiability. Extensions let you adapt without breaking upgrade paths.
  • Microsoft ecosystem leverage. Power BI, Outlook, Excel, Teams all integrate natively.

What it's not great at

  • Sales pipeline. Navision's CRM module is bare-bones. Run Dynamics CRM (or Salesforce or HubSpot) separately and integrate.
  • UX out-of-the-box. It's powerful but dense. Plan for power-user training, not "intuitive for everyone."
  • Cloud-first features in older versions. If you can run Business Central (the cloud version), do — it's significantly better than on-prem NAV 2018.

Stage 2 — Operational systems alongside Navision

By the time we were 500+ employees, the data architecture looked like this:

Navision (NAV)       ← finance, AR, AP, inventory, procurement
Dynamics CRM         ← sales pipeline, accounts, opportunities
In-house HRMS        ← employees, attendance, payroll, IT tickets
Asterisk CDR         ← calls (inbound/outbound), agent activity
SugarCRM (legacy)    ← deprecated, archived

Five systems, five sources of truth — about different things, which is fine. The problem: leadership questions usually span multiple systems. "Which clients are most profitable per agent hour?" needs CRM (client) + HRMS (agent hours) + CDR (utilization) + NAV (revenue, cost).

Stage 3 — Power BI as the analytics layer

We built Power BI on top of all five systems. The pattern:

[NAV]  [Dynamics CRM]  [HRMS]  [CDR]
   ↓        ↓             ↓       ↓
   └────────┴─────────────┴───────┘
                  ↓
         [Staging SQL Server / Lake]
                  ↓
           [Power BI semantic model]
                  ↓
        [Dashboards + Reports]
                  ↓
            [Leadership / Operators]

The architecture isn't sexy and isn't supposed to be. The hard parts aren't the visualizations.

What we built, in order

1. Staging layer. Don't query operational systems directly from Power BI. Replicate to a staging SQL Server (or eventually a small data lake) on a 15-minute or hourly cadence. Operational systems should never be slowed by analytics queries.

2. A canonical data model. Decide what "Account" means, what "Agent" means, what "Hour worked" means — once. Document the joins between CRM accounts, NAV customers, HRMS billable hours, CDR call records. This semantic layer is what makes the same numbers appear regardless of which dashboard you open.

3. DAX measures that match how leadership thinks. Not how the data is stored. Leadership thinks in "gross margin per account this quarter." That's a calculated measure across multiple tables. Build it once in DAX, reuse everywhere.

4. Refresh strategy. Daily for most things; hourly for sales pipeline and CDR. We used Power BI Gateway for on-prem-to-cloud refresh, and direct connections where possible.

5. Role-based access. Sales sees pipeline. Finance sees AR/AP. Leadership sees everything. Row-level security in Power BI enforces this without separate dashboards per role.

The five dashboards that actually got used

After a lot of building, only these were checked daily/weekly:

  • CEO dashboard — revenue, gross margin, headcount, top-5 accounts, AR aging — one page, daily refresh
  • Account profitability — revenue and cost per client account, agent utilization, margin trend — weekly
  • Cash flow — receivables, payables, cash balance, 13-week forecast — weekly
  • Sales pipeline — open opportunities, weighted pipeline, stage conversion, slip risk — daily
  • Utilization — agents at risk (high or low utilization), trend by team — weekly

Half the dashboards we built initially weren't used. That's normal. Build, measure usage, kill the ones nobody opens.

Lessons from a decade of running this

1. The system of record matters more than the BI tool. A great Power BI dashboard on top of garbage data tells you garbage with prettier charts. Invest in NAV/CRM/HRMS data quality first.

2. Don't try to be Snowflake before you need to be. A small SQL Server staging database carries you through several hundred employees. Don't over-engineer the data platform.

3. Power BI's semantic model is the moat. Once you have a well-designed model with clean DAX measures, future dashboards take hours instead of weeks. The model is the asset; the visualizations are disposable.

4. Train one person per business function on Power BI. A "Power BI champion" in finance, sales, ops. They build day-to-day reports themselves; IT only builds shared infrastructure.

5. Migrate to Business Central if you're still on NAV 2018 or older. The cloud version is better, the integration with Power BI is better, and the upgrade-vs-rewrite analysis usually favors moving.

What I'd build today

If I were starting fresh on a 200-person company in 2026:

  • Microsoft Dynamics 365 Business Central (cloud) for ERP
  • HubSpot for CRM (cheaper, better UX than Dynamics CRM for SMB scale)
  • An in-house HRMS or BambooHR — depending on whether you have engineering capacity
  • Power BI Pro/Premium with a small Azure SQL Database for staging
  • Fabric (Microsoft's unified data platform) when you outgrow SQL Database — usually around 1,000+ employees

The pattern doesn't change much: operational systems → staging → semantic model → dashboards. The tools change every five years; the architecture doesn't.

Use cases — what Navision and Power BI actually delivered

Use case 1 — The COVID-period business visibility

During the COVID lockdown (the full transition story), leadership wanted daily visibility into:

  • How many employees were productively logged in (HRMS data)
  • How many billable hours were being delivered (HRMS + Asterisk CDR)
  • What revenue was at risk if specific client engagements paused (CRM + Navision)
  • Cash position and 13-week forecast (Navision)

Power BI dashboards we already had — with maybe an hour of additional measure tweaking — surfaced all of this. In a crisis you don't have time to build new analytics. You operate on the dashboards you already have. The COVID period was a stress test of the BI layer; it passed.

Use case 2 — Account profitability that changed how sales was paid

We built an account-profitability dashboard combining Navision revenue + cost + Asterisk CDR + HRMS utilization. Leadership saw, for the first time, margin per account, not just revenue per account. Three implications:

  1. Sales compensation re-aligned from revenue-only to margin-weighted
  2. We renegotiated rates on the bottom 10% of accounts; some churned, the rest improved
  3. Account managers started caring about utilization in a way they hadn't before

The dashboard took eight weeks to build. It changed the company's operating economics within six months.

Use case 3 — Real-time cash flow during a slow-pay quarter

In Q3 2017 several large clients delayed payments due to their own quarter-end pressures. Without the cash-flow Power BI dashboard, we'd have hit a working-capital scare. With it, we saw the 13-week forecast turning red two weeks in advance, took a precautionary working-capital loan (cheaper because we asked early), and renegotiated with the slowest-paying clients. Forecasting is operational defense. We avoided a payroll problem two months before it would have happened.

Use case 4 — Navision custom workflow for a multi-currency invoicing rule

A UK client wanted invoicing in GBP based on USD-quoted rates, with a quarterly rate-true-up against a published exchange-rate index. Standard Navision invoicing couldn't model this without a custom workflow. We built:

  • A custom Navision extension (in C/AL at the time) that stored a per-client base currency, quote currency, and rate-true-up rule
  • A monthly batch that generated the GBP invoice from the USD hours-cost using current rate
  • A quarterly true-up batch that adjusted the next invoice based on the average index rate

The customization took 3 weeks of partner consulting time. It removed a manual Excel reconciliation that had been costing finance two days per month. Navision's customizability — when used surgically — replaces real labor.

Use case 5 — The CFO's monthly close went from 18 days to 6

Pre-Navision, our finance team closed the month in ~18 working days. Post-Navision + Power BI:

  • Standardized journal entries via Navision workflow
  • Automated revenue recognition driven by Navision billing data
  • AR aging dashboard in Power BI eliminated manual AR follow-up spreadsheets
  • Bank reconciliation automated through Navision's bank integration

Within two years the monthly close was a 6-day process. A 12-day reduction in close time means leadership decisions get made on numbers that are 12 days fresher. That compounds.

The meta-point

A real BI layer isn't a software project. It's the operational discipline of agreeing on what the numbers mean across every department, and then automating their delivery. The software is the easy part. The political work of defining "gross margin per account" so finance and sales actually agree — that's the hard part.

If you can ship that discipline, you've added a permanent edge to your operations. Decisions get faster. Hand-wavy debates get shorter. Performance reviews get fairer because the numbers are visible.

That's what 17 years at Virtual Employee taught me. It's what I'm building into every venture I run today.

← back to /blog