BI migration without a rollback: inventory, rationalisation and parallel running

A migration that recreates every existing report fails twice: once on budget, and again on adoption. In every estate we have inventoried, a large share of reports have no regular audience and a meaningful share are near-duplicates of each other. Rebuilding all of it is the most expensive way to arrive at the same mess on a different platform.

This is the method we use instead. It is slower at the start and considerably faster overall, and it has not produced a rollback.

Phase one: inventory with usage telemetry

Before any disposition decision, build a register: every report, dashboard and extract with its owner, its data sources, its business criticality, and, critically, twelve months of actual usage.

Usage data is available in every major platform. Qlik Sense has engine and audit logs. Tableau Server exposes historical_events and the metadata API. Cognos has audit tables. BusinessObjects has the auditing universe. SSRS has ExecutionLog. Power BI's own activity log is available via the admin API.

That telemetry is what converts a political argument into a data exercise. "Nobody uses this report" is contestable; "this report has been opened four times in twelve months, all by its author" is not.

In a typical mid-sized estate, the inventory takes about four weeks including the data extraction and the interviews needed to attribute ownership. It is the single highest-return phase of the programme and should be bought as a fixed-price piece of work in its own right.

Phase two: rationalisation with named owners

Then a disposition decision per object, made with the business:

• Migrate. It is used, it is needed, it goes across.
• Merge, several near-identical objects become one, usually with a parameter or a filter replacing the variants.
• Replace with self-service, a dozen fixed reports become one certified semantic model that users can explore.
• Retire, no audience, or the need has gone.

Two rules make this work. First, each decision is recorded against a named person who agreed it, not a team. Second, low usage is evidence rather than a verdict: a quarterly regulatory return opened four times a year is critical, and it gets migrated regardless of the number.

Expect the surviving set to be considerably smaller than the starting one. In the migrations we have run, the reduction has ranged from about 40 per cent to nearly 80 per cent. That reduction is the business case.

Phase three: extract the logic, not the layout

This is where migrations are won or lost, and where tooling helps least.

Business logic in a legacy estate lives in places that do not translate:

• Qlik: load scripts, with set analysis in the expressions and resident table transformations that encode business rules.
• Tableau: calculated fields, table calculations, and level-of-detail expressions, frequently duplicated per workbook.
• Cognos: Framework Manager models with query subjects, filters and calculated items.
• BusinessObjects: universes, with objects, conditions and derived tables carrying a decade of accumulated rules.
• SSRS: dataset SQL, with logic in stored procedures that nobody has read since the author left.

The work is to read it, document each calculation in business language, validate that documentation with the relevant business owner, and then reimplement once in a semantic model rather than per report. The documentation step is not optional: it is the only point at which you find out that the legacy calculation is wrong.

Because it frequently is. In several migrations we have found calculations in the old platform that produced incorrect figures, a date filter excluding the final day of the period, a join silently dropping rows with null keys, a currency conversion using the wrong rate date. Those get corrected, documented, and formally notified to the business, because people have been making decisions on them.

Phase four: rebuild native, not pixel-for-pixel

Clients often ask for a visual copy of the old reports to minimise retraining. We resist it, for a specific reason: a pixel-for-pixel copy carries over the previous platform's workarounds, and those workarounds exist because of limitations Power BI does not have. You pay to rebuild a constraint.

Keep the information content and the numbers identical. Redesign the presentation to native patterns. Where a report genuinely must look unchanged (a regulatory return in a prescribed format, or a document that goes to customers), say so explicitly and build that one as a faithful copy, usually as a paginated report.

Order of work: semantic model first, reports second. A model built after the reports is a model shaped by whichever report was built first.

Phase five: parallel run with reconciliation evidence

Nothing is switched off on trust. During the parallel period, both platforms are live and an automated harness compares the same measures, for the same periods and dimensions, on both. Every variance is either corrected or explained in writing.

The harness is not elaborate: a scheduled job pulling figures from both platforms into a comparison table, with a tolerance and a variance log. The value is that variances are found in days by a machine rather than in a board meeting by a director.

Sign-off per wave is the reconciliation pack, not a demo. A demo proves a report renders; a reconciliation pack proves it is right.

Phase six: decommission properly

The saving only materialises when the old platform is switched off, and that step is where programmes stall.

• Archive what must be kept. Historical outputs required for audit are archived as rendered documents, PDF or Excel with metadata, in an immutable store with an agreed retention period. Do not keep a legacy platform alive just to retain history.
• Revoke access on a published date, with a short read-only grace period for named individuals who ask.
• Terminate the licence on the contract anniversary, and record the realised saving with the date so the business case closes.

Plan the dual-licensing period explicitly. It is a real cost, it is unavoidable, and it should appear in the budget rather than as a surprise in month six.

Wave sequencing: how to choose the first one

Not the biggest, and not the easiest. The first wave should be a business area with a motivated sponsor, data sources you can access, and at least one contested number, because resolving a contested number in wave one buys the credibility that funds the rest of the programme.

Avoid starting with the area that has the most reports. Volume in wave one means a long gap before anyone sees anything, which is how programmes lose their sponsor.

Timescales, honestly

• Inventory and rationalisation: about four weeks for a typical mid-sized estate.
• Target design: two to three weeks, derived from the surviving reports rather than the old folder structure.
• Each wave: four to eight weeks depending on source complexity, with reconciliation inside that.
• A 60-report estate with clean sources: three to four months total.
• A 900-report BusinessObjects estate with a decade of undocumented universes: a year or more, and anyone quoting less has not read the universes.

The variable is almost never build speed. It is source access, data quality, and how long the organisation takes to agree a definition.

What we would do first

Buy the inventory as a standalone fixed-price piece of work, from anyone, and insist on the usage telemetry. If the resulting register shows that 60 per cent of your estate has no audience, you have saved most of the migration budget before any development has started, and you own the register regardless of who does the build.

Platform-specific notes

The method is the same everywhere. The archaeology is not.

Qlik. The logic lives in load scripts, and set analysis expressions encode business rules that look like filters. The specific risk is resident tables and IntervalMatch constructs that silently change grain. Budget time to read the scripts properly; automated extraction tools report the syntax, not the intent.

Tableau. Calculated fields and level-of-detail expressions are duplicated per workbook, so the same rule exists in slightly different forms across dozens of files. Extract them all, cluster the near-identical ones, and resolve the differences with the business. That clustering exercise usually reveals two or three genuine definitional disputes nobody knew existed.

Cognos. Framework Manager models are closer to a semantic layer than the others, which helps, but determinants and query subject filters carry rules that do not appear in the reports. Read the model, not only the report specifications.

SAP BusinessObjects. Universes accumulate for years. Objects, conditions, derived tables and contexts all contain logic, and the contexts in particular encode join behaviour that is invisible to report authors. This is the slowest estate type to migrate and the one where usage-based rationalisation saves the most.

SSRS and paginated reports. The logic is in dataset SQL and stored procedures. The good news is that it is readable; the bad news is that pixel-perfect output frequently must be preserved for regulatory or customer-facing documents. Those stay as paginated reports in the target rather than being redesigned.

Excel. Almost every estate has a parallel Excel layer, and ignoring it guarantees the migration fails on adoption. Inventory the critical workbooks, decide which become reports and which become a semantic model connection from Excel, and say explicitly which will not be replaced.

Training and adoption

A migration is only finished when people use the new thing, and the organisations that struggle are the ones that treated training as a one-hour launch session.

What works:

• Train authors separately from readers. Different needs, different session length, different follow-up.
• Use the client's own data and the client's own reports in training. Generic demo data teaches the tool and not the job.
• Run clinics, not courses. A weekly thirty-minute open session for the first quarter resolves more real problems than a full-day course, and it tells you where the model is unclear.
• Publish a measure dictionary that business users can read. Most "the new report is wrong" tickets are definitional, and a dictionary answers them without an engineer.
• Keep the old platform read-only for a defined period after cutover, with an end date published in advance. Removing the safety net too early generates resistance; leaving it indefinitely guarantees nobody moves.

That is what we sell as a first engagement, and it is deliberately the smallest thing we can sell. Book a technical review if you want to scope it.