Qualex Consulting Services, Inc. (QLX) currently provides BI solutions for one of the largest healthcare insurers in the US. QLX provides consulting and implementation expertise to develop ETL processes and BI reporting using OLAP and web technology to provide access to large amounts of data stored in the customer’s “Enterprise Data Warehouse.”
Our task was to work with the actuarial team to provide tools for analyzing up to 7 years of claim and enrollment data in a way that would allow for quick and easy analysis as well as a method for providing standard monthly reports. One of the challenges with claims and enrollment data analysis besides the massive amounts of data to be analyzed, is the non-additive nature of the data. Providing summarization of large data in a “cube” environment can become complicated when drilling into claim and enrollment data. Analysis fields like Utilization and Per Member/Per Month can present significant challenges when attempting to incorporate them into a multi-dimensional structure like OLAP Cube. For example, a common actuarial metric is the calculation of Per Member/Per Month cost, or PM/PM. The numerator in this ratio is total claims cost in dollars. The denominator is total members enrolled, including members who have no claim costs and are not represented in the numerator. This metric is a challenge in standard OLAP reporting tools because the numerator and denominator represent different populations at different levels of summation. If a user wants to look at the data across various levels of claims dimensions, such as CLAIM TYPE, the numerator will change depending on whether the user is looking at Inpatient, Outpatient, Professional or Drug claims. However, the denominator, the total population of members remains constant across these levels.
There are several tools by the client to develop and surface OLAP cubes. To develop cubes, we used the latest technology available in the market. These tools assist the developer in defining the structure of the cube and handling big amounts of data in an effective way.
The developer defines dimensions and hierarchies, or drill paths, based on the anticipated approach an analyst might take to addressing business problems. The analysis variables or measures are also defined. To surface cubes to users there are several tools available. Each of these allows the user to analyze the data by examining slices, of the cube quickly and easily. However, Qualex can provide more than just tools. With our extensive experience using a variety of BI and ETL tools, we can not only develop a tool for analysis or reporting, but can help you better understand your data. Qualex can assist with designing data structures from the complex maze of data sources inside the typical insurers’ structure. We can work with your team to not only design a valuable reporting environment, but also provide training and expertise in a wide variety of areas.
- Non-additive fields like Utilization and PMPM are key metrics in the health care industry to help spot trend in health care costs.
- While actuaries have a wealth of OLAP tools available to them now to analyze the data in their organization, this important statistic is not available out of the box.
- However, there are customizations that developers can perform to bring PMPM into the analysts’ arsenal of tools.
- Qualex has proven experience in this area and understands the complex nature of these tasks.