Connecting Power BI to a SQL Database:local & cloud option

INTRODUCTION:

Think of this you have raw data, for example let's say: customers data, products data, sales data and inventory data. Imagine, you needing to get meaningful insights from this manually. It can be hectic and overwhelming. That's why Microsoft produced a powerful tool known as Power BI. What is Power BI? its a detailed business intelligence(BI) and data visualization tool, that helps analysts transform raw data into meaningful insights and reports. In power BI, you can create interactive dashboards, clean data and perform detailed analysis, but that is just a drop in the ocean there's much more Power BI can do. Companies and organisations can easily connect Power Bi to their databases. By doing this, companies can be able to work on live and secure data in real-time, therefore enhacing their decision-making. In this article we will be discussing one of the most important uses of Power BI: how to connect Power BI to a SQL database both locally to your machine and through a cloud option.

Connecting Power BI to a Local PostgreSQL:

Connecting Power BI locally: means you are connecting from a database that's hosted inside your computer.

• First step is launching or opening Power BI Desktop. Below is how the loading screen looks like:

• Selecting a data source:

After Power BI has fully loaded, click blank report you will be redirected to the report view.
Inside the report view click get data. You will see multiple sources of data.

• Below is the pop up you will get after clicking get data

Since you want to connect from a database, click the database option. In this case, select postgreSQL and click connect.

• Entering Database Details:

You will get a pop up that will require you to enter the database details.

1. Server: This is the name of the server that your database runs on. By default, its usually: localhost:5432
2. name: This is the name for you database that you want to connect to Power BI. Reference Image:

• Data connectivity mode Now, in the above Image you can see data connectivity mode.

The modes include:

1. Import -> Copies data into Power BI directly. Its best for faster querying.
2. Direct Query ->In this mode querying occurs in real-time and directly from your database. It's best when dealing with large datasets.

The type of mode entirely depends on your data set requirements. But the most recommended for beginners is the import mode.

• Authentication:

After entering the database details, click ok.
A popup will appear requiring you to enter the database credentials these will be the:

1. username -> postgres
2. password -> ******

Click OK. Following these steps you will be able to connect your database to Power BI locally.

• Confirming the connection:

To confirm this: you will see the tables from the database you connected.
You can now be able to select the tables that you want to work on.

• Loading the tables into Power BI:

After connecting your database, you will see a navigator window, through this window you will select the tables that you wabt to load in Power BI.
Simply you do that by checking the boxes. And clicking the Load button.

Reference Image:

• Common Failures when connecting:

When selecting the data source, you might fail to see the PostgreSQL database.
To solve this:

1. Download the Npgsql PostgreSQL driver and install it in your computer.
2. Then afterwards restart Power BI.

Connecting Power BI to a cloud database:

The second option, is connecting Power BI to a cloud based database.
In this case, we will be connecting through the aiven platform.

• Step 1: Getting the connection details: Create an aiven account. After creating an aiven account. Click create service

Upon creating a service, you will need to select a database that you want to connect Power BI to.
In this case, we will be using PostgreSQL for the tutorial.

Below is a list of the services aiven provides:

Thereafter, choose the plan aiven offers multiple plans ranging from free to pro.
Choose the plan the best suits your needs. After that select the region that is near you.
Then click create service.

• Getting the connection information After creating a service, you will acquire the connecting credentials. Below is a sample of the credential details:

• Loading the credentials in Power BI

1. Open Power BI desktop
2. Navigate to the report view by clicking blank report.
3. And select the get data.
4. For the data source: select database and select postgreSQL.
5. Then click connect.

After that a prompt will appear requiring you to enter the credential details.
For example

1. server: Enter pg-12345.aivencloud.com:12345
2. name: defaultdb

• Downloading SSL certificate
  For cloud databases, a SSL certificate is required to ensure secure and realiable connection.
  To download the SSL certificate, inside aiven, under the overview you will see the connection information
  From this page you can also download the CA certificate. The CA certificate verifies the identity of your database.
  Simply, it's for establishing a trust between Power BI + Postgres + Aiven
  In most cases it usually looks like: ca.pem

• Configuring SSL Mode
  Now that you have acquired a CA certificate you will need to configure the settings in Power BI.
  Therefore, when Power BI asks for an encryption setting choose
  SSL MODE: required
  The importance of selecting this mode is that all the transactions between Power BI and PostgreSQL will be encrypted using the SSL/TLS.

• Authentication
  After that, Power BI will prompt you for an authentication. Whereby, you will be required to enter your credentials.
  Example:
  1.username:avnadmin
  2.password:********
  Then Click Connect

Loading Data into Power BI:

Now that we have connected Power BI to our PostgreSQL database and also loaded tables. The next thing we will explore is how relationships are created between the tables.

• Creating Relationships in Power BI:

The key to creating relationships, is understanding two concepts that is the primary key and the foreign key.

1.Primary key- This is a unique identifier in a table.

By unique -> it means it cannot be replicated. They include National IDs, CustomerIDs. There can never be a single person with two national IDs e.t.c
2.Foreign key -> a foreign key its a primary key inside another table.

For example: Your database can contain customers, products, sales and inventory tables.
Inside the customers you will have a customer_id now this is a primary key, but the customer_id we still appear at the sales table this is a foreign key.
In this case the customer_id links the customers and sales table.

