Connecting to Business Data

Introduction

Power BI is useful only when it can obtain reliable business data and maintain an appropriate connection to that data. Connecting to data is therefore more than selecting a file or entering a server address. It involves identifying the data source, authenticating securely, evaluating data quality, selecting a storage and query mode, and designing a connection that remains dependable as business requirements change.

Business data may be stored in spreadsheets, text files, relational databases, cloud applications, online services, webpages, or Power BI dataflows. Each source has different characteristics. A small monthly budget may be suitable for Import mode, whereas a large operational database may require DirectQuery. A semantic model already managed by another team may be accessed through a live connection. In some situations, a composite model combines more than one approach.

This chapter explains the principal connection types available in Power BI and provides a practical framework for choosing among them.

Learning Objectives

By the end of this chapter, you should be able to:

• identify common categories of business data sources;
• connect Power BI to files, databases, cloud services, web sources, and dataflows;
• explain authentication, gateways, privacy levels, and refresh considerations;
• distinguish Import, DirectQuery, Live Connection, and composite models;
• select a connectivity mode based on volume, latency, governance, and analytical requirements;
• apply a disciplined process for validating and documenting a connection.

1. The Business Data Landscape

A data source is the original location from which Power BI obtains data. A connector is the software interface that allows Power BI to communicate with that source. A semantic model is the structured analytical representation used by reports, including tables, relationships, measures, and metadata.

The major source categories are shown below.

The same business subject may exist in several locations. For example, customer information may be held in a CRM system, sales transactions in a database, and monthly targets in an Excel workbook. Connecting these sources requires attention to common identifiers, data types, update schedules, and ownership.

Data source, connection, and semantic model

These terms should not be treated as synonyms. The source is where data originates. The connection is the technical and security pathway used to access it. The semantic model is the analytical layer built from one or more sources. A report is the presentation layer that uses the semantic model.

A reliable design separates these responsibilities. Changes in a source should not unnecessarily destroy report logic, and report users should not require direct access to every underlying system.

2. Connecting to Files

Files are common in small and medium-sized business environments. Power BI Desktop can connect to Excel workbooks, comma-separated values (CSV), text files, XML, JSON, Parquet, and folders containing multiple files.

Excel workbooks

When connecting to Excel, Power BI displays available worksheets and tables. Structured Excel tables are generally preferable to arbitrary cell ranges because tables have named columns, clearer boundaries, and better resilience when rows are added. A worksheet may contain titles, blank rows, merged cells, subtotals, or formatting intended for human reading rather than analysis.

A good practice is to prepare a worksheet as a simple tabular source:

• one header row;
• one record per row;
• one attribute per column;
• no merged cells;
• consistent data types;
• no manually inserted subtotals within the data region.

CSV and text files

CSV files are simple and portable, but their interpretation depends on delimiters, text encoding, quotation marks, and regional settings. A date such as 03/04/2025 may mean 3 April or 4 March depending on locale. Data types should therefore be checked explicitly rather than accepted automatically.

Folder connections

A folder connector can combine files with a common structure, such as weekly sales extracts. Power Query uses a sample file to infer transformation steps and applies those steps to other files. This approach is effective when file names and column structures are reasonably consistent. It becomes risky when old files have different headers, extra columns, or changed business definitions.

File connection risks

Files often create operational weaknesses. They may be moved, renamed, overwritten, emailed to unauthorized recipients, or edited without a record of change. A file-based solution should document its owner, location, expected schema, refresh frequency, and error-handling procedure.

3. Connecting to Databases

Relational databases store data in tables linked through keys. Power BI can connect to database systems using connectors that support either Import, DirectQuery, or both, depending on the product and configuration.

When connecting to a database, the user normally supplies a server or host name, database name, and authentication method. The connection may be to an on-premises server, a virtual network, or a cloud-hosted database.

Navigator and query selection

The Navigator window allows users to select tables, views, and other objects. Views are often preferable to raw operational tables when the database team has created a controlled analytical interface. A view can hide sensitive columns, standardize definitions, and reduce unnecessary data transfer.

Native queries and folding

Power Query transformations may be translated into source-system operations. This capability is commonly called query folding. For example, filtering rows in Power Query may be translated into a database filter so that only required records are returned.

Query folding can improve performance, but it is not guaranteed for every transformation or connector. Complex custom functions, certain row-by-row operations, and unsupported transformations may cause later steps to run outside the source system. Analysts should place selective filters and column removal early in the query and verify whether folding remains available when performance matters.

Database connection considerations

Important questions include:

1. Is the database production-critical?
2. Does the user have least-privilege access?
3. Can queries affect operational performance?
4. Is a gateway required?
5. Does the source support the required connectivity mode?
6. Are database definitions and refresh windows documented?

A report should not create excessive load on a transactional system merely because the connection is technically possible.

4. Cloud Services and Business Applications

Cloud services expose data through connectors, APIs, or cloud storage. Examples include Microsoft 365 services, Dynamics 365, Salesforce, Azure services, and cloud data warehouses.

Cloud connections commonly use organizational accounts, service principals, OAuth-based authentication, or managed identities. Authentication should be separate from individual report ownership wherever possible. A personal account creates a continuity risk if the employee changes role or leaves the organization.

Cloud sources may impose API limits, throttling, pagination requirements, or licensing restrictions. A connector may retrieve only a subset of application data, and its behavior can change when the application vendor updates an API. Production solutions should therefore monitor refresh history and connector documentation.

Cloud does not automatically mean real time. A cloud source may still be refreshed hourly or daily. Conversely, a cloud database can support DirectQuery or other low-latency patterns if its architecture and capacity are appropriate.

5. Web Sources and APIs

Power BI can connect to webpages, OData services, and web APIs. Public data sources may require no credentials, while private APIs may require keys, tokens, or organizational authentication.

A webpage designed for human viewing is not necessarily a stable data source. Its visual layout may change without notice. A structured API or downloadable data file is generally more reliable than scraping a page intended only for presentation.

API-based connections require attention to:

• authentication and secret storage;
• request limits and throttling;
• pagination;
• response formats such as JSON or XML;
• incremental retrieval of new or changed records;
• error responses and retry behavior;
• licensing and permitted use of the data.

API keys should not be embedded in published reports or shared query text. Credentials belong in managed connection settings or an approved secret-management process.

6. Dataflows as a Reusable Preparation Layer

A dataflow is a cloud-managed collection of Power Query entities that performs data extraction and transformation independently of a particular report. It can provide a reusable, governed preparation layer for several reports or teams.

For example, a dataflow might connect to a CRM system, standardize customer identifiers, remove confidential fields, and produce a cleansed Customer entity. Multiple semantic models can then consume that entity rather than repeating the same preparation steps.

Dataflows can improve consistency and reduce duplicated work. They also introduce dependencies. If a dataflow refresh fails, downstream semantic models may receive stale data or fail to refresh. Ownership, refresh order, permissions, and monitoring must therefore be defined.

Dataflows are especially useful when:

• several reports require the same transformation;
• source access should be centralized;
• business rules should be maintained in one place;
• data preparation should be separated from report design.

They are not a substitute for a properly designed warehouse when the organization requires extensive historical integration, complex dimensional modelling, or enterprise-scale governance.

7. Connectivity Modes

The connection mode determines where data is stored and how queries are executed. The main approaches are Import, DirectQuery, Live Connection, and composite models.

Import mode

In Import mode, Power BI copies data into an in-memory storage engine in the semantic model. Reports query this stored data rather than contacting the original source for every visual interaction.

Advantages include:

• generally fast report interaction;
• broad modelling and transformation capability;
• reduced dependence on source availability during report use;
• efficient compression for many analytical datasets.

Limitations include:

• data is current only as of the last successful refresh;
• imported data consumes model storage and refresh resources;
• sensitive data is copied into the Power BI environment;
• large models may require capacity planning and incremental refresh.

Import is often the default choice for scheduled management reporting where hourly or daily freshness is acceptable.

DirectQuery mode

In DirectQuery mode, Power BI stores metadata and sends queries to the source when users interact with a report. The source system performs much of the query processing.

Advantages include:

• reduced duplication of source data;
• access to more current information;
• suitability for data volumes that are impractical to import fully;
• use of source-system security or governance in some designs.

Limitations include:

• report performance depends on source performance and network latency;
• frequent interactions may generate substantial source workload;
• some modelling and transformation features are restricted;
• source availability is required during report use;
• concurrency can become a problem when many users query the same system.

DirectQuery is not automatically real-time. It may still involve caching, source delays, gateway latency, or application refresh intervals. “Near real time” should be defined in business terms, such as a maximum acceptable delay of five minutes.

