How We Saved $110K in 90 Days by Rebuilding BI Before the Architecture Broke. The Dashboard We Didn’t Want to Open. The Dashboard We Didn’t Want to Open. Most dashboards show what’s happening in the business.This one showed what was happening toour BI architecture. our BI architecture It looked like this: A jagged skyline of $800 - $1,400 cost spikes. $800 - $1,400 Wild swings between days. Wild swings A dotted trendline barely drifting downward. dotted It wasn’t random - it was a heartbeat monitor for a system under stress. heartbeat monitor This dashboard became the one we avoided until the day we realized it was the only one that mattered. only one that mattered. ACT 1 - The Hidden Cost of “Everything Real-Time”. For years, our BI ecosystem did what most retail BI ecosystems do: Build big semantic models.
Keep everything real-time “just to be safe”.
Give stores dashboards connected directly to the warehouse.
Let self-service teams create dozens of near-duplicate datasets.
Refresh everything “as often as possible”.
Hope the cloud bill stays reasonable.
And with hundreds of stores and headquarters all hitting the same semantic models, every “live” interaction multiplied our warehouse load instantly. Build big semantic models. big Keep everything real-time “just to be safe”. “just to be safe”. Give stores dashboards connected directly to the warehouse. directly to the warehouse. Let self-service teams create dozens of near-duplicate datasets. self-service Refresh everything “as often as possible”. “as often as possible”. Hope the cloud bill stays reasonable. reasonable. And with hundreds of stores and headquarters all hitting the same semantic models, every “live” interaction multiplied our warehouse load instantly. “live” It worked - until it didn’t. The chart revealed the problem we weren’t measuring. weren’t measuring DirectQuery was silently multiplying our costs. DirectQuery was silently multiplying our costs. Every click → a live queryEvery filter → a live queryEvery store and HQ team → dozens of live queries per minute And our largest models were so big they could never be imported, which meant: could never be imported No caching
No query folding
No cost control
No way to protect the warehouse from user traffic No caching No query folding No cost control No way to protect the warehouse from user traffic The problem wasn’t our Cloud Warehouse. The problem was the architecture we had built on top of it. Cloud Warehouse on top And the chart was its confession. And the chart was its confession. ACT 2 - The First Structural Break. Look at the chart around March 28.The cost line suddenly dips - permanently. March 28 That was the first major intervention. We took our giant 500M+ row models - the ones too large to import and split them into two logical pieces: 500M+ row Purpose-built, optimized Warehouse tables/views, aligned to actual reporting use cases.
Semantic model designed around those pieces, making import mode not only possible but fast. Purpose-built, optimized Warehouse tables/views, aligned to actual reporting use cases. Purpose-built, optimized Warehouse tables/views Semantic model designed around those pieces, making import mode not only possible but fast. Semantic model designed around those pieces The entire system underwent a complete transformation because of this single architectural choice: entire system underwent a complete transformation The dashboards system transitioned to an import-based operation.
The system required less DirectQuery operations after implementing the change.
The system achieved its successful cache operation.
The system provided an efficient system to handle all incoming requests. The dashboards system transitioned to an import-based operation. import-based The system required less DirectQuery operations after implementing the change. less DirectQuery The system achieved its successful cache operation. successful cache operation. The system provided an efficient system to handle all incoming requests. efficient system And the chart reflected it instantly. instantly This wasn’t tuning.This was astructural correction to BI debt that had been accumulating for years. structural correction to BI debt ACT 3 - The Second Structural Break. A 5-Minute Hybrid That Replaced Real-Time. A 5-Minute Hybrid That Replaced Real-Time. The first act killed the ugliest spikes, but the chart made it clear we weren’t done yet. The problem remained visible although it had reduced in size. The need for real-time dashboard data for critical operational decisions locked us into DirectQuery and that’s what drove the remaining cost spikes. locked us into DirectQuery and that’s what drove the remaining cost spikes. The solution we created during that year turned out to be one of our most important accomplishment. one of our most important accomplishment. A 5-minute incremental-refresh import model. A 5-minute incremental-refresh import model. It worked like this: Import a small rolling window.
Refresh it every 5 minutes.
Only refresh the latest partition.
Allow all stores to hit a shared Fabric cache.
Keep the experience “live enough” without the cost of real-time. Import a small rolling window. Refresh it every 5 minutes. Only refresh the latest partition. Allow all stores to hit a shared Fabric cache. Keep the experience “live enough” without the cost of real-time. To the business, it felt identical to real-time. To the warehouse, it reduced load from hundreds of hits per minute to one hit every 5 minutes. one hit every 5 minutes If you look at the chart around May 8, you’ll see the second drop and the point where the chaos stops. May 8 After May 8, the chaotic spikes disappear the architecture finally enters a stable, predictable pattern. From that day forward, cost became: Stable.
Predictable.
Architecture-driven. Stable. Predictable. Architecture-driven. Spikes didn’t come back because the architectural cause of the spikes no longer existed. ACT 4 - The 90-Day Outcome Here’s what 90 days of BI re-architecture delivered: Metric

