DEV Community: Sylvia Ndili

How to Connect Power BI to SQL Databases (Local & Cloud)

Sylvia Ndili — Sun, 05 Jul 2026 17:55:40 +0000

Connecting your data to Power BI is usually the very first step in building any meaningful dashboard. SQL databases, whether located on a local office server or hosted out in the cloud, they remain the industry foundation for this data.

Let's walk through how to set up both local and cloud-based SQL connections in Power BI, step-by-step.

Part 1: Connecting Power BI to a Local SQL Server

To pull data from a local SQL Server, you will need to have a server name and database configuration parameters.

Step 1: Open the SQL Server Connector

Launch Power BI.
On the Home tab ribbon, click on Get Data.
Select More... and search postgres to locate it under the Database category.

Step 2: Configure Server Details & Data Connectivity

A dialog box will appear asking for your server credentials and access choices.

Server: Enter your local server path i.e (localhost:5432)
Database: Enter your database name (postgres)
Data Connectivity Mode:
- Import: Downloads a snapshot of the data into the Power BI file. Best for fast performance and complex modeling.
- DirectQuery: Queries the data live every time you interact with a visual. Essential for massive datasets or real-time tracking.

Step 3: Provide Credentials

For authentication:

Select Database to enter your username and password, then click Connect.
Preview and Load Tables The Navigator window will open. Expand your database instance, check the boxes next to the tables or views you want to use and click Transform Data to clean them inside Power Query.

Part 2: Connecting Power BI to a Cloud Database (Aiven)

Cloud solutions like Aiven make it easy to deploy PostgreSQL databases. Because Aiven exposes a public endpoint by default, Power BI can securely stream cloud data.

Step 1: Gather Connection Details from Aiven

Before switching to Power BI, grab your credentials from the Aiven Console.

In the Overview tab, find the Connection information and copy the following fields: Host, Port, User, Password, and Database Name.

Step 2: Install the CA Certificate on Your Windows Machine

Power BI relies on the Windows Certificate Store to validate SSL handshakes. Download and install your ca.pem file as a trusted certificate.

Click the Windows Start menu, search for Manage user certificates, and open it.
In the left panel, locate and click on Trusted Root Certification Authorities.
Right-click on the Certificates folder inside that directory, navigate to All Tasks, and click Import.
The Certificate Import Wizard will pop up. Click Next, change the file extension filter to "All Files (.)" so you can find your ca.pem file, select it, and complete the installation wizard instructions.

Step 3: Select the PostgreSQL Connector in Power BI

Back in Power BI, click Get Data on the Home ribbon.
Click More..., choose Database on the left menu, and select PostgreSQL database.
Click Connect.

Establish the connection on Power BI

Server: Combine your host and port separated by a colon (e.g., pg-your-project-name.aivencloud.com:12345).
Database: Type the exact name of the database you intend to read from (e.g., defaultdb).
Select either Import, then click OK.

Authenticate with Database Credentials - Username and Password, then click Connect.
Select Tables - The Navigator panel will look up your database. Select your dataset, then click Load for a clean dataset or Transform to start the process of cleaning on Power Query

Finally

Once these parameters are configured, your relationships can be built directly inside the Power BI Model view. The core difference ultimately boils down to network topology: local connections require safe local-network access paths or gateways, while cloud-hosted services like Aiven rely primarily on public connection strings backed by strong encryption and user credentials.

Power BI Finally Started Clicking(Schemas, Relationships & Joins)

Sylvia Ndili — Sun, 28 Jun 2026 19:31:47 +0000

You've loaded your data into Power BI. You've got the tables. Now what are you expected to do?

Here's the thing most beginners skip: how your tables connect to each other is more important than the data inside them. A poorly structured model will give you wrong numbers, slow reports and a difficult time every time you try to add a new chart.

This week we're covering the foundation of Power BI, data modeling. Specifically: what kinds of tables exist, how schemas work, what relationships are and what joins are doing behind the scenes.

One thing that took me a while to get in Power BI is that loading data is only half the job. The other half is telling Power BI how your tables talk to each other. That's what schemas, relationships and joins are about.

The Problem I hadn't anticipated

When I first loaded data into Power BI, I had three separate tables; customers, products and sales. I could build charts from each one individually, but the moment I tried to combine them like "show me sales by customer city", things either broke or gave me numbers that made no sense.
Turns out I had no relationships set up. Power BI had no idea how those three tables were supposed to connect. That's what I got to learn.

Fact Tables vs Dimension Tables

Every Power BI model has two kinds of tables doing different jobs.

Fact tables store your actual transactions - every sale, every order, every event. They're usually huge and full of numbers and IDs.

Dimension tables give those numbers context. Your Customers, Products and Dates tables are dimension tables (fewer rows but more descriptive columns). They answer the who, what and when behind every fact.

The Star Schema

Power BI is basically built for this layout. One fact table sits in the middle, dimension tables connect around it. For instance:

           DIM_Date
               |
DIM_Customer — FACT_Sales — DIM_Product
               |
           DIM_Store

Filters flow from the outside in, queries run fast and the model stays easy to read. There's a variation called the Snowflake Schema where you break dimension tables into sub-tables, but in Power BI it adds complexity without much payoff. Star schema is the standard.

Snowflake Schema

The snowflake schema extends the star by breaking dimension tables into sub-tables. For example, instead of one DIM_Customer table with a City and Country column, you'd have a separate DIM_City table linked to a DIM_Country table.

Relationships

A relationship is the link between two tables through a shared column.

