Guidance Needed - End-to-End Migration Lifecycle — Pre-Migration to Post-Migration

Hi nbhanu​ 

For a migration of 400+ SSRS reports, I recommend treating this as a phased reporting modernization program rather than a one-to-one conversion. Start by rationalizing the report portfolio, then migrate by business domain with clear validation and sign-off criteria.

An important distinction is that SSRS RDL reports can be migrated to Power BI paginated reports where compatible, while interactive Power BI reports generally require redesign. Print-ready statements, detailed operational reports, and large tabular outputs may remain paginated; analytical reports are better candidates for interactive Power BI.

1. Pre-migration assessment

Create a central inventory using SSRS catalog APIs, exported RDL definitions, SQL dependency information, and ExecutionLog3 usage data. Capture each report’s owner, business purpose, criticality, data sources, stored procedures, expressions, parameters, subreports, permissions, subscriptions, and export requirements.

Validate usage with business owners because execution-log retention may miss annual or quarter-end reporting. Classify each report as migrate, redesign, consolidate, retire, or defer. Low usage alone should not determine retirement.

2. Planning and target design

Group reports by business domain and design reusable semantic models around well-defined facts, dimensions, and business measures. Avoid creating a separate model for every report or one oversized model for the entire organization.

Map existing logic according to its purpose:

• Data preparation and reusable transformations belong in SQL or the data-transformation layer.
• Context-dependent analytical calculations belong in DAX measures.
• Formatting and presentation logic should remain in the report.

Document metric definitions, data grain, relationships, date handling, and security requirements. SSRS expressions should not be translated mechanically into DAX because calculation scope, filter context, and totals can behave differently.

3. Migration and development

Start with a representative pilot covering simple reports and challenging cases involving security, complex calculations, subscriptions, and performance. Use the findings to establish templates and plan subsequent migration waves.

Build shared semantic models first, followed by thin reports. Implement workspace/app permissions and RLS early; report filters and parameters are not security controls. Test security with actual consumer identities, including the implications of workspace roles.

You can initially retain existing SQL sources and gateways, then transition selected pipelines to Fabric separately. This reduces the risk of changing the reporting layer and data platform simultaneously.

4. Testing and validation

Reconcile SSRS and Power BI using the same source snapshot, date cutoff, parameters, and user/security context. Compare detail records, totals, subtotals, calculations, null handling, rounding, and date boundaries.

Also test refresh duration, report responsiveness, concurrency, exports, paginated layouts, and unauthorized-access scenarios. Agree acceptance thresholds in advance and obtain documented business-owner sign-off. A successful refresh or matching screenshot is not sufficient evidence of correctness.

5. Deployment and cutover

Use separate development, test, and production workspaces, source control where supported, and deployment pipelines for supported items. Verify environment-specific connections, credentials, gateways, permissions, refresh schedules, subscriptions, and app publication as part of release readiness.

Keep SSRS and Power BI available in parallel for an agreed business cycle, with clear communication about the authoritative report. Retire legacy reports only after validation, adoption, downstream dependency checks, and retention requirements are satisfied. Maintain archived RDL files and a rollback plan.

6. Post-migration support

Establish a hypercare period and then transition to a defined support model. Monitor refresh failures, stale data, gateway/pipeline health, capacity, report performance, data-quality exceptions, and user adoption.

Maintain runbooks covering ownership, dependencies, business definitions, security, refresh SLAs, deployment/rollback procedures, and incident escalation. Knowledge transfer should include practical troubleshooting and recovery exercises.

7. Governance and continuous improvement

Use Fabric/Power BI lineage views and Microsoft Purview to catalog supported assets and relationships. Combine automated discovery with assigned owners, business definitions, sensitivity classifications, and centralized documentation. Automated lineage does not replace documentation of dynamic SQL, custom code, or external dependencies.

The most useful checklist is a single migration register linking every legacy report to its disposition, target report/model, dependencies, validation evidence, business sign-off, cutover date, and support owner.

Microsoft’s guidance provides a good starting point:

• Power BI migration roadmap
• Planning SSRS/RDL migration to Power BI
• Fabric deployment pipelines
• Microsoft Purview tenant scanning

Thank you.

Hi nbhanu​,

The plan above covers the lifecycle well. I'll add the concrete pieces that made the biggest difference in practice: the assessment step, done straight from the ReportServer database, and a repeatable way to reconcile results. Those two decide most of the effort for 400+ reports.

1. Inventory + usage in one query (ExecutionLog3 keeps ~60 days by default, so start capturing it now if you need a longer window):

-- ReportServer DB: one row per report with 12-month usage
SELECT  c.Path,
        c.Name,
        c.ModifiedDate,
        COUNT(e.TimeStart)                         AS Runs12m,
        COUNT(DISTINCT e.UserName)                 AS DistinctUsers12m,
        MAX(e.TimeStart)                           AS LastRun,
        AVG(e.TimeDataRetrieval + e.TimeProcessing + e.TimeRendering) AS AvgMs
FROM    dbo.Catalog c
LEFT JOIN dbo.ExecutionLog3 e
       ON e.ItemPath = c.Path
      AND e.TimeStart >= DATEADD(MONTH, -12, GETDATE())
WHERE   c.Type = 2                                  -- 2 = report
GROUP BY c.Path, c.Name, c.ModifiedDate
ORDER BY Runs12m DESC;

2. Extract the query/procedure behind every report (for dependency mapping and grouping into shared models):

-- data sources + embedded queries per report (from the RDL XML)
WITH rdl AS (
  SELECT Path, CONVERT(xml, CONVERT(varbinary(max), Content)) AS x
  FROM dbo.Catalog WHERE Type = 2
)
SELECT r.Path,
       ds.q.value('(*:DataSourceName)[1]', 'nvarchar(200)') AS DataSource,
       ds.q.value('(*:CommandType)[1]',    'nvarchar(50)')  AS CommandType,
       ds.q.value('(*:CommandText)[1]',    'nvarchar(max)') AS CommandText
FROM rdl r
CROSS APPLY r.x.nodes('//*:DataSet/*:Query') ds(q);

Group reports by CommandText / stored procedure. Clusters that hit the same tables become one shared semantic model, and you will usually find many "different" reports are filters of the same dataset.

3. Triage rule I've found works:

0 runs in 6-12 months              -> retire (confirm with owner)
print/export, pixel-perfect, large  -> Power BI paginated (RDL lifts over mostly as-is)
analytical, filtered, few columns   -> interactive report on a shared model
same query cluster                  -> consolidate into one report + parameters/slicers

4. Reconciliation: compare numbers at a fixed grain, not visually. Run the SSRS query and an equivalent DAX query (DAX query view or XMLA) for the same parameters, export both, and diff on key + measure. Record the diff per report as the sign-off artifact.

EVALUATE
SUMMARIZECOLUMNS (
    'Date'[Month], 'Account'[AccountCode],
    TREATAS ( { "2026-09" }, 'Date'[Month] ),
    "Amount", [Total Amount]
)

So my overall approach would be:

ReportServer catalog + ExecutionLog3 -> inventory + usage
-> RDL query extraction -> cluster by source -> shared models
-> triage: retire / paginated / interactive / consolidate
-> per-report DAX vs SQL diff at fixed grain -> sign-off -> retire SSRS

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

Hi nbhanu​ 

We would like to inquire whether have you got the chance to check the solutions provided by other users in community to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Hi nbhanu​ 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Thank you.