Background & Overview

This was a project I designed to showcase my company's ability to build a dashboard for a device manufacturing company to track their testing process for devices in various stages of production.

Due to the sensitive nature of the client's data, we created a sample data set that is based on a device that consists of four stages of development and testing: die (DIE), printed circuit board assemblies (PCBA), and two stages of device sub-assembly (DSA1 and DSA2). To mimic a device company's manufacturing processes, we created sample tesing centers in 3 cities -- one in California and two in China. Each device goes through multiple tests, carried out by an array of sample employees.

Here is a quick presentation that walks through the process.

---

Once the sample dataset was created using Python and SQL, I designed a dashboard in Microsoft PowerBI to present our product to the client.

The dashboard consists of 5 pages:

Home Page

This page provides a quick glance at testing operations and results over a filterable time frame.
The goal was to allow a user to quikcly spot anomalies in their testing process.

Test Data Exploration Page

This page consists of the entirety of the test results recorded by the manufacturer.
A user can use many different filters to find groups of similar devices. The results are color coded to allow for easy viewing.
The Device Serial Number (DSN), device type, test name, and test results are all listed.

Device Test History Page

This page allows a user to keep track of the different stages of manufacturing that a device went through.
For example, if you selected a DSA2 device, you would see the serial numbers and test results for the previous stages (DSA1, PCBA, and DIE) along with the results from it's DSA2 tests.

Parametric Test Results Page

This page is designed to allow the user to see the distribution of test values for each test type. The user selects a device type, test, and filters to a desired date range.

Printable Test Report Page

This page allows the user to select a device and get it's full test history in a printable format.
The intent of this page was to allow for the user to easily share the test results of a problematic device with the rest of the organization.

Lessons Learned

In designing this project, we went through a few iterations of database design to come up with a structure that would be scalable for additional devices, device types, testing centers, employee names, and test types.

Once we had come up with the final database strucutre, we wrote a Python script that would generate random data for 200 different devices. We then loaded the data into our SQL database and imported it into PowerBI using SQLite.

This ETL process will continue to be used for samples we provide to potential clients in the future. Once we understand what a client is looking for in a dashboard, we can design and create our own fake data to showcase our strengths and capabilities without using any sensitive or proprietary data. This allows us to minimize risk, while still being able to present our services in a way that is relevant to the client.