Power BI Data Integration: How It Works and How to Choose the Right Approach

Power BI data integration is the work of connecting Power BI to your business systems, cleaning and combining what comes out of them, and loading the result into one semantic model that every report reads from. When it is set up well, the CFO and the head of sales quote the same revenue figure in the Monday meeting. When it is not, the dashboards still look polished, and nobody trusts them.

Leadership teams usually spot the symptoms long before anyone calls it an integration problem. “Why doesn’t this match the ERP?” “Which of these two revenue numbers is right?” “Why is Friday’s report still showing Tuesday?” The charts are rarely at fault. The trouble sits in the plumbing underneath:

• Sales, finance, and operations data live in separate systems that never meet.
• Analysts copy figures between spreadsheets by hand before every board pack.
• Each department calculates the same KPI its own way.
• A refresh fails overnight and no one notices until a director asks.

Power BI can fix a good share of this by itself, since it ships with hundreds of connectors, a capable data preparation engine, and scheduled refresh. The bigger decision is where your integration logic should live. It can sit inside Power BI, or in a separate data integration layer that Power BI simply reads. Below we walk through how each piece works, how to pick a connection method, and the signs that Power BI has been asked to carry more than it was built for.

What Is Power BI Data Integration?

It covers three jobs: pulling data out of source systems, shaping it so records from different systems line up, and storing it in a model that reports can query quickly. Power BI gives each job to a different component, and knowing which one owns what makes troubleshooting far faster.

Power Query: Connecting and Preparing Data

Power Query connects to a source and records every cleaning step as a repeatable recipe: drop duplicates, fix a date column that arrived as text, merge the customer list from the CRM with the one from billing. On each refresh, Power BI replays the recipe against fresh data, so nobody repeats the cleanup by hand.

The Semantic Model: One Version of the Numbers

Prepared tables land in a semantic model built in Power BI Desktop. Here they are related to each other, and business rules such as “net revenue” or “active customer” get written once, as DAX measures. Ten reports built on one shared model show one figure; ten reports with their own models tend to show ten. Our breakdown of Power BI’s technological layers goes deeper into how these pieces stack.

Power BI Service and the Gateway: Sharing and Refreshing

The Power BI Service is the cloud side. It publishes models and reports, decides who can see what, and runs the refresh schedule. If the data sits on your own servers, an on-premises data gateway carries those refresh requests from the cloud into your network and returns the results.

Because the three parts form a chain, a wrong number in a report almost always traces back up the chain, to a Power Query step, a model relationship, or a refresh that quietly failed.

Which Data Sources Can Power BI Connect To?

Almost anything a mid-size or large company runs: files, databases, cloud platforms, and SaaS applications, each through a built-in connector listed in Microsoft’s Power BI data sources documentation. For planning purposes, where the source lives matters more than what it is, because location decides whether you need a gateway.

Source type	Typical examples	Gateway needed?
Cloud databases and platforms	Azure SQL Database, Azure Synapse, Snowflake, Databricks	Usually no
SaaS business applications	Salesforce, Dynamics 365, ServiceNow, Google Analytics	Usually no
On-premises databases and ERP	SQL Server, Oracle, SAP, IBM Db2	Yes
Files	Excel and CSV on SharePoint or OneDrive; files on a network share	No for SharePoint and OneDrive; yes for network shares
Custom sources	REST APIs, OData feeds, in-house applications	Depends on where the API is hosted

Excel gets its own note, because most finance teams will keep using it whatever IT decides. Power BI reads workbooks natively, so the realistic goal is narrower than “replace Excel.” It is to stop Excel being the place where official numbers are calculated by hand and emailed around.

Before connecting a new source, settle four questions with its owner. How fresh must the data be? How big is it? What security rules apply? How fast do reports on it need to open? The answers point straight to a connection method.

Power BI Data Integration Methods: Import, DirectQuery, and More

Import is the right default for most business reporting. DirectQuery and live connections earn their place when size, freshness, or a shared enterprise model rules out keeping a copy, and composite models combine the two when a single method falls short.

Method	How it works	Best fit
Import	Copies data into Power BI’s in-memory engine on each refresh	Most reporting; fastest visuals; data that can be a few hours old
DirectQuery	Leaves data in the source and sends a query every time a visual loads	Very large or fast-changing data; strict rules on copying data
Live connection	Points reports at an existing model in Analysis Services or the Power BI Service	One certified enterprise model shared by many reports
Composite model	Mixes Import and DirectQuery tables in one model	History imported for speed, today’s transactions queried live

Import Mode: The Default for Speed

Import loads a compressed copy of the data into Power BI’s engine. Visuals respond almost instantly and every feature works. What you give up is freshness: the report knows only what the last refresh brought in.

