DWH migration

AgentData turns a data-warehouse migration from a rewrite into a provable, report-driven process. Instead of re-implementing hundreds of ETL jobs by hand, you reverse-engineer what the old estate actually does, migrate one business area at a time, and prove — report by report — that the new platform produces the same numbers before anything is cut over.

The migration lifecycle

Every migration project moves through a strictly forward pipeline, and the approval gate in the middle is enforced by the server, not by convention:

scoping → comparison → approved → schema built → flows ready → validated

1. Scope — pick 1–2 facts and their related dimensions from the discovered semantic model. Small scopes migrate fast and prove value early.
2. Compare — record baselines: the numbers an existing customer report shows vs. what AgentData computes from the same sources. A baseline with differences cannot be approved; resolve the model until the numbers match.
3. Approve — a human approves each matching baseline. Only when every baseline is approved does the migration unlock generation.
4. Generate the target schema — dimensional (dim_/fact_, surrogate keys, FK columns) or relational, as idempotent DDL. The target schema is versioned per migration: extending the scope later produces an additive upgrade script — a live target is never dropped.
5. Generate load flows — ordered load steps (dimensions before facts), each reading the bound source tables and writing the target table. With a staging-database target, one click registers them as a real, schedulable flow.
6. Validate — run the same baseline comparisons against the new structure. All matching → the migration is validated and a cutover report records the evidence.

Reverse-engineering the legacy estate

Upload definition files — no live access to the old servers required:

Tool	What you upload	What AgentData extracts
SSRS	.rdl report files	Datasets, queries, and the source tables each report reads
SSIS	.dtsx packages	Execute-SQL statements, OLE DB sources (reads) and destinations (write targets)
Informatica	pmrep repository XML export	One ETL asset per mapping with its sources and targets
Power BI / Qlik / OBIEE	live connection or files	Full report lineage via the existing BI adapters

Every parsed item becomes a legacy asset with its source tables. AgentData then computes relatedness: which scoped facts and dimensions each asset depends on — so you always know which reports and ETL jobs a finished fact unlocks, and what remains.

Baselines have a second intake path: if the customer can only hand over report outputs (PDF or Excel), run them through Documents Automation — the extracted figures become the expected baseline values.

Tracking the project (AgentDesk)

When you manage the engagement in AgentDesk, the migration starts there: it imports the asset inventory as a day-one snapshot (total reports, SSIS packages, processes), tracks each asset as a work item (discovered → mapped → covered → done), and records every comparison as test evidence. The two products talk over MCP — AgentData exposes migration_assets, migration_status, migration_coverage and compare_report for exactly this.

Sample use case: SQL Server DWH → Google Cloud

Starting point: an on-prem Microsoft SQL Server data warehouse, loaded by ~200 SSIS packages and reported two ways — classic SSRS reports plus newer Power BI dashboards. Target: Google Cloud, following the medallion model — bronze in object storage, silver/gold in BigQuery.

1. Connect and discover. Register the SQL Server DWH as a read-only source. Discovery profiles the schema and classifies candidate facts and dimensions.

2. Inventory the estate. Two intake paths feed one inventory:

• File upload: the .rdl files and .dtsx packages — parsed into assets with the tables each one touches.
• Live connection: register the Power BI workspace through the BI adapter; its reports, datasets and measures are harvested into the lineage catalog and imported into the inventory with one click (Import from BI lineage).

Say that yields 120 SSRS reports, 60 Power BI reports and 200 ETL packages. The AgentDesk migration project freezes these totals as the progress denominator.

3. First scope. The coverage view shows that the Sales fact plus Customer, Product and Date dimensions are read by 75 of the 180 reports (SSRS and Power BI alike — relatedness works from lineage, not from the tool) — that's the first migration scope.

4. Prove the numbers. Pick two trusted reports — one SSRS (e.g. Monthly Revenue by Region) and one Power BI dashboard page. Baselines come from whichever path fits: the Power BI figures straight from the connected workspace, the SSRS figures from the report query — or, when only printed output exists, drop the PDF/Excel export through Documents Automation. Record them against AgentData's semantic-layer results from the same SQL Server. Differences surface model problems (a missed join, a wrong filter); fix and re-compare until both match, then approve.

5. Materialize on GCP, medallion-style.

• Bronze (staging) — object storage: register the GCS bucket as a native GCS file store (service-account key, or its S3-interoperability endpoint + HMAC key) and set it as the migration's lake target. The generated load flows extract from SQL Server and write partitioned Parquet (e.g. bronze/sales/month=2026-01/part-0.parquet). Big facts can load as incremental keyed upserts instead of full reloads.
• Silver/gold — BigQuery: set a BigQuery target (project + dataset + the bronze store) and AgentData generates the BigQuery plan: the dimensional DDL translated to BigQuery types, facts partitioned by date and clustered by their dimension keys, plus one LOAD DATA … FROM FILES (format='PARQUET', uris=['gs://…/bronze/<table>/*.parquet']) statement per table. Apply it with bq/the console; gold views go on top.

6. Validate and unlock. Run the post-load validation: the same two baseline reports, computed from BigQuery. Numbers match → the migration is validated, the cutover report stores the evidence, and in AgentDesk the 75 dependent reports flip to covered. Power BI users repoint their datasets at BigQuery (or at AgentData's semantic layer over it) and keep their visuals; SSRS reports are rebuilt on the new schema only if someone still needs them. The next scope (say, Inventory) begins at step 3.

7. Repeat and extend. The second scope generates target-schema version 2 — an additive upgrade script against the live BigQuery/GCS layout, never a rebuild. Each cycle shrinks the uncovered asset list until the old DWH has nothing left that anyone reads.

Why report-first works

The old DWH's hundreds of ETL jobs encode how data was produced; the reports encode what the business actually needs. Migrating report-first means you only rebuild the pipelines that matter — assets nothing reads any more are retired, not migrated.