Fabric ProgressiveDataSet

The ProgressiveDataSet is PSI’s “Golden Set” semantic model in Microsoft Fabric / Power BI. It consolidates 32 AFTEC data exports into a single refreshable dataset used by the majority of Power BI reports.

---

Overview

Property	Value
Workspace	John Tymes (Personal)
Workspace ID	4879a6ff-c0c4-4f1a-87b5-638a21e04f5f
Dataset ID	51d7822c-8d17-4fb5-b28f-50a4c6fdd19e
Capacity	psfabric1 (F2, North Central US)
Capacity ID	147074f2-0635-43c7-9f50-31863a24dcba
Gateway	085039b5-9fba-412f-b325-571c72fa336a
Refresh	Daily at 5:00 AM ET (all 7 days)
Data Source	32 CSV files on \\ad.ptihome.com\DFS\DATA\unixshare\dgwreportfiles\
Configured By	JTymes@progressivesurface.com

---

Fabric Workspace Inventory

PSI has four workspaces in Microsoft Fabric:

Workspace	Type	Capacity	Purpose
John Tymes	Personal	psfabric1 (F2)	Main BI content — reports, golden dataset
My workspace (ADevereaux)	Personal	psfabric1 (F2)	Dev/test — ProcServ24, TestLakeHouse
PS Machine DB	Workspace	psfabric1 (F2)	Staging lakehouse & warehouse
Argo_Analytics	Workspace	psfabric1 (F2)	InspectionData lakehouse, Argo pipelines
PSI Analytics	Workspace	psfabric1 (F2)	Company workspace — cloned reports

Capacities

Capacity	SKU	Region	State
psfabric1	F2	North Central US	Active
Premium Per User	PP3	North Central US	Active

---

Querying via Azure CLI (DAX)

The dataset can be queried programmatically using DAX through the Power BI REST API. This is how Claude Code and other tools can access the data.

Prerequisites

# Must be logged in to Azure CLI
az login
 
# Verify access
az account show

Execute a DAX Query

az rest --method POST \
  --url "https://api.powerbi.com/v1.0/myorg/groups/4879a6ff-c0c4-4f1a-87b5-638a21e04f5f/datasets/51d7822c-8d17-4fb5-b28f-50a4c6fdd19e/executeQueries" \
  --resource "https://analysis.windows.net/powerbi/api" \
  --body '{
    "queries": [{"query": "EVALUATE TOPN(10, customer)"}],
    "serializerSettings": {"includeNulls": true}
  }'

Common Query Patterns

Count rows in a table:

EVALUATE ROW("count", COUNTROWS(customer))

Filter with conditions:

EVALUATE
FILTER(salesorder, salesorder[Order Amount] > 100000)

Aggregate data:

EVALUATE
SUMMARIZECOLUMNS(
    customer[State/Region],
    "Total Invoice LYR", SUM(customer[Invoice LYR]),
    "Count", COUNTROWS(customer)
)

Top N with sorting:

EVALUATE
TOPN(20, partmaster, partmaster[Last Cost], DESC)

Tables with special characters (hyphens, numbers) must be quoted:

EVALUATE TOPN(5, '100heparts')
EVALUATE TOPN(5, 'wc-capacity')
EVALUATE TOPN(5, 'part-service-goals')

Response Format

Results come back as JSON with column names prefixed by tablename[column]:

{
  "results": [{
    "tables": [{
      "rows": [
        {
          "customer[Customer No]": "101168",
          "customer[Customer Name]": "AAR ACS-NY",
          "customer[City]": "GARDEN CITY",
          "customer[State/Region]": "NY"
        }
      ]
    }]
  }]
}

---

Tables

The dataset contains 33 tables sourced from CSV exports on the on-prem file server via the data gateway. All files are at \\ad.ptihome.com\DFS\DATA\unixshare\dgwreportfiles\.

Table Summary

