Power BI
Power BI is a business intelligence and data visualization tool that helps organizations collect, analyze and visualize data so as to make better and informed business decisions. It converts raw data from multiple sources into interactive dashboards, charts and reports that are used in decision making.
Power BI is used in data analysis and business intelligence because it allows the users to import data i.e. from MS Excel, databases and cloud services. Power BI has tools like power query which is used for data cleaning and Data Analysis Expressions(DAX) functions which are used to perform calculations. It has charts that are used for data visualization too.
Databases are used by large organizations to store large volumes of data. These databases are connected to Power BI to retrieve the data which is then used for analysis. Companies connect power bi to databases because the provide data security and integrity, can handle large sets of data and provide real time data access which allows dashboards to show regularly refreshed data.
SQL databases are important for storing and managing analytical data because they provide data storage, facilitate data management i.e. inserting new data, updating and deleting outdated data which helps maintain data consistency. databases also facilitate data retrieval which is used for analysis.
Connecting Power BI to a local postgreSQL database.
then open a blank report
from the home tab click on get data
from get data click on database then select postgreSQL database then click on connect.
On the connection window that appears enter the server and database details.
from the database window provide the authentication credentials then click on connect
from the navigator select the data you want to upload then load from the available tables.
wait for the data to load
Connecting Power BI to Cloud Databases.
From your browser search Aiven then create an account or login if having an existing account.
Once on the Aiven console click on services to create a new service
select service type
select service tier, cloud, plan then on service basic enter a name then create service.
open the service and copy the connection information.
open dbeaver to connect to the cloud database
from dbeaver click on database then new database connection
select the database you want to connect to. Ensure it matches the cloud database. Then click on next.
on the connection settings carefully replace them with the connection information copied from the cloud database (Aiven). i.e. host, database, port, username and password then test connection.
after testing the connection then click on finish.
Downloading secure sockets layer (SSL) certificates.
From the Aiven connection information click on the download button. A file named ca.pem is downloaded to your local computer. when connecting on Power BI when configuring SSL the SSL mode should be verify.ca then on root certificate select the downloaded ca.pem file from your downloads then click connect. SSL certificates are important because they encrypt data sent between Power BI and the postgreSQL server, verifies authentication and ensures data integrity between the client and the server.
Why Is an SSL Certificate Required?
When connecting to a cloud database over the internet, your credentials and data travel across public networks. An SSL (Secure Sockets Layer) certificate encrypts this connection, ensuring that:
- Your data cannot be intercepted by third parties (man-in-the-middle attacks)
- The server you're connecting to is verified and legitimate
- Your username and password are transmitted securely
- Aiven enforces SSL by default on all connections, which is a security best practice.
Step 3: Connect in Power BI
Follow the same steps as the local connection:
Click Get Data - PostgreSQL Database
In the Server field, enter your Aiven host:port (e.g., pg-yourservice.aivencloud.com:12345)
Enter your database name
Expand Advanced Options and paste the following into the Additional Connection Parameters field:
sslmode=require;sslrootcert=C:\certs\ca.pem
Enter your Aiven username and password when prompted
Click Connect
Relationships in Power BI.
After loading data to Power BI click on model view to view the relationship between the tables
The primary keys connect with the foreign keys in the other tables.
Data modelling is the process of organizing data tables and defining how they relate to each other so that data can be interpreted correctly. Relationships define how records from one table relate to records in another allowing Power BI to correctly combine data. The common types of relationships are one to many, many to one and many to many. A star schema is the most common structure that is used in Power BI. The star schema contains the fact tables which contain measurable data and Dimension tables which contain descriptive data.
Why SQL skills are important for Analysts.
SQL skills enable analysts to retrieve, organize and prepare data efficiently before using it for visualization and reporting. SQL enables analysts extract specific information from large datasets using queries instead of importing the entire datasets which can be tiresome and time consuming. It also enables analysts to filter relevant records that are needed at a particular time using conditions like where. Aggregation functions like sum, count, avg, max and min help analysts to quickly summarize large datasets and identify trends before building visualizations. SQL also enables joining of tables, removing duplicates, sorting and grouping data which improve Power BI performance.
Top comments (0)