# Connecting to Data Sources

> Chapter 2 of the Courseiva PL-300 curriculum — https://courseiva.com/learn/pl-300/data-sources-and-connections

**Official objective:** 1.2 — Connect to data sources including relational, non-relational, and cloud-based

## Introduction

How do you get the right data from a database, a website, or a file into Power BI so you can actually build a report? Without connecting to a data source, you have nothing to analyse, making the entire process of creating a dashboard impossible. For the PL-300 exam, understanding how to connect to different types of data sources – relational, non-relational, and cloud-based – is a fundamental skill you will be tested on directly, and it's the very first step to unlocking all of Power BI's capabilities.

## The Library Card Analogy

Have you ever needed to find a specific book in a massive library, but you weren't allowed to just wander into the stacks?

You need a library card. This card isn't the book itself, but it gives you permission to access the information. When you connect to a data source in Power BI, you're doing something very similar. The library (a server or cloud service) has all the books (the data). But you can't just walk in and grab them. You need to present your library card (your connection credentials, like a username and password) at the front desk. The librarian (the data source's security system) checks your card, sees that you're allowed to read certain sections, and then lets you 'borrow' the data by either taking a copy (importing) or reading it while standing in the library (DirectQuery). If you try to connect to a book from a different library system (a different data source, like a cloud database vs. an Excel file), you'll need a different kind of card or a special inter-library loan process (a specific connector and set of credentials). This whole process – presenting your credentials, getting permission, and then accessing the specific information you need – is precisely what Power BI does when you 'Connect to Data Sources'.

## Core explanation

Connecting to a data source is the process of establishing a link between Power BI and the place where your data lives. Think of it as plugging a hose into a tap. The tap is your data source, and the hose is Power BI. But not all taps are the same. Some are in your house (a local file), some are at the water company (a cloud database), and some are old and rusty (legacy systems). Power BI needs specific 'connectors' – like universal adaptors – to fit onto each type of tap.

There are three main types of data sources you need to know about for PL-300, and they are at the heart of exam objective 1.2.

**Relational Data Sources**

These are the most organised type. A relational data source stores data in tables, like spreadsheets, but with strict rules about how those tables relate to each other. Imagine an address book. You might have one table called 'Customers' and another called 'Orders'. They are 'related' because each order in the 'Orders' table has a Customer ID that matches a Customer ID in the 'Customers' table. Common relational databases include SQL Server, Oracle, and MySQL. Power BI connects to these using specific 'database connectors'. The key thing here is 'structured query language' (SQL), which is a programming language for managing data in these systems, though Power BI often writes the SQL for you in the background.

**Non-Relational Data Sources**

This category is the ‘everything else’ of data storage. Non-relational data sources don't rely on tables and rows. They can store data in many formats. The most common type for PL-300 is a file. Common non-relational file sources include:

- Excel workbooks (.xlsx): A very common source where data is in sheets.
- CSV files (.csv): Simple text files where each line is a row and values are separated by commas.
- JSON files (.json): A way of storing data in a human-readable text format using key-value pairs, often used by web applications.
- XML files (.xml): Another text format that uses tags to define data.

Another important non-relational source is 'Azure Blob Storage' or 'Azure Data Lake Storage', which are cloud-based storage containers (like a giant hard drive in the sky) that can hold any type of file, not just organised tables.

**Cloud-Based Data Sources**

This category refers to data sources that live on the internet, managed by a cloud provider like Microsoft Azure, Amazon Web Services (AWS), or Google Cloud. These are not defined by their structure (they can be relational or non-relational) but by their location and delivery model (Software as a Service, or SaaS). Common examples for PL-300 include:

- **Azure SQL Database**: A relational database hosted in Azure.
- **Dataverse**: The data platform used by Microsoft Power Platform (Power Apps, Power Automate). It's a cloud-based, low-code database.
- **SharePoint Online List**: A list of data stored in a SharePoint site.
- **Microsoft Entra ID**: Not a data source in the traditional sense, but it governs who can connect to cloud sources. You use your Microsoft Entra ID identity to authenticate.
- **Analysis Services**: Microsoft's enterprise-grade analytical engine, which can be on-premises (Tabular) or in the cloud (Azure Analysis Services).

