Case Study
Transforming Traceability and Supply Chain Efficiency with modern business intelligence insights system
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 analytics that could automate analytics and insights to stakeholders, thereby improving efficiency, accuracy, and customer satisfaction.
Business Requirement:
The client faced several challenges with their existing analytics and insights system and wanted to develop near-real-time production analytics on Tableau to enable automated insights and insights for stakeholders. The primary objective of the project was to build a cost-effective, near-real-time analytics system that significantly improved data refresh time and analytics 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 analytics execution times.
- Implementing a data warehousing solution to manage increasing data volumes, improve the performance of analytics and insights, 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 analytics and insights system that consisted of SQL Server, PostgreSQL, and Tableau. One major issue was the slowness in analytics 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 analytics and insights 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 |
| Analytics 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 analytics and insights 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 analytics and insights 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 analytics and insights system into an automated, cost-effective modern business intelligence analytics and insights 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 analytics execution time, data refresh time, and traceability efforts while saving the client money.
