Combining SAP and Non-SAP Data in Power BI: Architecture Patterns for BI Teams

Combining SAP and non-SAP data in Power BI sounds like a modeling task, but the decisive choices happen before anyone draws a relationship. SAP BW and SAP HANA can expose measures, hierarchies, variables, and security rules that do not behave like ordinary relational tables. Salesforce, SQL Server, and flat files bring different keys, refresh cycles, and data ownership. A model that ignores those differences may refresh successfully and still return slow or misleading totals. The architecture therefore has to settle three questions early: where data is joined, which system owns each business definition, and how fresh the combined result must be.

For a BI team, the practical goal is a model where an SAP billing document can be analyzed beside a Salesforce opportunity, a budget file, or operational data without losing the meaning of revenue, customer, currency, and time. Sometimes Power BI can perform that integration directly. In other cases, a warehouse, lakehouse, BW model, or HANA calculation view needs to do the work first. The correct boundary depends on SAP connection mode, source performance, security, and the amount of reuse expected from the combined data.

What Is SAP and Non-SAP Data Integration in Power BI?

SAP and non-SAP data integration in Power BI is the process of presenting facts from SAP systems and external sources through one governed semantic model.

The external source could be Salesforce, SQL Server, a planning application, a web service, or a file maintained by finance. The SAP side may come from BW queries, HANA calculation views, S/4HANA extracts, or staged tables. Power BI must bring those inputs to compatible grains and relate them through shared dimensions such as customer, product, company, fiscal period, sales territory, or project. A successful design preserves source semantics while giving report authors one coherent field list.

There are three broad integration locations. Report-level integration combines sources inside a Power BI semantic model. Shared staging copies data into a warehouse or lakehouse and performs conformance before Power BI connects. Source-side integration places external data into SAP BW or HANA so that SAP logic remains the analytical authority. Each location can work, but each moves performance, governance, and maintenance responsibilities to a different team.

Architecture Components That Control a Mixed-Source Power BI Model

The architecture becomes easier to reason about when the SAP connection, integration layer, semantic model, and gateway are treated as separate components.

SAP BW and SAP HANA Connection Semantics

SAP BW is a multidimensional source. In DirectQuery, Power BI sends visual queries back to BW, where BW evaluates its measures and applies source behavior. The approach can preserve SSO and source-defined calculations, but Power Query shaping is restricted and interactive performance depends on BW answering aggregate queries within seconds. Import mode offers more modeling freedom and faster report interaction, although data currency then depends on refresh.

SAP HANA DirectQuery has two distinct behaviors. The default multidimensional mode exposes one analytic or calculation view and keeps HANA measures and hierarchies authoritative. It does not support defining relationships or combining multiple HANA views in the same report. Relational mode permits multiple views, calculated columns, relationships, and additional sources. That flexibility introduces a calculation risk: averages, ratios, counters, and other non-additive measures can be reaggregated incorrectly if the HANA view and Power BI model use different grains.

Shared Dimensions and Conformed Keys

A combined model needs stable join keys before it needs DAX. SAP customer numbers may include leading zeros, Salesforce accounts may represent global parents, and a spreadsheet may use a local sales name. Product codes, company structures, currencies, and fiscal calendars often have similar conflicts. A conformed dimension resolves these identities and records the mapping rule, including effective dates where ownership or hierarchy changes over time.

Consider an order-to-cash model. SAP billing facts may be recorded at invoice-item grain while Salesforce opportunities sit at opportunity-product grain. Joining the two fact tables directly invites duplication. A safer design connects both to shared customer, product, sales organization, and date dimensions, then uses documented bridge tables for any many-to-many mappings. The model can compare pipeline and billed revenue without pretending that an opportunity line and an invoice line are the same event.

For a deeper explanation of facts, dimensions, and grain, see Dimensional Data Modeling: Facts, Dimensions, and Star Schema.

Gateway and Refresh Infrastructure

On-premises SAP sources normally require a gateway for scheduled import refresh or DirectQuery. The gateway machine needs the same required SAP client components or drivers used by report authors. A semantic model that merges an on-premises source with a cloud source can also route the cloud access through the gateway, depending on how the mashup is built and configured. Every source, credential, privacy level, and server name must align between Desktop and the service.

The first architecture flow is direct: SAP BW or HANA → enterprise gateway → Power BI semantic model → report.

The staged flow adds a durable integration boundary: SAP plus Salesforce, SQL, or files → ingestion pipelines → conformed warehouse or lakehouse → Power BI semantic model → report.

Integration Methods for SAP and External Data

Power BI teams usually choose among four patterns, and the useful comparison is about operational fit rather than feature count.

