FinOps Toolkit is unusable :: Too Slow

[francesco1119]

As much as I like this project I don't think I can keep going using it.

I'm running the ADX on a Standard_E4ads_v5 and the dashboards are simply unusable...
At first I was thinking it was because of the 6 years of data import.
But then, even if I filter the Power BI pages to 1 year or the thole dataset to 1 year... it was still slow.

Then when I added external data, not related to the FinOps Toolkit, the whole thing got even slower and it now takes several minutes just to open a tab on the report.
Then more minutes if you click on the pie charts, etc...

I wander if Azure Table Storage could be a valid candidate for an ADX alternative.

Do you think Azure Data Lake Storage Gen2 could be an alternative?

[francesco1119]

I think I found the problems: Union: The Costs() function alone projects 130+ columns and unions two underlying tables (Costs_final_v1_2 + Costs_final_v1_0) from the Ingestion database. Do we need this Union?

[francesco1119]

Is there a way we can use Direct Lake for the FinOps Toolkit?
Do we need to put the data into a Datalake or can we just use ADX?

[francesco1119]

I have another idea:

• Export to FinOps Hub (Storage Account)
• Export to Table Storage (because they are faster)
• Query Table Storage through Power BI
• Ideally create a connector for querying Table Storage through Direct Query or CData

[francesco1119]

OK, I have another idea: can I create a materialized view inside the FinOps Hub?

.create-or-replace function My-Company-Name_CostsByResourceAndDate() {
    Costs_final_v1_2
    | summarize TotalCostUsd = sum(x_EffectiveCostInUsd)
      by ResourceId, ChargePeriodStart
}

@RolandKrummenacher , if I upgrade the FinOps Hub will my materialized view disappear?

[RolandKrummenacher]

Hi @francesco1119 — short answer: a materialized view on the *_final tables won't work reliably with how the hub ingests data, so I'd steer away from it. Here's the why, and a better direction for the performance problem.

Why materialized views break here

Cost Management exports give us the full month-to-date on every run, so the same month is re-exported (with growing data) over and over. To avoid double-counting, the hub's ETL doesn't dedup — it replaces extents: on each run it ingests the fresh file and then drops the previous run's extents for that month from the final table (the "Post-Ingest Cleanup" step).

Materialized views are incompatible with that pattern. Per the docs:

A materialized view only processes new records ingested into the source table. Records that are removed from the source table … by … drop extents … have no impact on the materialized view.

So the view never subtracts the dropped extents — it only adds the freshly ingested ones. For a summarize sum(...) view like yours, the open month gets over-counted a little more on every daily ingestion:

Day	Hub action	Final table (correct)	Your view
1	ingest June MTD = 100	100	100
2	drop old (100), ingest June MTD = 150	150	250 ❌
3	drop (150), ingest June MTD = 220	220	470 ❌

And it fails silently — there's no error, since plain .drop extents on a source table is allowed (only .move/.replace extents would throw). A take_any/arg_max dedup view doesn't save you either, because the FOCUS rows have no stable unique key across re-exports, and charges that disappear in a later export would linger forever.

On "will my view disappear on upgrade?"

A custom view you create won't be dropped by an upgrade — the setup scripts use .create-merge and only touch their own named entities. But if an upgrade changes the schema of Costs_final_v1_2, the view gets auto-disabled (SourceTableSchemaChange) and you'd have to fix + .enable materialized-view it manually.

Better direction for the slowness

You already found the real cost driver in the other comment: Costs() unions two tables and projects 130+ columns. That's where to optimize. If you want a cached aggregate, use a scheduled .set-or-replace snapshot (a function that fully recomputes the aggregation on a timer) instead of a materialized view — that recomputes from scratch each time, so it's immune to the drop/replace pattern.