powerbi-repoint-to-azure-sql

Re-Pointing Power BI Reports from Fabric to Azure SQL

How to migrate Power BI reports from the ProgressiveDataSet (Fabric semantic model backed by CSV/gateway) to Azure SQL PSI_Analytics on procserv-proddata.database.windows.net.

---

Why Migrate

Fabric / CSV Gateway (current)	Azure SQL (target)
On-premises gateway dependency	No gateway required
Import mode only — data up to 1 day stale	DirectQuery option — near real-time
33 raw AFTEC tables only	33 raw tables plus 17 enriched analytics tables (AI-classified redbooks, workforce features, machine DNA, lessons learned, etc.)
Full overwrite on refresh — no change history	Pipeline is extensible for snapshot archiving
Personal workspace (John Tymes)	Company-managed infrastructure
5 AM refresh only	Nightly 1 AM rebuild; DirectQuery = live

Column names in Azure SQL match the CSV headers exactly. Existing DAX measures work unchanged when re-pointed.

---

Prerequisites

• Power BI Desktop installed (latest version recommended)
• Azure AD account with access to PSI_Analytics database
• VPN connection (Azure SQL uses private endpoint at 10.160.140.4)
• Download the .pbix file from the Power BI Service if you don’t have it locally

---

Report Inventory — What Needs to Migrate

These reports currently use the ProgressiveDataSet semantic model:

Report	Dataset	Priority
Executive	ProgressiveDataSet	High
Production	ProgressiveDataSet	High
Purchasing	ProgressiveDataSet	High
Spare Parts	ProgressiveDataSet	Medium
ProcessServices	ProgressiveDataSet	Medium
Progressive IQ	ProgressiveDataSet	Medium
ProgressiveDataSet	ProgressiveDataSet	Low
Capacity2	ProgressiveDataSet	Medium

These use standalone datasets (also gateway-dependent, same migration approach):

Report	Dataset
Capacity	Capacity (standalone)
Accounting	Accounting (standalone)
TSLabor	TSLabor (standalone)
ServiceTickets	ServiceTickets (standalone)
ProjectDashBoard	ProjectDashBoard (standalone)

Cloned reports in the PSI Analytics workspace (5acabe18-9e94-4144-816f-32bbdde02f07) use cross-workspace binding to the originals — they will follow automatically once the source dataset is migrated.

---

Migration Paths

There are two approaches. Choose based on your situation:

Approach	When to Use	Effort
A. Re-point the semantic model	You want all downstream reports to switch at once	Low — one change, all reports follow
B. Re-point individual reports	You want to migrate reports one at a time, or a report has its own embedded dataset	Medium — per-report work

---

Approach A: Re-Point the ProgressiveDataSet Semantic Model

This is the recommended first step. By changing the data source of the shared ProgressiveDataSet from CSV/gateway to Azure SQL, every report connected to it switches automatically.

Step 1 — Download the Dataset .pbix

1. Go to the John Tymes workspace in the Power BI Service
2. Find the ProgressiveDataSet semantic model
3. If a .pbix file is available for download, download it. If not, you’ll need to recreate the connection in Power BI Desktop (see Step 2 alternative below)

Step 2 — Change the Data Source

1. Open the .pbix in Power BI Desktop

2. Go to Home > Transform data (opens Power Query Editor)

3. In the Queries pane on the left, you’ll see all 33 tables (customer, openwo, partmaster, etc.)

4. Select the first table (e.g., customer)

5. In the Applied Steps pane, click the gear icon on the Source step

6. The current source will look like:

   Csv.Document(File.Contents("\\ad.ptihome.com\DFS\DATA\unixshare\dgwreportfiles\customer.csv"), ...)
   

7. Replace it with a SQL Server source:

   • Click Home > New Source > SQL Server database
   • Server: procserv-proddata.database.windows.net
   • Database: PSI_Analytics
   • Click OK
   • Authentication: Select Microsoft account (Azure AD), sign in with your PSI account
   • Select the matching table name

8. Repeat for each table. The table names and column names are identical, so no downstream changes are needed.

Tip: To do this faster in the Advanced Editor, replace the M code source line. For each table, change:

Source = Csv.Document(File.Contents("\\ad.ptihome.com\DFS\DATA\unixshare\dgwreportfiles\customer.csv"), [Delimiter="|", ...])

to:

Source = Sql.Database("procserv-proddata.database.windows.net", "PSI_Analytics", [Query="SELECT * FROM [customer]"])

Or use the navigation approach:

Source = Sql.Database("procserv-proddata.database.windows.net", "PSI_Analytics"),
customer = Source{[Schema="dbo",Item="customer"]}[Data]

Step 3 — Choose Import vs DirectQuery

When connecting to Azure SQL, Power BI Desktop will ask for the Data Connectivity mode:

Mode	Pros	Cons
Import	Fastest report performance, works offline	Data only as fresh as last refresh, larger .pbix file
DirectQuery	Always current data, no scheduled refresh needed	Slightly slower visuals, requires VPN/network access at query time