Table	Rows	Source File	Domain
inventoryhistory	4,145,691	inventoryhistory.csv	Inventory
tslabor2	2,773,043	tslabor2.csv	Labor/Timesheets
wiprouteline	927,343	wiprouteline.csv	Manufacturing
openwo	519,285	openwo.csv	Work Orders
partmaster	420,950	partmaster.csv	Parts
aphistory	272,246	aphistory.csv	Accounts Payable
pohist	268,238	pohist.csv	Purchase History
wiplabor	253,395	wiplabor.csv	WIP Labor
jobparts	203,277	jobparts.csv	Job-to-Parts
invoiceline	163,833	invoiceline.csv	Invoicing
purchaseorders	108,768	purchaseorders.csv	Purchasing
wipledger	99,461	wipledger.csv	WIP Ledger
prdprice	64,161	prdprice.csv	Pricing
spquoteline	54,970	spquoteline.csv	Quote Lines
redbook	50,057	redbook.csv	Quality/Redbooks
ecn	28,413	ecn.csv	Engineering Changes
capacity	23,834	capacity.csv	Capacity Planning
spquote	22,573	spquote.csv	Spare Parts Quotes
inventory	17,389	inventory.csv	Current Inventory
ganttwo	7,775	ganttwo.csv	Gantt (Work Orders)
service	5,194	service.csv	Service Tickets
employee	1,921	employee.csv	Employees
salesorderline	1,578	salesorderline.csv	Sales Order Lines
budgetjob	1,058	budgetjob.csv	Job Budgets
ganttjob	891	ganttjob.csv	Gantt (Jobs)
fixedpriceparts	356	fixedpriceparts.csv	Fixed Price Parts
salesorder	351	salesorder.csv	Sales Orders
customer	277	customer.csv	Customers
100heparts	159	100heparts.csv	100-Hour Parts
inflationparts	60	inflationparts.csv	Inflation Tracking
jobshop-repair-orders	38	jobshop-repair-orders.csv	Jobshop Repairs
part-service-goals	36	part-service-goals.csv	Service Goals
wc-capacity	31	wc-capacity.csv	Work Center Capacity

Total: ~9.6 million rows across 33 tables

---

Key Table Schemas

customer

Column	Description
Customer No	Customer number
Customer Name	Company name
City	City
State/Region	State or region code
Zip	Postal code
Country	Country code
Invoice LYR	Invoice total last year
Invoice MTD	Invoice total month-to-date
Invoice YTD	Invoice total year-to-date

openwo (Work Orders)

Column	Description
WO	Work order number
Job	Job/project number
Part	Part number
CC	Cost center
Qty	Quantity
WO Status	Work order status
WO Type	Work order type
Fin Status	Financial status
Make Date	Make date
Start Date	Start date
Due Date	Due date
Release Date	Release date
Close Date	Close date
Labor Hours	Labor hours
WODuration	Duration in days
WODuration (Hours)	Duration in hours
WODaysLate	Days past due

partmaster

Column	Description
Part No	Part number
Description	Part description
MClass	Manufacturing class
CAClass	Cost accounting class
ABC	ABC classification
UM	Unit of measure
Last Cost	Last cost
Last Cost OH	Last cost with overhead
On Hand	Quantity on hand
On Order	Quantity on order
Gross Req	Gross requirements
Mfg LT	Manufacturing lead time
Pur LT	Purchase lead time
Cum LT	Cumulative lead time
Min Qty	Minimum quantity
Max Qty	Maximum quantity
EOQ Qty	Economic order quantity
Saf Qty	Safety stock quantity
Mul Qty	Multiple quantity
MRP	MRP flag
MTS	Make to stock flag
Manf	Manufacturer
Paint	Paint specification
Issue24	Issues in last 24 months
Sales24	Sales in last 24 months

purchaseorders

Column	Description
PO NO	Purchase order number
PO Line	PO line number
Line	Line number
Part No	Part number
Part Description	Part description
Vendor No	Vendor number
Vendor Name	Vendor name
Buyer	Buyer code
PO Date	PO date
Promise Date	Promise date
Schedule Date	Scheduled date
Confirm Date	Confirmation date
Confirm Days	Days to confirm
Lead Time	Lead time
Orig Qty	Original quantity
Open Qty	Open quantity
Unit Cost	Unit cost
Ext Cost	Extended cost
UM	Unit of measure
Job/WO No	Job or work order number
IsOpen	Open status flag
MRP	MRP flag
Exempt	Exempt flag

