The Problem
The SSAS OLAP cubes I inherited had been built up over years by developers who were long gone, with no documentation of which dimensions were actually load-bearing and which had been left in from a report nobody ran anymore. Processing time was excessive, and because the cube's source queries hit BigQuery, every processing run also generated a BigQuery scan cost proportional to a schema nobody had audited. There was no usage log to just query for an answer — the only way to know what was actually in the cube was to read the cube's own definition and compare it against what production was really touching.
What Is an SSAS Cube's XML Definition
An SSAS cube's structure — every dimension, attribute, and measure group — is stored as an XML/XMLA definition inside the Analysis Services database. That definition doesn't say anything about usage, only about existence: it lists everything the cube was ever built to expose, whether a report queries it or not. Nothing in the platform tracks which of those objects a report or query actually touches, so the XML is simultaneously the most complete and the most misleading source of truth available — complete because it's authoritative about what exists, misleading because "exists" and "needed" had drifted apart over years of report requests nobody ever revisited.
The Alternatives
One option was to ask the people who'd built or maintained the cube over the years which dimensions still mattered. That relies on institutional memory that had already partly left the organisation, and even the developers still around couldn't be confident about objects added for a report that might have been retired without anyone updating the cube. A second option was to leave the cube alone and accept the processing time and BigQuery scan cost as fixed overhead — the safest option in the short term, but one that let the same drift keep compounding indefinitely. A third option was to prune conservatively based on gut feel about which dimensions "looked" unused, which risked breaking a report that queried something infrequently but legitimately. None of these produced a defensible, verifiable answer to what was actually in use.
The alternative that did was to treat the cube's own XML definition as the ground truth for what existed, and cross-reference it against real production usage to find out what was actually needed — turning the question from a matter of memory or guesswork into something that could be measured and shown.
The Decision
I wrote a PowerShell script to parse the cube's XML directly, walking the dimension and attribute nodes and producing a flat inventory of every object the cube defined — the first time that inventory had existed anywhere outside the XML itself. That inventory was necessary but not sufficient: knowing what the cube defined doesn't tell you what the cube needed. For that, the PowerShell-extracted metadata fed into a Python preprocessing step that cross-referenced each dimension and attribute against real production usage — which objects were actually being pulled into reports and queries running against the cube, versus which ones sat untouched. I brought that cross-reference into Power BI to visualize the gap directly: a clear, browsable view of used-versus-unused dimensions instead of a spreadsheet nobody would trust or maintain. That view was what turned "I think some of these aren't used" into a defensible removal list.
Prune before you optimise anything else
Removing the confirmed-unused dimensions came first, before touching partitioning or query patterns. Every dimension the cube processes adds to its build time regardless of whether any report ever reads it — optimising query patterns against dimensions that shouldn't have existed in the first place would have meant tuning dead weight instead of removing it.
Then fix what's left: partitioning and query patterns
With the cube trimmed to what was actually in use, the remaining processing and query cost came from how the source queries hit BigQuery. I applied hash partitioning and BigQuery query best practices to those source queries — shaping them so BigQuery scanned less data per run instead of paying full-table-scan cost every time the cube processed.
Automate the documentation, or the same drift happens again
The original problem existed because nobody had documentation showing what was actually in use — dimensions accumulate silently over years of report requests, and nothing forces anyone to revisit them. Leaving the pruning as a one-off exercise would only have reset the clock on the same drift, so I automated cube documentation generation with Python and PowerShell, driven by the same XML-parsing approach used for the audit, so the object inventory regenerates itself instead of depending on someone remembering to write it down. That regeneration was also the honest answer to the trade-off in choosing automated inventory over manual review in the first place: a script can't judge business relevance the way a person can, but it can guarantee the inventory never goes stale again, which manual review never managed to do. Ongoing cube maintenance and the development of new cubes for new business needs could then start from that live documentation instead of institutional memory.
The cube's XML definition already contained the answer to "what's actually in here" — it just wasn't something anyone had ever parsed and compared against reality. Automated documentation ended up being as much the fix as the pruning itself.
The Outcome
Cube processing time dropped from removing dimensions that were doing work nobody used, and BigQuery data processing costs dropped from partitioning and query improvements applied to a schema that had already shed its dead weight. The automated documentation removed the manual governance overhead that had let the original problem accumulate unnoticed for years in the first place, replacing a one-time audit with a self-updating inventory that later cube maintenance and new-cube development could build on directly.