Connecting Power BI to SQL Databases

Power BI is a data visualization tool and a business intelligence platform that turns raw data into interactive dashboards and reports. This tool looks to connecting users to various data sources, including SQL databases.

Connecting to a Local Database (PostgreSQL)

Let's look at two examples of connecting Power BI to SQL databases:

• Connecting to a local database (PostgreSQL)
• Connecting to a cloud SQL database (Aiven)

Connecting to a Cloud SQL Database (Aiven)

Aiven is a cloud platform that provides database services. It requires a certificate, which is SSL for connections.

Step-by-Step Instructions for Aiven

Let's start by creating or logging in to Aiven:

1. Click the 'Create services'.
2. Choose the provider and region.
3. Click 'Create service'

Get the following information and note them down:

• Host
• Port
• Database
• Username
• Password

Download the CA certificate from Aiven and install it on Windows in the Trusted Root Certification Authorities stores.

Using DBeaver to Connect to Aiven

Use DBeaver to connect to Aiven and import the raw dataset. Verify that the data has loaded successfully.

Connecting Power BI to the Database

To connect Power BI to the database:

1. Open Power BI.
2. Click 'Get data' to the PostgreSQL database.
3. Input the server and database.
4. Set the SSL mode.
5. Connect and load the table.

Look for the table that you either want to load or transform. You can also choose to clean the data.

Cleaning the Data

Here, we will turn the messy raw data into a cleaned dataset that will be used later for our dashboard.

Issues with the Data

The data has the following issues:

• Text in number column
• Missing values
• Mixed capitalization
• Mixed date types format

Measures and Calculations Used

The measures include:

• Total revenue
• Total Units Sold
• Total Orders
• Gross Profit
• Gross Profit Margin
• Avg Delivery Days
• Avg Rating

The calculations include:

• Sum
• Count
• Divide
• Average

Visuals in the Dashboard

The visuals I include in my dashboard are:

• Slicers
• Pie chart
• Bar chart
• Column chart
• Donut chart

Conclusion

Building a dashboard is a result of importing raw data, connecting Power BI, cleaning and transforming the data, and then finally designing an interactive dashboard. The end product helps management make good, data-driven decisions.