redbook (Quality Issues)

Column	Description
ID	Redbook ticket number
Ent By	Employee ID who entered
Ent Date	Entry date
Comp Date	Completion date
First Action	Date of first action
Days Open	Days currently open
Days To Close	Days from open to close
Days To FirstAction	Days to first action
Project	Project/job number
Drawing	Drawing number
Drawing Description	Drawing description
Prob Type	Problem type (Standard, etc.)
Audit Type	Audit classification
Priority	Priority level (e.g. “4-None”)
Problem	Problem description
Cause	Activity log
Solution	Solution/resolution text
Runoff	Runoff indicator
No of Depts	Number of departments assigned
A102-A170	Department assignment flags
C102-C170	Department completion flags

wiplabor (WIP Labor Tracking)

Column	Description
Job No	Job number
WO No	Work order number
Line No	Line number
Part No	Part number
Part Description	Part description
Oper	Operation code
Oper Desc	Operation description
CCen	Cost center
Quantity	Quantity
Labor	Labor hours
Setup	Setup hours
Act Labor	Actual labor
Act Setup	Actual setup
Date Comp	Date completed
Total Actual	Total actual hours
Total Routed	Total routed hours
Act - Routed	Actual minus routed variance
102/104/106/108 Actual	Actual hours by department
102/104/106/108 Routed	Routed hours by department
102/104/106/108 Act-Rte	Variance by department

employee

Column	Description
Emp No	Employee number
Emp Name	Full name
First Name	First name
Last Name	Last name
Department	Department code
Initals	Initials (note: misspelling is in source)
Active	Active status
F/P	Full/Part time
H/S	Hourly/Salary
P/C	Production/Clerical

service (Service Tickets)

Column	Description
Ticket	Ticket number
Project	Project/job number
Company	Customer company
Type	Ticket type
Priority	Priority level
Status	Current status
Description	Ticket description
Enter Date	Entry date
Complete Date	Completion date
Days Open	Days open
Days To Close	Days to close
Count	Count

ecn (Engineering Change Notices)

Column	Description
ID	ECN number
Project	Project/job number
Drawing	Drawing number
Description	Change description
Ent By	Entered by (employee ID)
Ent Date	Entry date
Chg Dept	Change department
Code	ECN code
Rcrd Only	Record only flag
D102-D160	Department flags

salesorder

Column	Description
Order No	Sales order number
Customer No	Customer number
Order Date	Order date
Order Amount	Order amount
Order Type	Order type
PO No	Customer PO number
Quote No	Quote number
Ship City	Ship-to city
Ship State	Ship-to state
Ship Country	Ship-to country
Ship Zip	Ship-to zip

inventory

Column	Description
Part No	Part number
Bin No	Bin number
Location	Storage location
Bin Qty	Quantity in bin
Bin Value	Value in bin
Unit Cost	Unit cost
Vendor No	Vendor number
Lot No	Lot number
Lot Date	Lot date
Last Date	Last transaction date

---

Reports Using This Dataset

The ProgressiveDataSet is the backbone of most Power BI reports:

Report	Dataset	Description
Executive	ProgressiveDataSet	Executive overview dashboards
Production	ProgressiveDataSet	Production metrics and tracking
Purchasing	ProgressiveDataSet	Purchasing analytics
Spare Parts	ProgressiveDataSet	Spare parts analysis
ProcessServices	ProgressiveDataSet	Process services metrics
Progressive IQ	ProgressiveDataSet	Business intelligence overview
ProgressiveDataSet	ProgressiveDataSet	General data exploration
Capacity2	ProgressiveDataSet	Capacity planning
Capacity	Capacity (standalone)	Detailed capacity scheduling
Accounting	Accounting (standalone)	Financial reporting
TSLabor	TSLabor (standalone)	Timesheet labor analysis
ServiceTickets	ServiceTickets (standalone)	Service ticket tracking
ProjectDashBoard	ProjectDashBoard (standalone)	Project budget vs actual, Gantt