Live Connection

A live connection connects a report to an existing semantic model or Analysis Services model. The report consumes the model’s established tables, relationships, measures, and security rules rather than importing source tables into a new local model.

Live Connection is useful when a central data team owns the semantic model and wants consistent definitions across reports. It supports governance and reuse, but report authors have less freedom to add local modelling logic. Changes usually need to be made in the central model.

A live connection to an existing semantic model should be distinguished from DirectQuery to a database. DirectQuery queries a source database; Live Connection queries an already modelled analytical layer.

Composite models

A composite model combines storage or connection behaviors. For example, an analyst may use imported dimensions and connect a large transaction table through DirectQuery. Another design may combine local imported data with tables from an existing semantic model.

Composite models provide flexibility but increase complexity. Relationships across different storage modes may behave differently, performance can be difficult to predict, and security must be tested across all sources. Composite models should be used to solve a clear business problem, not merely because multiple connectors are available.

8. Selecting an Appropriate Mode

Connectivity mode should be selected through requirements analysis rather than personal preference. The following decision factors are useful.

A practical selection sequence is:

1. Define the required freshness. “Real time” should be translated into a measurable service expectation.
2. Estimate data volume, growth, and query complexity.
3. Identify whether the source can support expected concurrent demand.
4. Determine whether a governed semantic model already exists.
5. Review security, privacy, residency, and regulatory requirements.
6. Test representative report interactions.
7. Document the decision and its trade-offs.

The technically most current option is not always the best option. A fast, well-governed model refreshed every hour may provide more value than a slow DirectQuery model that technically queries the source continuously.

9. Gateways, Authentication, and Privacy

On-premises data gateways

An on-premises data gateway provides a secure communication path between Power BI and data sources that are not directly accessible from the Power BI service. It is commonly used for local SQL Server databases, file shares, and other internal sources.

A gateway solution requires appropriate installation, network access, credentials, high availability where necessary, and monitoring. The gateway is not a general-purpose data warehouse; it is an access component. If it is offline, scheduled refreshes and queries depending on it may fail.

Authentication

Common authentication approaches include organizational accounts, database credentials, OAuth, service principals, and anonymous access for genuinely public data. Credentials should follow least privilege: users and services should receive only the permissions necessary for the required data and operations.

Authentication answers “who may connect?” Authorization answers “what may that identity access?” Both should be reviewed. A report can accidentally expose confidential data even when the connection itself is authenticated correctly.

Privacy levels and combining sources

When queries combine sources, Power Query privacy settings help control whether data from one source may be passed to another source. The settings are intended to reduce unintended data disclosure, but they do not replace organizational data-classification policies, access controls, or legal review.

10. A Practical Connection Workflow

A disciplined workflow reduces technical and governance errors.

Step 1: Define the analytical requirement

Document the business question, users, required freshness, expected history, and security constraints.

Step 2: Profile the source

Inspect row counts, column names, data types, null values, duplicate keys, date ranges, and update behavior. Confirm who owns the source and whether it is authoritative.

Step 3: Choose the connector and authentication

Use a supported connector where possible. Avoid unreliable scraping when a stable API, view, or export exists. Select an organizational identity rather than an individual identity for shared production solutions.

Step 4: Select the connectivity mode

Compare Import, DirectQuery, Live Connection, and composite approaches against the requirements. Record why the selected option was chosen.

Step 5: Transform and model responsibly

Remove unnecessary columns, standardize types, filter irrelevant rows, and preserve query folding where appropriate. Avoid embedding business-critical logic in a single report if it should be shared.

Step 6: Test performance and failure behavior

Test common filters, drill-through, cross-highlighting, refresh, gateway interruption, and multiple simultaneous users. A connection is not production-ready merely because it works once on a developer’s computer.

Step 7: Publish, secure, and monitor

Configure refresh credentials, gateway mappings, row-level security where required, ownership, alerts, and documentation. Monitor refresh duration, failures, source load, and data freshness.

11. Illustrative Case: Regional Retail Performance

Background

A regional retailer has 180 stores. Point-of-sale transactions are stored in a cloud SQL database, product targets are maintained in an Excel workbook, and customer service data is held in a CRM application. Managers want a sales dashboard updated every hour. The finance department requires consistent definitions of net sales and gross margin.

