The real rule behind each number
Billing date, item and header discounts, returns and service orders by type of service, calculated the way the ERP calculates them.
Target, actual and forecast for each business unit, without building a spreadsheet.
I designed and built a data platform for Eletrolux Materiais Elétricos e Iluminação — an electrical supplies and lighting retailer in southern Santa Catarina, Brazil, with two stores plus installation and repair-shop services. The platform reads each store’s legacy ERP, consolidates 18 years of history in a data warehouse and delivers dashboards that reproduce the numbers management used to pull by hand from the system: revenue by business unit, targets, salespeople, services and inventory.
Role · Architecture, ERP reverse engineering, data engineering and BI
Scope · End-to-end project · in production · expanding
In short, for decision-makers
The problem
Each store has its own server and database, and one of them holds an incomplete copy of the other. To see how the month was going, management pulled reports from several ERP screens and built by hand a spreadsheet with target, actual and commission per business unit.
What LNData did
We reverse-engineered the ERP to find the real rule behind each number and brought both stores into a single base that updates itself. On top of it, dashboards with the view management already used: the four business units, target versus actual and the month’s forecast.
The result
The month shows up in near real time: how much each unit billed, how far it is from target and where the month should close. The numbers match the ERP and the closing spreadsheet, and margin is calculated item by item.
Southern SC
electrical supplies and lighting
2 stores
each with its own ERP server
4
business units tracked
since 2008
analytical history
~3M
records consolidated in the data warehouse
hourly
sales, items and service orders updated
~80
charts across 3 BI dashboards
to the cent
checked against the ERP’s own reports
Architecture
The platform reads each store’s database, loads the history month by month into an analytical data warehouse and delivers the result in BI dashboards with role-based access. The whole stack is containerized and runs on the client’s server.
Source systems
- Retail ERP (Firebird) · one database per store
- Management’s target spreadsheet
Data platform
Hourly and daily pipelines
Analytical data warehouse with a materialized revenue base
Client server · read-only on the ERP
Consumption
- BI dashboards · in the client’s visual identity
- Read-only access by role
ERP reverse engineering
The ERP’s data dictionary was empty. Before the first dashboard, the tables were mapped and every rule checked against the system’s own reports until the numbers matched.
Each store from its own database
One database holds a partial copy of the other. Each store is read from its own server, so the branch is never counted twice.
Out of the counter’s way
The ERP is read in read-only mode, without affecting the cash registers and POS: sales and services every hour; inventory, finance and cash every day, before the stores open.
Areas covered
The dashboards follow the four business units management already tracked: the two stores, field service and the motor repair shop.
Sales
Revenue by business unit
Stores and services split the way management tracks them, with item-level margin and ABC curves for products and customers.
Targets
The month at a glance
Target, actual, how much is left and the closing forecast per business unit, compared with the same month last year.
Salespeople
Performance by salesperson
Sales, returns, discounts and target attainment per salesperson, tracked day by day.
Services
Technicians and repair shop
Hours per technician against the bonus target, field services delivered and motors rebuilt in the shop.
Inventory
Idle stock and stockouts
Inventory value per store, products with no sales for months and stockout risk, product by product.
Finance · next step
Daily report and commissions
Receivables and payables, cash and payment methods are already in the base. Next: the daily financial report, today in a spreadsheet, and commission tracking.
Platform stack
AirflowClickHouseSupersetPythonPostgreSQLDocker
Your ERP doesn’t talk to your BI?
From the legacy system to the target dashboard — tell us what you need to solve and we’ll come back with scope, timeline and deliverables.