The Client
The client is one of the largest European automotive suppliers selling to all leading car manufacturers. They employ more than 50,000 people and are present in 25+ countries worldwide.
Service Offerings
Services Provided
Tech Stack Used
Microsoft SSIS
MSSQL
Power BI
The Problem
Their size necessitates the use of multiple tools and platforms to manage their operations. This generates a large volume of data that is spread across different applications, databases, excel spreadsheets and sharepoint. Their business team currently spends a considerable amount of time to aggregate and analyse this data. While they use PowerBI for data visualisation, they are dependent on a large number of excel macros and manual processes to clean and process the data before generating reports.
In the current scenario, they developed numerous python scripts to partially extract data from various databases, process it and save it to folders and files. PowerBI is used to load these files and data from other sources. Various transformations and other data processing operations are performed within PowerBI. This leads to complexity, performance issues and difficulty in maintaining the system. Scalability of this system and process is also a big question.
Review of Existing System
As a first step, we reviewed the existing implementation, identified potential issues with the current approach and recommended changes to create a new solution which will be scalable, more efficient and easily maintainable. The following were the major problems identified by our architects.
- Non-modular design and lack of a layered approach
- Data sets were reloaded and computed during each refresh, resulting in slow loading time
- Same data was loaded multiple times to PowerBI
- Slow performance resulting from the non-modular approach
- High maintenance, hard to make changes
- The current architecture was not scalable
- PowerBI dashboards were not designed properly
Due to frequent fixes to the system, a lot of requirements from the business teams were yet to be implemented, resulting in a large backlog.
Proposed System Architecture
After a detailed review and analysis of the existing implementation, we proposed the following layered architecture for the new implementation. We transferred data transformations from PowerBI to the SSIS layer, which improved speed and performance. Many of the python scripts were also moved to the SSIS layer, there by creating a simpler and cleaner layered architecture.
Implementation & Results
- Integration of data sources to the SSIS layer
- Building of the data warehouse
- Creating reports and dashboards using PowerBI
- Testing & QA
- Data refresh after every 6 hours
- Set up Alerts which are triggered in case of any failure in the SSIS jobs
- Role based access control and RLS
- Option to upload snapshots of previous data (history)
Implementation was done in the following four phases
We also implemented the following additional features along the way:
How can we help you?
Get in touch with us to schedule a consultation
