Reporting, Analytics, and BI

Reports are what most users actually see. In a typical ERP project, reporting is 30–40% of the custom development effort and the source of most post-go-live complaints.

The reporting layers 

1. TRANSACTIONAL REPORTS     "Show me open items for customer X"
   Real-time, operational, from the live system

2. OPERATIONAL ANALYTICS     "Ageing by bucket, this month vs last"
   Near-real-time, aggregated, live system or replica

3. MANAGEMENT REPORTING      "P&L by profit centre, budget vs actual"
   Periodic, structured, often a dedicated ledger or cube

4. STRATEGIC / DATA SCIENCE  "Which customers will churn?"
   Historical, cross-source, warehouse or lakehouse

Different layers, different tools, different data stores. A common failure is trying to serve layer 4 from layer 1 — running heavy analytics against the live OLTP database and degrading transaction processing for everyone.

Standard reports every ERP needs

Finance

  • Trial balance (with drill-down to line items)
  • Balance sheet and P&L, actual vs budget vs prior year
  • GL account line items
  • AR/AP ageing
  • Customer/vendor open items and statements
  • Cash position and forecast
  • Fixed asset register and depreciation forecast
  • Tax reports (VAT return, withholding, EC sales list)
  • Cost centre actual vs plan
  • Profitability by product/customer/channel

Supply chain

  • Stock on hand by material/plant/batch
  • Stock valuation and inventory ageing
  • Slow-moving and obsolete
  • Open purchase orders and GR/IR ageing
  • Vendor performance (OTIF, price variance, quality)
  • MRP exception list
  • Consumption analysis

Sales

  • Open sales orders / backlog
  • Sales by product/customer/region/rep, with comparisons
  • Margin analysis
  • Delivery performance (OTIF)
  • Quotation conversion
  • Rejection reasons

Manufacturing

  • Production order status and cost analysis
  • Variance analysis by category
  • Capacity load
  • Scrap and yield
  • OEE

Cross-cutting

  • Document flow / traceability
  • Audit trail and change log reports
  • User activity and authorisation reports
  • Master data quality dashboards

Report architecture 

                    ┌──────────────────┐
   Live OLTP  ─────▶│ Transactional    │  document lists, open items, drill-down
                    │ reports          │
                    └──────────────────┘
        │ replication / CDC
        ▼
   ┌─────────────┐  ┌──────────────────┐
   │ Read replica│─▶│ Operational      │  dashboards, ageing, KPIs
   └─────────────┘  │ analytics        │
        │           └──────────────────┘
        │ ETL / ELT
        ▼
   ┌─────────────┐  ┌──────────────────┐
   │ Warehouse   │─▶│ Management BI    │  Power BI / Tableau / Looker
   │ (star schema)│ │ + data science   │
   └─────────────┘  └──────────────────┘

The star schema for ERP data 

                 dim_date
                     │
  dim_customer ──┐   │   ┌── dim_material
                 │   │   │
                 ▼   ▼   ▼
              ┌─────────────────┐
              │  fact_sales     │
              │  order_id       │
              │  date_key       │
              │  customer_key   │
              │  material_key   │
              │  quantity       │
              │  revenue        │
              │  cost           │
              │  discount       │
              │  margin         │
              └─────────────────┘
                 ▲   ▲   ▲
  dim_org ───────┘   │   └── dim_sales_rep
                dim_channel

Typical ERP fact tables: fact_gl_line, fact_sales, fact_purchase, fact_inventory_movement, fact_inventory_snapshot, fact_production, fact_headcount.

Slowly changing dimensions

A customer moves from the “North” region to “South” in June. Do last year’s sales move with them?

Type Behaviour
Type 1 Overwrite. History changes. Simple, and usually wrong for org data
Type 2 New dimension row with validity dates. History preserved. The right default for ERP
Type 3 Keep the previous value in a second column. Limited

Type 2 is what makes “sales by region, as it was reported at the time” possible. It’s also what lets you say “and here it is restated under the current structure” — both questions are legitimate, and only Type 2 answers both.

Grain

Decide and document the grain of every fact table: “one row per sales order line per day” or “one row per journal line”. Mixing grains in one fact table is the most common data warehouse defect and produces double-counted measures that nobody can explain.

