A Three-Hour Reload and a Forty-Second Sheet

Order by impact per unit of risk, cheapest and safest first.

1. Drop Comment. 31M distinct values on no sheet is the single most expensive thing in the model - high-cardinality text is the classic memory sink, and 46GB against 38M rows is consistent with a field like this dominating. Removing it is a one-line change with no user-visible effect.
2. Split the archive out. If retained history is, say, 5 years and users consult 13 months, the productive app can hold roughly a fifth of the rows. Memory and most calculations scale with rows, so this is a large, predictable win - but confirm the retention number first, because deleting history is irreversible.
3. Move the 6 archive-only dimensions to the archive app. They cost memory in every reload and every sheet in the productive app, and are used by one screen.
4. Only then instrument the reload. Put timestamp logging between load steps and find where the 3h10m actually goes. My expectation is one join or ApplyMap over the 38M-row table, or an ORDER BY on a resident load, but that expectation is worth nothing until it is measured - do not start rewriting steps on a hunch.

Measure first: memory by field (use the model viewer or document analyzer), rows per table, distinct counts on the top 20 fields, and time per script step. Then change one thing at a time and re-measure; a 3-hour reload has too many confounders to attribute an improvement otherwise.

Aggr() builds a temporary virtual table at chart evaluation time, one row per distinct combination of its dimensions, and the outer aggregation then runs over that. Nested Aggr means one virtual table is built from another. Cost scales with the number of distinct dimension combinations after the current selection, not with the fact row count - which is why it is fine in the model's development environment and falls apart in front of 2,000 users making arbitrary selections.

Replace it with grain. Work out the exact grain the pivot needs - those 4 dimensions plus the measure's own granularity - and pre-aggregate the fact in the script: LOAD ... Resident Fact Group By Dim1, Dim2, Dim3, Dim4. The chart then does a plain Sum(), which is a linear scan of a much smaller table. The cost moves to reload, where it happens once and can be scheduled, instead of to every user interaction.

Also strip If() from inside the measure and use set analysis, which is evaluated as a set operation rather than a row-by-row branch.

Keep Aggr for: genuinely dynamic grain - where the grouping depends on what the user selects, or where pre-aggregating would force a grain so fine it saves nothing. Those are few. If a pivot is on the main sheet and every user hits it, it should not depend on a runtime virtual table.

Adding RAM raises the ceiling; it does not fix either symptom.

Reload time is dominated by disk I/O, sorting and CPU work, not by available memory - the app already fits at 46GB. More RAM will not shorten the 3h10m by any meaningful amount.

The 40-55 second sheet is an expression-engine problem: a nested Aggr() virtual table is computed per selection and its size depends on dimension cardinality. More memory makes it hold more distinct combinations before spilling, but does not make the computation cheap; users would still wait.

What more RAM actually buys is the ability to keep loading data nobody uses - it defers the decision and produces a larger app that reloads more slowly and takes longer to open. Buying a bigger node to avoid dropping an unused 31M-distinct text field is a poor trade.

The honest answer: memory is not currently the constraint, so sizing is the wrong lever. Do the reduction work first - drop Comment, archive the old history, move the archive dimensions - measure again, and then right-size the node against a real number. If after that the app still sits at, say, 40GB, a resize may be justified, but you would be buying headroom for growth rather than a fix. Say that plainly rather than blocking the request outright.