Reporting and business intelligence on a custom NZ ERP: building the layer most projects defer

One of the most consistent patterns in custom ERP projects is that reporting gets deferred. The business case focuses on the operational system — the ordering module, the inventory layer, the ERP integrations — and reporting is treated as something to add once those are working. By the time go-live arrives, the reporting scope has either been deferred indefinitely or compressed into a quick "connect Power BI to the database" task that produces something technically functional but practically unusable.

The problem with deferring reporting is not that it delays dashboards. It is that the operational data model — designed without the reporting use cases in view — often needs to be changed before reporting can be built correctly. And changing a data model in a system that is already live is significantly more expensive than getting it right during the original build.

Why standard BI connectors do not apply to a custom ERP

Power BI has certified connectors for Dynamics 365, SAP, NetSuite, MYOB Business, and a range of other packaged ERP systems. These connectors work because those vendors have defined, documented database schemas that Microsoft and its partners have mapped into semantic models. When you point the Dynamics 365 connector at a Business Central environment, Power BI already knows what the sales order table is called, which columns represent quantities and prices, and how the customer, product, and ledger entities relate to each other.

A custom ERP has none of that. The schema is bespoke — designed for the specific business's data model, operational workflows, and integration requirements. The tables are named for what they do in that system, not for what a BI vendor's connector expects. There is no pre-built semantic model to import and no vendor documentation to rely on.

Pointing Power BI directly at a custom ERP's operational database without a semantic model produces a list of tables. A business user building a sales report cannot work from raw tables — they cannot know that net revenue for a given period means summing the extended price column on invoice lines, filtered by document status, joined to the customer master via a debtor ID, while excluding credit notes flagged in a separate status field. That logic lives in the heads of the developers who built the system, not in the database structure itself.

What the semantic model layer actually is

The semantic model sits between the custom ERP's operational database and the BI tool. It does two things: it exposes the underlying tables as business-friendly dimensions and measures, and it encodes the business logic — the joins, the filters, the aggregation rules — that a business user would otherwise need to know in order to query the data correctly.

In practice, for a NZ business running a custom ERP on SQL Server, this is typically a Power BI tabular model (or an Analysis Services tabular model for larger deployments). The model defines: the date dimension that all time intelligence measures reference; the customer dimension sourced from the ERP debtor master; the product dimension from the item master; the sales fact table joining order lines to their headers, customers, and products; and the measures — Revenue, Margin, Volume, Units Sold — that encode the business logic in one place.

Once the semantic model exists, business users can build dashboards and run ad hoc analysis without writing SQL or knowing the underlying schema. The model is the stable, documented interface between the operational system and the people who need to understand what is happening in the business.

Two implementation approaches and when each fits

Direct semantic model on the operational database. Power BI (or Analysis Services) connects directly to the production ERP database. The semantic model is built on top of the operational schema using DirectQuery or a scheduled import. This approach is faster to implement — there is no separate data pipeline to build and maintain — and it is appropriate for smaller to mid-scale custom ERPs where:

• Transaction volumes are manageable (the analytical queries won't visibly affect write performance).
• The operational database is accessible for BI connections (no VPN or firewall restriction on analytical access).
• The reporting requirement is primarily current-state operational — "what happened today / this week" — rather than deep historical analysis.

The risk with this approach is coupling: when the development team changes the operational schema (adding a column, renaming a table, changing a relationship), the semantic model needs to be updated. Schema changes that break the model break all the reports built on it. Managing this requires treating the semantic model as a first-class deliverable with its own change control, not a one-time configuration task.

Reporting database with an ETL pipeline. An extract-transform-load process copies data from the operational ERP database into a separate reporting database designed for analysis — typically a star schema, where fact tables (sales, inventory movements, AR transactions) join to dimension tables (customer, product, date, rep, route). Power BI connects to the reporting database, not the operational system.

This approach is appropriate for larger deployments: multi-entity businesses (where data from multiple operating companies needs to be consolidated), high-volume transaction systems where analytical queries on the production database would affect operational performance, or businesses that need deep historical analysis spanning several years of transaction data. The reporting database insulates the operational system from analytical load and provides a stable schema for the semantic model even when the operational schema evolves.

The trade-off is infrastructure and maintenance overhead: there is now an ETL pipeline to monitor, schedule, and maintain alongside the ERP itself. For a single-entity mid-market NZ business with moderate transaction volumes, this overhead often does not earn its cost — the direct semantic model approach is simpler and sufficient.

What reporting actually needs to cover for NZ B2B operations

The reporting requirements for a NZ food distributor or B2B wholesale business have a recognisable shape. Understanding this shape early — during the discovery phase — allows the operational data model to be designed to support it.

Sales performance. Daily, weekly, and monthly sales by account, sales rep, delivery route, product group, and channel — compared against the same period in the prior year. This is the highest-priority management view in most NZ distribution operations. Building it correctly requires the pricing hierarchy to be resolved in the data model: the revenue figure must reflect the actual price charged per line (after trade pricing, customer-specific overrides, and quantity breaks), not the list price. Businesses running Accredo or CSB-System with complex pricing hierarchies find that the most common reporting failure is margin analysis based on list price rather than actual pricing — producing systematically incorrect margin figures for their highest-volume accounts.

Inventory and stock movement. Current stock by location and temperature zone (ambient, chilled, frozen), stock in transit, picking variance, and shrinkage. For businesses with MPI batch traceability requirements, the stock movement view needs to carry batch data from the operational system.

AR and debtor status. Debtor aging (current, 30, 60, 90+ days) with live balance from the ERP debtor master, credit limit utilisation, and overdue account flags. This view is typically used by the credit team daily. Getting it right requires a live or near-live data refresh from the ERP — a weekly ETL schedule is not sufficient for a view that drives credit hold decisions.

Delivery and fulfilment. On-time delivery rate by route, order-to-dispatch time, short-shipment rate by product and account. This view requires the dispatch confirmation data from the ERP or WMS to be captured and related to the original order — something that is straightforward to design in from the start and difficult to retrofit if the operational schema did not capture the relationship.

Why reporting requirements belong in the discovery phase

The discovery phase of a custom ERP build produces a documented scope — the data model design, integration architecture, workflow definitions, and functional boundaries. Adding "what reporting views does management need to run the business?" to the discovery scope costs very little in that phase. It produces two things: a list of key reports that determines whether the operational data model captures everything it needs to, and an early signal about whether the reporting layer requires a full reporting database or whether a direct semantic model will suffice.

The cost of getting this wrong is not a missing dashboard at go-live. It is discovering, six months after go-live, that the operational schema does not capture the relationship between dispatch batches and order lines — and that fixing it requires a data model change in a system that is already processing live transactions. That work is not expensive in absolute terms. It is expensive relative to what it would have cost during the original build.

The custom ERP build timeline guide covers the five phases of a custom ERP project and where each type of work belongs. The discovery phase guide covers what a well-run scoping engagement produces. For businesses deciding between building a custom system and implementing an off-the-shelf ERP, the custom ERP vs off-the-shelf guide covers the ownership economics and the decision logic. The ERP data migration guide covers the data side of the cutover — what goes into the new system and how to get it there cleanly. If you are building or planning a custom ERP and want to understand what the reporting layer looks like for your specific business environment, talk to the team — Zeabyte designs and builds custom ERP and integration systems for established NZ businesses, including the operational reporting layer that makes those systems useful to the people running the business.

Four failure modes to design out

1. Reports connected directly to operational tables with no semantic model. Business users cannot maintain them as the schema evolves, and the reports embed business logic (joins, filters, aggregation rules) in individual dashboards rather than in a single shared model. When the schema changes, every report breaks independently.
2. Reporting deferred until after go-live, then found to require schema changes. The operational data model was designed without the reporting use cases in view. Retrofitting the semantic model requires changes to the live system.
3. Margin reports that use list price rather than the actual pricing hierarchy. For NZ businesses running complex per-customer pricing — trade price levels, customer-specific overrides, quantity breaks — the margin figure in any report is only meaningful if it resolves from the same pricing logic the ERP uses to generate invoices. Reports that read list price from the item master produce margin figures that are systematically wrong for accounts with negotiated pricing.
4. No isolation between operational and analytical load. Complex dashboard queries running on the production operational database during business hours — picking runs, order creation, ERP sync processes — can affect transactional performance. In high-volume systems, this is not a theoretical concern.