Architecture patternBest fitMain constraint
Single Import modelModerate volumes and predictable refresh windowsRefresh duration and copied-data governance
HANA relational DirectQuery with other sourcesHANA views that behave relationally and need low latencyNon-additive measures and cross-source query cost
Shared warehouse or Fabric lakehouseReusable enterprise data with several consumersPipeline ownership and added platform operations
SAP-side consolidation in BW or HANASAP measures, variables, hierarchies, or security must remain authoritativeSAP development capacity and external-data ingestion effort

Single Import Model in Power BI

An Import model is often the fastest route to a dependable result. Power Query extracts SAP and external data, standardizes keys, and loads all tables into one in-memory source group. Relationships are regular, report interaction is fast, and the team can use the full modeling surface. Incremental refresh can reduce processing for large fact tables when date filters reach the source.

This pattern works well for daily sales reporting, monthly finance packs, and departmental models with bounded history. It becomes less attractive when refresh windows exceed service limits, policy prohibits persisted SAP copies, or consumers need source-enforced security. Large full extracts also compete with production SAP workloads. Filter early, select only required columns, validate query folding where the connector supports it, and schedule refresh outside peak operational periods.

HANA Relational DirectQuery With Imported Data

Treating HANA as a relational source opens a narrow but useful mixed-source pattern. A team can keep a large HANA fact table in DirectQuery, import small external tables, and relate them inside Power BI. Queries that cross source groups use limited relationships, so Power BI may materialize filter values and send them to the remote source. Performance drops quickly when join keys have high cardinality or visuals group by many external values.

A workable example is a HANA sales fact joined to a small imported target table by month and region. The join columns are compact, the grain is explicit, and users need current actuals with periodically updated targets. A customer-level join across millions of accounts is a different proposition. It increases query payload, exposes key-quality problems, and deserves a staged design or source-side consolidation.

Shared Staging in Fabric or an Enterprise Warehouse

Shared staging is the strongest general pattern when several reports need the same SAP and non-SAP definitions. Fabric Data Factory can ingest SAP HANA through Dataflow Gen2, pipelines, or copy jobs, while SAP BW Open Hub offers a controlled route for distributing BW data downstream. Salesforce, SQL Server, and file feeds land beside the SAP data. Transformation logic then produces conformed customer, product, organization, currency, and date dimensions before Power BI connects.

A bronze, silver, and gold layout is helpful when it reflects real responsibilities. Bronze preserves the extract and supports traceability. Silver standardizes types, keys, and source-specific codes. Gold exposes dimensional facts and shared dimensions at reporting grain. The semantic model remains focused on measures, relationships, and user-facing terminology instead of becoming the only place where enterprise identity resolution exists.

The architectural payoff grows with reuse. One customer crosswalk can serve sales, finance, and service models. Pipeline monitoring can detect schema drift before dozens of reports fail. Data engineers can tune ingestion independently from BI authors, and the Power BI team can test a stable presentation layer. This separation costs more to operate, so a one-off departmental report may not justify it.

To compare live access with staged copies, read Live Data Connectivity vs Data Replication for BI Teams.

SAP-Side Consolidation

Some integrations belong in HANA or BW. A HANA calculation view can combine tables and calculation views, define measures, and control aggregation behavior. BW can integrate external sources or distribute governed data through Open Hub. Keeping the join near SAP is valuable when a report depends on BW variables, HANA hierarchies, currency conversion, or source-authorized measures.

This route also reduces cross-source work at report time. Power BI receives one curated SAP-facing model, so visual queries do not have to coordinate multiple remote systems. The cost appears in delivery ownership. SAP teams must ingest and maintain the external data, and BI changes may enter a slower governed release process.

Context-Specific Usage for Salesforce, SQL Server, and Files

The non-SAP source changes the architecture because each source brings a different key strategy and operational profile.

Salesforce integration usually hinges on account identity and time. One Salesforce account can map to several SAP sold-to or bill-to customers, while corporate hierarchies change after territory realignments or acquisitions. Build an explicit account-to-customer bridge with effective dates and mapping status. For teams that need SAP extraction into a governed Power BI pipeline, the Power BI Connector for SAP can serve as the SAP access component while identity matching remains in the integration layer.

SQL Server is often easier because it is relational and usually supports folding, indexing, and source-side views. It can act as an external operational source or as the staging platform itself. Push large filters and transformations to SQL, align data types before relationships are created, and keep high-cardinality cross-source joins out of DirectQuery visuals. When both SAP extracts and non-SAP facts land in SQL, Power BI sees one source group and gains simpler relationship behavior.

Flat files deserve stricter controls than their size suggests. A finance target workbook may have no durable keys, loose date formats, silent column changes, and manual corrections. Land the file in a controlled folder, validate its schema, preserve the original version, and assign an owner. The semantic model should consume a validated table, not an analyst’s latest desktop copy.

Common Challenges in Mixed SAP Power BI Architectures

Most failures appear at the boundaries between systems, where familiar modeling shortcuts stop behaving predictably.

Limited Relationships and Missing Members

