The client is a leading manufacturer of precision electronic components. They have a vast production line with several plants where work orders are grouped by batches and assigned to a specific production line. All work orders are executed on the shop floor, where data is collected in real-time.
Business Need:
The client needed to create a robust data foundation and develop near-real-time production reports that could automate reporting services and provide insights to stakeholders, thereby improving efficiency, accuracy, and customer satisfaction.
Business Requirement:
The client faced several challenges with their existing reporting system and wanted to develop near-real-time production reports on Tableau to enable automated reporting services and insights for stakeholders. The primary objective of the project was to build a cost-effective, near-real-time reporting system that significantly improved data refresh time and report execution time.
Approach:
To address the challenges faced by the electronic manufacturing company, Systech considered several potential solutions after thoroughly assessing their business operations and data landscape.
Upgrading underlying technology to improve performance and capacity.
Optimizing query design to reduce the time it takes to retrieve data, improving report execution times.
Implementing a data warehousing solution to manage increasing data volumes, improve report performance, and provide better traceability during parts movement.
To help the company overcome existing challenges and improve efficiency, accuracy, and customer satisfaction, Systech laid out a roadmap for project execution.
Challenge: The electronic manufacturing company faced several challenges with their existing reporting system that consisted of SQL Server, PostgreSQL, and Tableau. One major issue was the slowness in report execution, which prevented timely decisions and could lead to missed opportunities or decreased competitiveness. The increasing data volume was another challenge, causing slower performance and increased storage requirements. Additionally, the company struggled with difficulty in traceability during parts movement, which impacted their ability to track products and parts through the supply chain. This resulted in lost or misplaced inventory, delayed production times, and decreased customer satisfaction. Overall, these challenges had a significant impact on the company’s business operations and required effective solutions to improve performance and efficiency.
Solution: Systech proposed a modern business intelligence reporting system by adopting open-source technologies like Druid Data Mart and Presto Query Engine. They suggested replacing the PostgreSQL with Apache Druid Data Store as a landing layer after data extraction from SQL Server. They used Presto SQL Engine as the bridge between Druid and Tableau.
To achieve optimum traceability for parts movement, Systech proposed their product, Dopplr. Dopplr can handle significant volume (100 Mn+) and achieve traceability for shop floor parts movement.
Before and After Comparison:
Value proposition KPI
| Before Implementation | After Implementation |
Report execution time | 6 to 10 min depending on the filter selection using SQL Server & PostgreSQL | Achieved to 3 to 5 seconds using Druid |
Data refresh time from PROD to Reporting System (Tableau) | 40 to 45 min using PostgreSQL | 5 to 10 min using Druid |
Traceability efforts in line with increasing volume | Hours to days and dependency on teams to provide data dumps | Dopplr was able to fetch data within 10 to 15 seconds for data chunk up to 150Mn records. |
Cost of reporting system | Cost Effective | Cost optimized further by deploying open source software such as Druid Data store & Presto SQL Engine |
Value proposition KPI Before Implementation After Implementation Report execution time 6 to 10 min depending on the filter selection using SQL Server & PostgreSQL Achieved to 3 to 5 seconds using Druid Data refresh time from PROD to Reporting System (Tableau) 40 to 45 min using PostgreSQL 5 to 10 min using Druid Traceability efforts in line with increasing volume Hours to days and dependency on teams to provide data dumps Dopplr was able to fetch data within 10 to 15 seconds for data chunk up to 150Mn records. Cost of reporting system Cost Effective Cost optimized further by deploying open source software such as Druid Data store & Presto SQL Engine
Conclusion: Systech successfully transformed the client’s existing reporting system into an automated, cost-effective modern business intelligence reporting system. The solution adopted open-source technologies like Druid Data Mart and Presto Query Engine, which brought significant value to the client’s business and its stakeholders. The solution coupled with Dopplr helped improve report execution time, data refresh time, and traceability efforts while saving the client money.