This case study deals with a medical device manufacturer bogged down by a manual data calculation and reporting process that produced insufficient insight into their machine operations.
Our Data & Analytics experts helped transform the organization’s data-analysis process to a more efficient Overall Equipment Effectiveness (OEE) model, providing tremendous value and insight into the behavior of the machines.
TABLE OF CONTENTS:
- Challenge: Ineffective Data Calculation & Reporting Process
- Establishing Data Model for Improved Analysis & Insights
- Creating Power BI Dashboards for Insight into OEE KPIs
- Results of Implementing OEE Data Modeling & Visualization
Challenge: Ineffective Data Calculation & Reporting Process
A global medical device manufacturer was in the process of acquiring and installing four unique machines to produce lithium battery parts. It wanted to determine how many sets of each machine would be needed.
To address this, product engineering leads decided to adopt OEE, the gold standard for measuring manufacturing productivity, which consists of a series of KPI metrics that enable valuable insight into the following areas:
- Availability: How often a machine operates versus how long it is down.
- Performance: The number of parts a machine produces in a given amount of time.
- Quality: The percentage of working parts created versus those that are rejected.
The existing process for measuring manufacturing productivity was highly manual:
- The machines were installed in a test laboratory. As they were run, machine operational data was being written out to Mongo DB (a document database) at a high velocity.
- Engineers would then manually extract the data from MongoDB and send it to analysts.
- The analysts would copy and paste the data into Excel spreadsheets for analysis and data visualization reporting.
- Conditional formatting would alert users to critical events such as machine errors, but the inefficient process often caused delays in finding and correcting the causes of those errors.
In effect, the manual data calculation and reporting process was a burden that produced insufficient insight into machine operations.
The issue was compounded by the immense amount of data generated by the machines, which could trigger messages with values from 0 to 214 billion.
Engineers were uncertain about the best way to transform from their current data-analysis process to the more efficient OEE model. Based on positive previous engagements, they turned to AIM Consulting’s Data & Analytics practice for help.
Establishing Data Model for Improved Analysis & Insights
We delivered a data architect and senior data visualization engineer who built a state-of-the-art proof-of-concept solution for a single machine to enable users to quickly view and drill down into the OEE metrics.
The solution automatically transfers the machine data from MongoDB into a star schema data model on SQL Server, and then into Microsoft Power BI dashboards for data analysis and visualization.
We began by working with engineers to understand the existing data model and what the data represented on a granular level. Following this analysis, we created the star scheme data model with additional data transformation including the following:
- Tables to calculate machine downtimes to show how often a machine was down per shift and why (e.g. “machine down” might mean that the machine is not working or that it could be in wait mode).
- Correct mapping of production shifts to actual dates and times, in cases where shifts overlap calendar days.
- Converting the hundreds of billions of machine error triggers at runtime from decimal to binary code and mapping to specific trigger values.
Creating Power BI Dashboards for Insight into OEE KPIs
With the new model in place, we created a series of four Power BI dashboards:
- Detailing overall OEE KPIs
- Detailing individual OEE metric, availability
- Detailing individual OEE metric, performance
- Detailing individual OEE metric, quality
Working closely with stakeholders to determine informational requirements and best practices to include in the dashboards, we built out and tested initial wireframes and then determined the most efficient way to ingest the data from SQL Server into Power BI.
We ensured the dashboards followed internal data governance and hierarchy procedures, and that log-ins were limited to a specific user population.
The dashboards enable end users to see overall OEE metrics and then drill down into specific days, shifts, and supporting dimensions for more detail into the machine’s behavior.
As the company had just made the decision to move to Power BI throughout their enterprise, our experts ensured that user interface (UI) best practices were built into the dashboards. The medical device manufacturer implemented this template into its future Power BI solutions.
Results of Implementing OEE Data Modeling & Visualization
The proof-of-concept data solution has enabled tremendous insight into the behavior of the new machines, providing far more value than the old manual data process.
With OEE statistics at hand, business leaders can now correlate metrics such as machine uptime and downtime and product quality into monetary gains and losses.
Additionally, the speed and ease at which users can access this information are vastly improved. More importantly, they now have insight into how many sets of each machine they will need to maximize Overall Equipment Effectiveness.
The company is planning to introduce the remaining machines into the dashboards and selected AIM once again to partner on this phase.
Engineers and business leaders are also researching enhancements beyond the OEE principles in areas such as data trending and predictive analysis and enriching the solution with additional information like operator training data.
The project has generated considerable interest from other groups across the company, with the central IT team taking notice of the proof-of-concept and looking at how to extend it to other areas of the enterprise.
Need Help Leveraging the Full Value of Your Data?
We are technology experts & subject-matter thought leaders who have come together to form a consulting community that delivers unparalleled value to our client partners.
