Databases are like containers used to store data,the data may be structured, semi structured, or unstructured.The databases may be located on the local machines or on the cloud. In this article we shall discuss how to join databases to powerbi.
On our local machines we need to have a database management system like Dbeaver to manage all the activities happening on the database.
In Dbeaver , there is a ribbon with Database title.Click it, a drop down will appear and select a new connection.
Select your preferred database in this case we choose postgresql.
In the connection settings we are given the host,database,port,username but we have to input the password(The password should be the one we created when we were installing postgresql).
Proceed by clicking Test Connection ,if you are prompted to download database drivers click download.Once connection has been established click ok .
On the defaultdb create a database and and Schema with the following SQL commands.
CREATE database mytrial;
CREATE Schema Powerbi;
The database is empty and it's important to load data into the database.Hover the cursor on the Schema,right click it and import data.Click input files and then browse the file in your computer.Select the file you want in your computer and select open.The file will be loaded in your database.
To connect the database to powerbi open powerbi, click Get Data then more and select Database. On the Database tab select your preferred database e.g. postgresql.
On the Postgresql database tab there are server and database spaces and it's important to fill the spaces. On the server paste this ip 127.0.0.1 full colon and the port 5432.Type the database.
Type the username and the password.
Now we have connected database on our machines with power bi.
If the database is on the cloud we need to have a Saas subscription (Software as a service) platforn that gives us the password,host,ca certificate,port,username and password.
Once we have the above information open powerbi and select Get Data,select more and click Database.On the database tab click on Postgres.A small tab will appear titled Postgres database with database and server spaces.On the Saas platform copy host,port fullcolon and database.
Go ahead and type the username and password then click connect.
You will encounter "Unable to connect" error in powerbi.
On the Saas platform download ca certificate.
In your computer there is a start button,search Manage user certificate and select it.Select the Trusted Root Certification Authorities.Click on the greater than sign before Trusted Root Certification Authorities.Right click on certificates.A dropdown will appear,click on the greater sign on "All Task" an import table will appear select it.Click next then browse,select the ca certificate then open it.Click next and finish.Certificate import wizard will appear showing th import was successful.
On powerbi click the close button and open powerbi.
Select Get Data,select more and click Database.On the database tab click on Postgres.A small tab will appear titled Postgres database with database and server spaces.On the Saas platform copy host,port fullcolon and database.
Go ahead and type the username and password then click connect.
We have successfully connected database on aiven to powerbi
Top comments (0)