Skip to main content

Business Intelligence in the ERP Stack

Was ist Business Intelligence?

Business Intelligence (BI) is the layer of software, data structures and operating practice that turns the transactional records inside an ERP system into the management information that decision-makers use day to day. In the US mid-market, BI is no longer the discrete “reporting project” it was in the early 2000s — it is a continuously evolving capability that spans the ERP itself, a separate data warehouse or lakehouse, a semantic-modeling layer, one or more visualization tools, and the data literacy of the people who consume the output.

This guide covers what BI is, how it sits inside or alongside an ERP, the architectural distinction between classic OLAP and cloud-native data warehouses, the role of semantic layers like dbt and Cube, the vendor landscape for visualization tools (Power BI, Tableau, Qlik, Looker), US-specific BI considerations, and the practical question of when BI investment pays off for a mid-market company.

What Business Intelligence covers

BI as a discipline covers four layers, each of which has its own market and vendor landscape:

  • Data integration: extracting data from operational systems (ERP, CRM, e-commerce, MES, finance, HR) into a central store. Tools: classic ETL (Informatica, Talend), modern ELT (Fivetran, Stitch, Airbyte), event streaming (Kafka, Confluent).
  • Data storage and modeling: the central store itself — historically an on-premises data warehouse (Microsoft SQL Server, Oracle, Teradata), now increasingly cloud-native (Snowflake, Google BigQuery, Databricks, Amazon Redshift). The modeling layer translates raw operational tables into the dimensional structures (facts, dimensions) that BI consumers understand.
  • Semantic and metrics layers: a relatively new category that defines business metrics centrally (revenue, gross margin, customer lifetime value) so that every BI tool reading from it returns the same number. Tools: dbt for transformations, Cube and AtScale for live semantic layers.
  • Visualization and consumption: the dashboards, reports and self-service analytics that end users actually see. Tools: Microsoft Power BI, Tableau, Qlik Sense, Looker, MicroStrategy, plus operational analytics tools like Sigma Computing and Mode.

A mature BI stack rarely uses one vendor for all four layers. The pragmatic mid-market pattern is Fivetran or Airbyte for integration, Snowflake or BigQuery for storage, dbt for transformations and semantic modeling, and Power BI or Tableau for visualization.

BI in the ERP context

Every modern ERP system ships with embedded reporting. SAP S/4HANA includes embedded analytics through CDS views and Fiori; Microsoft Dynamics 365 includes Power BI integration and the Common Data Model; Oracle NetSuite has SuiteAnalytics; Sage Intacct has built-in dashboards and dimensional reporting. For the most common operational reports — open invoices, sales by customer, inventory positions, purchase commitments — the embedded reporting is sufficient and the right answer is to stay inside the ERP.

The case for a separate BI stack arises when one or more of the following conditions hold. Cross-system reporting — combining ERP data with CRM (Salesforce), e-commerce (Shopify, Magento), HR (Workday, Bamboo HR), marketing (HubSpot, Google Analytics) — cannot be done inside the ERP alone. Historical trend analysis over multiple years stresses the operational ERP database; offloading history to a data warehouse improves both operational performance and analytical depth. Self-service analytics for business users who want to slice and dice without IT involvement requires a separate semantic layer that the ERP's embedded tooling does not provide. Group-level consolidation across multiple ERP instances (post-acquisition heterogeneity, two-tier ERP strategies) needs a layer above the operational systems.

For a mid-market company on a single ERP instance with limited cross-system reporting needs, embedded ERP analytics plus an Excel-based finance reporting layer is often sufficient. For a group operating two or more ERPs, with e-commerce and CRM in the mix, a separate BI stack is essentially mandatory by the time revenue passes $50–100 million.

OLAP vs OLTP and the cloud data warehouse

The architectural distinction at the heart of BI is between OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing). OLTP systems — including every ERP — are optimized for many small, fast transactions: posting an invoice, releasing a production order, reserving stock for a sales order. The data is stored in normalized relational tables that minimize duplication but require many joins for analytical queries.

OLAP systems are optimized for the opposite pattern: relatively few queries that scan huge volumes of historical data, aggregated along multiple dimensions (time, geography, product, customer). The data is stored in denormalized dimensional structures (star schemas, snowflake schemas, or modern column-oriented formats) that trade storage efficiency for query speed.

