Recovered 115 million records of lost operational history and automated enterprise reporting across three countries to establish a singular source of truth.
| Industry | Multinational Assurance & Financial Services Provider |
|---|---|
| Region | Southern Africa (Botswana, South Africa, Zambia) |
| Data Volume | 200+ Million Rows |
The Situation
The organization's legacy transactional databases overwrote historical contract states rather than versioning them, actively destroying the lifecycle history of policies. Furthermore, regional operations across Botswana, South Africa, and Zambia were constrained by disparate, siloed data sources.
Why this engagement mattered
The destruction of historical data prevented actuaries and executives from conducting accurate churn, vintage, and retention analyses. Compounding the issue, cross-border reporting required heavy, manual Excel compilation, causing severe bottlenecks and conflicting KPI values across national branches. They needed an architect to mathematically reconstruct the lost historical data and centralize operations into a single, verifiable environment.
My Approach
- Discovery & Logic Mapping: Embedded directly with business representatives to map operational processes and define exact metric logic prior to development, ensuring high user adoption.
- ETL & Storage Architecture: Engineered an orchestration layer of over 30 SSIS packages to extract data via ODBC. Designed a specialized relational snapshot database specifically to rebuild historical contract states by parsing flattened audit trails.
- Semantic Modeling: Constructed highly optimized SSAS tabular models compartmentalized by business function, implementing complex DAX measures to track dynamic currency conversions (Pula FX adjustments).
- Governance & Presentation: Deployed a centralized SSRS Native-Mode web portal, enforcing data sovereignty by implementing rigid Row-Level Security (RLS) across the semantic models.
The Outcome
| Data Governance Metric | Before Intervention | After Rollout |
|---|---|---|
| Historical Data | Overwritten and actively destroyed by legacy transactional databases. | 115 million+ raw audit log records parsed and successfully rebuilt into historical snapshots. |
| Data Centralization | Disparate silos causing conflicting cross-border KPIs. | 20 million+ contract records and 39 million+ transaction rows consolidated into a single SQL Server 2019 truth. |
| Reporting Cadence | Heavy, manual Excel compilation. | Scheduled, daily automated refreshes via SSRS subscriptions. |
Three Values in Action
Curiosity
Instead of accepting the data loss, I untangled the legacy architecture to locate and parse flattened audit trails, discovering a pathway to reconstruct the operational history.
Integrity
Cross-border data sovereignty is non-negotiable. I ensured strict Row-Level Security (RLS) so stakeholders only accessed data legally pertinent to their specific country.
Sustainability
Automated the entire pipeline via SQL Server Agent and over 30 SSIS packages, freeing analysts from manual extraction entirely.
Architectural Decision Record (ADR)
Strategic Rationale: Snapshot Tables vs. Direct Querying
When tasked with recovering the overwritten data, I elected to build a specialized relational snapshot database rather than attempting to compute the historical states dynamically via direct querying.
Because the 115 million record audit logs were flattened, querying them on the fly would crash the analytical cubes. By preprocessing the history via SSIS into physical snapshot tables, the SSAS tabular models could ingest the multi-tier arrears bucket migrations rapidly, delivering high-speed time-intelligence without taxing the source system.
"Sandile took absolute ownership of the architectural delivery for one of our most complex multinational engagements. His ability to untangle legacy data and engineer a highly scalable SSIS and Tabular pipeline not only recovered critical operational history but set a new standard for our technical deployments."
— Principal BI Consultant, UGT Analytics Training & Consulting