An enterprise data warehouse solution for enhanced business analytics and reporting for an insurance service provider.

Business Need

Advanced business analytics is a prerequisite for survival in the current competitive market. Our client sought to make an investment in Business Intelligence for enhanced business analytics. A centralized Enterprise Data Warehouse (EDW) was required to store the insurance claim related data of the clients from both in-network and out-of-network pharmacies. The system was required to source the data from multiple internal and third party data systems to this data warehouse. The data warehouse needed to generate reports that provide better insights to the current business penetration and provide indicators to aid in better decision making for business growth including the conversion of pharmacies to in-network. This data warehouse also had to serve as a single centralized source of data for other systems.


We addressed the following challenges while designing the solution.

  • The claim data had to be sourced from multiple data sources like PICK based Pharmacy Benefit Management system and other third party data sources.
  • Issues due to missing key data in the multiple source systems.
  • Disparities in the data layout and format across multiple sources and the huge data volume raised challenges.
  • Simultaneous scheduling of multiple ETL processes without any resource lock issues was essential

Our Solution

Our team has closely associated with the client in many technology initiatives and offered a Microsoft SQL server based data warehouse platform that well aligns with their business strategies. The BI solution delivered provides better analytics for taking insightful decisions to handle the upcoming business needs of the client. Our solutions had the following key features.

Data Loading:

The claim details from both In-Network and Out-Of-Network data sources in different formats are extracted to flat files and then loaded to staging tables by SSIS technology.

Mapping and conversion:

Mapping and conversion of data based on business rules occur in staging database. The loaded data is stored in the stage tables. SQL agent jobs load the data to different facts and dimensions in the data warehouse.

Outbound data feeds:

The solution included the deployment of outbound data feeds from the new EDW to downstream systems for enabling better automation of regulatory reporting and for providing value added services across the claim life cycle for cost savings.


The system generates multiple reports using MicroStrategy and SSRS technologies to provide powerful visualization of the custom data based on the user’s preference. The reports are sent as a scheduled mail or as a web link. It facilitates the client to avail quarterly business reviews and other details that provide better business insights. This includes data on:

  • Network penetration
  • Spend/ savings by service line
  • Generic dispensing and efficiency trends
  • Expiration of brand patents
  • Planned performance Metrics
  • Various key claim statistics

Technologies Used

MS SQL, SSIS, Micro strategy, SSRS, BI xPress.


We worked with the client to deliver an enterprise data warehouse solution for better business analytics and reporting. It offered the following benefits to our client:

  • Drill-down key business performance data for better business decisions.
  • Centralized database for both in-network and out-of-network pharmacy data
  • Accurate market penetration reports for making strategic decisions to prioritize and expedite the out-of-network to in-network conversions.
  • Easily accessible slices of pharmacy data reports to deliver better customer service