Problem

An initial prototype imported all transaction data each time the report was refreshed. The prototype worked with six months of data but became slow as history grew. Different analysts also calculated gross margin differently, creating disagreement between reports.

Analysis

The transaction table is large and continues to grow. Managers need recent information, but not second-by-second updates. Product targets change weekly and are small enough to import. Finance already maintains a certified semantic model containing approved measures for net sales and gross margin.

Approach and solution

The organization adopts a mixed architecture:

• the certified finance semantic model is accessed through a Live Connection;
• the small target workbook is imported and maintained by the planning team;
• current transaction detail is accessed through DirectQuery for operational views;
• shared customer preparation is performed in a governed dataflow;
• an on-premises gateway is not required for the cloud database, but gateway monitoring remains part of the general operating procedure for other internal sources.

Before implementation, the team tests query response times during peak usage and confirms that the database can handle expected demand. It also documents that the dashboard’s target freshness is one hour, not real time.

Outcome

The report uses consistent financial measures, avoids importing the entire transaction history, and gives managers sufficiently current information. The architecture is more complex than a single imported workbook, so ownership and monitoring responsibilities are explicitly assigned.

Learning points

1. Connectivity mode should follow business requirements.
2. Existing governed semantic models should be reused where appropriate.
3. A small imported source can coexist with a larger queried source.
4. Freshness claims must be measurable.
5. Performance testing and ownership are part of connection design.

12. Challenges, Limitations, and Governance

Connectivity decisions create technical and organizational risks. Import models may contain duplicated sensitive information. DirectQuery may overload a production database. Live Connection may create dependence on a central team whose change process is slow. Composite models may be difficult to troubleshoot.

Data governance should address:

• classification of confidential and personal data;
• lawful purpose and authorized use;
• retention and deletion requirements;
• data residency and cross-border transfer;
• ownership and stewardship;
• lineage from source to report;
• access reviews and row-level security;
• audit logging and incident response.

A connector does not make data accurate. Source records can be incomplete, duplicated, biased, or defined differently across systems. Analysts should validate totals against trusted operational or financial controls and record known limitations.

AI-assisted features may help users discover connectors, generate transformation suggestions, or describe data. Such assistance must be reviewed by a knowledgeable analyst. It should not be trusted to infer business definitions, approve permissions, or decide whether sensitive data may be combined. Human accountability remains essential.

Best Practices

• Prefer stable, structured sources over presentation-oriented pages.
• Use named tables, database views, APIs, or governed entities where available.
• Import only the columns and rows required for the analytical purpose.
• Define freshness as a measurable requirement.
• Use least-privilege credentials and avoid personal production ownership.
• Reuse certified semantic models and dataflows when they meet the requirement.
• Test performance with realistic data volumes and user concurrency.
• Monitor refreshes, gateway health, source load, and data age.
• Document source ownership, transformations, credentials, dependencies, and limitations.
• Treat security, privacy, and lineage as design requirements rather than final checks.

Chapter Summary

Power BI can connect to files, databases, cloud applications, web services, APIs, and dataflows. Each connection involves more than technical access: it also requires source assessment, authentication, data-quality validation, privacy controls, refresh planning, and operational ownership.

Import stores data in the semantic model and commonly provides strong interactive performance. DirectQuery sends queries to the source and can support large or frequently changing data, but it depends heavily on source and network performance. Live Connection uses an existing governed semantic model, promoting consistency while limiting local modelling freedom. Composite models combine approaches and can solve mixed requirements, although they require careful testing and governance.

The appropriate mode depends on freshness, scale, performance, security, source capability, existing models, and organizational responsibility. A successful connection is therefore one that is technically functional, analytically appropriate, secure, maintainable, and clearly documented.

Key Terms

Data source: The original location from which data is obtained.

Connector: A software interface used to communicate with a data source.

Semantic model: The analytical structure containing tables, relationships, measures, and metadata.

Import mode: A mode in which data is copied into Power BI storage for querying.

DirectQuery: A mode in which queries are sent to the source system during report interaction.

Live Connection: A connection to an existing semantic model or Analysis Services model.

Composite model: A model that combines different storage or connection behaviors.

Query folding: Translation of Power Query operations into operations performed by the source system.

Dataflow: A cloud-managed set of reusable data extraction and transformation processes.