The process of connecting always involves the same few steps, regardless of the source:

1.  **Choosing the Connector**: In Power BI Desktop, you go to the Home tab and click 'Get Data'. You then search for or find your specific source (e.g., 'SQL Server', 'Excel', 'SharePoint Online List'). This is your universal adaptor.
2.  **Providing Connection Details**: You give Power BI the address of the data source. For a database, this is a server name and database name. For a file, it's a file path. For a cloud service, it's a URL.
3.  **Authentication**: You prove you are allowed to access the data. This is often a username and password, but can also be 'Windows Authentication' (using your Windows login) or 'Microsoft Account' (using your Microsoft Entra ID credentials). This is the most common place for connection failures if your IT department hasn't given you permission.
4.  **Selecting Data**: Once connected, a 'Navigator' window pops up. Here you can preview the data and select which tables or objects you want to bring into Power BI. You can choose to load the data directly or first transform it using Power Query (a data cleaning tool).
5.  **Choosing a Connection Mode**: This is a critical step. You have two main choices:
    - **Import**: Power BI copies the entire selected data into its own internal storage (the VertiPaq engine). This is the fastest mode for reports, but it creates a snapshot in time. If the original data changes, you need to refresh the report.
    - **DirectQuery**: Power BI does not copy the data. Instead, each time you interact with a visual (like a slicer or chart), it sends a query directly to the original data source. This gives you real-time data but is slower and has some limitations (you can't use all of Power Query's data transformation features).

Why does this all exist? Before tools like Power BI, connecting to different data sources required writing custom code for every one. If you wanted data from a database and an Excel file, you'd often have to export the database to Excel first, which was slow and error-prone. Power BI's role as a universal connector means one tool can talk to hundreds of different 'taps', saving immense amounts of time and reducing the risk of human error.

## Real-world context

Imagine you are a data analyst working for 'Contoso Ltd.', a mid-sized retail company. Your boss wants a single report showing total sales for last month, broken down by product category and region. The problem? The data you need lives in three different places.

- **The Sales Database**: This is a relational SQL Server database that lives on a server in your company's office (on-premises). It holds all transaction-level sales data: what was sold, when, to whom, and for how much. It's the 'golden source' for revenue.
- **The Product Categories File**: The marketing team manages product categories in a messy Excel spreadsheet on a shared SharePoint Online site. They update it once a week. It maps each product code to a category name (e.g., 'Sleeping Bags', 'Camp Stoves').
- **Regional Data**: Your company's HR data – which stores which employees, and by extension which stores, are in which region – is stored in a cloud-based Azure SQL Database managed by your IT department.

As the IT professional (you), here is what you actually do step by step to make this report happen.

1.  **Open Power BI Desktop and Start Connecting**: You click 'Get Data' on the Home tab. You see a long list of connectors.
2.  **Connect to the Sales Database (Relational)**: You search for 'SQL Server'. A dialog box appears. You type the server name (e.g., 'Contoso-SQL-01') and the database name ('ContosoSales').
3.  **Authenticate**: A credentials pop-up appears. You select 'Windows Authentication' because your Windows user account has been granted read access to this server by the IT team. You click 'Connect'.
4.  **Select Tables**: The Navigator window shows you all the tables in the database. You tick the boxes for 'SalesOrders' and 'Products'. You click 'Load' to import the data. You choose Import mode because this historical sales data doesn't change minute-by-minute.
5.  **Connect to the SharePoint Online Excel File (Non-Relational, Cloud-Based)**: You click 'Get Data' again. You search for 'SharePoint Online Folder'. A new dialog asks for the SharePoint site URL (e.g., 'https://contoso.sharepoint.com/sites/Marketing').
6.  **Authenticate (Cloud)**: Power BI prompts you to sign in. You use your organisational email address – this authenticates via Microsoft Entra ID. This is crucial; if your account doesn't have access to that specific SharePoint folder, the connection will fail.
7.  **Select the File**: The Navigator now shows all files in that folder. You find 'ProductCategories.xlsx'. You select it and see a preview of the first sheet, 'Sheet1'. You click 'Transform Data' instead of 'Load', because you know the file is messy.
8.  **Transform the Data (Power Query)**: Power Query Editor opens. You remove a few empty columns, promote the first row to headers, and rename the table 'ProductCategories'. You then click 'Close & Apply' to load this data into your data model.
9.  **Connect to the Azure SQL Database (Relational, Cloud-Based)**: Again, you click 'Get Data', choose 'Azure SQL Database'. You enter the server name (e.g., 'contoso-hr.database.windows.net') and database name ('ContosoHR').
10. **Authenticate Again**: This time, you choose 'Microsoft Account' and sign in using your organisational credentials. (This is also a Microsoft Entra ID-based login, just for a different kind of resource). You select the 'StoreRegions' table. You choose 'Load'.