Before

After



Avg Daily BigQuery Cost

~$545

~$260



Annual Spend

~$199,000

~$95,000



Annual Savings

-

~$110,000



Model Size

Too large to import

Split + optimized



DirectQuery Usage

Heavy

Minimal



Stability

Chaotic

Controlled Metric

Before

After



Avg Daily BigQuery Cost

~$545

~$260



Annual Spend

~$199,000

~$95,000



Annual Savings

-

~$110,000



Model Size

Too large to import

Split + optimized



DirectQuery Usage

Heavy

Minimal



Stability

Chaotic

Controlled Metric

Before

After Metric Metric Before Before After After Avg Daily BigQuery Cost

~$545

~$260 Avg Daily BigQuery Cost Avg Daily BigQuery Cost ~$545 ~$545 ~$260 ~$260 Annual Spend

~$199,000

~$95,000 Annual Spend Annual Spend ~$199,000 ~$199,000 ~$95,000 ~$95,000 Annual Savings

-

~$110,000 Annual Savings Annual Savings - - ~$110,000 ~$110,000 ~$110,000 Model Size

Too large to import

Split + optimized Model Size Model Size Too large to import Too large to import Split + optimized Split + optimized DirectQuery Usage

Heavy

Minimal DirectQuery Usage DirectQuery Usage Heavy Heavy Minimal Minimal Stability

Chaotic

Controlled Stability Stability Chaotic Chaotic Controlled Controlled And most importantly: Data freshness remained fast. Query performance improved. User experience stayed “live.” Query performance improved. User experience stayed “live.” We didn’t sacrifice capability - we designed it intentionally. We didn’t sacrifice capability - we designed it intentionally. ACT 5 - What We Learned From the Architecture Collapse. These 90 days taught us more about BI than any new tool could. 1. If a model is too big to import, it’s a cost problem. 1. If a model is too big to import, it’s a cost problem. Not a data problem.Not a business problem.A structural BI problem. 2. Real-time is a feeling, not a feature. 2. Real-time is a feeling, not a feature. Most retail operations don’t need sub-second freshness - they need predictability. predictability 3. DirectQuery should be the exception, not the default. 3. DirectQuery should be the exception, not the default. It looks easy.It sounds flexible.And it becomes expensive the moment users actually use it. 4. Refresh schedules are architectural decisions. 4. Refresh schedules are architectural decisions. Every 5-minute refresh that doesn’t need to exist becomes a tax on the warehouse. 5. BI debt compounds silently until the cloud bill displays it. 5. BI debt compounds silently until the cloud bill displays it. Closing Thought Architecture is invisible until it becomes expensive.This chart made it visible. Two structural changes on 3/28 and 5/8 were enough to turn three months of chaos into a stable, predictable and affordable BI ecosystem. 3/28 5/8 Not by throttling.Not by cutting features.Not by downgrading experience. Not by throttling.Not by cutting features.Not by downgrading experience. But by designing BI intentionally around how the business actually needs data, not how queries happen by default. But by designing BI intentionally around how the business actually needs data, not how queries happen by default. Every BI team has a chart like this somewhere.Most just haven’t looked at it yet. And maybe that’s the real lesson here: BI is never done.The architecture stabilizes, the cost drops, the charts flattenbut the curiosity doesn’t. but the curiosity doesn’t. I’m constantly exploring new approaches, rethinking old ones, and finding ways to make BI lighter, faster, and more meaningful. Continuous improvement isn’t a requirement in BI - it’s the passion that keeps the whole discipline moving forward. it’s the passion that keeps the whole discipline moving forward.

This story contains new, firsthand information uncovered by the writer.

The is an opinion piece based on the author’s POV and does not necessarily reflect the views of HackerNoon.

We Built Dashboards for the Business. Then the Cloud Bill Built One for Us.

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

When 300 Stores Printed at Once: Debugging a 350% Compute Spike in Power BI Fabric

10 Great Articles On Data Science And Programming!

10 FinTech Trends in 2021 [Part II]

10 Financial Predictions for 2021 to Protect Your Wealth

13 API Metrics That Every Platform Team Should be Tracking

If You’re Trying to Talk to Everybody, You’re Not Reaching Anybody

When 300 Stores Printed at Once: Debugging a 350% Compute Spike in Power BI Fabric

10 Great Articles On Data Science And Programming!

10 FinTech Trends in 2021 [Part II]

10 Financial Predictions for 2021 to Protect Your Wealth

13 API Metrics That Every Platform Team Should be Tracking

If You’re Trying to Talk to Everybody, You’re Not Reaching Anybody

Light-Mode

Classic

Newspaper

Minty

Dark-Mode

Neon Noir

Minty

HN StartUps