WHAT IS POWERBI
Power BI is a business analytics created by Microsoft that transforms raw, disconnected data into interactive visuals and actionable insights. It allows users to data sources, clean and model the information, and build stunning, real-time dashboards to drive smarter business decisions.
*How it works *
1.Connect: Pulls in data from virtually anywhere, including local Excel files, cloud platforms (like Salesforce or Google Analytics) and major databases .
2.Transform & Model: Cleans up messy data and establishes relationships between different tables.
3.Visualize: Uses intuitive drag-and-drop actions to build charts, graphs, maps, and matrices so data tells a clear story.
4.Share: Publishes reports to the cloud so teams can interact with the data, filter it, and make informed decisions.
SQL AND IT'S IMPORTANCE
SQL in full means Structured Query Language. It is the foundational standard for storing and managing structured data across modern businesses.
Benefits of SQL
Databases Guaranteed Data Integrity: Uses ACID properties (Atomicity, Consistency, Isolation, Durability) to ensure every transaction completes flawlessly without corrupting your data.
Massive Scalability: Handles millions of data rows smoothly while maintaining fast search speed
Standardized Language: Uses SQL, a universal language, allowing developers to write queries that work across different systems like MySQL, PostgreSQL, and SQL Server.
Advanced Security: Controls access through precise user permissions, encryption, and secure login protocols to protect sensitive business assets.
Powerful Relationships: Links different tables together using keys, eliminating the need to duplicate data across your system.
Connecting PowerBi to a local Postgre database
Install powerbi : Incase one does not have it
*Open Powerbi * : Open Powerbi and select Get data on the top bar menu
- _Scroll through the list _ Scroll through the list and head to Database whereby you will see Postgre SQL Database displayed on your right, click it and click connect
_Name the server and database _ Set your server name and database then click OK.
Username and password Set your username and password for authentication then click connect
NB: The name of the database must exists in the PostgreSQL
Connecting Power BI to a Cloud PostgreSQL Database (Aiven)
Many people and organizations store their data in cloud databases such as Aiven because of its flexibility and scalability.
Log in Aiven Log in to your aiven account and select the postgres database and activate it(power it on)
Click the service option To display details in the service option.
Scroll to CA Certificate Click the download option and save the certificate on your computer
Rename the certificateThe certificate will download as a file named: ca.pem
Windows expects to use the .crt extension. To make the certificate compatible,
rename the downloaded file ca.pem file to ca.crt.
Double click the file Double click ca.crt to install the certificate in your computer
Select **_Local Machine as the installation location_**
Select the second option Which is Place all certificates in the following store
Select the Trusted Root Certification Authorities
_Click finish _ A notification will appear to show that the import was successful
Part 2: Login to the Database
Gather the details from the Aiven Overview Page:
- Host Name
- Port
- Database Name
- Username
- Password Open PowerBI Then click Get Data
_ In the list, select More and choose PostgreSQL Database_
Enter the Server and Database using the values copied from Aiven. Leave the data connectivity mode on the default Import option.
Note: The server name uses the format hostname:port_number.
Insert the server name as hostname and name of the database, then click OK.
Insert the username and password, then click Connect.
Top comments (1)
Good work, Richard. The article is practical, well-structured, and the screenshots make the PostgreSQL, Aiven Cloud, and Power BI connection steps easier to follow.