SSRS → Power BI: Practical Migration Steps| Analytic Pulse

on
SSRS → Power BI Migration — Step-by-step | Analytic Pulse

SSRS → Power BI: Practical Migration Steps

Step-by-step guide, decision flow, actionable commands and copyable snippets — all in one single HTML tutorial page.

Quick decision flow

A

Paginated (RDL) reports — Keep as Paginated

If your reports are printable (invoices, long tables) and you have Power BI Premium or PPU — upload them as paginated RDLs. No semantic model is created.

B

Interactive dashboards — Rebuild in PBIX

For visuals, filters, and a semantic model you must rebuild the report in Power BI Desktop, connecting to your on-prem SQL Server (via Gateway).

Step-by-step: Rebuild SSRS as PBIX

1

Open Power BI Desktop & Connect to SQL Server

Get Data → SQL Server. Choose Import for fast performance or DirectQuery for live queries.

Copy Connection Snippet
-- Power BI: SQL Server connection example (for docs)
Server: YOUR_SQL_SERVER_NAME\INSTANCE
Database: YourDatabase
Authentication: Windows or SQL Server
Mode: Import or DirectQuery
2

Install & Configure On-Premises Data Gateway

Install the gateway on a Windows server with network access to SQL Server. Register the gateway with your Power BI account and add a SQL Server data source (use same credentials).

Copy Install Notes
1. Download gateway: https://powerbi.microsoft.com/gateway/
2. Install on Windows machine
3. Sign in with Power BI account and register
4. In Power BI Service → Manage gateways → Add data source → SQL Server
3

Recreate Datasets and Logic

Translate SSRS queries and stored procedure logic into Power BI queries. Use Power Query for ETL and DAX for measures. Recreate parameters as needed.

Copy Power Query Example
let
  Source = Sql.Database("YOUR_SQL_SERVER", "YourDB"),
  dbo_Sales = Source{[Schema="dbo",Item="Sales"]}[Data]
in
  dbo_Sales
4

Build Visuals, Measures & Publish

Create visuals and DAX measures that match SSRS calculations. When ready: Home → Publish. You will now see a Dataset (semantic model) in the Power BI service.

Copy DAX Example
Total Sales = SUM('Sales'[Amount])
Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))

Paginated RDL → Power BI (when to keep RDL)

If your report is printable and you require exact pagination (invoices, long exports), and you have Power BI Premium/PPU, upload RDL as a Paginated Report in a premium workspace. It will connect to SQL via gateway but will not produce a semantic dataset.

Copy RDL Upload Steps
Power BI Service → Workspace (Premium) → + New → Paginated report
Choose .rdl file → Set up gateway connection → Save

Troubleshooting: "I cannot see semantic model"

If you publish and cannot see a dataset/semantic model:

  1. You uploaded an RDL (paginated) — so no dataset appears.
  2. You published a PBIX to a workspace but used DirectQuery or published from a different account — confirm workspace and account.
  3. Check workspace permissions and dataset settings in Power BI Service.
Copy Publish Troubleshoot
1. In Power BI Service → Workspace → Datasets + dataflows
2. Confirm your PBIX dataset is listed
3. If not: ensure you published from Power BI Desktop with the same login
4. If using DirectQuery, dataset still appears but uses live connection
Tip: Rebuilding reports takes effort but pays off — semantic models make scaling, refreshes, and interactive exploration much easier.

Comments

Post a Comment