Recommendation: Start with Import mode to match current behavior. Switch to DirectQuery later once validated.

Step 4 — Validate

1. In Power Query Editor, click Close & Apply
2. Check that all tables loaded successfully (look for error icons)
3. Verify row counts are reasonable (compare to the table inventory)
4. Open a few report pages — visuals should render identically
5. Spot-check a few DAX measures to confirm they return the same values

Step 5 — Publish

1. File > Publish to the same workspace
2. The dataset will now source from Azure SQL instead of the gateway
3. If using Import mode, set up a scheduled refresh (Settings > Scheduled refresh)
4. All downstream reports (Executive, Production, Purchasing, etc.) will automatically use the updated data source

Step 6 — Disable the Gateway Refresh

Once validated, disable the old 5 AM gateway refresh on the ProgressiveDataSet to avoid conflicts:

1. In Power BI Service, go to the dataset settings
2. Under Gateway and cloud connections, remove or disable the on-premises gateway binding
3. Under Scheduled refresh, update or remove the old schedule

---

Approach B: Re-Point an Individual Report

Use this when migrating standalone-dataset reports (Capacity, Accounting, TSLabor, etc.) or when you want to migrate a specific report independently.

For Reports with Embedded CSV Data Sources

Same process as Approach A, Steps 2-5, but applied to the individual report’s .pbix file.

For Reports with a Live Connection to ProgressiveDataSet

These reports don’t have their own data model — they send DAX to the shared semantic model. To migrate these individually:

1. Open the .pbix in Power BI Desktop
2. Go to Home > Transform data. If this is grayed out, the report uses a live connection.
3. To switch from live connection to a local model:
   • File > Options and Settings > Options > Current File > Data Load — uncheck “Auto date/time”
   • Go to Home > Get data > SQL Server database
   • Server: procserv-proddata.database.windows.net
   • Database: PSI_Analytics
   • Select the tables the report uses
   • Click Load
4. The report visuals will break temporarily (they reference the old dataset columns)
5. Fix each visual by re-mapping fields to the newly imported tables
6. Recreate any DAX measures that were defined in the shared dataset (not in the report)
7. Publish to the workspace

Note: This is significantly more work than Approach A. If the report only uses ProgressiveDataSet tables, Approach A is strongly preferred.

---

Connection Details Quick Reference

Property	Value
Server	procserv-proddata.database.windows.net
Database	PSI_Analytics
Auth	Azure AD (Microsoft account)
Private Endpoint	10.160.140.4 (VPN required)
M Code	Sql.Database("procserv-proddata.database.windows.net", "PSI_Analytics")
Schema	dbo (all tables)

---

Bonus: Accessing the New Analytics Tables

After migrating, reports gain access to 17 enriched tables not available in the old Fabric dataset:

Table	What It Adds
redbooks	AI-classified quality issues (73 columns — severity, category, root cause)
labor_detail	Timesheets filtered to project jobs
work_orders	Work orders filtered to project jobs
department_summary	Per-(job, dept) aggregates with P2/P98 dates
purchase_orders	PO data matched to projects
ecn_detail	ECNs with type Code field
ncn_detail	Non-conformance notices
machine_dna_features	Quality prediction features (82 columns)
projects	Core project identity (2,569 rows)
workforce_features	Per-project workforce metrics
lessons_learned	OneNote lessons extraction
handoff_extractions	Handoff doc parsing
change_orders	K: drive change order data
plc_complexity	PLC program complexity scores
comprehensive_view	SQL view joining all analytics tables

These can be added to any report as additional tables after migration.

---

Validation Checklist

Use this checklist when validating a migrated report:

• All tables load without errors in Power Query
• Row counts are in the expected range (see Fabric table summary)
• Report pages render without broken visuals
• Filters and slicers work correctly
• DAX measures return reasonable values (spot-check 3-5 measures)
• Scheduled refresh works (if using Import mode)
• Other users can access the report (Azure AD permissions)
• Old gateway connection is disabled (after validation period)

---

Troubleshooting

Issue	Cause	Fix
”Cannot connect to server”	VPN not active or private endpoint not reachable	Connect to VPN, verify with Test-NetConnection procserv-proddata.database.windows.net -Port 1433
”Login failed”	Azure AD account doesn’t have DB access	Contact admin to add your account to PSI_Analytics
Column name mismatch	Shouldn’t happen — column names are identical	Check for trailing spaces or encoding differences; compare with SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tablename'
DAX measure returns different values	Data refresh timing difference (CSV = 5 AM, SQL = 1 AM)	Expected for a few hours; values should converge after both refresh
”Transform data” is grayed out	Report uses a live connection, not an embedded model	See “Reports with a Live Connection” section above
Report performance is slower	DirectQuery adds latency per visual	Switch to Import mode, or add indexes (contact data pipeline team)

---

Related Pages

• PSI Data Brain — Master data reference
• Fabric ProgressiveDataSet — Current dataset documentation and table schemas
• Azure Resource Map — Azure infrastructure details

---

Created: 2026-03-26