Case Study I - Healthcare Datawarehouse

The Organization:

Our client is a Public Health service provider currently administering federal AIDS Drug Assistance
Program (ADAP) services to the states of California and Washington. In California, the program is known as the AIDS Drug Assistance Program (ADAP) while in Washington, the program is known as the AIDS Prescription Drug Program (APDP).

The Business Issue:

The increase in program size as well as the number of eligible, medically indigent population has
necessitated a solution that can meet the growing reporting needs for the users. The core of the solution proposed by Caarma was a data warehouse that would aggregate data from the different operational data sources. The data warehouse platform was Microsoft SQL Server and the ETL tool was Data Integrator from Business Objects. The Business Intelligence solution was Business Objects – Crystal Reports and WebI.

The Solution

Caarma utilized their domain knowledge in healthcare to analyze the customer’s requirements and designed data warehouse consisting of a staging area and several data marts. Reporting and Analysis was enabled from the data warehouse.

Standard Reporting:

• Some of the standard reports developed by Caarma included:
• Adherence Reports with multi level drill down to Pharmacy, doctor and patient
• Exception Reporting including duplicate claims and suspended claims
• Recommended drug combination effectiveness report

PRESCRIPTION CLAIMS PROCESSED Claims approved for payment   Downloaded Claims 810,116 56,2%   PMDC Input 87,800 6,1%   TOTAL APPROVED 897,916 62,3%   Claims not Approved   Duplicate 14,828 1,0%   Denied 280,973 19,5%   Rejected 177,635 12,3%   PMDC Suspended 11,377 0,8%   TOTAL NOT APPROVED 484,813 33,6%   Claim Reversals 59,143 4,1%	Prescription Claim Distribution

Fraud Detection:

Using data from the data warehouse Caarma was able to develop reports that detected possible
fraudulent behavior patterns among patients.

Analytics:

Using the data warehouse and Business Object’s Web Based Analysis tool – WebI, Caarma was
able to enable key business users to analyze program data and patient statistics without the help of IT intervention increasing their productivity significantly.