1. Connecting to a Database

Overview

Databases are common in most businesses, but connecting to them can be more difficult than connecting to simple files. In this lesson, we’ll learn how to connect to a PostGreSQL database hosted online.

To explore more Kubicle data literacy subjects, please refer to our full library.

Summary

  1. Course Outline (00:03)

    Many Power BI courses focus on connecting simple data sources like Excel files to Power BI. This course demonstrates how to connect to more advanced data sources. This course contains two main sections: 

    1. Connecting to a database and working with associated data

    2. Connecting Excel directly to Power BI, using Analyze in Excel, and setting up scheduled refreshes

  2. Lesson Goal (00:45)

    The goal of this lesson is to connect to a database that contains information related to UK pharmaceutical data.

  3. Connecting to a Database (01:02)

    Power BI Desktop can connect to a wide variety of database types, using the Get Data option. To connect to a database, you need to provide certain information. For example, connecting to a PostgreSQL database, you need to provide the server where the database is found, and the name of the database.

    Many databases give you the option to import the data or use DirectQuery. If you use Import, Power BI creates a copy of the database, and stores that copy in the workbook. This copy is then used when you want to create charts and visualizations. You need to manually refresh the connection to update the data stored in the workbook. If you use DirectQuery, Power BI creates a direct connection between the workbook and the database. Power BI queries the database directly any time we use its data in a visualization.

    The main advantage of DirectQuery is that data is updated any time you update a visualization. The main disadvantage is that frequently querying a database can be slow, especially if the database is large.

    In our case, we connect to a database stored locally in a Microsoft Access file. After connecting to this database, the navigator window will display a list of all the tables in the database. You can preview a table by selecting it from this list. Generally, a database contains many tables, and you’ll want to select only a small number of them to analyze.

Transcript

In our previous Power BI courses, we've connected Excel spreadsheets to Power BI Desktop, created reports there, and uploaded them to the Power BI Service. While Excel is an incredibly common data storage medium, you'll likely need to connect to more advanced data sources to Power BI. In this course, we'll look at how to connect and work with other data sources in Power BI.

In the first few lessons, we'll look at how to connect to databases, as well as how to work with the associated data. In the second half of the course, we'll see how to directly connect Excel to the Power BI Service, analyze Power BI data in Excel, and how to schedule refreshes.

In this lesson, we'll connect to a database that contains information related to UK pharmaceutical data.

Over the next few lessons, we'll work with the associated data to create this chart, showing the total monthly prescriptions of laxative drugs in England and Wales over time.

We'll start with a new blank report in Power BI Desktop. To connect to the database, we'll select get data, and then click the database tab.

As we can see, power BI Desktop connects to a wide variety of databases, including Access, SQL Server, and MySQL.

The exact procedure for connecting to a database varies depending on the type of database selected. As an example, let's select PostgreSQL.

In this window, we need to specify the location of the server where the database is found and the name of the database. Sometimes you may also need to specify a username and password to access the database.

For many databases, we have the choice of importing data or using Direct Query.

If we select import, then Power BI will connect to the database, create a copy of the data from the database, and store this copy in the workbook.

This copy of the data is then used whenever we create visualizations in our workbook.

We can manually refresh the data connection to update the data stored in this copy.

However, if we select Direct Query, no data is imported into the workbook. Instead, Power BI creates a connection between the database and the workbook.

Whenever we create or update a visualization that uses data from a database, Power BI queries the database directly to find the data it needs to create the visualization.

The advantage of Direct Query is that the data in the workbook is updated anytime we change the visualization. This reduces the need to manually refresh the data in the workbook.

The disadvantage is that querying the database directly can be slower, especially if the database is very large.

In our case, we won't connect to an online PostgreSQL database. Instead, we'll connect to a local database contained in an Access file. We'll click cancel to close the window, then select get data, and Access database.

We'll navigate to the file and open it.

We now see the navigator screen. On the left, we can see a long list of all the tables that are present in the database.

You can think of each table as being similar to a single sheet in an Excel workbook. We can see a preview of the data in any single table by selecting it. It it's uncommon that you would want to import every single table in a large database like this, as doing so would make our Power BI model very large and typical to use. Instead, we'll need to figure out which tables are necessary for our chart. We'll stop this lesson here. In the next lesson, we'll learn how to identify these relevant tables.

Online Service and Data Connections
Advanced Data Connections

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial