Optimizing Deviation Management and Release Readiness: A Scalable PowerBI Solution for Catalent’s Manufacturing Operations

Note: All information and images described herein are censored and presented using dummy data. The numbers and figures do not represent trending data for any organization.

Situation, Obstacles, Actions, and Results (SOAR):

Situation:

As a Biotechnology Cell Development and Manufacturing Organization (CDMO), timely product release is essential for revenue generation. The final step before product disposition requires all deviations associated with a batch to be closed. The site previously relied on an Excel workbook maintained by a single individual to track deviation status. When that individual left the company, the existing system became unsustainable, jeopardizing the site's ability to release products and receive payment.

Obstacles:

The Excel workbook required significant manual effort, with daily updates taking one hour and weekly graph updates taking an additional four hours.
Data linking between Batch Disposition and MTO data sources was hindered by inconsistent or missing lot number information, preventing deviations from syncing with specific runs or thaws.
The dynamic nature of the MTO team and frequently changing release schedules made it challenging to assign and prioritize deviations effectively.
The TrackWise system did not track when records were sent to clients ("Pending Customer Approval"), lacking historical data needed for accountability and compliance.

Actions:

Obtained permission to design and implement a scalable, user-friendly tool to replace the Excel workbook.
Developed a PowerBI solution that integrated data from Batch Disposition and MTO, providing real-time, dynamic tracking of deviations.
Created backend calculations to assign and sort deviations based on the closest target release date, accommodating deviations impacting multiple runs or thaws.
Designed interfaces for Supervisors and Managers to filter deviations, assign investigations fairly, and assess team workloads.
Developed tools to highlight deviations missing or incorrectly filled product grid information, enhancing data accuracy.
Established an MTO-owned server, set up security protocols, and created a database to track deviations pending customer approval, linking it to PowerBI for calculations and trending.

Results:

Enhanced efficiency and accuracy in tracking deviations, reducing manual effort and time previously required.
Provided site leadership with improved visibility into release readiness, investigator workloads, and deviation priorities.
Enabled the MTO team to prioritize and complete investigations more effectively, supporting timely product release and revenue generation.
Improved data integrity and accountability by highlighting missing or incorrect information and tracking deviations pending customer approval.
Facilitated better communication and alignment between the site and clients regarding deviation expectations and compliance.

Introduction

A Biotechnology Cell Development and Manufacturing Organization (CDMO) depends on product release to generate revenue. The final step before product disposition is to ensure that all deviations associated with a batch have been closed. A deviation is an investigation into a process excursion intended to confirm that the Safety, Integrity, Strength, Purity, and Quality (SISPQ) of the product have not been compromised, and to ensure that Corrective Actions and Preventative Actions (CAPAs) have been implemented to prevent recurrence. If any deviations associated with a batch remain open, that batch cannot be released, and the site cannot receive payment. Therefore, it is paramount for the site to accurately and efficiently track the status of deviations for each batch intended for release.

Previously, the status of deviations at Catalent was tracked using an Excel workbook owned by a single individual. This document required one hour daily to update raw data and an additional four hours at the end of the week to update graphs. After the owner left the company, the utilization of that document became untenable. Consequently, I was granted permission to design and implement a revamped version of that tool, with the intent for it to scale to meet company needs, be easier to use and update, and provide improved granularity for site leadership. The following is the end product I designed for site leadership and my team

Site Leadership: DVs Impacting Runs and Thaws

This section integrates data sources owned by both Batch Disposition and Manufacturing Technical Operations (MTO). The data includes a dynamic lot genealogy tree maintained by Batch Disposition, along with product information extracted from TrackWise and managed by MTO. By combining these sources, site leadership can select any molecule of interest for any client, choose the specific run they wish to analyze, and view every record at any stage—from initiation through investigation to closure. They can identify which investigator is working on each deviation and see a count of how many deviations are impacting each run.

Similar to run releases, site leadership is also interested in the status of each thaw within a run. The same information as above is displayed, but with the inclusion of the thaw details. This information is presented in a separate table because each run may have multiple thaws, and PowerBI does not allow columns to be toggled on and off.

Using the two graphs above, site leadership has real-time visibility into the release readiness for both runs and thaws for every client at the site. This list is dynamic and updates throughout the day as Batch Disposition and MTO refresh their data sources with new information.

The graph below can be used to see an overall view of how each Run is performing.

Team Leads

The structure of the MTO team at Catalent is inherently dynamic. Since no two deviations are alike, Supervisors and Managers of the MTO department require an interface that is as flexible as possible to fairly assign investigations to their respective teams. Therefore, the table below is designed to provide them with comprehensive information.

While the presented information may seem overwhelming at first glance, it serves two main purposes. First, it allows Supervisors and Managers to filter and identify which deviations need assignment. Second, it helps them assess the current workload of every team and team member.

The top two rows enable Supervisors and Managers to control the deviations they are reviewing and the teams to which they are assigning records. At Catalent, there are multiple teams that focus on specific areas of interest for investigations. The assigning Supervisor or Manager may be interested in assigning certain classifications of deviations, assigning deviations that have not yet been assigned, or reassigning deviations currently assigned to someone else. These top rows provide the flexibility to sort and filter deviations based on any required criteria.

The bottom row offers visibility into the team's workload. As previously mentioned, no two deviations are the same. Therefore, Supervisors and Managers can see both the number of Minor, Major, and Critical deviations each team member is handling and the number of lots their deviations impact and require investigation. Using these criteria, those assigning deviations can assess the total workload of each member at any given time and exercise judgment in assigning additional work. This information is presented both graphically and in tables on the left, allowing them to see exact numbers broken down for individual investigators.

MTO Investigators

At Catalent, the MTO team is heavily driven by metrics. Investigators are expected to complete a certain number of investigations each month while also prioritizing specific deviations based on the runs that site leadership plans to release next. The runs scheduled for release are dynamic and change frequently, making it challenging for the MTO team to appropriately prioritize which deviations to address next.

The next page addresses these challenges. The primary element on this page is designed to provide each team member with their deviation priorities. Batch Disposition tracks the run and thaw release schedule in the same document as the lot genealogy tree. By combining this information with the TrackWise data, deviations assigned to each operator can be linked to a specific release date. However, deviations can impact multiple thaws and runs, each potentially having different release dates. I developed backend calculations to assign and sort deviations based on the closest target release date. If a deviation impacts a run or thaw without a target release date, the system follows the logic of addressing the oldest records first, prioritizing Major deviations over Minor ones.

Additional features on this page include the ability to filter all information by investigator by typing their name into a sidebar list. Below the provided worklist, investigators can view their current metrics at a granular level. Specifically, they can see how many of each type of record they have closed, how many records they have sent to the client and are awaiting approval, and their Right First Time (RTF) metric for Major deviations over 7-day, 30-day, 60-day, and 90-day time frames. Additionally, they can see this information broken down by the company's "reporting week" on the left side, or filter information by specific dates of interest.

Linking Data Sources For Operational Use

One of the challenges encountered during the construction of this resource was linking the Batch Disposition data to the MTO-owned TrackWise data. The link between the data sources is the lot numbers present in both; if they do not match exactly, a deviation will not sync with a specific run or thaw. However, the issue was that deviations often did not have lot numbers entered into the product grid upon initiation, and the MTO department tended not to include them until the record was ready to route.

To resolve this problem, the page below was designed to provide clear insight into which deviations and teams are missing product grid information in the TrackWise deviation records, as well as records that might have incorrect product lots. Supervisors and Managers can sort all records by designated teams. If a deviation lacks any information in the product grid, it populates in the top table.

At Catalent, there are no uniform rules governing lot numbers. Some consist of numbers only, others include dashes or letters, and there is no standard length. At the time this tool was being created, the site was moving towards lot numbers containing only numbers. Therefore, the code was designed to capture any deviation with product grid information that does not convert into a numeric value on the backend.

MTO Escalations to Leadership

As the final step in the process prior to batch release, the MTO team is constantly under pressure to complete their investigations. However, due to the integrated nature of deviations at the site, roadblocks are frequently encountered that halt progress on numerous investigations. History has shown that without capturing this information, leadership remains unaware of the underlying reasons.

To overcome this and provide visibility into what is preventing the team from advancing their investigations, two separate pages were created. The first is based on information submitted by the team itself, captured in a SharePoint file and represented in PowerBI. Users can filter by team and date of their choosing to see what roadblocks are being encountered. This information is presented both graphically and in a side table.

The second page is designed to illustrate where the MTO team can improve or highlight areas where the Catalent site and the client can discuss and better align on deviation expectations. This page captures and displays comments sent back to the Catalent site. These are grouped based on categories and can be filtered by specific teams and by the date when the comments were received.

Site Analysis: Overall Site Health

The following sections illustrate a variety of tools developed to analyze the site's status regarding deviation numbers.

The page below showcases a quick overall status for the site's deviations. The waterfall diagram groups deviations based on their classification as Major/Critical or Minor, as well as deviations that are less than 30 days old and those that are more than 30 days old. Additionally, this information can be sorted and assessed for any individual or group of clients.

The next group of pages utilizes several metrics to show the site's trend for deviations.

The following graph shows a comparison of how many deviations were opened and closed on a weekly basis, as defined by the company's "reporting week." Additionally, this graph displays the total number of open deviations on a weekly basis, and all values can be assessed for any client.

The subsequent graph presents similar information but focuses explicitly on a comparison of deviations created and closed on a monthly basis. Users can select specific time frames and clients of interest.

The page below focuses more explicitly on a comparison between how many Minor deviations and Major deviations are being closed on a weekly basis while also showing the total closed. These values can be adjusted to inspect any client of interest. This information resets for each month and updates daily, allowing the user to see how the week is progressing, as indicated by the drastic drop-off at the far right.

To provide a holistic view of how each client is performing, the following pages show the total number of Major and Minor deviations for each client.

One of the challenges leadership faced was tracking how well the site is addressing both new and old deviations. To address this concern, the graph below shows the total age of all open deviations on a weekly basis. This number does not include deviations until they reach 30 days of age. By observing how this number trends over time, leadership can determine how effectively the site is handling older deviations.

Similarly, it is important that the Manufacturing and QA teams open deviations promptly from the date of occurrence. This is crucial because the older a deviation is, the more difficult it becomes to perform a thorough investigation, as details may be forgotten by the operators involved. Additionally, since different teams are dedicated to each client, the information presented below can highlight if any groups are struggling to keep up with the site's requirements.

The information presented below may initially appear overwhelming. However, it provides invaluable insight into the root causes at the site. The numerous selectors allow users to filter and analyze information to any degree needed. Users can inspect the root causes for every deviation based on the investigating team, the impacted client, the deviation number, the dates the deviation was created and closed, and the individual investigators who performed the investigations.

Database

The final chart developed as part of this project was the most challenging to establish. The reason is that, while every other chart could be calculated from the raw data provided by the TrackWise system, TrackWise does not track when records are sent to the client. Effectively, for records that are "Pending Customer Approval," the TrackWise data lacks historical tracking.

To overcome this obstacle, I undertook the following actions:

Worked within Catalent procedures to establish and become the owner of a server for the MTO department.
Established and approved the security procedures for accessing the server.
Calculated the data requirements for a period of five years and allocated the necessary memory for MTO use.
Created a database on the MTO-owned server and linked the PowerBI system to: 1) send SQL statements to the database I created, and 2) receive the full database list for calculations and trending purposes.

By performing these actions, I was able to overcome the challenge of tracking how many deviations are in "Pending Customer Approval." This metric is important because, while the Catalent site has obligations to close deviations, we must also hold our partners accountable for reviewing the information we send to them, as outstanding items can quickly become a compliance risk.

Users can sort this information per client, by deviations classified as Major, Minor, or Critical, and select the timeframe of their interest.