Gateway: A service that provides communication between Power BI and inaccessible internal sources.

Authentication: Verification of the identity attempting to connect.

Authorization: Determination of what an authenticated identity may access or do.

Least privilege: Granting only the permissions necessary for a defined task.

API: An application programming interface that enables structured software-to-software data access.

Pagination: Dividing a large API response into multiple smaller requests or pages.

Refresh: The process of updating imported or prepared data from its source.

Data lineage: The documented path of data from its origin through transformations to its use.

Row-level security: Rules that restrict which rows a user may see.

Query latency: The time between a request and the receipt of its result.

Review Questions

Short Answer Questions

1. What is the difference between a data source, a connector, and a semantic model?
2. Why are structured Excel tables generally preferable to arbitrary worksheet ranges?
3. What is query folding, and why can it improve performance?
4. How does Live Connection differ from DirectQuery to a database?
5. Why should data freshness be expressed as a measurable business requirement?

Long Answer Questions

1. Compare Import and DirectQuery modes, including their advantages, limitations, and suitable use cases.
2. Explain how authentication, authorization, gateways, and privacy settings contribute to secure connectivity.
3. Describe a complete workflow for connecting Power BI to a new business data source.
4. Explain the role of dataflows in an organization that produces several related reports.
5. Discuss why a technically possible connection may still be unsuitable for production use.

Analytical / Application Questions

1. A company has a small monthly budget workbook and a rapidly growing sales database. Managers need hourly sales information, while finance requires centrally governed calculations. Recommend a connectivity architecture and justify it.
2. A DirectQuery report is slow during working hours and causes high load on a production database. Identify likely causes and propose at least three corrective actions.
3. An analyst wants to combine a public web table, customer records, and employee data in one report. What governance, privacy, data-quality, and technical checks should be completed before publication?

Multiple Choice Questions with Answers

Question 1

Which connectivity mode stores a copy of the data in the Power BI semantic model?

A. Import

B. DirectQuery

C. Live Connection

D. Gateway mode

Correct Answer: A. Import

Question 2

Which source is generally most suitable for reusable, centrally managed data preparation across several reports?

A. A screenshot embedded in a webpage

B. A dataflow

C. A manually edited worksheet range

D. A personal email attachment

Correct Answer: B. A dataflow

Question 3

What is the main purpose of query folding?

A. To encrypt every report visual

B. To translate supported transformations into source-system operations

C. To convert DirectQuery into Live Connection

D. To remove all duplicate records automatically

Correct Answer: B. To translate supported transformations into source-system operations

Question 4

Which statement best describes DirectQuery?

A. All source data is permanently copied into the report file

B. The report queries the source system as users interact with it

C. The report can use only Excel files

D. The report cannot depend on source availability

Correct Answer: B. The report queries the source system as users interact with it

Question 5

A report connects to an existing certified semantic model owned by a central data team. Which approach is most appropriate?

A. Live Connection

B. Anonymous web access

C. Folder import only

D. CSV pagination

Correct Answer: A. Live Connection

Question 6

What is the principle of least privilege?

A. Giving every analyst administrator access

B. Granting only the permissions required for a defined task

C. Allowing access without authentication

D. Using the same password for all sources

Correct Answer: B. Granting only the permissions required for a defined task

Question 7

Which issue is especially associated with combining files from a folder?

A. The files must always be stored in a database

B. Different files may have inconsistent schemas

C. Folder connections cannot use transformations

D. Folder connections automatically guarantee data quality

Correct Answer: B. Different files may have inconsistent schemas

Question 8

Why might an on-premises gateway be required?

A. To connect Power BI to a source that is not directly accessible from the Power BI service

B. To replace all semantic models

C. To calculate every DAX measure

D. To make a public webpage private

Correct Answer: A. To connect Power BI to a source that is not directly accessible from the Power BI service

Question 9

Which factor is most important when evaluating whether DirectQuery will perform well?

A. The report’s color palette

B. Source performance, network latency, and expected concurrent usage

C. The number of worksheet tabs in Excel

D. Whether the report has a title

Correct Answer: B. Source performance, network latency, and expected concurrent usage

Question 10

What is a composite model?

A. A model containing only imported CSV files

B. A model combining different storage or connection behaviors

C. A model that cannot contain relationships

D. A model used only for webpage scraping

Correct Answer: B. A model combining different storage or connection behaviors