Now you have three different slices of data in your Power BI model. Your real-world skill is not just pressing the buttons, but understanding the distinctions: the SQL Server uses Windows Auth, the cloud sources use Microsoft Entra ID, and the file needed transforming. If any of these connections fail – say the SharePoint URL had a typo, or you didn't have the right role-based access control (RBAC) permission on the Azure SQL Database – your report would be empty. You then have to trace the error message, check your credentials, and confirm you have the correct connector. This constant troubleshooting of 'why can't I connect' is the bulk of an IT professional's daily work with data sources.

## Exam focus

The PL-300 exam tests objective 1.2, 'Connect to data sources', in several predictable ways. You will not be asked to perform a live connection; instead, you will be given scenario-based multiple-choice questions. The exam loves to set traps by making you choose between similar-sounding options. Here is exactly what you need to know.

**What They Love to Test**

- **The Correct Connector**: You must know which Power BI connector to use for a given scenario. For example, connecting to a table in an Azure cloud data warehouse requires the 'Azure SQL Database' or 'Azure Synapse Analytics' connector, not 'SQL Server', even though both use SQL.
- **Authentication Methods**: This is a major trap. The exam tests your knowledge of which authentication method to use in which situation. The core trinity is:
  - **Windows Authentication**: For on-premises data sources like a local SQL Server connected to your corporate domain.
  - **Microsoft Account / Microsoft Entra ID Authentication**: For all Azure cloud services and many other cloud services like SharePoint Online.
  - **Database Authentication (Username/Password)**: For some generic databases (like MySQL) or legacy systems where credentials are stored separately from your Windows or organisational login.
  - **Anonymous / Key Authentication**: For some web services or Azure Blob Storage, where access is granted via a key rather than a user identity.
- **Connection Modes (Import vs. DirectQuery)**: The exam will present scenarios asking which mode to use. The pattern is:
  - If the scenario mentions 'real-time data', 'live data', or 'data that changes constantly', they want DirectQuery.
  - If the scenario mentions 'large datasets', 'complex transformations', or 'fast performance', they want Import.
  - If the scenario mentions 'on-premises data source' and 'you need to enforce row-level security at the source', focus on DirectQuery, but check for other constraints.
- **On-Premises Data Gateway**: This is a critical concept. If a data source is in your office (on-premises) and you want to refresh a report in the Power BI Service (the cloud), you need a gateway. The exam will test whether you know this requirement exists. A common question: 'You have a Power BI report connected to an on-premises SQL Server. You publish to the Power BI Service. Users complain the report is not updating. What is the likely cause?' Answer: 'No gateway is configured.'
- **Power Query vs. DirectQuery Limitations**: The exam tests that you know that certain Power Query transformations are not supported when you use DirectQuery mode. For example, merging two tables in Power Query is often not supported in DirectQuery.

**Traps to Watch Out For**