• Automatic relationship creation:

Power BI can automatically detect relationships after you have loaded your data into it.

But your data needs:

1. to be correctly structured,
2. to have well defined columns
3. to have correct data types

By clicking the model view in Power BI you will see the relationships

Illustration:

• Manual creation of relationships in Power BI In a case where Power BI doesnot automatically detect relationships, you can create the manually.

1. Still at the model view.
2. Drag one common column and match it with another column from another table.
3. A relationship dialog box will appear.
4. To confirm column pairing, relationship type and the cross-filter direction.

• Using the manage relationships method:

-> Go to the modelling tab.

-> Select New relationship

-> Select the first table and the common column
-> Then, select the second table.
-> Choose a relationship type and cross-filter direction.
-> Click OK.

Data Modelling in Power BI:

Data modelling is the process of structuring and connecting data from various sources.
Through data modelling, data analysis becomes accurate, efficient and easy.
There are three types of data modelling:
-> Conceptual data modelling - Just like its name conceptual its basically a concept defined by the business stakeholders, collaborating with the technical teams.
It defines the entities and relationships between them.
-> Logical data modelling - This level spices things from the conceptual model, actually it builds more from the conceptual.
In the logical model attributes and keys are added to the defined entities.
-> Physical data modelling - This is the most detailed level.
Its the actually implementation of the logical model.
In this you can actually visualize how data is stored in databases, also it includes the data types and Indexes.

Components of Data Modelling:

1. Relationships:

Relationships are the main feature of data modelling.

They define how tables connect based on a common column.

Types of relationships include:
-> One-to-One-One user can only have one profile
-> One-to-Many-One customer can have multiple orders.
-> Many_to-Many-Many-Many students can register for multiple classes
Basically, these relationships help Power BI connect data logically.

2.Star Schema Method:
The star schema, its the most recommended and widely adopted model in Power BI.
It usually takes the shape of a star with a:

• Facts table -> which contains measurable data like sales, revenue e.t.c
• Dimension table ->which contains descriptive data, this is data that describes more of what happened. For example: (products) -> product_details, Product_category

The advantages of star schema include:

1. Fast performance - due to optimized quering data retrieval becomes faster.
2. Ease of use - application of the star schema is straight forward.
3. Lower Maintenance - due to the less complexity of the dimension tables, maintaining the star schema is simple and easy.

Importance of relationships to Data analysis:
Relationships in Power BI are very important as they tell us how different tables connect and relate to each other.
Lets discuss how this helps Power BI analyze data correctly.

1. Improves the performance analysis -> Through linking tables efficiently, relationships help Power BI reduce the amount of data processed for a retrieval.
2. Data integrity and accuracy -> Relationships ensure there is consistency through the data.
3. Reduce redundancy -> Relationships ensures there are no duplicates, by making sure that data is only stored once and is referenced from other tables.
4. Table connections -> Power BI connects different tables based on the matching columns, through this data retrieval becomes easy. For example you can match a customerID in customers with a Sales or Orders. Without relationships this is impossible.

The Importance Of SQL Skills

1. Access to data sources -> Having SQL skills allows analysts to access and connect data from various sources.(e.g. MySQL, PostgreSQL)
2. Handling large datasets -> SQL concepts enable you as a data analyst to manipulate and work on large data easily and efficiently.
3. Data cleaning and preparation -> Oftenly, Raw data is messy but SQL has powerful functions for data cleaning, filtering and removing duplicates.
4. Perform complex analysis -> Through the use of operations like joins anaysts can connect different tables, this allows for comprehensive analysis e.g. you can link customerID with sales/orders table to view the amount of peroducts bought by a customer.

How SQL is used For Daily Tasks:

1. Data Retrieval: You can retrieve or get data from a specific path you want:

setting a specific search_path :

Data retrieval:

The above example shows data retrieval from specific tables.

2.Data Filtering:
SQL allows you to select a subset of data from a larger dataset by applying conditions.
Filtering in SQL, the where clause is used with the select, update and delete statements.

Example:

1. Performing Aggregations: SQL can summarize your data based on several functions like: SUM(), COUNT(), AVG()

Example:

4.Preparing data before building dashboard:

For you to build a dashboard, the data has to be structured properly for the readiness of analysis.

The following are methods of preparing data before building a dashboard:

• Performing Joins: Joins combine multpile tables based on matching columns Example: Below you can see we have joined the customers and sales table base on the customer_id. We are finding top 5 customers who made the highest purchase

• Adding new calculated columns: This adds new caluclated columns to your table In this case we use aliases AS to name the new columns' For example:

• Handling Missing Values: Nulls in your data can result to incorrect or inaccurate results. Therefore, replacing nulls is essential for accurate results. We use the COALESCE() function. Example:

• Sorting Data: This is organizing data for better analysis

In the above example you can see we have sorted the names of the customers in a descending order.

Conclusion:

Connecting databases to Power BI is an essential skill for any data analyst. This gives you access to real-time and realible data for analysis. Therefore, based on your data requirements there are multiple options to connect. At first, it seems complicated. However, with more practice it becomes easy to understand and apply. Remember to always practice, trial and error builds confidence and you will be able to acquire the expertise required to connect Power BI to local or cloud databases as well as work with the data.