Business intelligence platform

Warc runs across several African countries on an Odoo ERP, two custom mobile apps, an AppSheet operation in Uganda, and a scatter of third-party sources for exchange rates and directory data. Each answered questions on its own terms. The same figure came out differently depending on who pulled it and from where, so every month closed with a reconciliation exercise and an argument about whose number was right — before anyone could get to the decision the number was for.

The shortage was never data. It was that no figure could be used without first being checked by hand, which put a ceiling on how fast the business could be run.

There was a plumbing problem underneath it. Neither the Odoo.sh database nor the Farmer App's PostgreSQL accepts inbound connections from the internet, so before any modelling could start there had to be a route from those databases into the Microsoft cloud that survives a reboot without someone logging in to restart it.

A dedicated AWS EC2 Windows Server is the bridge. NSSM runs the SSH tunnel to Odoo.sh as a Windows service, forwarding PostgreSQL to localhost:5433 so it comes back on its own after a restart; the PostgreSQL ODBC driver connects over it, and the On-Premises Data Gateway sits on the same box orchestrating everything the cloud asks for. A Power Automate Desktop bot on the same instance pulls daily and monthly Oanda rates into Dataverse, and historical Kedebah records held in S3 are read through Amazon Athena rather than moved.

Staging is one Power BI Dataflow per source system, each with its own schedule. Decoupling the cadences means a slow ERP extract does not hold up the mobile app data, and a source that breaks fails on its own rather than taking the refresh down with it.

The Medallion architecture is built inside Power Query rather than bolted on beside it. Bronze queries pull each dataflow untransformed — no type changes, no cleanup — so an upstream schema change lands in exactly one place. Silver, marked with an (S) suffix, does the type casting and the business logic: Table.Combine unifies AppSheet, Farmer App and Kedebah into a single conformed view, and coalesce rules settle the conflicting timestamp fields the three apps disagree on. Gold is deliberately thin — dim_farmers, dim_hubs, dim_products and fct_transactions are references to the Silver expressions and nothing more, which is what keeps the star schema fast and keeps reporting off the transformation logic.

Governance is enforced rather than documented. Row-Level Security means the Ghana and Uganda teams see only their own rows out of the same model. Data and report workspaces are kept apart on a thin-report architecture, so backend schema work and report work deploy independently instead of one breaking the other. Six workspaces are promoted dev → test → prod through Power BI Deployment Pipelines, with an Azure DevOps refresh.yml driving dataflow and model refresh — no schema change reaches production without going through it.

Infrastructure, semantic model and the first tier of operational dashboards are in. Produce inventory, purchase transactions consolidated across the Farmer App and Kedebah in both Ghana and Uganda, and live exchange rates all run off the governed model with Row-Level Security enforced.

The reconciliation exercise is gone, and with it the argument. Every business unit reads the same semantic layer, so a disagreement about a figure is now a disagreement about a definition — which is a thing that can be settled once and written down.

It is not finished. Sales transaction consolidation and the sales-input inventory dashboard are in progress. Ahead of that sit the PO tracker and offtaker distribution KPIs, hub performance, an executive dashboard, an AI-written weekly business briefing, QuickBooks integration and real-time Odoo inventory sync — all of which are additions to the same model rather than another system beside it, which was the point of building it this way.