Sign in or start a free trial to avail of this feature.
1. Connecting to a Database
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.
Overview of Databases
- Previous courses have focused on connecting to Excel sheets, but more complex connections, like databases are common in business
- In this course, we’ll see how to connect to a PostGreSQL database
Connecting to a PostGreSQL Database
- Power BI Desktop lets you get data from a wide variety of data source, including a large number of databases
- If you have not connected to a database before, you may need to download the program “ngpsql”, which is available here
- This allows Power BI Desktop to connect to the PostGreSQL server
- In order to connect to the database, you need to know the server, the database name, and you may also need a username and password
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 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 PostgreSQL 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 total monthly prescriptions of lots of drugs in England and Wales over time.
We'll start with a new blank report in Power BI Desktop.
To connect to a database, we'll select Get Data and then click on the Database tab.
As we can see, Power BI Desktop connects to a wide variety of databases including Access, SQL Server and MySQL.
In this case, we'll select PostgreSQL.
If you haven't connected to a PostgreSQL database before, you may receive an error message stating that one or more components needs to be installed.
If so, you should download the program Npgsql which allows Power BI Desktop to access the PostgreSQL database server. You can find a link to this program in the lesson notes.
If you already have the relevant components installed, you'll see this screen prompting you to enter the server and database name, these details should generally be given to you by your IT administrator.
We'll enter these details now.
You'll next be prompted to enter the username and password.
Again, these details should be provided to you by an IT administrator.. Once we've correctly input all of our details, we'll 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's very uncommon that you would want to import every single table in a large database such as this as doing so would make your Power BI data model very large and difficult 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.