All reports have been cloned to the PSI Analytics company workspace (5acabe18-9e94-4144-816f-32bbdde02f07) with cross-workspace dataset binding back to the originals.

---

Data Pipeline

Current: CSV via On-Premises Gateway

AFTEC (UniData/ERP)
        │
        ▼
Scheduled Export Jobs
        │
        ▼
\\ad.ptihome.com\DFS\DATA\unixshare\dgwreportfiles\*.csv   (32 CSV files)
        │
        ▼
On-Premises Data Gateway (085039b5-...)
        │
        ▼
Power BI Service — ProgressiveDataSet (Import Mode)
        │  Scheduled Refresh: Daily 5:00 AM ET
        ▼
Power BI Reports / DAX API Queries

Migration Target: Azure SQL (PSI_Analytics)

Status: Complete (Phase 6). All 33 raw AFTEC tables are loaded nightly into the PSI_Analytics Azure SQL database by build_analytics_db.py. Column names match the CSV headers exactly, making this a drop-in replacement for the gateway/CSV data source. Reports can now be migrated from the on-premises gateway to Azure SQL at any time.

AFTEC (UniData/ERP)
        │
        ▼
Scheduled Export Jobs (CSV files on DFS)
        │
        ├─── (legacy) ──→ On-Premises Gateway → Power BI Import
        │
        └─── (new) ────→ build_analytics_db.py Phase 6
                                │
                                ▼
                        Azure SQL PSI_Analytics
                        (procserv-proddata.database.windows.net)
                                │
                                ▼
                        Power BI DirectQuery or Import
                        (no gateway required)

To migrate a report: See the full step-by-step guide: Re-Pointing Power BI Reports to Azure SQL. In short: open Power Query Editor, change each table’s source from Csv.Document(File.Contents(...)) to Sql.Database("procserv-proddata.database.windows.net", "PSI_Analytics") and select the matching table. Column names are identical, so DAX measures and visuals continue working.

Benefits: Eliminates on-premises gateway dependency. Enables DirectQuery mode (real-time data). Also gives Power BI access to enriched analytics tables (AI-classified redbooks, workforce features, machine DNA) alongside the raw data.

Data Freshness

• CSV files on \\ad.ptihome.com\DFS\DATA\ are updated by AFTEC export jobs
• Azure SQL raw tables refresh nightly at ~1 AM ET (same CSVs, loaded by pipeline)
• The Power BI dataset refreshes daily at 5:00 AM ET
• Data is typically 1 day old at most
• Refresh takes approximately 5-9 minutes (gateway) or seconds (DirectQuery from SQL)

---

Standalone Datasets

Besides the golden ProgressiveDataSet, John’s workspace has standalone datasets for specialized reports:

Dataset	Gateway	Description
Capacity	Yes	Hours-to-go, planned start/end dates, labor/machine load
Accounting	Yes	Financial data
TSLabor	Yes	Timesheet labor records
ServiceTickets	Yes	Service ticket tracking
ProjectDashBoard	Yes	Project budget vs actual, Gantt charts

---

Access & Permissions

Current Setup

• Admin: ADevereaux@progressivesurface.com (Fabric capacity admin)
• Content Owner: JTymes@progressivesurface.com (dataset configured by)
• API Access: Any user logged in via az login with workspace access can execute DAX queries

Querying from Claude Code

Claude Code can query the dataset using the Azure CLI when the user is logged in:

# Verify login
az account show
 
# Run a DAX query
az rest --method POST \
  --url "https://api.powerbi.com/v1.0/myorg/groups/4879a6ff-c0c4-4f1a-87b5-638a21e04f5f/datasets/51d7822c-8d17-4fb5-b28f-50a4c6fdd19e/executeQueries" \
  --resource "https://analysis.windows.net/powerbi/api" \
  --body '{"queries": [{"query": "YOUR_DAX_QUERY_HERE"}], "serializerSettings": {"includeNulls": true}}'

---

Related Pages

• Data Brain — Master data reference
• Data Dictionary — Detailed field definitions
• Re-Point Power BI to Azure SQL — Migration guide
• Redbook Analysis — Analytics pipeline using similar source data

---

Last updated: March 2026