A major South African medical scheme now benefits from accurate member HIV status reporting thanks to a sophisticated solution designed by leading data and digital consultancy, Keyrus.
Previously, the collection and analysis of this data were beset with challenges. These ranged from finding unique employee identifiers to keeping accurate historical data for individual members aligned with the POPIA act. The data reporting solution solves these issues to reveal precise, up-to-date information, which is secure, about members and their dependents.
The challenge
Platinum Health is a closed medical scheme for employees and dependents of mining companies. It is vital that the scheme has full insight into its members’ HIV status. In the past, Platinum Health produced an Excel-based HIV Wellness report, aligned with the government guidelines to improve the health outcomes and wellness for long-term HIV survivors of HIV.
The collection and analysis of HIV status data posed major challenges for Platinum Health. The data was derived from disparate data sources that lacked rigorous data integrity cross-checks. Exacerbating the problem, the volume of records had exceeded Excel’s capability and the organisation had to use aggregated datasets for its final reports. In addition, the Wellness report had to be maintained manually.
“The real challenge was matching employees and their dependents to the testing results,” explains Nicky Mahler, Principal BI Consultant at Keyrus. “Multiple combinations for a single employee were possible because not all members could be identified by SA ID numbers.”
Historical data also had to remain associated with individuals, whose membership status could have changed over time. The existing Excel system was unable to cater for this.
The solution
Platinum Health selected Keyrus, for its competence in implementing world-leading BI technology, to develop a solution.
The result? A reporting system that overcomes all the challenges to produce accurate reporting. Keyrus’ certified consultants, with their vast experience with Microsoft Power BI as well as Qlik BI and analytics tools, transformed and modeled data from the various data sources into a well-designed data warehouse.
“We used the core component of SQL Server, the Database Engine,as well as SQL Server Integration Services (SSIS) scripting and views to transform the data from our Platinum Health’s medical systems into the structured database,” explains Nathi Xulu, Head of Data Engineering & Principal Consultant at Keyrus. “QlikView models provide cross-checking, while Power BI delivers the final visualisation.”
The SSIS Fuzzy Lookup feature was employed to overcome the challenge of matching employees and their dependents to the testing results. All possible combinations are mapped so that results are accurately associated with the correct person. A QlikView model validates member and dependant mapping across all the different data sources.
Benefits
“Our organisation now benefits from dramatic time-saving and improved visibility into those all-important HIV Wellness statistics,” says Alta De Beer, Service Delivery Manager: Applications at Platinum Health. “We can be confident that our monthly reports are an accurate reflection of the health of our members and that the data is aggregated for reporting purposes”
Platinum Health can now run its monthly HIV Wellness report within minutes rather than days, freeing up time for valuable analysis. The final report produces wellness statistics month-on-month, which was previously impossible.
Finally, Platinum Health is able to accurately identify data mismatches, resulting in process and integration issues being resolved quickly, and enforce consistent application of the correct logic.
“We are delighted the scheme has joined our growing list of customers who trust us on their data and digital transformation journeys,” says Adam Walker, Managing Director at Keyrus. “We pride ourselves on our hands-on approach to the solutions we deliver as well as our ability to draw on best practice, knowledge, and experience from our colleagues internationally. Above all, we strive to make our customers’ data matter”, Walker concludes.