The classic on-premises BI architecture put a separate OLAP cube (Microsoft Analysis Services, Oracle Essbase) between the ERP and the reporting tool. The cube was rebuilt overnight from the operational data, which limited reporting to T-1 (yesterday) freshness and required substantial cube-design discipline. From 2015 onwards, the architecture has shifted to cloud-native data warehouses — Snowflake, Google BigQuery, Amazon Redshift, Azure Synapse, Databricks — that decouple storage from compute and run analytical queries directly against denormalized columnar tables without a pre-built cube.

The shift matters for US buyers in two ways. First, the cloud data warehouse dramatically reduces time-to-value: a Snowflake plus dbt plus Power BI stack can be operational in weeks rather than the months a classic on-premises warehouse project required. Second, compliance and data residency become real considerations for regulated workloads — the major platforms hold SOC 2 Type II and ISO 27001 certifications, support HIPAA workloads under a signed business associate agreement (BAA) for healthcare data, and let you pin storage to specific US regions for organizations with state-level or federal residency requirements; reviewing the vendor's security attestations is part of every cloud data warehouse selection.

Semantic and metrics layers: dbt and Cube

One of the structural problems of BI for the past 30 years has been the “same metric, different numbers” problem: marketing reports revenue one way, finance reports it another way, sales reports it a third way. The semantic layer solves this by defining business metrics centrally, once, in a tool that every downstream BI consumer reads from.

Two categories of tool dominate this layer today:

  • dbt (data build tool): an open-source transformation framework that defines models in SQL with dependencies, tests and documentation. The output is a set of clean, tested tables in the data warehouse that downstream tools can query. dbt is the de-facto standard for the “T” (transform) step in modern ELT architectures. The dbt Semantic Layer (introduced 2023) adds a metrics definition layer on top of the transformation models.
  • Cube: a headless BI / semantic layer that defines measures, dimensions and pre-aggregations once, and exposes them through SQL, REST and GraphQL APIs to any downstream tool. Particularly useful when multiple BI tools (Power BI for finance, Looker for product, Tableau for ad-hoc) need to agree on metric definitions.

In the US mid-market, dbt has gained substantial adoption since 2021 alongside the move to Snowflake and BigQuery. The semantic-layer category is younger and adoption is still concentrated in tech-forward companies, but the direction of travel is clear: metric definitions are moving out of individual BI tools and into a central, version-controlled, code-reviewed layer.

The BI visualization tool landscape

Four vendors dominate the visualization layer in the US mid-market, with a handful of credible specialists alongside them.

  • Microsoft Power BI: the dominant choice in the US by user count, particularly in companies already on Microsoft 365 and Azure. Strengths: tight Excel integration, low entry-point pricing (Power BI Pro at roughly $14 per user per month as of 2025), deep ecosystem of certified connectors. Weaknesses: governance becomes complex at enterprise scale; the “Power BI everywhere” pattern can produce hundreds of unmanaged reports.
  • Tableau (Salesforce): the historical visualization leader, particularly strong for exploratory analytics and complex visualizations. Strong in companies that have invested in analyst skills. Pricing materially higher than Power BI; the Salesforce acquisition (2019) has tightened integration with Salesforce CRM but slowed feature innovation in some areas.
  • Qlik Sense / QlikView: a long-established platform with a strong presence in manufacturing and wholesale distribution. The associative engine handles complex multi-dimensional analysis differently from the SQL-on-warehouse model that competitors use, which is a strength for some use cases and a complication for others.
  • Looker (Google): cloud-native BI built around LookML, a semantic-modeling language. Strong fit for tech-forward companies on BigQuery. Pricing has moved upmarket since the Google acquisition (2019), making it less attractive at the lower end of the mid-market.

Specialist vendors fill credible niches: MicroStrategy for large enterprises with complex governance needs, Sigma Computing for spreadsheet-centric analysts on a cloud data warehouse, Hex and Mode for analytical notebook workflows, Metabase and Apache Superset for the open-source segment. SAP Analytics Cloud is the natural choice for SAP-centric environments; its integration with S/4HANA and BW/4HANA is deep.

US-specific BI considerations

