ETL Analysis and ETL Strategy for Prescription Benefit Facilitator (Pbf):

Organization

Our client is a family and pharmacist-owned and operated prescription benefit facilitator. Its mission is to deliver an innovative, clinically focused model and provide the industry’s best service. Through complete transparency and pass-through arrangement, customer goals align with reducing inappropriate drug utilization, improving health outcomes, and guaranteeing savings. Over the past decade, the company has redefined what to expect from prescription benefit facilitators: they put an end to the games that prescription benefit managers (PBM) play by delivering a completely transparent, pass through arrangement aligned to customers’ interests. Just as it’s extending its commitment to prioritizing member health and safety, they streamline processes connected to customer and partner.

Challenge

Our client uses Pentaho for ETL data transformations, data warehouse, and reporting purposes. The original implementation was designed initially to serve limited data warehousing and reporting needs. As the company grew over the years, the data volume and various other data warehouse and reporting needs increased. But the current implementation was not scalable and flexible to serve the current requirements and volume. This poised various challenges for the IT team, business, clients, and partners. The client sought an experienced partner in ETL and Data warehousing solutions to analyze their processes, design and provide recommendations to simplify and optimize the process, and define the ETL strategy – for current and future needs. XTIVIA assessed the information gathered from client and found gaps in the following areas:

• Lack of focus on infrastructure quality
• Main operational data source too complex
• One big database for all application needs
• ETL tools being used incorrectly
• Poorly designed data warehouse loads
• Multiple copies of same data
• Lack of archival and purging process
• Not utilizing tool capabilities
• Inconsistent compliance controls
• Using old versions of tools no longer being supported

TECHNICAL SOLUTION

XTIVIA provided short-term and long-term solutions to fill the gaps focusing on their immediate needs and future goals of Performance, Availability, Scalability, and Maintenance. XTIVIA recommended the following :

• Improving performance through concurrency/multithreading, efficient use of transformation, efficient use of row-based operations, and proper use of indexing
• Data archival and purging mechanism for Hot, Cold, and Warm data
• Achieve data availability through disaster recovery and near real-time loads
• Implementing the data governance process
• Improving compliance and controls for sensitive data
• Replatform and redesign of ETL pipeline
• Consolidate data using flatter data model and use of data hub for downstream data consumption needs
• Efficient tools utilization to increase productivity and ease of maintenance
• Monitor the capacity utilization and perform capacity management by distributing the workload efficiently

Focusing on the recommendations and solutions, XTIVIA proposed the Future-State roadmap to achieve client’s short, mid, and long-term goals. The short-term solution provided immediate performance benefits and freed up infrastructure capacity without significant investment and impact on internal and external stakeholders. The mid-term solution focussed on preparing and redesigning the ETL pipeline solution. The long-term solution focussed on realizing the vision for the future goals.

BUSINESS SOLUTION

XTIVIA found the gaps and current-state ETL pain points and provided a solution that addresses their future goals. XTIVIA’s recommendations and solutions helped our client in improving:

• Performance of ETL process
• Data Availability
• Compliance and Controls
• Redesigning the ETL Pipeline
• Efficient Capacity Utilization
• Providing the near-real-time reports to customers
• Increased productivity and ease of maintenance