The problem
A UK car insurance broker running on SSP Electra wanted a daily view of the previous day''s trading — total premiums written, markups by product, renewal conversion rate, and how well agents were upselling add-ons. The data existed inside Electra''s Informix database, but it wasn''t reachable through the front-end reports and it wasn''t going anywhere useful.
Management needed the numbers to see how each department was performing. Team leaders needed them to coach agents. Nobody had them.
The complication: no documentation for the source schema. Electra''s Informix database is fully populated and consistent, but the table and column names aren''t self-explanatory, relationships aren''t declared, and vendor documentation covers the platform, not the underlying tables. Any ETL had to start with discovery.
What we built
Week 1 — mapping the source. Reverse-engineered an entity diagram of the Informix schema by profiling every table involved in a quote, policy, renewal, and payment. Identified the primary and foreign keys, worked out which columns held the fields we actually needed, and confirmed each hypothesis against known reference data from the front-end.
Week 2 — the pipeline. Every morning at 03:00, the ETL pulls core tables from Informix into a SQL Server 2019 data warehouse. Watermarks on each table''s primary key mean we only pull yesterday''s changes rather than reloading history every night — the daily run finishes in minutes instead of hours, and grows sub-linearly as the source keeps growing.
Reporting layer. On top of the warehouse we built the queries and views that answer the questions management were asking: yesterday''s totals and markups, renewal rates by product and by agent, add-on attach rates. Power BI picked those up and turned them into dashboards for each department — one for the sales floor, one for renewals, one for management with the roll-up view.
The result
Team leaders now walk into a morning stand-up with actual numbers instead of gut feel. When an agent''s add-on attach rate drops, it''s visible the next day rather than the next quarter — and the conversation with that agent can happen in time to matter. Renewal rates get tracked against product and price change, not just measured after the fact.
The warehouse also becomes the foundation for anything that comes next. Planned as the next phase: AI-assisted financial projection, letting senior management ask forward-looking questions against the historical data — projected quarterly revenue given current renewal trends, expected impact of a price change on retention, and so on. That work only becomes possible once the underlying data is in a shape a language model can reason about, which is what this warehouse now provides.