Cross-source relationships in a composite model are limited relationships. They are evaluated at query time, and unmatched values can disappear from grouped results because of inner-join behavior. A target for a future period may vanish when no matching SAP transaction exists, even though the target total still contributes elsewhere. Reconcile unmatched keys explicitly and test subtotals, blank members, and future-dated records.

Aggregation Errors From SAP Measures

SAP measures can carry aggregation rules that ordinary relational columns do not. A HANA average, ratio, or distinct count may already be evaluated at the view grain. Importing or querying the result as a relational column and then summing it in Power BI can produce a credible but wrong number. Record the additive behavior of every measure, recreate calculations only when the base facts are available, and compare results with the authoritative SAP query at several filter levels.

Refresh and Gateway Contention

A mixed-source model can fail even when every connection works independently. Privacy levels may block a merge, a gateway may lack a cloud-source mapping, or SAP imports may consume the same gateway resources needed by DirectQuery users. Schema changes in a workbook or API can break the mashup after deployment. Separate heavy Import refresh from latency-sensitive DirectQuery traffic where practical, monitor refresh history, and keep gateway components current.

Security Across Copied and Live Data

DirectQuery with SSO can preserve SAP authorization, while imported external data follows Power BI security. Combining the two requires a deliberate security model. A local role does not automatically reproduce every rule enforced by a remote model, and copied data may fall under different retention obligations. Test representative identities against detail pages, totals, exports, and drillthrough. Security validation belongs in acceptance testing, alongside numerical reconciliation.

Best Practices for Reliable SAP and Non-SAP Models

Reliable delivery comes from a small set of controls applied before the model reaches production.

  • Define the grain and business owner for every fact table.
  • Maintain conformed keys and log all unmapped records.
  • Keep transformations close to the source or shared staging layer when they are expensive or reusable.
  • Reconcile measures against SAP at total, period, organization, and document levels.
  • Load-test DirectQuery with realistic user concurrency and visual interactions.
  • Monitor refresh duration, gateway health, schema changes, and rejected joins.
  • Promote pipelines and semantic models through development, test, and production environments.

Start with a thin vertical slice. One business process, one SAP fact, one external fact, and two or three conformed dimensions expose most architectural risks. Test refresh, security, totals, and performance before adding history and more domains. This sequence gives the team evidence about the design while changes are still inexpensive.

Real-World Scenarios for Combined SAP Analytics

Business scenarios clarify when a direct model is sufficient and when staging earns its cost.

Salesforce Pipeline Compared With SAP Billing

A revenue operations team wants pipeline, bookings, shipments, and invoices by customer and product. Salesforce owns opportunity stages and expected close dates. SAP owns delivery and billing documents. A shared staging layer is usually the durable choice because account-to-customer mappings, product crosswalks, currency conversion, and slowly changing sales territories will be reused across several metrics.

Financial Close With External Forecasts

Finance needs SAP actuals beside forecasts maintained in a planning tool or controlled workbook. Monthly grain, a modest number of entities, and scheduled reporting often favor Import. The team can load SAP actuals, validated forecasts, account mappings, and a fiscal calendar into one model. Reconciliation to the general ledger and file-version controls matter more than low latency.

Operational Monitoring With SQL Telemetry

An operations team compares SAP production orders or inventory with machine events stored in SQL Server. Current conditions matter, but the required latency may differ by measure. A staged warehouse with frequent incremental loads can support most analysis. DirectQuery may be reserved for a narrow current-state page after source response time and gateway capacity have been tested under concurrency.

How to Choose an Architecture for SAP and Non-SAP Data

The final choice should follow the hardest constraint in the business requirement.

Choose a single Import model when refresh latency is acceptable, data volume is controlled, and one BI team owns the result. Consider HANA relational DirectQuery with imported dimensions only when the HANA view is safe to treat relationally, join keys are compact, and interactive tests meet the response target. Choose shared staging when data will support several reports, mappings need stewardship, history must be preserved, or source combinations extend beyond one team. Keep consolidation in BW or HANA when SAP calculations, variables, hierarchies, and source security must remain authoritative.

Run the decision in order. First, determine whether policy permits copying SAP data and whether users require SSO. Second, set a measurable freshness target instead of asking for real time by default. Third, test whether the SAP source can sustain expected query concurrency. Fourth, examine grain and key quality across systems. Finally, decide who will own pipelines, mappings, semantic measures, gateway operations, and production support. A design without named owners will degrade even if its first release performs well.

The cleanest Power BI model is often the result of integration work performed elsewhere. Report-level combination remains useful for bounded problems, especially with Import. Enterprise reporting across SAP, Salesforce, SQL, and files usually benefits from a shared conformance layer where identities, timing, and calculations can be tested once. Architecture succeeds when every join has a business meaning, every measure has an authority, and every refresh has an operator.

M
Author
Metrica Software Team
Share