Food distribution · cold chain
Business Intelligence platform for sales and purchases
Multi-company data consolidation, Data Warehouse, ETL and dynamic reports in Power BI.
4 companies
Consolidated in a single model
Daily
Updates via ETL and ERP integration
Star
Dimensional model of facts and dimensions
Overview
BI platform that unifies sales and purchases data from Grupo Pomona Iberia's companies into a single analytical system. From Excel, SharePoint and different ERPs to a common Data Warehouse with interactive reports in Power BI. Layered, scalable and automated architecture: Python ETL every night, MySQL DW with a dimensional model, and dashboards that analyze performance by company, customer, zone, product and period.
Technologies
The client
Iberian branch of a cold-chain food distribution group that brings together several refrigerated and frozen product companies —Friolisa, Novafrigo, Sanamar and DIL—. Each company with its own systems, customers and catalogs generates a large volume of data. Management needed a consolidated and comparable view of performance, but the information was spread out and in heterogeneous formats.
The challenge
Each company managed its data separately in different sources: its own Excel, SharePoint and different ERPs. Without a common model or unified query path, reports were done manually, fragmented and dependent on each format. Consolidating the group was costly and error-prone. They needed a unified, scalable BI platform, ready to incorporate new companies, connect to more ERPs and expand to new analyses.
The solution
Data Warehouse with dimensional model
Data Warehouse in MySQL with a star model that homogenizes all companies. Facts —sales and purchases: quantity, amount, cost, margin— and dimensions —customer, product, supplier, province—. Common structure that consolidates under a single analytical language and enables geographic, catalog and sales-structure analysis.
Automated, multi-source ETL processes
Python ETL that reads each source in its own format —Excel and SharePoint, or direct ERP connection— and normalizes to the DW model. Pipelines run every night via cron, without manual intervention.
Direct integration with the ERPs
Direct integration with the ERPs via SQL, replacing files and shared folders with a permanent connection to the source databases. It involved studying each database and a secure VPN network. Result: reliable updates, up to daily, without manual errors.
Dynamic reports in Power BI
Dashboards in Power BI published on Power BI Service with automatic refresh via gateway. The sales report analyzes performance by customer, zone, team, salesperson, segmentation and product, with geographic and temporal analysis. Includes advanced measures —margin %, PMVK, active customers, moving averages, YoY comparisons— and filters by fiscal year. Same architecture for the purchases report.
Results
Single view of the group: sales and purchases data, previously scattered across Excel, SharePoint and different ERPs, were consolidated into a single Data Warehouse with a common, comparable analytical model.
End of manual processing: automatic ETL processes update the information every night, eliminating manual report preparation and the associated error possibilities.
Scalable layered architecture: an ETL + Data Warehouse + Power BI architecture that grows without redoing work when adding companies, variables or domains.
Direct integration with the ERPs: permanent connection to the source databases via SQL and VPN, enabling reliable, high-frequency updates, up to daily.
Rich decision-making reports: dashboards with analysis by customer, zone, team, product and geography, advanced measures and filters by fiscal year.
Reliable, managed operation: infrastructure deployed with daily backups and transparent management for the client, who doesn't have to worry about the technical layer.