Downloads

Utility field operations (fieldops)

A water and electric utility: assets, work orders with labor and materials, vendors with purchase orders and invoices, customer billing and service interruptions, all keyed.

Last updated

View as Markdown For AI agents All guides as text

On this page

You'll be able to

  • Trace a work order through its labor, materials and invoices.
  • Connect vendors, purchase orders and invoices with keys.
  • Analyze customer billing and service interruptions.
  • Report utility assets and maintenance in Power BI and Excel.

fieldops is the operating data of a small water and electric utility: the equipment it maintains, the work orders crews complete, the vendors and purchase orders behind the parts, the customers it bills each month, and the outages it has to explain. It is built for maintenance, procurement, billing and reliability reports in Excel, Power BI and SQL, and every relationship is a declared key, so a modeling tool finds them on its own.

These are invented records. The people, vendors, customers and dollar amounts are made up. The shape of the work, such as emergency jobs finishing in days and low-priority jobs in weeks, is realistic on purpose.

Connect to it

Tables at a glance

Area Tables What they hold
Organization Regions, Departments, Employees 6 regions, 10 departments (each name appears once), 400 employees with job title and hourly rate
Equipment Assets 1,500 transformers, poles, meters, valves, pumps, hydrants, pipe segments and breakers, with install date, replacement cost and condition
Maintenance WorkOrders, WorkOrderMaterials, Materials 25,000 work orders (type, priority, status, days to complete, labor hours and cost), 50,000 material lines, 300 materials
Purchasing Vendors, PurchaseOrders, VendorInvoices 150 vendors with payment terms, 7,000 purchase orders, 9,000 invoices with days to pay and a disputed flag
Customers CustomerAccounts, MonthlyBills 5,000 accounts in four rate classes and twelve monthly bills each for 2025
Reliability ServiceInterruptions 3,000 outages with duration, customers affected and cause

Views that save the joins: vw_WorkOrderDetail, vw_WorkOrderMaterialCost, vw_BillingDetail, vw_VendorSpend and vw_Outages.

What the data does

Questions to try

-- Which asset types cost the most to maintain?
SELECT AssetType, COUNT(*) AS orders, SUM(LaborCost) AS labor
FROM vw_WorkOrderDetail WHERE Status = 'Completed'
GROUP BY AssetType ORDER BY labor DESC;

-- How long do work orders take, by priority?
SELECT Priority, AVG(DaysToComplete) AS avg_days
FROM vw_WorkOrderDetail WHERE Status = 'Completed'
GROUP BY Priority;

-- Which vendor categories get paid slowest, and how many invoices are disputed?
SELECT Category, AVG(DaysToPay) AS avg_days_to_pay, SUM(CASE WHEN Disputed = 1 THEN 1 ELSE 0 END) AS disputed
FROM vw_VendorSpend WHERE InvoiceID IS NOT NULL
GROUP BY Category ORDER BY avg_days_to_pay DESC;

-- Customer-minutes of outage by cause
SELECT Cause, SUM(CustomerMinutes) AS customer_minutes
FROM vw_Outages GROUP BY Cause ORDER BY customer_minutes DESC;

In Power BI and Excel

Try it on the data

Open the fieldops database page, browse it in the Playground, write your own queries in the SQL editor, see how the tables connect in the schema explorer, or ask an AI assistant.