Introduction
The data we work on using PowerBI is collected from different sources. These sources include:
- Files such as excel, csv and PDF.
- Databases such as MySQL, PostgreSQL, SQL Server, Oracle.
- Cloud Services such as Azure SQL, Snowflake, BigQuery.
- Web/APIs such as REST, OData, SharePoint.
PowerBI can connect to these data sources through different ways. The most basic way is through importing a local csv/excel/PDF files which can be quite tedious especially when the data to be imported is huge. Huge collections of data are majorly stored in databases since it is relatively easy to manage data when it is stored in a database. In this tutorial, we will be covering the basic steps one should apply when connecting PowerBI to either a local or cloud PostgreSQL database.
Connecting to a Local PostgreSQL Database
To connect to a local PostgreSQL database, you have to ensure that PostgreSQL server is installed on your computer. You can get it at: PostgreSQL Download. Once installed, follow the instructions in the documentation to install it correctly on your device and set a password that you will remember easily. Another essential software tool needed to manage your PostgreSQL database is Dbeaver which provides an interactive interface for you to interact with your PostgreSQL database.
Once installed, ensure that you connect Dbeaver to your PostgreSQL database by pressing Ctrl+Shift+N and enter your database details as shown below:
Note: The default database that comes installed with PostgreSQL server is named postgres. You can, however, create different databases that you would like to use.
Once you have entered your details, click on Test Connection then click on OK to connect to your database. To upload data to your database, follow the steps that I included in a previous article here.
Now that the data has been uploaded, we can import it using PowerBI. First, open PowerBI, create a blank report and click Get data on the home ribbon as shown below:
Next, select the More option and the following dialogue box will appear:
Since we are connecting to a PostgreSQL database, select the PostgreSQL database option to access the following dialogue box:
The server details include the host and the port and they should be entered in the format; host:port, which in this case will be 127.0.0.1:5432. The rest of the details in this section are similar to the details entered in Dbeaver initially.
Note:The Use encrypted connection box is left unchecked because the database is hosted locally.
After entering the details, click next and the following dialogue box will appear:
From this point, click Next and the data will be imported by PowerBI for you to analyze.
Connecting to a Cloud-based PostgreSQL Database
The process involved in connecting to a cloud-based PostgreSQL database is relatively similar to that of a locally hosted database. In this case, we will use an Aiven database. To get an Aiven database, you need to create an account and subsequently create a new project which should be a PostgreSQL instance. Once that is created, you will have a database with details in this form:
Downloading and Importing the SSL Certificate
Now that we have a cloud database, it is possible to connect to it through PowerBI, however, we need to download an SSL certificate to enable the computer to trust the connection between PowerBI Desktop and the Aiven cloud-based database. This is where the Use encrypted connection box will be needed. To download the SSL certificate, go to the CA certificate section as shown in the screenshot above and click on the download button.
Next, on your taskbar, search for computer certificates and select the result shown below:
Once selected, the following dialogue box will appear:
In this dialogue box, navigate to Trusted Root Certification Authorities and click on the drop-down arrow, then right click on Certificates, then go to All Tasks, then Import....
This will lead you to the following dialogue box:
Click Next, upload the certificate file which will have been downloaded as ca.pem from Aiven. You will be required to select the all files option when browsing your device for the certificate file. Continue until you get the confirmation that import is successful as shown below:
Now that the SSL certificate is imported, we can connect PowerBI to the Aiven database.
The procedure to connect to the Aiven database shares the same steps as connecting to the local PostgreSQL database, only that the details entered will be different as shown below:
Since the device now trusts the Aiven cloud-based database after importing the certificate, we can check the Use encrypted connection box to encrypt the connection and access the data needed as shown below:
Conclusion
PowerBI is a useful tool in extracting data from either local sources or cloud-based sources. Local database connections are quite straightforward since everything is available locally. Sources that are cloud-based require additional authentication in order for the extraction to work. This enables any PowerBI user to pull data from different sources all over the internet.
Top comments (0)