Size is the other limit. A Power BI Pro license caps a single semantic model at 1 GB, while Premium per user and Premium or Fabric capacity allow far larger ones. That cap is less tight than it sounds, because Microsoft’s semantic model modes documentation notes that 10 GB of source data often compresses to about 1 GB in memory.

DirectQuery: Current Data, Slower Reports

With DirectQuery, the data never leaves the source. Every time someone opens a page or changes a filter, Power BI sends a query to the database and waits for the answer.

That makes report speed hostage to the database behind it, and it narrows what you can build, since some DAX functions and Power Query steps are not supported and calculated tables are off the table. Teams sometimes reach for DirectQuery to cure stale data in general. It works better as a targeted choice for very large tables, data that changes by the minute, or data that policy says cannot be copied.

Live Connections and Composite Models

A live connection lets a report use a model that already exists, such as a certified semantic model in the Power BI Service or an Analysis Services model. All calculations stay in one place, which is often the shortest path to a single version of the truth across dozens of reports.

Composite models mix storage modes inside one model. A typical setup imports five years of sales history for fast trends and keeps today’s orders in DirectQuery. It is a good pattern with a cost in design complexity, so prove it on realistic data volumes before rolling it out to executives.

A Note on Real-Time Streaming

If you run push or streaming semantic models or streaming tiles today, put a migration on the roadmap. Microsoft has announced the retirement of real-time streaming in Power BI: from October 31, 2027, new real-time semantic models can no longer be created, and Microsoft recommends Real-Time Intelligence in Microsoft Fabric as the replacement. Existing streaming dashboards in Power BI keep working for now, which is exactly why they tend to get forgotten.

How to Prepare and Model Integrated Data in Power BI

Most of the effort, and most of the risk, sits here rather than in the connectors. This is the step where the CRM, the ERP, and the finance system have to agree on what a customer is, which product codes match, and which calendar the business reports on.

Does Power BI Have an ETL Tool?

Yes, within one model. Power Query handles extract, transform, and load, and covers the transformations most teams use week to week:

• merging and appending tables from different sources,
• splitting, combining, and renaming columns,
• removing duplicates and fixing mismatched data types,
• reusable custom functions, for instance one that standardizes product codes from three ERPs.

When several models need the same prepared data, Power BI dataflows let you define the preparation once and share it. Microsoft now classes the original dataflows (Gen1) as legacy and steers customers on Fabric capacity toward Dataflow Gen2. Neither replaces an enterprise ETL platform once you are reconciling a dozen systems with complex business rules.

Data Modeling Best Practices

The pattern that holds up best is the star schema. Fact tables hold transactions such as orders and invoices; dimension tables around them describe customers, products, and dates. A few habits keep that model fast and consistent:

• Delete columns no report uses, since each one slows refresh and eats memory.
• Join tables on integer keys instead of long text fields.
• Write each business metric once as a DAX measure and reuse it everywhere.
• Name tables and measures so a business user can find “Gross Margin %” without a glossary.

Models that have grown slow usually need the techniques in our write-up on optimizing Power BI reports for large datasets. One limit no modeling technique removes: if the CRM and the ERP disagree about a customer’s address, the model will faithfully report both.

Enterprise Power BI Data Integration: Gateway, Refresh, and Governance

Ask why executives stopped trusting a Power BI report and the answer is usually operational. The gateway server was patched and not restarted. Credentials expired during someone’s vacation. A refresh failed on Saturday and Monday’s numbers were Friday’s. Running Power BI as enterprise business intelligence means treating these moving parts like any other production system.

What Is the Power BI Gateway Used For?

The on-premises data gateway is how the Power BI Service reaches data inside your network, such as an on-premises SQL Server or SAP system. It receives encrypted queries from the cloud, runs them against the source, and sends the results back. Cloud sources generally connect without one.

Personal mode ties the gateway to one user and suits experiments on a laptop. Anything other people depend on belongs on a standard gateway, set up like this:

1. Install it on a dedicated, always-on server close to the data sources.
2. Add a second machine to a gateway cluster, so one failed server does not stop every refresh.
3. Keep the recovery key in your password vault and limit admin rights to a small, named group.
4. Register each data source, test it, and put the gateway on your standard monitoring.

How Often Can Power BI Refresh Data?

Up to 8 scheduled refreshes a day on a Power BI Pro license, and up to 48 on Premium per user or Premium and Fabric capacity, according to Microsoft’s scheduled refresh documentation. The same page lists two rules that regularly surprise teams. Power BI switches a refresh schedule off after four consecutive failures, or as soon as credentials stop working. It also pauses scheduled refresh when nobody has opened a report on the model for two months.

Set the schedule by how decisions are made, not by the license maximum. A leadership dashboard reviewed at 8 a.m. needs one dependable refresh at 6 a.m. far more than eight fragile ones spread through the day. For large tables, incremental refresh reloads only new and changed rows, which shortens the window and takes pressure off the source systems.

