Database Data Sources
Learn how to connect Power BI Desktop to relational databases, select tables and views, understand Import and DirectQuery storage modes, configure database connections, and troubleshoot common connection problems.
1. Learning Objectives
Organizations commonly store business information in databases. Sales transactions, customer details, inventory, finance records, and operational information may all be maintained in relational database systems.
By the end of this lesson, you will be able to:
- Understand the role of databases in Power BI.
- Identify common relational database sources.
- Connect Power BI Desktop to a supported database.
- Understand server and database connection details.
- Select tables and views for reporting.
- Compare Import and DirectQuery storage modes.
- Recognize authentication and permission requirements.
- Troubleshoot common database connection problems.
2. Understanding Database Data Sources
A database is an organized collection of data managed by a database management system. Relational databases store information in tables containing rows and columns, and related tables can be connected using key columns.
Power BI Desktop provides connectors for various database technologies. The available connectors and supported features depend on the database system, Power BI version, drivers, permissions, and connection method.
| Database Source | Typical Use | Example Data |
|---|---|---|
| Microsoft SQL Server | Enterprise applications and business reporting. | Orders, customers, products, and sales. |
| Azure SQL Database | Cloud-hosted relational data. | Application and operational records. |
| MySQL | Web applications and business systems. | Users, transactions, and inventory. |
| PostgreSQL | Applications and analytical databases. | Operational and reporting data. |
| Oracle Database | Enterprise and large-scale business systems. | Financial and operational records. |
3. How Power BI Connects to a Database
Power BI uses a database connector to establish a connection to the selected source. Depending on the storage mode and connector, data may be imported into the Power BI model or queried from the source when users interact with a report.
Simplified database connection workflow.
4. Information Required for a Database Connection
Before connecting to a database, obtain the correct connection information from the database administrator or authorized system owner.
| Connection Detail | Purpose | Example |
|---|---|---|
| Server name | Identifies the database server or endpoint. | SERVER01 |
| Database name | Identifies the database to access. | SalesDB |
| Authentication method | Determines how the user or application is authenticated. | Windows, database credentials, or supported organizational authentication. |
| Network access | Allows the client to reach the database server. | Approved network route and firewall rules. |
| Permissions | Controls which data can be read. | Read access to approved reporting tables. |
5. Connecting to Microsoft SQL Server
Microsoft SQL Server is a common source for Power BI reports. The following workflow describes a typical connection using Power BI Desktop and the SQL Server database connector.
Step-by-Step Procedure
- Open Power BI Desktop.
- Select the Home ribbon.
- Choose Get data.
- Select SQL Server database.
- Enter the server name provided by your administrator.
- Enter the database name if you want to specify a particular database.
- Select the appropriate data connectivity mode.
- Choose OK to continue.
- Select the supported authentication method and enter the required credentials.
- In Navigator, select the required tables or views.
- Review the data preview.
- Choose Load or Transform Data.
- Verify that the selected data appears correctly in Power BI Desktop.
Example Connection Information
The following values are illustrative placeholders. Replace them with the connection details supplied for your own database environment.
| Server | SERVER01 |
|---|---|
| Database | SalesDB |
| Table | Sales |
| Authentication | Use the method approved for the environment. |
6. Selecting Tables and Views
After connecting successfully, Power BI may display a list of available database objects in Navigator. The exact list depends on the database, connector, and your permissions.
Illustrative Navigator layout showing selectable tables and a data preview.
Selection Guidelines
- Choose tables or views relevant to the reporting requirement.
- Review the available columns before loading data.
- Prefer approved reporting views when provided by the database team.
- Avoid importing unrelated or sensitive tables.
- Confirm that the selected objects contain the expected records.
7. Import Mode and DirectQuery
Power BI supports different ways to access data. Two important modes for relational database sources are Import and DirectQuery. The appropriate choice depends on data volume, performance, freshness requirements, connector support, and model design.
| Feature | Import | DirectQuery |
|---|---|---|
| Data storage | Data is loaded into the Power BI model. | Data generally remains in the source system. |
| Report interaction | Usually queries the imported model. | May send queries to the source as users interact with the report. |
| Data freshness | Requires refresh to retrieve updated source data. | Can reflect source changes when queries execute, subject to caching and configuration. |
| Performance factors | Model size and design affect performance. | Source performance, network latency, query design, and concurrency matter. |
| Typical use | Analytics using data loaded into the model. | Scenarios requiring source-side queries or specific data access patterns. |
8. Understanding the Two Storage Modes
Conceptual comparison of imported model data and source-side querying.
9. Tables, Views, and SQL Queries
Database Tables
A table stores records in rows and fields in columns. For example, a Sales table may contain OrderID, ProductID, OrderDate, Quantity, and SalesAmount.
Database Views
A view is a database object that presents data through a defined query. Database administrators may create views that expose only the fields and records needed for reporting.
SQL Queries
Some database connectors allow users to provide a native SQL statement. Whether this is available and which storage modes support it depend on the connector and environment.
SELECT
OrderID,
OrderDate,
ProductID,
Quantity,
SalesAmount
FROM dbo.Sales;
This example selects five columns from a hypothetical SQL Server table. The table name and column names must match your actual database schema.
10. Preparing Database Data for Reporting
Database data may require additional preparation before it becomes suitable for analysis. Power Query can help filter records, remove unused columns, correct data types, and standardize values.
Recommended Preparation Steps
- Select only the tables and fields required for reporting.
- Inspect the preview and confirm the expected row structure.
- Verify date, text, whole-number, and decimal data types.
- Remove unnecessary columns when appropriate.
- Filter records using clearly defined business requirements.
- Check for unexpected null values and conversion errors.
- Review relationships between related tables.
- Apply changes and validate the results before building visuals.
Example: Sales and Products
Suppose the database contains a Sales table and a Products table. Sales contains ProductID, while Products contains ProductID, ProductName, and Category.
These tables may be related through ProductID. Confirm that the key values match and that the Products table has the appropriate uniqueness for the intended relationship.
11. Authentication and Database Permissions
A successful database connection requires both a reachable server and an authentication method accepted by that source. The available methods vary by database connector and organizational configuration.
| Requirement | Purpose | What to Check |
|---|---|---|
| Authentication | Verifies the identity used for the connection. | Use an approved account and supported method. |
| Read permissions | Controls which objects can be accessed. | Confirm access to the required tables or views. |
| Network connectivity | Allows Power BI to reach the server. | Check network access, endpoint, and firewall rules. |
| Gateway requirements | May provide access to on-premises sources after publishing. | Check whether a gateway and appropriate mapping are required. |
| Credential management | Controls stored credentials and authentication settings. | Follow the organization's security policy. |
12. Database Connection Troubleshooting
If a database connection fails, identify the failure stage before changing settings. The cause may be the server address, network access, authentication, permissions, or a problem with the selected database object.
A simplified troubleshooting sequence for diagnosing database connection issues.
| Error or Problem | Possible Cause | Recommended Action |
|---|---|---|
| Server not found | Incorrect server name or network problem. | Verify the server address and network access. |
| Authentication failed | Incorrect credentials or unsupported authentication method. | Confirm the approved authentication method and account. |
| Access denied | The account lacks permission to the requested object. | Ask the database administrator to review access. |
| Database not found | Incorrect database name or insufficient visibility. | Verify the database name and account permissions. |
| Query is slow | Large result set, expensive query, or source performance issue. | Reduce unnecessary columns and rows; review query performance. |
| Published report cannot refresh | Gateway, credential, or source configuration issue. | Review the published semantic model's source and refresh configuration. |
13. Hands-On Practice Assignment
Assignment: Connect to a Sample Database
- Use an approved training database or a sample database available in your environment.
- Obtain the server name and database name from your instructor or administrator.
- Open Power BI Desktop and select Get data.
- Choose the appropriate database connector.
- Enter the connection details.
- Select an approved authentication method.
- Open Navigator and inspect the available tables and views.
- Select one suitable table and review its preview.
- Choose Transform Data and inspect the columns.
- Verify the data types and remove unnecessary columns when appropriate.
- Apply the changes and validate the imported data.
- If supported, compare Import and DirectQuery behavior using a non-production test source.
Practice Questions
- Which connector did you use?
- Which server and database did you connect to?
- Which tables or views were available?
- What authentication method was approved for the connection?
- Which columns did you select for reporting?
- Did you identify any null values or data-type issues?
- Which storage mode was suitable for your practice scenario, and why?
Completion Checklist
- ☐ I understand what a database source is.
- ☐ I can identify the required connection details.
- ☐ I can connect to a supported database.
- ☐ I can select tables or views in Navigator.
- ☐ I understand Import and DirectQuery.
- ☐ I can identify common connection errors.
- ☐ I understand database access and credential security.
14. Knowledge Check
Question 1
What is a relational database?
Answer: A database that organizes data in related tables containing rows and columns.
Question 2
What information is commonly needed to connect to a database?
Answer: The server or endpoint, database name where applicable, authentication details, and sufficient permissions.
Question 3
What is the purpose of the Navigator window?
Answer: It allows users to inspect and select available tables, views, or other supported objects.
Question 4
What is Import mode?
Answer: A storage mode that loads data into the Power BI model.
Question 5
What is DirectQuery?
Answer: A mode in which supported report queries can be sent to the underlying data source rather than importing all source data into the model.
Question 6
Why are database permissions important?
Answer: They restrict access to authorized data and help protect sensitive information.
Question 7
What should you check if the server cannot be reached?
Answer: Verify the server name, network connectivity, endpoint availability, and any relevant firewall requirements.
Question 8
Why might a published report require a gateway?
Answer: A gateway may be needed to provide the Power BI service with secure access to an on-premises data source.
Summary
In this lesson, you learned how Power BI connects to supported database systems, how to enter connection details, and how to select tables and views through Navigator.
You also compared Import and DirectQuery, reviewed authentication and permission requirements, explored basic data preparation, and learned how to troubleshoot common connection issues.