Three factors regularly shape BI architecture decisions in the US mid-market. Compliance and data governance is the most material: any BI stack that processes personal data (customer master, employee data) must satisfy the relevant security and privacy reviews. For healthcare data, the data warehouse and pipeline must be covered by a HIPAA business associate agreement; for financial data at public companies, Sarbanes-Oxley (SOX) controls over the reporting process apply; and state privacy laws such as the California Consumer Privacy Act (CCPA/CPRA) and similar statutes in Virginia, Colorado and other states increasingly govern how consumer data may be processed and surfaced. Most cloud-DWH and BI vendors hold SOC 2 Type II attestations and support US region pinning, so the practical work is reviewing those attestations and mapping data flows rather than navigating cross-border transfer rules.

Second, employee-data and monitoring sensitivities shape any BI tool that surfaces individual performance data — sales rep performance, helpdesk-agent metrics, time-tracking analytics. While the US has no statutory works-council regime, employee-monitoring laws vary by state, and in unionized workplaces a collective-bargaining agreement may constrain which metrics are visible to whom and how they are aggregated; BI tools that handle row-level security and aggregation rules cleanly have an advantage. Third, integration with US finance and tax practice matters more than international vendors sometimes expect. A US GAAP chart of accounts, ASC 606 revenue recognition, multi-state sales-tax obligations under economic nexus rules (post-South Dakota v. Wayfair, generally a $100,000 sales threshold per state), 1099 vendor reporting and fixed-asset depreciation schedules all need representation in the data model; pre-built finance content from a vendor that has invested in US localization saves substantial modeling work.

BI inside the ERP suite vs a separate stack

The choice between using the ERP's embedded BI capability and building a separate stack has a clear decision logic. Stay with the embedded ERP analytics when the reporting needs are predominantly operational (today's open orders, this month's margin per product, current inventory positions), when there is a single ERP instance, and when cross-system reporting needs are modest. SAP Analytics Cloud + S/4HANA, Power BI + Dynamics 365 Business Central, SuiteAnalytics + NetSuite all cover this scope well and avoid the cost of operating a separate stack.

Build a separate BI stack (cloud data warehouse plus modeling plus visualization) when cross-system reporting becomes the norm, when historical depth (multi-year trends) is needed, when multiple ERP instances exist post-M&A, or when self-service analytics for business users without IT involvement is a strategic priority. The economic threshold is typically in the $50–100 million revenue range, with significant variation depending on business model: e-commerce-led companies cross it earlier (lots of cross-system reporting needed), single-ERP service businesses cross it later.

A hybrid model is increasingly common: ERP analytics for operational reporting (fast feedback loops, real-time dashboards) plus a cloud data warehouse for cross-system analytics and management reporting (typically refreshed daily). This pattern lets the ERP team and the analytics team move at different paces and reduces the burden on the ERP's operational database.

When BI is worth the investment in the Mid-Market

The honest answer to “when does BI pay off for a mid-market company” is: when the cost of decisions made without it exceeds the cost of operating the stack. That is rarely the case for the smallest companies, where Excel plus the ERP's standard reports is sufficient. It is almost always the case for groups with multiple ERP instances, multi-channel revenue, or growth ambitions that depend on understanding customer behavior at a granular level.

Three signals that a mid-market company is ready to invest in a separate BI stack:

  • The same business question is answered three different ways by three different teams. This is the “single source of truth” problem. A semantic layer and a central data warehouse are the structural answer.
  • Excel files are emailed around the company as the de-facto reporting system. This pattern hides errors, prevents version control and breaks when the analyst who built the workbook leaves. A managed BI stack with governed access replaces it.
  • Strategic decisions are being deferred because the data is not available in time. When the M&A pipeline, pricing changes or customer-acquisition investment depends on data that takes weeks to produce, the operational cost of slow BI exceeds the license cost of a faster stack.

The typical total cost of a mid-market BI stack — Snowflake or BigQuery, dbt, Power BI or Tableau, plus 1 to 2 FTE for analytics engineering and BI development — runs to roughly $300,000 to $600,000 per year inclusive of cloud spend, licenses and staff. For companies above $100 million revenue, this is typically a sound investment; below that, the case has to be made carefully against the alternative of better-trained Excel users and improved ERP standard reporting.

Related Topics