Real-time vs batch

Need Latency Approach
“Can I ship this order?” Instant Live OLTP
“What’s my AR ageing?” Minutes Read replica
“Yesterday’s sales by region” Hours Nightly ETL
“3-year trend analysis” Days Warehouse

Most “real-time dashboard” requirements are not real. Ask what decision is made and how often. A daily-refreshed dashboard costs a fraction of a real-time one and usually serves the decision identically. Push back with that question, not with a refusal.

Self-service vs governed

  Self-service Governed
Who builds Business users IT/BI team
Speed Fast Slow
Consistency Six versions of “revenue” One definition
Governance Weak Strong
Best for Exploration, ad hoc Statutory, board, KPIs

The workable middle: a governed semantic layer (certified datasets with agreed definitions and calculations) that self-service tools consume. Users get freedom over the visualisation; the numbers stay consistent.

The classic failure: three departments present three different revenue figures to the board, all technically correct, all computed differently. The fix is a semantic layer with a published definition, not more dashboards.

Performance of ERP reports

Reports are the #1 cause of ERP performance complaints. The usual culprits:

Problem Fix
Unbounded selection Mandatory date range and org filter, with sensible defaults
SELECT * on wide tables Project only needed columns
Filtering in application code Push predicates to the database
N+1 in the loop building the output Batch fetch, single join
No aggregate; re-summing millions of rows Pre-aggregate or use a columnar store
Running against production OLTP Move to a replica
No pagination Paginate; stream large exports to a file
Joining 15 tables live Build a reporting view or a materialised table

The single most effective control: force a date range on every report. Users will select “all dates” every time otherwise, then complain the system is slow.

Report design principles

  1. Answer a question. A report with 60 columns answers nothing.
  2. Summary first, detail on demand. Drill-down, not a wall of rows.
  3. Variance columns, not just values. Actual vs budget vs prior year, with the delta and the percentage.
  4. Consistent sign conventions. Decide once whether favourable is positive.
  5. Show the parameters and the run timestamp on the output. “Which filters produced this?” is asked constantly.
  6. Every figure must be traceable to source documents. If a user can’t drill from a total to the journal lines behind it, they won’t trust the number — and they’ll rebuild it in Excel.
  7. Export to Excel. Users will do this anyway. Make it clean, formatted, and with the parameters included, so you at least control the starting point.

The Excel problem

Every ERP implementation discovers that critical business processes run on spreadsheets. Fighting this outright fails. What works:

  • Understand why — usually the report doesn’t exist, or is too slow, or lacks a needed dimension
  • Provide clean exports so the spreadsheet starts from good data
  • Replace the highest-risk spreadsheets first — those doing calculations that feed the ledger
  • Accept spreadsheets for genuine ad-hoc analysis; that’s a legitimate use

Spreadsheets that feed financial statements are an audit finding (end-user computing risk). Those specifically need replacing, and that’s the argument that gets funding.

Embedded analytics

Modern ERPs push analytics into the transaction screen:

  • KPI tiles on the home page
  • A customer’s payment history shown on the order entry screen
  • Stock trend beside the material master
  • Budget remaining shown at the moment of requisition entry

This is where analytics has the most impact — at the point of decision, not in a monthly report nobody reads. If you build one thing in this space, build the in-context indicator.

Tools

Tool Fit
The ERP’s native reporting Operational reports with drill-down to documents
Power BI Dominant in the Microsoft world; strong and affordable
Tableau Strong visualisation, exploration
Qlik Associative model, strong in mid-market ERP
SAP Analytics Cloud / Datasphere SAP-native planning + BI
Looker / Metabase / Superset Modern semantic layer, SQL-first
dbt + warehouse The modern transformation stack

Developer notes

  1. Never run heavy analytics on the OLTP instance.
  2. Every report has a bounded selection.
  3. Drill-down to source documents, or users won’t trust it.
  4. Type 2 dimensions for anything organisational.
  5. Document the grain and the definitions of every fact and measure.
  6. Show parameters and timestamp on every output.
  7. Design for export, because it will be exported.
  8. Measure report usage. Most reports built during an implementation are never run again — retire them and reclaim the maintenance.

Next