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.
Microsoft SSIS
MSSQL
Power BI
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.
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.
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.
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.
Get in touch with us to schedule a consultation