- **Confusing Data Sources with Destinations**: Questions may ask about connecting to 'Azure Blob Storage' (a source of files) versus 'Azure SQL Database' (a source of tables). They are both sources, but require different connectors and have different navigation experiences.
- **Overlooking the 'Single Sign-On' (SSO) Option**: For DirectQuery connections to SQL Server or Azure SQL, you can enable SSO so that the report viewer's identity is passed to the database. The exam often tests that you need to select this option in the Power BI Service settings for the data source.
- **Thinking 'Import' is Always Better**: New beginners often think importing is the only way. The exam will give you a scenario where DirectQuery is the only viable option (e.g., a data source that cannot be copied for compliance reasons, or needs to be queried in real time every second).
- **Ignoring the 'Select Related Tables' Option**: In the Navigator window, there is a button that automatically selects related tables from a database. The exam might ask about it, testing if you know it exists or if you know it can bring in unwanted data.
- **Forgetting the 'Data Source Settings' Dialog**: This is in Power BI Desktop (under File > Options and Settings) and in the Power BI Service. The exam tests that you know you can change credentials or connection details here, rather than rebuilding the entire connection.

**Words and Concepts to Memoise**

- **Connector**: The specific adaptor in Power BI for a given data source.
- **Authentication**: The process of verifying your identity to access the data.
- **Gateway (On-premises data gateway)**: Software that bridges the gap between on-premises data sources and the cloud Power BI Service.
- **Import Mode**: Data is copied into Power BI. Fast, but needs refresh.
- **DirectQuery Mode**: Data stays in the source. Queries run live, but are slower and have feature limitations.
- **Power Query**: The data transformation engine you use after connecting.
- **Microsoft Entra ID**: The identity service used for cloud authentication.

## Step by step

1. **Open Power BI Desktop and Initiate Connection** — Click the 'Get Data' button on the Home ribbon. This opens the 'Get Data' dialog, which lists all available connectors, organised by category (e.g., Database, File, Online Services). This is the starting point for every connection.
2. **Select the Appropriate Connector** — Search for or find your specific data source (e.g., 'SQL Server', 'Excel', 'Azure SQL Database', 'SharePoint Online List'). Choosing the wrong connector will result in a failed connection or data from the wrong source. This step maps your data source to Power BI's universal adaptor.
3. **Enter Connection Details and Authenticate** — A dialog box appears asking for the server/URL and database/file path. You then select an authentication method (e.g., Windows, Microsoft Account, Basic). This is where you prove you have permission. If you don't have the right credentials, the process stops here. Always verify your access before proceeding.
4. **Preview and Select Data in the Navigator** — After successful authentication, the Navigator window displays the available data objects (tables, views, worksheets). You can tick boxes to select exactly what you need. Use the preview pane to check the data looks correct. This step lets you avoid loading unnecessary data, saving memory and time.
5. **Choose Load or Transform and Choose Connection Mode** — Click 'Load' to import the data directly into your model using Import mode, or 'Transform Data' to open Power Query Editor for cleaning. Before loading, you can also configure the connection mode (Import or DirectQuery) for the source. 'Load' finalises the connection and populates the Fields pane. 'Transform Data' lets you change data shape before loading.
6. **Verify and Manage the Connection in the Power BI Service** — After publishing the report, you must check the Data Source Settings in the Power BI Service. For on-premises sources, configure an On-Premises Data Gateway. For cloud sources, verify credentials are stored correctly. This step ensures the report can refresh automatically (scheduled refresh) without manual intervention. Without this step, your published report will be stale.

## Comparisons

### Import Mode vs DirectQuery Mode

**Import Mode:**
- Copies entire data set into Power BI's internal storage (VertiPaq engine)
- Supports all Power Query data transformation features
- Requires manual or scheduled refresh to update data; data may be stale between refreshes

**DirectQuery Mode:**
- Data stays in the original source; no copy is made to Power BI
- Limited Power Query capabilities (e.g., no table merges)
- Always shows the most current data from the source; no refresh needed

### Windows Authentication vs Microsoft Account (Microsoft Entra ID) Authentication

**Windows Authentication:**
- Uses the Windows user credentials of the person running Power BI Desktop
- Best for on-premises resources like a SQL Server on your corporate network
- Does not work well for cloud services outside the corporate domain

**Microsoft Account (Microsoft Entra ID) Authentication:**
- Uses an organisational Microsoft Entra ID account (work email)
- Required for any Azure service (Azure SQL, Azure Data Lake) and SharePoint Online
- Supports Single Sign-On (SSO) and multi-factor authentication

