SITUATION & BUSINESS CHALLENGE
A global medical device manufacturer was in process of acquiring and installing four unique machines to produce lithium battery parts and wanted to determine how many sets of each machine would be needed. To address this, product engineering leads decided to adopt Overall Equipment Effectiveness (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 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, who 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.
SOLUTION
AIM Consulting 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.
AIM began by working with engineers to understand the existing data model and what the data represented on a granular level. Following this analysis, AIM 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.
With the new model in place, AIM Consulting created a series of four Power BI dashboards — one detailing overall OEE KPIs and separate dashboards for each of the three OEE metrics of availability, performance and quality. Working closely with stakeholders to determine informational requirements and best practices to include in the dashboards, AIM built out and tested initial wireframes and then determined the most efficient way to ingest the data from SQL Server into Power BI. AIM 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 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, AIM ensured that user interface (UI) best practices were built in to the dashboards. The medical device manufacturer implemented this template into the company’s future Power BI solutions.
RESULTS
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 is 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 Consulting 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 having taken notice of the proof-of-concept and looking at how to extend it in other areas of the enterprise.