Primary Key (PK) - uniquely identifies every row in a table. No duplicates.
Foreign Key (FK) - that same ID living in another table as a reference.

The most common type is One-to-Many (1:N) - one customer, many orders. One product, many sales. The dimension table always sits on the "one" side, the fact table on the "many" side.

When you have a situation where many rows in Table A relate to many rows in Table B - like students and courses, that's a Many-to-Many relationship. Power BI doesn't handle this cleanly on its own so you use a bridge table in between, which breaks it into two clean one-to-many links.

Cross-Filter Direction

When you click on something in a visual, other visuals filter too. That behaviour is controlled by cross-filter direction.

Single direction - filters flow from dimension into fact only. This is the default and what you should stick with most of the time.

Bidirectional - filters travel both ways. Useful in specific cases but can slow things down and cause circular logic if overused.

Active vs Inactive Relationships

You can have more than one relationship between two tables but only one can be active at a time. A common example - a Sales table with both an OrderDate and a ShipDate, both linking to the same date table. One relationship is active by default, the other sits inactive until you call it explicitly in DAX using USERELATIONSHIP().

Joins

This is what Power BI does behind the scenes when combining tables in a visual.

Inner join - only rows with a match in both tables come through. Unmatched rows get dropped silently, which is why some totals can look off unexpectedly.

Left join - all rows from the left table come through, matched or not. Unmatched rows just show blank on the right side. This is what Power BI uses most of the time when pulling from a dimension into a fact table.

Cross Join - every row in Table A paired with every row in Table B. Rarely useful; produces enormous result sets.

Little hacks

Use Star Schema whenever possible
Keep dimension tables on the "one" side, fact tables on the "many" side
One active relationship per table pair
Avoid bidirectional filtering unless you have a specific reason
Resolve Many-to-Many with a bridge table

Wrapping Up

Data modeling isn't the flashy part of Power BI, but it's the part that makes everything else work. Get your schema right, define clean relationships and your reports will be fast, your numbers accurate and your filters will just work.

If you have questions about anything covered here, drop them in the comments!

Excel and Real-World Data Analysis - What I Learned in Week 1

Sylvia Ndili — Sat, 06 Jun 2026 16:24:56 +0000

To be honest, when I signed up for a Data Science and Analytics course, it hadn't crossed my mind that Excel was the very first tool I would start with. I figured we'd jump straight into dashboards, maybe some Python, something that felt more "data sciency". But here we are, Week 1, deep in spreadsheets.

And the best part about this is that I'm actually glad it started here.

So, what is Excel?

Excel is a spreadsheet tool by Microsoft. You work with data in rows and columns, run calculations, sort things, filter things and build reports. Sounds simple, right? But once you start using it on real data, you realize it's literally the backbone of a huge chunk of the business world.

Ways Excel shows up in real work

- Financial Reporting - Accountants and finance teams use Excel to track income, expenses, budgets and forecasts. Every month, someone somewhere is pulling together a financial report in Excel before it ever reaches a boardroom.

- Sales and Business Performance - Sales teams track numbers in Excel — who sold what, which region is hitting targets, which product is underperforming. It's the first place most managers go when they want to understand their numbers.

- Marketing Analysis - Marketers use Excel to measure campaigns, click rates, conversions and customer behavior over time. Before any fancy BI tool, most marketing teams have a spreadsheet tracking what's working.

Features I used this week

Before jumping into formulas, the first thing I had to do was just understand how Excel is laid out. It sounds obvious but getting comfortable with the interface first made everything else easier.

Once I had that down, I started working with these features:

Sorting - Organizing data by numbers, text or dates. Useful when you need to quickly rank results or find the highest/lowest values.
Filtering - Showing only the rows that match a certain condition. Instead of scrolling through hundreds of entries, you just filter and focus on what matters.
Data Validation - This one is about keeping data clean at the entry point. You can set rules so that only certain values are accepted in a column. It stops messy, inconsistent data before it becomes a problem.
Freeze Panes - When you're scrolling through a big dataset, your headers disappear off the top of the screen. Freeze Panes keeps them visible no matter how far down you scroll. Simple but incredibly useful.

Functions I Learned

SUM, AVERAGE and COUNT - These feel basic but they're everywhere. I used SUM to total a column of sales figures, AVERAGE to find a mean score and COUNT to quickly see how many entries were in a dataset.

COUNTIF - This was my favourite this week. Instead of manually counting how many times something appears, COUNTIF does it automatically based on a condition. I used it to count how many entries matched a specific category. Such a small thing that makes a big difference.

SUMIF and SUMIFS - Add values that meet one or more conditions.

AVERAGEIF and AVERAGEIFS - Calculate averages based on selected conditions.

These are the functions that help you answer actual business questions - total sales by product, customer count by region, average performance across departments.

Text and Date Functions - I also got introduced to LEFT(), RIGHT(), LEN(), and CONCAT() for working with text, and TODAY(), NOW(), DAY(), MONTH(), YEAR() for dates. These are used a lot in data cleaning — pulling out parts of a string, combining columns, or breaking a date into components you can analyze separately.

How this changed the way I see data

Before this week, I looked at a spreadsheet and just saw a table. Now I see questions. What's the total? What's the average? What's pulling the numbers up or down? Excel didn't just teach me formulas, it taught me to ask better questions about data.

I'm only in Week 1 and I already feel like I'm thinking differently. That's quite the leap.

If you're also starting out in data analytics, don't skip Excel thinking it's too basic. It's the foundation everything else is built on.

💬 Are you also learning data analytics or just getting started with Excel? Drop a comment - I'd love to connect with others on the same path.