The first symptom is a refresh that takes longer than it used to. The second is a refresh that fails once a week, succeeds on the retry, and never gets reported. By the time anyone raises it, the scheduled refresh has often disabled itself after five consecutive failures and the dashboard is showing last Tuesday's numbers.
We audited a dataset in that state recently. What follows is the method and what it turned up.
The model
An executive reporting dataset with four thin reports connected live to it. Sources:
- NetSuite via SuiteAnalytics Connect (ODBC), through an on-premises data gateway
- SQL Server, an internal warehouse, through the same gateway
- Nine separate queries against one Excel workbook on a mapped OneDrive path
- Six DirectQuery entities pointing at two other Power BI semantic models in different workspaces
182 tables, 2,616 columns, 278 measures, 188 relationships, 2,015 MB in memory. Refresh took 1 hour 35 minutes on the runs that completed.
Method
The model side comes from a VertiPaq Analyzer export (.vpax), taken through DAX Studio. That gives every table, column, measure, relationship, RLS role and Power Query expression, with storage cost and cardinality for each.
The report side is more work. In Power BI's PBIR format every visual is its own visual.json, so you walk each page, visual, bookmark and filter definition. The file that matters most is reportExtensions.json, which holds report-level measures. Their DAX references model objects and none of it appears anywhere in the visual definitions. Skip that file and you will condemn tables that are in daily use.
With both halves you can build the dependency graph. A table survives if any of the following is true: a report references one of its columns or measures; it hosts a measure a report references; its columns appear in the DAX of any live measure, including inside CALCULATE filters, USERELATIONSHIP, TREATAS, RELATED and nested measure calls; it sits on a filter path between two used tables, tracing bidirectional relationships as well as single; it is the source of a used calculated column or calculated table; it appears in a row-level security filter; it backs a field parameter or calculation group; or it is a disconnected parameter table read by a live measure.
Only tables failing all of those are candidates. Two findings from this model show why the later tests earn their place.
One table had no direct reference anywhere. No visual, no measure, no calculated column. It was the bridge that let a department slicer on the finance report reach the transaction detail, via Dep_lvl2 to Dep_lvl1 to Department_Direct to transactionLine. Deleting it raises no error at all. The slicer simply stops filtering and the numbers on that page quietly become wrong.
A second table looked unused because its only consumer was a security rule written as [Full Name] = "...". Unqualified column references in RLS resolve to the permission's own table, so a scan looking for Sales_Reps[Full Name] finds nothing. That table gates every one of the twelve security roles.
What was in there
18 tables were referenced by nothing at all. The largest was 490,000 rows and turned out to be a DAX Studio query result someone had pasted in as a calculated table, filtered to eight months of 2022, refreshed faithfully several times a day ever since.
209 of the 278 measures were not used by any report.
The two transaction tables held 8.4 million rows each and carried 75 columns between them. 48 were unused. Five of those were ETL audit timestamps, last-modified dates that nothing reads, worth 116 MB on their own. Two surrogate keys with no relationship attached accounted for another 72 MB.
One dimension table was loading 59 columns to use 2, because its query was SELECT * FROM Task.
106 hidden date tables existed because Auto Date/Time had never been turned off. One of them was genuinely in use, on a hierarchy in the finance report, which is worth knowing before you flip that setting.
None of this was anyone's error. It is the shape a reporting stack takes after five years of small, reasonable requests, none of which anyone had a reason to reverse.
The changes
Three hours of work: delete the 18 tables and the calculated objects holding the unused columns in place, rewrite the two transaction queries, and cut the refresh schedule from six runs a day to four.
The Power Query change is worth spelling out. The original query used Table.RemoveColumns with a list of 110 columns to drop. That is a blacklist, so any column added at source later arrives in the model silently and permanently. Replacing it with Table.SelectColumns and an explicit list of the 14 columns actually needed inverts the default. New source columns are ignored until someone decides otherwise.
Check the folding before you trust it. Right-click the step, View Native Query. If it produces a narrowed SELECT the projection is reaching the source. If it is greyed out, the filtering is happening locally, and you have saved model size without saving any query time.
Results
| Before | After | |
| Model size | 2,015 MB | 1,584 MB (−21%) |
| Refresh duration | 1h 35m | ~66 min avg, best 54 min (−30%) |
| Tables | 182 | 131 |
| Columns | 2,616 | 2,044 |
Row counts unchanged, all four reports validated, nothing lost.
What the audit actually surfaced
The working theory going in was that the dataset had outgrown what Power BI could ingest. That theory was wrong.
Once the refresh was fast enough to observe properly, the underlying error became visible: ERROR [08S01] [NetSuite][ODBC 64bit driver] Connection closed due to session kill. NetSuite was terminating the session from its end. The failures had never been timeouts. They were the source hanging up, most likely on a SuiteAnalytics Connect concurrency limit, because Power BI opens one ODBC session per table it refreshes in parallel and this model was reaching for twenty of them, several times a day, alongside three other datasets and a separate warehouse integration authenticating as the same NetSuite user.
The pruning still helped. Shorter queries hold connections open for less time, which narrows the window in which a collision can happen, and the failure rate dropped accordingly. But it was treating a symptom.
That is the case for auditing, more than the megabytes are. A cluttered model hides its own root cause, because every failure looks like a size problem when size is the obvious variable. Strip it back and the real constraint becomes measurable. In this instance it was a source-side connection limit that no amount of Power Query tuning was ever going to resolve.
Cadence
Twice a year suits an actively developed stack. More often if reports are being added monthly.
After the first dataset it is a few hours each, needs no infrastructure change, and is fully reversible if you keep a copy of the file. The output is a faster model and a documented list of what is genuinely in use, which is worth having on its own the next time someone asks whether a table can be retired.
Vision BI builds and supports Power BI reporting for mid-market businesses running NetSuite and SQL Server. If your refreshes have started failing intermittently, that is usually the first symptom rather than the problem.