### Relational Data Sources vs Non-Relational Data Sources

**Relational Data Sources:**
- Data is organised into tables with predefined columns (schema)
- Very efficient for storing and querying structured data
- Common examples: SQL Server, Oracle, Azure SQL Database

**Non-Relational Data Sources:**
- Data can be stored in various formats (files, blobs, documents) without a fixed schema
- More flexible for unstructured or semi-structured data
- Common examples: Excel, CSV, JSON, Azure Blob Storage

### On-Premises Data Source vs Cloud-Based Data Source

**On-Premises Data Source:**
- Data stored on servers physically located in your company's building
- Requires an On-Premises Data Gateway for cloud refresh to work
- Often uses Windows Authentication for access

**Cloud-Based Data Source:**
- Data stored on servers managed by a cloud provider (e.g., Azure)
- Can be accessed directly without a gateway from the Power BI Service
- Usually requires Microsoft Entra ID authentication

## Diagram

_This diagram shows the logical flow of connecting to a data source in Power BI, from choosing a connector through to configuring scheduled refresh in the Power BI Service._

```mermaid
flowchart TD
    A[Start: Click 'Get Data'] --> B[Choose a Connector]
    B --> C{Is it a database?}
    C -- Yes --> D[Enter Server & Database Name]
    C -- No --> E[Select File / Web / Other]
    D --> F[Authenticate]
    E --> F
    F --> G{Can you authenticate?}
    G -- No --> H[Connection Failed - Check Credentials]
    G -- Yes --> I[Select Tables/Objects in Navigator]
    I --> J{Choose Mode}
    J -- Import --> K[Copy data to Power BI]
    J -- DirectQuery --> L[Leave data in source]
    K --> M[Report ready (static snapshot)]
    L --> N[Report ready (live queries)]
    M --> O[Publish to Power BI Service]
    N --> O
    O --> P{Is data on-premises?}
    P -- Yes --> Q[Configure Gateway for refresh]
    P -- No --> R[Configure credentials in Service]
    Q --> S[Scheduled refresh works]
    R --> S
```

## Common misconceptions

