Our Sybase IQ-powered data warehouse has solved the problems we had in the past, attempting to run queries against and generate reports from our operational database. Sybase IQ has proven to be the best database for our data warehousing and analytics needs. Oracle and Microsoft SQL Server are good operational databases but when you need a data warehouse to serve your reporting, BI and analytics needs, Sybase IQ is the way to go. It’s fast, flexible, and cost-effective – it’s just the best. I can’t think of how to say it more simply than that.
Senior Manager-BI, Medihelp
Medihelp, South Africa’s third largest health insurance company, needed a better way to access and analyze data on customers, claims and third party providers in order to monitor the effectiveness of its insurance products and to fine-tune and create new products as needed. It found its solution in Sybase IQ, which it implemented to serve as its high-performance, cost-effective, dedicated data warehouse.
- Various groups within Medihelp, from senior management to product development and client service, are now able to rapidly access and act on comprehensive data sets to make better business decisions.
- Enables better informed business decisions
- Slashes average query response times by as much as 74%
- Reduces storage hardware needs and costs
More Informed Decisions Lead to Better Healthcare Coverage
In a world overwhelmed with problems, crises and challenges of all sorts, one of the most pressing is the delivery of quality healthcare to individuals and families. The healthcare crisis is a front-burner issue in the United States. However, it is no less pressing in other parts of the world.
At the heart of these crises is the matter of how to manage the cost of healthcare. In most developed countries, the solution to this challenge and the gateway to quality and timely healthcare is having some sort of healthcare insurance coverage – either private or public – that reimburses providers including physicians, hospitals, laboratories, pharmacies and so on for services rendered. Creating health insurance products that are suited to the needs of various individuals and groups of individuals, requires insurers to have real- or near-real-time access to subscriber, provider and other healthcare data and the ability to analyze those very large volumes of data to make business decisions that are appropriate and fair for all parties.
Consider the case of Medihelp, South Africa’s third largest health insurance company. Medihelp insures approximately 350,000 individuals through a variety of product offerings. To provide the most effective and cost-effective products while complying with various regulations and information sharing obligations, Medihelp needs to maintain mountains of data including customer information, current and historical claims, network provider information and more. This data must be quickly and easily accessible and, of course, accurate. It’s a formidable challenge, as the volume of data grows daily. Currently, just the member and claims information amounts to about 300 gigabytes. That translates into about 15 million rows of member data and about 55 million rows of claims.
Until recently, Medihelp data was maintained on a traditional relational database (MS SQL Server). However, the company experienced technical difficulties with reports and analytics executed from MS SQL Server taking a considerable amount of time to run. Medihelp was only able to generate “snapshot reports” based on limited data sets rather than standard and ad hoc reports and analytics based on complete data sets. This time consuming and snapshot limited process had an adverse effect on the company’s ability to make informed business decisions.
Building a Dedicated, High-Performance Data Warehouse
“Logging into our MS SQL Server database, which was really an operational database with limited reporting capability, was not providing us the information we needed to make the best business decisions for Medihelp and our various constituencies,” explains Jan Steyl, Senior Manager of BI at Medihelp. “We needed a dedicated, high-performance data warehouse.
To obtain the data warehousing and data analytics capabilities it needed, Medihelp decided to evaluate a move to Sybase IQ, the highly optimized data warehousing and analytics server designed specifically for business intelligence and analytics.
“We chose to conduct our POC on Sybase IQ,” says Steyl, “because it seemed that a column-based, analytics server was the way to go and from everything we were able to learn, Sybase IQ was and is the leader in column-based database technology. Both from a performance perspective and from an operational cost perspective, Sybase IQ seemed the logical choice for us.”
The first step in this process was to design and execute a proof of concept (POC) to determine if a Sybase IQ-powered data warehouse could deliver the performance improvements and cost-effectiveness the company required. The scope of the POC included:
- Installing Sybase IQ 15.1
- Generating the MS SQL Server database schema and reengineering it for Sybase IQ
- Exporting data from predetermined tables in MS SQL Server to flat files
- Loading the data from flat files into Sybase IQ
- Testing the given queries against the Sybase IQ database to ensure compatibility
- Adding relevant indexes to Sybase IQ
- Executing and evaluating the results
Clear and Impressive Results
The POC results were clear and impressive. Overall, query response time on Sybase IQ versus on MS SQL Server was reduced by an average of 71.5%. Some queries ran even faster, with response time slashed by as much as 92.8%. In the case of ad hoc queries (versus routine/pre-determined queries) the average reduction in query response time was 74.1%. Sybase IQ’s ability to dramatically compress large volumes of data was also confirmed, meaning that the data storage capacity of the data warehouse could scale without heavy investments in storage hardware.
Once the PO was complete, Medihelp moved onto the implementation of a full-scale data warehouse encompassing all Medihelp transactional and historical data. This is currently in progress and scheduled for full implementation within a few months. Membership and third party (employer group and broker) data has been loaded. Claims data is in the process of being loaded into the warehouse as this is being written. Once that is completed, the project will be about 90% complete.
In the meantime, the data warehouse is being used by the product development team, which is finding it quite beneficial as it examines membership and claims data to make strategic and operational decisions regarding the creation of new products, fine-tuning of existing products, and to retain members.
In the past, Medihelp’s Health Economics Division spent a great deal of time gathering statistical data for use in profile reports. The data were obtained from different sources. The information obtained from these sources was comprehensive and needed to be summarized for the purpose of compiling the group profile reports. For this reason, additional calculations had to be performed by a data analyst in order to provide information in the correct format for the final report. A new process was implemented in 2010 that required business intelligence personnel to furnish the necessary information to the Health Economics Division in its final format.
This revised process, which is enabled by the new data warehouse, significantly simplifies the production of the group profile reports, as the data analyst now receives the final results in the required format, thereby eliminating the need for additional calculations to be done.
“One of the advantages of having more accurate information available on the Medihelp’s customers is the ability to produce what we call the “Employer Group Report,” says Theo Els, Medihelp’s Senior Manager of Client Relations. “It is a standard practice in our industry that administrators and health insurers supply data to employer groups. These demographic and claims profiles are essential for employer groups seeking to understand the their employees’ health risks as the basis for making future business decisions that have to do with managing employees’ total health risk and the consequent impact that risk can have on business productivity. This information also provides clients [employers] with a better perspective regarding the underwriting decisions we have to make in order to sustain the risk of insuring a specific employer group. Brokers and healthcare consultants also use this information in their annual client reviews to ensure that employees receive the most suitable coverage and that their risk is sufficiently. Lastly, for those of us in Client Relations, serving as the intermediaries between Medihelp and the employer, it is imperative that the business intelligence department, as the custodians of the Data Warehouse, extract and provide accurate information in a format that is easily understood.”
The data extracted from the Data Warehouse is used on a daily basis to make a variety of operational and strategic decisions in various different departments within Medihelp.
It is also used to provide critical information to organizations including The Council of Medical Schemes, The Board Of Healthcare Funders (with their annual health quality assessment project) and pharmaceutical research companies, seeking information on the use and effectiveness of their products.
The biggest beneficiary of the new data warehouse within Medihelp, however, is the product development team (This team includes the Research, Finance and Sales Teams and the Statistician.) Data from the Data Warehouse is used year round, throughout the entire product development life cycle. It is used to identify and understand trends in claims patterns by benefit code, chronic condition, area, age group and other factors. It is also used to determine what financial effects changes to a benefit in a specific product will have. This helps the Finance team price the product for the year, which in turn provides the sales force with the right offering at the right price for specific target markets in South Africa.
Information extracted from the data warehouse also allows the company to spot indicators of customer dissatisfaction, enabling the company to act proactively to retain customers.
“Sybase IQ Is the Way to Go”
“We needed a solution that would enable us to serve our existing customers better and attract new customers through new product offerings and better daily business decisions,” says Steyl. “Our Sybase IQ-powered data warehouse has solved the problems we had in the past, attempting to run queries against and generate reports from our operational database. Sybase IQ has proven to be the best database for our data warehousing and analytics needs. Oracle and Microsoft SQL Server are good operational databases but when you need a data warehouse to serve your reporting, BI and analytics needs, Sybase IQ is the way to go. It’s fast, flexible, and cost-effective – it’s just the best. I can’t think of how to say it more simply than that.”