Machine Data Collection

Goal

The main goal for this fictional case is to demonstrate the possibility to visualize a machine data collection in Power BI. Let's imagine we have a company with production machines. The machines combine various raw materials under controlled heat to produce one final product. Each machine has four inlets for the raw materials, meaning that only up to four raw materials can be melted to the final product.

The goal of the report is to show the current status of the machine (production / no production) and the process parameters over time to analyze if the production might have any defects. A defect occurs if the actual temperature is not matching the target temperature or if the actual throughput of the raw materials is not matching the target throughput.

Data

The machine has several sensors:

• Five temperature sensors to record the current temperature for each zone where the melting process takes place
• One sensor for each raw material inlet to measure the throughput of the raw materials
• One sensor to measure the throughput of the final product
• One sensor to record if the machine is producing or not

Every 20 seconds a snapshot of each sensor is sent to a database, together with the date&time, the lot number, the produced product, the machine name, the pressure and the target temperatures for each zone.

Challenges

One challenge is to show when the possible defect happened, because we only have a snapshot of the current throughput recorded every 20 seconds (Challenge #1).

The other challenge is to compare the actual throughput of the raw materials with their target throughput. The target throughput of the raw materials is not exported (Challenge #2).

Data Preparation

The data preparation takes place in PowerQuery. First of all let’s talk about the size of the data. In the real world, data size would be an issue very soon. When every row represtens a snapshot made every 20 seconds, the table contains 1 Mio. rows after 8 months of data for one machine. To reduce the data load into PowerQuery I propose to use a SELECT-statement to only load data for the last 6 weeks. Like this, current productions can be compared with previous productions for the same product.

To solve Challenge #1 we need to define a running total that only counts the actual throughput of the final product of one lot and when the machine is in state production. In other words: When a new lot starts the running total should start at 0 and when the machine is in state "standstill" the running total should not add any new quantity.

For Challenge #2 we need to define the target throughput for each raw material to enable analyzing if the current throughput is inside the threshold. The target throughput varies for every lot: E.g., the first lot has three raw materials, with a mixture ratio of 60%, 30%, 10% and the next lot has two raw materials with a mixture ratio of 80%, 20%. To solve this four paramters are created, each from 0 to 100 %, visualized as slicers. Like this the user is able to adjust the mixture ratio for each prodcut analyzed. The use case would look like this: The user is checking lot "100500". The mixture ratio is 30% raw material 1, 68% raw material 2 and 2% raw material 3. He adjusts the correspondening slicers to these values. Slicer 4 stays at 0.

Design

The final report has two pages. Both pages have a date picker slicer, a lot no slicer and a production state slicer on the top to ease navigation.

The first page has three visuals one above the other over the total width to match the time axis. The first visual is the current status of the machine. Below is a visual to show the temperature difference between actual and target temperature. When the delta of the temperatures goes over a threshold the deviation will be visible. The last visual is a line chart showing the pressure, the throughput of the raw materials, as well as the throughput of the final product and the the running total of the good quantity in different colors to clearly differentiate them while analyzing in detail with the sliders.

On the second page, below the parameter slicers, is a visual to show, similar to the temperatures, the difference between actual and target raw material throughput. When the delta goes over a threshold, the deviation will be visible. On the bottom of the page is a data table to show the delta throughput of the raw material slots of each time stamp for detailed analysis. The last column shows if the data is “OK” or “Not OK”. The timestamp is markes as "Not OK" if the actual throughput is outside the threshold. To quickly show the user the time stamps that are “Not OK” a slicer is on top of the report page.