- **Misconception:** I can connect to any data source using just the 'Get Data' button without needing any special permissions. **Reality:** Connecting to a data source always requires some form of permission. This might be a file path you have access to, a database username and password, or a Microsoft Entra ID account with the correct role-based access (RBAC) in Azure. (Many beginners come from using software where you just open files, not understanding that enterprise databases and cloud services are locked down for security. They think a connection is a purely technical step divorced from security policy.)
- **Misconception:** Import mode and DirectQuery mode are essentially the same thing; one is just slightly slower. **Reality:** They are fundamentally different. Import makes a full copy of the data, while DirectQuery leaves the data in the source and sends queries back to it for every visual interaction. DirectQuery has many limitations (e.g., no Power Query merges) and is not just a 'slower version' of Import. (This misconception arises because both modes result in a visual appearing on the report canvas. Beginners don't immediately see the architectural difference in how the data is retrieved and stored, so they assume it's just a performance tweak.)
- **Misconception:** If I connect to a cloud data source like Azure SQL Database, I should always use my personal Microsoft account (like a Hotmail or Outlook.com account) to log in. **Reality:** For Azure and most enterprise cloud services, you must use your organisational Microsoft Entra ID account (your work email), not a personal account. The organisational account has the permissions granted by the company's IT department. (People often associate 'Microsoft account' with their personal Xbox or Outlook login. They don't distinguish between a personal Microsoft account (for consumer services) and a work/school account (managed by Microsoft Entra ID for enterprise resources).)
- **Misconception:** Once I create a connection to a data source in Power BI Desktop, the connection details and credentials I used are saved permanently and will work automatically in the Power BI Service. **Reality:** Power BI Desktop and Power BI Service use different security contexts. You often need to reconfigure credentials (set up a gateway for on-premises sources or re-enter your cloud credentials) in the Power BI Service's data source settings before scheduled refresh will work. (New users think of Power BI Desktop as the complete tool. They don't realise that the Service is a separate environment with its own security layer, especially when dealing with on-premises data or cloud sources that require stored credentials for unattended refresh.)
- **Misconception:** Power BI can connect to any data source I have on my computer, including personal files on my Desktop. **Reality:** Power BI can only connect to data sources for which a connector has been built. It cannot connect to arbitrary file formats or custom databases. Also, when publishing to the Power BI Service, it cannot reliably access files on your local computer unless you re-upload them or use a personal gateway for small-scale scenarios. (This comes from using general-purpose software that can open any file. Beginners overestimate Power BI's universality, forgetting that each connector is a specific piece of code built in advance by Microsoft.)

## Key takeaways

- Connecting to a data source in Power BI is not a single action—it involves choosing a connector, authenticating, selecting data, and deciding between Import or DirectQuery mode.
- Use Windows Authentication for on-premises databases and Microsoft Entra ID for all cloud-based sources in an enterprise environment.
- DirectQuery sends queries to the source every time you interact with a visual, making it ideal for real-time data but not for complex transformations.
- An On-Premises Data Gateway is mandatory for refreshing on-premises data in the cloud-based Power BI Service.
- The Navigator window allows you to preview data and select specific tables or objects before loading them into your model.
- Both the server name and database name are required when connecting to a relational database like SQL Server.

## FAQ

**What is an On-Premises Data Gateway in Power BI?**

An On-Premises Data Gateway is a software bridge you install on a local server that allows the cloud-based Power BI Service to connect securely to data sources on your internal network, such as a local SQL Server or a file share.

**Can I use Power BI to connect to a private company database from my home computer?**

Yes, but you must use the correct authentication method (usually Windows Authentication or a VPN) and you must have permission from your IT department. The connection will fail if you lack the necessary credentials or if the database is behind a firewall that blocks external connections.

**What is the difference between Import and DirectQuery in Power BI?**

Import copies the data into Power BI's storage for fast performance, but requires a data refresh to see updates. DirectQuery leaves data in the source, querying it live for every report interaction, giving you real-time data but with slower response times and limitations on transformations.

**Do I need to use the same Microsoft Entra ID login for all cloud data sources?**

Ideally yes, because your Microsoft Entra ID account is centrally managed by your IT team and is the secure, single identity for accessing all company resources, including Azure SQL Database, SharePoint Online, and Dataverse.

**Why does my connection say 'Failed to authenticate' even though my password is correct?**

This often happens because you are using the wrong authentication method. For example, using 'Database' authentication when you need 'Windows Authentication', or using a personal Microsoft account instead of your organisational Microsoft Entra ID account. Also check that you have network access to the server.

**Can I connect to an Excel file stored on my local machine and share the report with others?**

No. If you import a local Excel file into Power BI Desktop, the file path is local. When you publish to the Power BI Service, it cannot access that file unless you upload it to a cloud location (e.g., SharePoint or OneDrive for Business) or use a personal gateway. For others to refresh the report, the data must be accessible from the cloud.

## Check your understanding

1. **What is the primary difference between Import and DirectQuery modes in Power BI?**

   Answer: Import copies the data into Power BI's internal storage, while DirectQuery leaves the data in the source and sends queries to it for each visual interaction.

2. **You are connecting to a SharePoint Online list. Which authentication method should you use?**

   Answer: You should use your organisational Microsoft Account (Microsoft Entra ID) credentials, not a personal Microsoft account or Windows Authentication.

3. **What is an On-Premises Data Gateway used for?**

   Answer: It is a software bridge that allows the Power BI Service to securely connect to and refresh data from on-premises data sources.

4. **A user connects to a SQL Server database using Windows Authentication in Power BI Desktop, but the report refresh fails after publishing. What is the most likely missing component?**

   Answer: An On-Premises Data Gateway is likely missing, because the Power BI Service cannot use Windows Authentication directly from the cloud to reach an on-premises SQL Server.

5. **Which connector should you use to connect to a table stored in a Microsoft Azure cloud data warehouse?**

   Answer: You should use the 'Azure SQL Database' or 'Azure Synapse Analytics' connector, depending on which specific Azure service hosts the data.

---

Interactive version with quiz and diagrams: https://courseiva.com/learn/pl-300/data-sources-and-connections