Governance and Security

Integration puts more data in front of more people, so access rules need to keep up. Certify the semantic models that hold official figures. Use row-level security where a regional manager should see only their region. Give every production model a named owner who answers when it breaks. The details are in our checklist of Power BI security best practices.

When Power BI Alone Is Not Enough for Data Integration

Power BI models data for reporting very well. It was never meant to be the central integration hub for a whole enterprise, and companies that use it that way end up with dozens of models, each holding its own copy of the same transformation logic and each drifting a little further from the others.

These signs suggest it is time to look at a dedicated integration layer, such as a cloud data warehouse or a lakehouse:

• The same customer or product looks different in the ERP, the CRM, and the finance system.
• Several Power BI models repeat identical Power Query steps.
• You need long history, an audit trail, or data that tools other than Power BI also consume.
• Refreshes run for hours or time out.
• Analysts fix data quality problems in Power Query because nobody fixes them at the source.

In that situation the integration work moves upstream. Pipelines pull and reconcile data into a governed store, and Power BI reads clean, modeled tables from it. One of our published use-case blueprints, The $5M Negotiation That Sparked a Data Revolution, follows this route: procurement data from SAP, Oracle, and regional ERPs goes through Azure Data Factory into a data lake and a Synapse star schema, and Power BI sits on top. The architecture itself is explained in our post on Azure Synapse and Power BI integration.

An extra layer also means an extra platform to pay for and run. With a handful of clean sources and one reporting team, Power BI on its own is frequently the cheaper and simpler answer.

Common Power BI Integration Scenarios

Enterprise projects tend to combine the same handful of scenarios, each with its own connector quirks:

• CRM data, covered step by step in connecting Power BI to Salesforce.
• IT service management, where the setup options are compared in ServiceNow and Power BI integration.
• ERP and finance, starting with connecting NetSuite and Power BI; on-premises SAP and Oracle usually need a gateway or an upstream layer.
• Microsoft Azure, where Azure SQL Database, Azure Synapse, and Azure Analysis Services connect natively without a gateway.

To automate the pipeline inside Power BI, pair dataflows for shared preparation with incremental refresh for the big tables and failure alerts that go to a named person rather than a shared inbox. Add pieces only when a reporting need asks for them.

Need Help With Power BI Data Integration?

Reliable Power BI data integration comes down to every report drawing on the same prepared, governed data. In practice that means starting with Import, keeping business logic in one shared model, treating the gateway and refresh schedule as production systems, and moving integration upstream when Power BI starts carrying logic it was not designed for.

If your reports disagree with each other, refreshes keep failing, or nobody can say which model is the official one, our Power BI consulting team and data integration specialists can sort it out. We usually work in four steps:

1. Review your sources, models, gateway, and refresh history.
2. Show where the numbers split and what each fix would cost.
3. Make the agreed changes in stages, without taking live reports offline.
4. Document the setup and train your team to run it.

Book a 30-minute review with Multishoring and bring one report your leadership team argues about. We will trace where its numbers come from.

Power BI Data Integration FAQ

What is data integration in Power BI?

It is the process of connecting Power BI to several source systems, cleaning and combining their data in Power Query, and loading the result into a single semantic model, so that every report built on it uses the same definitions and figures.

Does Power BI have an ETL tool?

Yes. Power Query runs extract, transform, and load steps inside a model, and dataflows make that preparation reusable across models. Organizations reconciling many systems with complex rules usually add a dedicated ETL or integration platform as well.

What is the Power BI gateway used for?

The on-premises data gateway lets the Power BI Service refresh or query data stored inside your own network, for example an on-premises SQL Server, Oracle, or SAP system. Cloud sources normally connect directly.

Is Import or DirectQuery better in Power BI?

For most reports, Import, because it is faster and supports every feature. DirectQuery is the better choice when the data is too large to copy, has to be current to the minute, or is not allowed to leave the source system.

How often can Power BI refresh data?

Power BI Pro allows up to 8 scheduled refreshes per day; Premium per user and Premium or Fabric capacity allow up to 48. Reports in DirectQuery mode read the source directly and do not rely on scheduled refresh in the same way.

Is Power BI integrated with Excel?

Yes, in both directions. Power BI uses Excel workbooks as a data source, and users can analyze Power BI semantic models from inside Excel, so finance teams can keep their spreadsheets while the official numbers come from one governed model.

Official Microsoft References

• Power BI data sources (Microsoft Learn)
• Semantic model modes in the Power BI service (Microsoft Learn)
• Use composite models in Power BI (Microsoft Learn)
• On-premises data gateway architecture (Microsoft Learn)
• Configure scheduled refresh (Microsoft Learn)
• Incremental refresh for semantic models (Microsoft Learn)
• Retirement of real-time streaming in Power BI (Microsoft Power BI Blog)