One clear picture of 120,000 devices across 70 locations
Large multi-site organization | SQL Server + Power BI, star schema
Industry and identifying details have been changed to protect the client. The scale and the lessons are real.
The Problem
A large organization had roughly 120,000 computers reporting their status from 70 locations into a single SQL Server. The data existed, but it landed as one enormous flat file, and nobody could actually review it. Leadership had no way to answer simple questions: which locations are struggling, what is the top IT issue at each site, and where should the next round of updates and security spending go.
The Solution
I rebuilt the flat file as a proper star schema, splitting it into lookup tables and data tables. That one decision is what made the report fast. The speed came from how the model was designed, not from bigger hardware.
The interesting problems were in the details. Software versions were stored as text, like 10.2.14, and text sorting gets those wrong because it reads left to right and decides 9.10 is newer than 10.2. So I split each version into its numbered parts and compared them one by one, which ranks 10.2 correctly ahead of 9.10. Each PC also reported many times, so I built logic that keeps only the most recent record per machine. And the report was built for drill through, so a leader could start at the company wide view and click down to a single location, and eventually to a single machine.
Try the drill through
This is a small working version of the same idea, built on invented data. Start at the company view, click a location, then click a device. Flip the version ranking button to see why sorting version numbers as text gets the answer wrong.
| Location | Region | Devices | Healthy | Top issue |
|---|---|---|---|---|
| North | 1,400 | 78% | Agent out of date | |
| South | 1,610 | 61% | Antivirus definitions stale | |
| Midwest | 1,640 | 73% | Low disk space | |
| Mountain | 2,620 | 88% | Unsupported OS build | |
| Coastal | 2,960 | 97% | Antivirus definitions stale | |
| North | 800 | 83% | Encryption off | |
| South | 3,080 | 76% | Agent out of date | |
| Midwest | 1,190 | 64% | Antivirus definitions stale | |
| Mountain | 1,010 | 64% | Pending reboot | |
| Coastal | 3,060 | 60% | Pending reboot | |
| North | 2,140 | 71% | Antivirus definitions stale | |
| South | 2,500 | 76% | Antivirus definitions stale | |
| Midwest | 2,260 | 69% | Low disk space | |
| Mountain | 770 | 63% | Low disk space | |
| Coastal | 1,200 | 74% | Low disk space | |
| North | 2,200 | 89% | Low disk space | |
| South | 970 | 88% | Pending reboot | |
| Midwest | 2,520 | 88% | Low disk space | |
| Mountain | 1,330 | 59% | Pending reboot | |
| Coastal | 1,840 | 84% | Pending reboot | |
| North | 970 | 73% | Low disk space | |
| South | 1,390 | 58% | Pending reboot | |
| Midwest | 2,540 | 80% | Agent out of date | |
| Mountain | 1,040 | 84% | Agent out of date | |
| Coastal | 2,390 | 80% | Encryption off | |
| North | 1,130 | 81% | Antivirus definitions stale | |
| South | 700 | 66% | Pending reboot | |
| Midwest | 1,270 | 69% | Agent out of date | |
| Mountain | 750 | 75% | Agent out of date | |
| Coastal | 2,300 | 86% | Pending reboot | |
| North | 2,940 | 75% | Pending reboot | |
| South | 830 | 73% | Encryption off | |
| Midwest | 1,330 | 89% | Unsupported OS build | |
| Mountain | 2,070 | 76% | Agent out of date | |
| Coastal | 1,750 | 89% | Agent out of date | |
| North | 2,380 | 78% | Low disk space | |
| South | 1,620 | 64% | Agent out of date | |
| Midwest | 1,280 | 97% | Low disk space | |
| Mountain | 2,650 | 64% | Pending reboot | |
| Coastal | 2,500 | 77% | Antivirus definitions stale | |
| North | 2,350 | 93% | Pending reboot | |
| South | 1,730 | 69% | Antivirus definitions stale | |
| Midwest | 1,990 | 60% | Agent out of date | |
| Mountain | 2,000 | 80% | Antivirus definitions stale | |
| Coastal | 1,470 | 93% | Pending reboot | |
| North | 2,720 | 80% | Agent out of date | |
| South | 980 | 95% | Agent out of date | |
| Midwest | 1,210 | 93% | Agent out of date | |
| Mountain | 2,010 | 92% | Low disk space | |
| Coastal | 660 | 59% | Antivirus definitions stale | |
| North | 2,120 | 94% | Encryption off | |
| South | 1,550 | 82% | Unsupported OS build | |
| Midwest | 750 | 92% | Low disk space | |
| Mountain | 1,970 | 87% | Agent out of date | |
| Coastal | 1,430 | 94% | Low disk space | |
| North | 1,120 | 96% | Pending reboot | |
| South | 2,500 | 69% | Unsupported OS build | |
| Midwest | 1,190 | 59% | Pending reboot | |
| Mountain | 800 | 96% | Pending reboot | |
| Coastal | 1,340 | 67% | Low disk space | |
| North | 1,380 | 66% | Low disk space | |
| South | 2,580 | 81% | Agent out of date | |
| Midwest | 2,600 | 61% | Low disk space | |
| Mountain | 1,710 | 81% | Pending reboot | |
| Coastal | 810 | 68% | Encryption off | |
| North | 790 | 83% | Antivirus definitions stale | |
| South | 2,270 | 63% | Agent out of date | |
| Midwest | 880 | 83% | Low disk space | |
| Mountain | 2,890 | 92% | Pending reboot | |
| Coastal | 1,310 | 93% | Unsupported OS build |
Illustrative data only. Roughly 120,040 devices across 70 locations, invented for this demo.
The Results
The finished report gave a quick overview of every location's PCs and the top ranking IT issue at each one. The leadership team could set an update and security strategy for a single location based on that location's own data, or pull back and look at the whole company at once. They could also select several locations together, which made it easy to spot systemic issues across a city or a state instead of treating every site as its own mystery. What used to start with a guessing meeting now starts with a filter.