Back to all case studies

When Real-Time Became Possible Again

Snapshot

Delivery model: Principal-led engagement (Stefan, Founder & Principal Consultant) Client: Ironbridge ERP (German ERP provider for machinery trade and rental) Challenge: In-house Business Intelligence system with nightly batch processes couldn't scale—larger customers exceeded the overnight window, resulting in incomplete data and lost trust Duration: 3 months Approach: Replaced complex ETL architecture with optimized SQL Views, proper indexing, and runtime calculations on the source database

The Challenge

A German ERP provider had built a specialized Business Intelligence system for their machinery-sector clients. Every night, a custom-built transformation process would extract data from the ERP database, restructure it, calculate derived metrics, and load it into a separate SQL Server dedicated to BI reporting. The front-end—a functional UI using List & Label for report generation—was well-accepted by customers.

But the system was failing. For larger customers with millions of transaction records, the nightly process couldn't complete before business hours resumed. Management dashboards displayed incomplete, stale, or missing data. Customer trust evaporated. The BI system—previously a differentiator—had become a liability the provider could no longer confidently sell.

The root cause wasn't data complexity. The original developers, lacking SQL expertise and modern database optimization knowledge, had assumed real-time querying was "impossible." They'd built an entire ETL architecture on that premise, and over the years it had become unmaintainable—every extension caused regressions, and no one dared touch it.

Constraints

The client explicitly rejected a full rewrite. The BI front-end worked and customers accepted it. Any solution had to preserve the existing UI while eliminating the scaling bottleneck.

There was also a human constraint: the original development team had deep institutional knowledge but fragile egos. The transformation system was their creation, and acknowledging its failure required diplomatic navigation.

Finally, any solution that directly queried the live ERP database had to prove it wouldn't disrupt daily operations—database locks or slow queries that impacted business users were unacceptable.

Approach

1. Rapid Discovery and Hypothesis Testing (10 Days) Vionix audited the existing transformation logic and examined the SQL queries the BI system executed against the secondary database. Within days, the pattern was clear: missing indexes caused full table scans, and the "complex" transformations were straightforward JOINs and aggregations. The nightly ETL existed solely because the team believed real-time was impossible—not because it actually was.

2. Proof of Concept with SQL Views Vionix built a prototype that replicated 2-3 of the most commonly used BI reports using SQL Views on the source ERP database. The views abstracted the data preparation layer—implementing proper JOINs, adding calculated fields (inventory levels, average sales prices), and leveraging stored procedures where complexity warranted. Vionix used SQL Server's query optimizer and execution plans to identify exactly which indexes were missing, then added them to the live tables.

3. Performance Validation in Parallel Vionix ran both systems side-by-side: the legacy nightly ETL and the new real-time Views. This allowed direct data comparison to ensure accuracy and provided a safety net. Load testing under production-like conditions (tens of millions of records for the largest customers) confirmed negligible impact on ERP INSERT/UPDATE operations. Exclusive locks were minimal; daily business workflows remained unaffected.

4. Developer Buy-In Through Collaboration The original developers initially resisted—their transformation system was being dismantled. Vionix reframed the conversation: rather than assigning blame, Vionix positioned them as essential partners in the migration. Once they saw the prototype's simplicity (updating a SELECT statement in a View versus debugging thousands of lines of brittle transformation code), enthusiasm replaced resistance. The new approach was objectively easier to maintain.

5. Full Migration and Knowledge Transfer Over the remaining weeks, Vionix systematically migrated all BI data structures to the View-based architecture. Vionix ensured feature completeness, then conducted structured training sessions—presentations, tutorials, and walkthroughs with the development team. They took notes, asked questions, and received the materials as references. Code comments served as inline documentation; no separate manuals were requested or required.

6. Rollback Safety and Gradual Confidence Building The financial risk was contained to consulting hours; the old system remained operational throughout. If the real-time approach had failed, the client could have reverted. It didn't. By the time the migration completed, the team's confidence in the new architecture was absolute.

What Was Delivered

• Optimized SQL Views on the source ERP database, replacing the entire secondary BI database and nightly ETL pipeline
• Strategic indexes on ERP tables to eliminate table scans and enable sub-second query performance
• Calculated fields and aggregations implemented via Views and stored procedures, computed at query time rather than pre-materialized
• Parallel validation environment demonstrating data consistency and performance under production load
• Training materials and live sessions equipping the internal team to maintain and extend the system independently
• A scalable architecture that grows naturally with customer data volume, eliminating the nightly time-window ceiling

Results

The BI system returned to active marketing status. Customers could query real-time, coherent data without delays or data gaps. Performance concerns proved unfounded—daily ERP operations remained unaffected.

Service ticket volume dropped to normal levels. Customer satisfaction increased. Internal stress decreased sharply; the development team no longer feared regressions with every change.

Most importantly, the system now scales. Customers with 10+ million records in transaction tables—previously beyond the nightly window's capacity—operate without issue. The architecture no longer imposes an artificial growth ceiling.

By project end, the original developers had become advocates. Maintaining a SELECT statement in a View was objectively simpler than debugging a fragile transformation pipeline. The enthusiasm wasn't diplomatic politeness—it was genuine relief.

Why It Worked

Questioning Legacy Assumptions The entire ETL architecture existed because someone once believed real-time was impossible. No one revisited that assumption as SQL Server capabilities evolved. Vionix audited the premise, not just the implementation.

Leveraging Native Database Capabilities Modern relational databases are designed for exactly this workload: indexed lookups, JOINs, and runtime aggregations. The transformation logic wasn't complex enough to justify an external process—it belonged in the database layer.

Minimizing Architectural Complexity Every additional system (secondary database, transformation pipeline, nightly scheduler) is a maintenance burden and failure point. The View-based approach collapsed three moving parts into one, drastically improving maintainability.

Proof Over Persuasion Vionix didn't argue that real-time would work; Vionix demonstrated it. The prototype with 2-3 reports, backed by execution plans and parallel validation, made the case irrefutable.

Managing the Human System Technical correctness isn't enough when egos are involved. Vionix involved the original developers as collaborators, let them experience the simplicity firsthand, and celebrated their institutional knowledge. The solution succeeded because people accepted it, not just because it was technically sound.

How Vionix Worked

Vionix operated as embedded technical advisors over three months. The discovery phase (10 days) established feasibility; the remaining time focused on systematic migration, validation, and knowledge transfer.

Training was structured but practical: live sessions with the development team, presentation materials for reference, and inline code documentation. Vionix didn't impose heavy process—just enough rigor to ensure the team could sustain the system independently.

The engagement was billed hourly, reflecting the iterative nature of the work. Vionix identified the solution early but took time to validate thoroughly, migrate carefully, and build genuine internal capability. By handover, the client's team owned the architecture—not just the code, but the reasoning behind it.