Most Data Analytics stacks comprise three stages: An ETL tool + a Data Warehouse to centralize data + a visualization or BI tool. Here we will show how to use Microsoft Power BI as a BI tool, together with PostgreSQL as the Data Warehouse and Kondado as your ETL tool.
3. Creating your first integration: Now that your data destination and data sources are registered, you can create your first integration by following the platform onboarding steps.
Open the downloaded file and follow the installer's step-by-step guide. On the “Custom Setup” screen, select the option “Npgsql GAC Installation” and complete the installation:
If your PostgreSQL database is hosted by AWS, click here to download AWS's public key.
Convert the downloaded key into a certificate by accessing https://www.sslshopper.com/ssl-converter.html and using the settings from the image below:
When you click on “Convert Certificate” in the previous step, a file will start downloading.
Open the Windows command center by searching for “mmc” in the start bar:
Click on “Add/Remove Snap-in”:
Select “Certificates” and then click on “Add”:
Select “Computer Account” and click on “Next”:
Select the “Local Computer” option and click on “Finish”:
Note that “Certificates” will appear in the right box. Click on “Ok”:
Click on “Certificates”, and right-click on “Trusted Root Certification Authorities”. Select “All Tasks” and click on “Import”:
In the window that opens, click on “Next” and then “Browse”.
Go to the folder where you saved the certificate, and change the file format to “PKCS #7 Certificates”. You should then select the certificate file and click on “Open”:
In the certificate import wizard, click on “Next” and then on “Finish”. You will receive an alert notifying that the import was successful.
6. Connecting Postgres to Power BI:
- On the Power BI home page, click on “Get Data” in the top bar:
- Select the “Database” option in the side menu:
- Then choose the “PostgreSQL database” option from the list and click on “Connect”:
- Fill in the server data and database name and select the “DirectQuery” option so that your data is updated in real-time. Click on “OK”:
- Enter the database user name and password. Click on “Connect”:
- Now you will need to choose from a list which tables from your PostgreSQL database you want to bring into Power BI (remember to search by the table names you defined when creating your integration in Kondado, as per step 4). Just check the box next to the table names and click on “Load” once all are selected:
- Done! Now your PostgreSQL is connected to Power BI and your data will be available for viewing or transforming whenever you need:
