Every analytics organization eventually hits the same wall. There are forty critical reports, twelve “single sources of truth,” and the same metric — revenue, active users, gross margin — calculated four different ways depending on which SQL file you opened first. The fix is well understood: move metric definitions out of report SQL and into a semantic layer. The hard part is doing it without breaking trust, on a date the business actually accepts. Here is the migration plan we use.
Phase 0: Inventory before you architect
Before naming a single metric, build two artifacts.
The first is a report inventory: every Looker dashboard, Power BI report, Tableau workbook, recurring CSV, and exec PDF that anyone in the company opens monthly. Tag each with owner, last-viewed timestamp (most BI tools expose this; if not, parse query logs), and the top three metrics it computes. Expect 30-50% of the inventory to be unused — those are your free wins later.
The second is a metric inventory: every distinct definition of “revenue,” “MRR,” “active customer,” “lead,” etc., extracted from the SQL backing those reports. Diff them. Almost always you will find three flavors of revenue (booked, recognized, billed), two flavors of active user (logged in vs. completed an action), and a “churn” definition that disagrees with itself across two finance reports. This is the document you use to negotiate canonical definitions in Phase 1.
Skip Phase 0 and you will rebuild the same disagreements inside the new semantic layer.
Phase 1: Negotiate canonical definitions
Pull the inventory into a working session with finance, product, and the GMs who own the P&L. The output is a one-page metric contract per metric: name, formula in business English, dimensions it can be sliced by, the SQL grain it lives at, the owner accountable for changes, and an explicit list of the three legacy reports it replaces.
Two rules make this phase succeed. First, the GM owns the metric, not the data team. The data team owns the implementation. Second, you do not negotiate every metric — you negotiate the top twenty by report frequency. The long tail follows the patterns you set.
Phase 2: Build the semantic layer in parallel
Pick one tool — dbt’s MetricFlow, Cube, LookML if you are already on Looker, or AtScale — and stand it up alongside the existing warehouse, not on top of it. Implement the top twenty negotiated metrics. Wire it to one BI tool, not all of them.
Critical discipline: the semantic layer reads from clean, modeled marts (fct_*, dim_*), never from raw warehouse tables and never from staging models. If your marts are not ready, fix the marts first. A semantic layer over messy marts inherits the mess and obscures it behind a friendly name.
Run the new layer in parallel for at least one full reporting cycle (typically a month). Reconcile every published number against the legacy report. Differences will appear; document them, fix the canonical definition or fix the report — but never both at once.
Phase 3: Cut over by report cohort, not big-bang
Group reports into cohorts by audience: exec board pack, finance close, marketing weekly, product weekly, ops on-call, and the long tail. Cut over one cohort at a time, in this order.
Start with the exec board pack. It is small (5-10 reports), has a clear owner (CFO usually), and a successful cutover here gives you political cover for everything downstream. Replace each board-pack report with a semantic-layer-backed version, run both for a month, retire the legacy version after the third clean reconciliation.
Then finance close, which is the highest-stakes cohort but also the one most willing to invest in correctness.
Then marketing and product weeklies, where the long tail of self-serve dashboards lives. This is where the report inventory pays off — anything not viewed in 90 days gets deprecation-noticed for 30 days, then deleted. Do not migrate dead reports.
Last, the ops cohort, which often contains operational queries that belong in application monitoring, not BI. Reroute, do not migrate.
Phase 4: Retire the legacy estate aggressively
The migration only succeeds if the old reports go away. Three mechanisms.
Deprecation banners. Every legacy report gets a banner in the BI tool: “This report is deprecated. The supported version is here: . This URL will be deleted on YYYY-MM-DD.” Pick a date 60 days out. Hold the date.
Query log monitoring. Watch which deprecated reports are still being opened in week 8. Email those users individually with the replacement link. Most “I can’t migrate” objections evaporate when someone walks the user through the new report once.
Hard delete on the announced date. Do not negotiate. The credibility of every future deprecation depends on the first one going through. If a user discovers on day 61 that their report is gone, the answer is the replacement link, not a restore.
What success looks like
Six months in, you should be able to point any analyst at a metric and have them produce the same number you produce, without writing SQL. The number of distinct revenue queries running daily against the warehouse should drop by an order of magnitude. Finance close should get faster, not slower, because reconciliation arguments are now arguments about facts, not about formulas. That is the test. If the formulas are still in dispute, the migration is not done — the semantic layer is just another place where the disagreement lives.