The client is one of the largest European automotive suppliers, selling to all leading car manufacturers, with more than 50,000 employees across 25+ countries. Operating at that scale across many tools and platforms, the organisation wanted to move beyond a fragile, manual reporting setup toward a scalable, efficient, and easily maintainable data architecture. The goal was to free the business team from time-consuming manual data wrangling and deliver fast, reliable, well-governed reporting that could keep pace with enterprise-wide demand.
The client’s size necessitates many tools and platforms, generating large volumes of data scattered across applications, databases, Excel spreadsheets, and SharePoint. Their reporting had grown organically into a fragile web of Python scripts, Excel macros, and in-Power BI transformations that no longer scaled leaving the business team to spend considerable time just aggregating and analysing data.
Operational data lived across multiple applications, databases, spreadsheets, and SharePoint, forcing the business team to spend significant time aggregating and analysing it before any reporting could happen.
Numerous Python scripts partially extracted data into files and folders, while Excel macros and manual steps cleaned the data ahead of reporting - a complex, error-prone pipeline.
Heavy transformations ran inside Power BI itself, with the same data loaded multiple times and full datasets recomputed on every refresh, causing slow load times and poor performance.
The non-modular design was hard to maintain and change, carried a large backlog of unimplemented business requirements from constant firefighting, and was never built to scale.
The existing Power BI dashboards were not designed properly, limiting their usefulness to the business teams who relied on them.
Focaloid proposed and built a clean, layered architecture that moved data processing out of Power BI and into dedicated ETL and warehouse layers making the system faster, easier to maintain, and ready to scale.
Designed a layered architecture and shifted data transformations out of Power BI into a Microsoft SSIS ETL layer, significantly improving speed and performance.
Migrated many of the scattered Python scripts into the SSIS layer, replacing the tangle of standalone scripts with a simpler, cleaner, centrally managed pipeline.
Built a data warehouse so datasets are processed once and served efficiently, rather than reloaded and recomputed on every Power BI refresh.
Added role-based access control with row-level security (RLS), automated alerts on any SSIS job failure, a scheduled 6-hour data refresh, and the ability to upload historical data snapshots.
Focaloid started by reviewing the existing system to pinpoint bottlenecks, then delivered the new architecture in four structured phases.
Reviewed the existing implementation, identified the issues with the current approach, and designed a scalable, efficient, and maintainable layered architecture.
Integrated the various data sources into the new SSIS layer, consolidating extraction and transformation logic in one place.
Built the data warehouse to process and store data once, serving it efficiently to the reporting layer.
Created reports and dashboards in Power BI and validated the full pipeline through testing and QA adding scheduled refreshes, failure alerts, RLS, and historical snapshots along the way.
For an enterprise of this scale - tens of thousands of people across dozens of countries supplying the world’s leading carmakers decisions ride on data being timely, trustworthy, and fast to access. A reporting setup held together by manual scripts and overloaded Power BI files doesn’t just frustrate analysts; it slows the business and caps how much insight the organisation can actually use. By re-architecting the pipeline into clean ETL, warehouse, and reporting layers, Focaloid turned a fragile, unscalable system into a fast, governed, maintainable one giving business teams reliable answers and giving the enterprise room to grow.
We help enterprises re-architect fragile BI setups into clean, scalable ETL and data-warehouse pipelines - so your teams spend time on insight, not data wrangling.