Multi-Billion Dollar Automotive Component Manufacturing Giant Streamlines Decision Making With QlikTushar Sonal
Our client is a huge global conglomerate. They manufacture & supply Tier 1 automobile components and have a global revenue of over $11 billion. They have around 250 factories operating out of 40 countries. They have a subsidiary company that provides IT solutions within the group. They also have partnerships with firms spread across the globe, where they focus on delivering cutting-edge IT solutions.
There were more than 600 sites involved in transactions at over 100 group companies which formed the group. The group used an in-house SQL Server Data Warehouse for data management.
A team of analysts used to create data extraction scripts in SSIS for connecting to their various source systems.
At each company, a team was responsible for collecting and consolidating sales and financial figures. Then, they would share these figures with the corporate office.
1) Bad Reporting Performance
The group has a vast business expanse in multiple geographies and LOBs. The business processes created a huge volume of enterprise data daily. The companies use various applications/ source systems built on diverse technologies. The data was voluminous and varied. It was a challenge to get a single version of the group metrics. Additionally, the existing applications were quite heavy and there were performance issues with it. The reporting application was not optimised and it was affecting the source system’s performance. There was a large number of user requirements for dashboards. Governance of enterprise data across companies spread across multiple locations was a huge challenge.
2) Unable to Get a 360-degree and Granular View of the Business
The group did not have an enterprise-wide reporting platform. They lacked visibility for different stages. The senior management wanted to see the details of sales and finance KPIs at the plant level, customer group level and product group level. They were looking for a solution to keep track of their purchases, inventories, quality, equipment efficiency, payables & receivables and budgets. For individual companies and plants, the management wanted to view granular operation details. They wanted to have all of this reporting environment within a consistent and governed framework.
3) Unable to Get Reports on Time
Because of the challenges with the reporting system’s performance & capabilities, there was a considerable delay in reporting and the management was unable to see the report in real time. For example, the chairman of the group used to get their Sales report on 7th of the following month and financial statements by 20th of the next month. Additionally, the report was error-prone. It took 1-1.5 months to extract data and prepare reports and see P&L, Balance Sheet & Sales numbers. The group was looking for a system where they can get their sales and financial figures on time.
4) Spreadsheet-based analysis and reporting was prone to errors
Working with excel was a tedious task and involved lots of manual processes, tedious tasks and too many workforces were cleaning, consolidating and developing the reports. Complex information such as budgetary logics was stored and maintained in Excel files. With so much manual intervention involved in preparing reports, it invariably used to result in unintended human errors. The management could not trust the reporting system for their decisions.
Our solution delivered reports for 521 users on Qlik BI and NPrinting.
Our goal was to provide a platform from where business users can get updated information from all the source system.
The business uses did not have some specific UI requirements. Our first step was to document and finalise requirements. We did multiple sessions with all the stakeholders to come up with wireframes and prototypes in Qlik. After capturing the solution expectations, we validated it.
We did the required amount of data cleanings such as removing junk data and multiple names for the same entry.
We extracted data by using the increment process and then we created a central database repository in Qlik with multiple transformation layers. We built this system by ensuring that the repository is scalable so that they can seamlessly integrate with the new source system in future.
We implemented the Qlik business intelligence system on the top of their warehouse. It was a robust reporting system which provided them with a 360-degree view of data at the enterprise level. Our implementation methodology was agile to deliver incremental benefits.
The dashboard that we developed was designed to be used by the chairman, company heads and other top management.
Results & Benefits
Our expert analytics consulting helped the client derive maximum benefit from their Qlik purchase.
1) Single Version of Truth
The solution was helpful to IT as now they have a single version of truth for their group reporting. Our implementation resulted in an automated and scalable solution, so now the IT team need not do much manual work. Now they had a single place where they can see the group numbers coming from all the data sources. With NPrinting reports delivered, on demand and email reports, it spread the reporting capabilities to a large user base.
2)Robust Governance and Security to ensure data sanctity
Using the various security mechanisms provided in Qlik, they could control access to the BI platform. The governance of the environment was pretty easy, and the different logs were helping them to understand and audit the system. Now, IT can define user and role-based access to data-sets.
3)Reduced Report Turn-around Time
The solution helped the customer reduce the time taken to process their primary as well as detailed report. The data from source systems was coming daily so they can have a look at the N-1 day’s numbers. Now, they have access to all their reports at a central place and everyone can access the reports anytime. The management can now see the report of last day. It resulted in reduced number of persons involved in generating & distributing manual reports
We reduced the loading time of data from 25 mins to 22 secs. Ultimately this resulted in improved performance of their inventory dashboard.
With our expertise, we did team management and trained their resources. We consulted on the appropriate visualisation styles to incorporate into their dashboards and reports. We also consulted on server sizing and load balancing. All of this resulted in a robust, scalable solution on Qlik that handles their enterprise-wide analytics and reporting requirements.
Our Solution Areas
Our analytics projects covered multiple subject areas: MIS, Budget Management System, PMS, Sales, purchase & spend, Inventory, Accounts Receivable and Accounts Payable. We also did QCDDMSES (Quality Control Delivery Development Management Sustainability Environment Security). We did document chaining to create management dashboards by creating multiple applications. We optimised the requests for data loading.
We executed an OEE (Overall Equipment Effectiveness) project to track OEE for plant maintenance. We created reporting applications over multiple subject areas: Inventory, AR, AP, Sales, Procurement, OEE, Finance, Budget.
Our thorough business requirement sessions with stakeholders helped us understand the metrics and performance measures that they needed and the kind of reports that different business users wanted to see.