Forem: Analyst Kinywa

Connecting Power BI to a SQL Database

Analyst Kinywa — Sat, 14 Mar 2026 11:56:26 +0000

Introduction

Microsoft Power BI is a powerful business intelligence and data visualization tool that helps organizations transform raw data into meaningful insights. It allows users to create interactive dashboards, reports and visualizations that support data-driven decision-making.

Power BI can connect to many data sources, including spreadsheets, cloud services, APIs and relational databases. In many organizations, data is stored in structured databases which Power BI accesses to analyze and visualize for insights.

One commonly used database for analytics is PostgreSQL, an SQL-based database system designed to efficiently store, manage and retrieve large volumes of structured data. Connecting Power BI to a PostgreSQL database allows analysts to access large datasets, run SQL queries, combine multiple tables and build real-time or scheduled dashboards for better business insights.

By the end of this article, we will have a step by step guide on how to connect to a local database and also to a cloud-based database.

Connecting Power BI to a Local PostgreSQL Database

This is the most common starting point for any data analyst where you are able to connect Power BI to a local PostgreSQL DB running locally on your machine.

Step 1: Open Power BI Desktop
Once you open, click Blank report. This navigates you to the main page (see below) of the application.

On the top ribbon, click Get data. This option allows Power BI to connect and get data from different data sources.

Step 2: Choose PostgreSQL database
From the list of the sources, look for PostgreSQL database and then click Connect.

Step 3: Enter Database connection details
After selecting PostgreSQL database and clicking Connect, a connection window will appear where you must enter the local database details.

For a database that runs locally on your machine, the server is usually localhost. For the Database name, we will navigate to my local database and trace the connection details from there.

Since I have pg4Admin 4, a graphical tool for managing and developing my PostgreSQL databases, I will open the tool and trace the details from my server properties.

For this connection, let's use the below connection details:

Server hostname localhost
Username postgres

Input the above details and click OK.

Step 4: Enter Authentication credentials
Power BI will then prompt you to enter your database credentials. Authentication details typically include: Username & Password. For this, I will copy the username from pg4Admin 4 and for the password I will use the same password I used when setting up my pg4Admin during installation then click Connect.

Step 5: Load tables into Power BI
After connecting successfully, Power BI opens the Navigator window, which lists all available tables in the database. Here you are able to preview each available table and select the tables you want to load then click Load.

Alternatively, you can select Transform Data to clean or modify the data in the Power Query Editor before loading it. Once the tables are loaded, begin analyzing and visualizing your data for insights.

Connecting Power BI to a Cloud PostgreSQL DB

Many companies host their databases in the cloud instead of locally. One example is Aiven which provides managed PostgreSQL databases. Connecting Power BI to a cloud PostgreSQL database follows a similar process but requires additional connection details.

Step 1: Create a PostgreSQL DB on Aiven
Creating a PostgreSQL database on Aiven is a straightforward process that allows you to host and manage a cloud database without worrying about infrastructure setup. First, create an account and log in to the Aiven console. Once logged in, click Create Service on the dashboard. From the list of available services, select PostgreSQL as the database engine.

Next, configure the service by choosing a cloud provider, selecting a region where the database will be hosted and picking a pricing plan that fits your needs. After entering a name for the service, click Create Service. Aiven will then automatically deploy the PostgreSQL database in the cloud.

Step 2: Download SSL certificate from Aiven
Cloud databases often require secure encrypted connections. From the Aiven console, navigate to Connection Information and download the CA Certificate (SSL certificate).

Step 3: Connecting to Aiven Using PostgreSQL ODBC
To connect applications such as Microsoft Power BI, Excel or other analytics tools to a cloud database hosted on Aiven, you can use the ODBC (Open Database Connectivity) driver for PostgreSQL. ODBC provides a standardized way for software applications to communicate with database systems.

First, download the version that matches your system (32-bit or 64-bit) and install the PostgreSQL ODBC driver, commonly known as psqlODBC.

Download the driver from https://www.postgresql.org/ftp/odbc/releases/REL-17_00_0008-mimalloc/

Open ODBC Data Source Administrator, navigate to System DSN then click Add. Another Create New Data Source will appear and give you an option to select a driver for which you want to set up a data source. Choose PostgreSQL Unicode(x64) and click Finish.

Another psqlOBDC set up window will appear requesting for connection details. All these connection details can be traced from a running service on Aiven. Copy and paste on this set up window and test connection.

Note: Ensure the SSL Mode is set to require.

Step 4: Connect Power BI Using the ODBC Source
Once the ODBC data source is configured, navigate to Power BI Desktop. On the home page, click Get data, select ODBC and Connect. Another window will appear where you will be required to choose your DSN (Data Source Name). Choose PostgreSQL ODBC and click OK.

You will then be prompted to input the Username and Password available from Aiven then click Connect. Power BI will then retrieve the tables from the PostgreSQL database hosted on Aiven. Select the tables you need and click Load.

It is important to note that, SSL certificates are required because they encrypt the connection between Power BI and the database. When we attempt to get data from the cloud-based database hosted on Aiven, the PostgreSQL ODBC driver automatically establishes a secure and an encrypted connection using the SSL certificate we downloaded locally.

Creating relationships between tables

Power BI automatically detects relationships between tables based on matching columns based on the primary & foreign keys.

See below relationships we are working with from the data we just loaded on Power BI.

Table	Related Table	Relationship
customers	sales	customer_id
sales	products	product_id
products	inventory	product_id
sales	inventory	product_id

From these relationships, Power BI knows how tables relate to each other so that calculations and visualizations are accurate.

Why SQL skills are important for Power BI analysts

Although Power BI provides powerful visual tools, SQL remains an essential skill for data analysts. SQL helps analysts:

Write queries that assist to retrieve data efficiently
To filter data before importing it into Power BI which reduces the dataset size and improves performance.
Perform aggregate operations that assist to prepare analytical datasets before visualization
To join multiple tables, clean the dataset and build aggregated tables in preparation for the creation of dashboards in Power BI.

Conclusion

Power BI is a powerful business intelligence platform that enables organizations to transform raw data into interactive dashboards and insights. By connecting Power BI to local or cloud-based SQL databases such as PostgreSQL, data analysts can work with large datasets directly from the source rather than relying on manual data exports.

Feel free to leave comments!

SQL Joins and Window Functions

Analyst Kinywa — Mon, 02 Mar 2026 16:49:18 +0000

SQL Joins

What is a Join? A JOIN combines rows from two or more tables based on a related column. They are the backbone of relational databases, spreading data across normalized tables and reuniting it at query time.

Types of Joins

INNER JOIN - returns only rows with a match in both tables.

SELECT e.full_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

LEFT JOIN - returns all rows from the left table; unmatched right-side rows return NULL.

SELECT e.full_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

RIGHT JOIN - opposite of LEFT JOIN; all rows from the right table are returned.

SELECT e.full_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

FULL OUTER JOIN - returns all rows from both tables; NULLs fill the gaps where there's no match.

SELECT e.full_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

CROSS JOIN - produces the Cartesian product; every row from table A paired with every row from table B.

SELECT products.name, colors.color
FROM products
CROSS JOIN colors;

SELF JOIN - joins a table with itself; perfect for hierarchical data like org charts.

SELECT e1.full_name AS employee, e2.name AS manager
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.id;

Example:

-- Sales report combining orders, customers and products
SELECT
    c.name        AS customer,
    p.product_name,
    o.order_date,
    o.quantity,
    (o.quantity * p.price) AS total_value
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products  p ON o.product_id  = p.id
WHERE o.order_date >= '2024-01-01'
ORDER BY total_value DESC;

Window Functions

What Are Window Functions? A Window Function performs calculations across a set of rows related to the current row - its window - without collapsing the result set like GROUP BY does. Every row keeps its identity, the function just adds a computed value beside it.

Key Window Functions with sample queries

ROW_NUMBER() - unique sequential number per partition

SELECT full_name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees;

RANK() vs DENSE_RANK() - RANK skips numbers on ties while DENSE_RANK does not

SELECT full_name, salary,
    RANK()       OVER (ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

LAG() / LEAD() - look backwards or forwards without a self-join

SELECT month, revenue,
    LAG(revenue, 1)  OVER (ORDER BY month) AS prev_month,
    LEAD(revenue, 1) OVER (ORDER BY month) AS next_month,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS mom_change
FROM monthly_sales;

NTILE(n) - bucket rows into n equal groups

SELECT name, salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile
FROM employees;

Summary

Joins at a glance

INNER JOIN - Returns matched rows only
LEFT JOIN - Returns all left rows + matched right (NULLs for no match)
RIGHT JOIN - Returns all right rows + matched left (NULLs for no match)
FULL OUTER JOIN - Returns all rows from both tables, NULLs where unmatched
CROSS JOIN - Returns every row combination (Cartesian product)
SELF JOIN - Returns a table joined to itself

Window Functions at a glance

ROW_NUMBER() - Unique sequential ID per row/partition
RANK() / DENSE_RANK() - Rankings with/without tie gaps
LAG() / LEAD() - Compare current row to prior/next row
SUM() / AVG() as window - Running totals, moving averages
NTILE(n) - Percentile buckets
FIRST_VALUE() / LAST_VALUE() - First or last value in a window

Joins are like smart VLOOKUPs - they stitch data from multiple tables together using a shared key.

Window Functions are like adding a calculated column to a spreadsheet that is "aware" of its neighbors.

From messy data to meaningful action: How analysts use Power BI to drive decisions

Analyst Kinywa — Sun, 08 Feb 2026 12:51:34 +0000

In most organizations, data rarely arrives in neat tables ready for analysis. Instead, analysts face fragmented systems, inconsistent formats, missing values, duplicate records and unclear business definitions. Yet executives still expect clear answers: Are we profitable? What should we fix? Where should we invest next?

This is where Power BI becomes more than a reporting tool. When used well, it is a decision engine. Analysts translate messy data into action by combining data modeling, DAX logic and intuitive dashboards, bridging the gap between raw data and real-world impact.

This article explores how that translation happens in practice.

Understanding the business problem before touching the data

A critical but often overlooked skill is problem framing. Before opening Power BI, effective analysts ask:

What decision will this analysis support?
Who is the decision-maker?
What action should change based on this insight?
What business rules define success or failure?

For example:

A Head of Operations does not need all transaction data, he or she needs only exceptions, delays and trends.
A Finance Manager cares about net performance but not raw totals.
A Risk team focuses on outliers and exposure not averages.

This clarity ensures that technical work aligns with business outcomes not just technical completeness. If you don’t know the decision, the dashboard will fail, no matter how good it looks.

Making sense of messy data

Most business data is messy by default and visualizing this data “as is” doesn’t create insight, it creates confusion.

At this point is where analysts get the data from one of the data sources and use power query to clean & organize the data. Let's handle this part step by step:

Getting the data

Here we will import our data saved in CSV format.

Click load and your data will be loaded into Power BI.

Cleaning the data

Open Transform Data to enter Power Query. This is where you standardize formats (dates, currencies, IDs) and remove duplicates and invalid records.

Fixing incorrect data types (timestamp to Date)

Unit_price & total_amount to (decimal / whole number)

Enabling Column quality helps you check for missing or inconsistent values.

DAX: Embedding business logic into the data

DAX allows analysts to translate business questions into precise logic:

Total Sales - Total Sales = SUM(supermarket_transactions[total_sales_amount])
Total Quantity Sold - Total Quantity Sold = SUM(supermarket_transactions[quantity])
Average Transaction Value - Average Transaction Value = AVERAGE(supermarket_transactions[total_sales_amount])
Sales by Customer Type - Total Sales = SUM(supermarket_transactions[total_sales_amount]) then categorize by customer_type in report view.

These measures will power your KPI cards and charts.

See DAX results in report view

DAX turns Power BI from a visualization tool into a decision framework.

Dashboards: Translating insights into action

A good dashboard doesn’t show everything, it shows What’s working, What’s broken and What needs attention now.

Effective Power BI dashboards use KPIs with thresholds and color indicators, highlight trends rather than static numbers, enable filtering by key dimensions using slicers and match how decision-makers scan information.

Dashboard design principle: The goal is clarity, not decoration. Every visual should answer a specific business question.

See below sample dashboard showing sales analysis of a supermarket with multiple store locations.

In conclusion, Power BI skills are not valuable because they produce charts. They are valuable because they translate complexity into clarity.

Analysts sit at the intersection of:

Messy operational data
Technical tools like Power Query, data models and DAX
Business decisions that affect cost, revenue, risk and customer experience

When done well, Power BI turns raw data into insight, insight into action and action into measurable business impact.

In today’s data-driven organizations, the analyst’s true role is not just to analyze but to enable better decisions, faster.

Enjoy the article and drop comments.

Power BI: Schemas & Data Modelling

Analyst Kinywa — Mon, 02 Feb 2026 07:00:59 +0000

When most people think about Power BI, they imagine dashboards and visuals. But the truth is great Power BI reports start with great data modelling, long before a single visual is created. A strong model improves performance, ensures accurate reporting and provides a scalable foundation for analytics. In this article, we explore fact and dimension tables, star and snowflake schemas, relationships and the importance of good modelling in Power BI.

What is data modelling in Power BI?

Data modelling refers to the process of structuring data into tables, defining relationships between them, and optimizing them for analytical queries. In Power BI, the data model sits at the heart of the solution, powering calculations written in DAX and driving how filters and aggregations behave across visuals. Unlike transactional systems that are optimized for inserts and updates, Power BI models are optimized for fast read and aggregation operations. This makes modelling techniques borrowed from data warehousing such as schemas especially relevant.

Poor modelling leads to slow, inaccurate, or confusing reports. As one source puts it: "Poor data models turn Power BI dashboards into expensive lies."

Star schema

Star schema is a mature modeling approach widely adopted by relational data warehouses. It requires modelers to classify their model tables as either fact or dimension.

key features of the star schema:

Single-level dimension tables: The dimension tables connect directly to the fact table without extra layers. Each table focuses on one area, like products, regions or time, making it simple to use.
Denormalized design: In a star schema, related data is stored together in one table using a denormalized approach.
Common in data warehousing: The star schema is used for quick analysis. It can easily filter or calculate totals.

Let’s understand this with a simple star schema diagram. The fact table Sales is in the center. It holds the numeric data you want to analyze, like sales or profits. Connected to it are dimension tables with descriptive details, such as product names, customer location or dates:

Fact tables

Fact tables store observations or events and can be sales orders, stock balances, exchange rates, temperatures and more. A fact table contains dimension key columns that relate to dimension tables and numeric measure columns. The dimension key columns determine the dimensionality of a fact table while the dimension key values determine the granularity of a fact table.

It is also important to note that dimension tables contain a relatively small number of rows. Fact tables, on the other hand, can contain a large number of rows and continue to grow over time.

Dimension tables

Dimension tables describe business entities - the things you model. Entities can include products, people, places and concepts including time itself. The most consistent table you'll find in a star schema is a date dimension table. A dimension table contains a key column (or columns) that acts as a unique identifier, and other columns. Other columns support filtering and grouping your data.

For example, if you are looking to determine the sales targets, you can store the attributes of the sales targets in a dimension table. Dimension tables group the data in the database when the business creates reports. For example, you can group sales targets by country, product, or retailer and those groupings are stored in dimension tables.

Snowflake schema

The snowflake schema consists of one fact table that is connected to many dimension tables, which can be connected to other dimension tables through a many-to-one relationship. Tables in a snowflake schema are usually normalized to the third normal form. Each dimension table represents exactly one level in a hierarchy.

Relationships in Power BI

Relationships define how tables connect and how filters propagate through the model.

Key relationship concepts:

Cardinality: One-to-many is the recommended standard
Cross-filter direction: Single direction (from dimension to fact) is preferred
Active vs inactive relationships: Only one active relationship can exist between two tables at a time

Poorly designed relationships such as many-to-many or bi-directional filters can lead to ambiguous results, performance degradation and incorrect totals.

Why good data modelling is critical?

Faster performance - Star schemas reduce query complexity, enabling sub‑second response times.
Accurate reporting - Good modelling prevents conflicting calculations and improves trust in analytics.
Scalability - A well‑structured model grows easily with your data.
Simpler DAX - Cleaner relationships mean easier formulas and fewer workarounds.
Stronger governance - Consistent modelling improves data integrity and decision quality.

In conclusion, if you want fast, accurate and scalable Power BI reports, start with great modelling. A strong model-built around a star schema, clean fact/dimension separation and well-defined relationships leads to high performance, reliable insights, simpler DAX and long‑term scalability

Great visuals don’t make great reports, great data modelling does.

Introduction to Microsoft Excel for Data Analytics

Analyst Kinywa — Sat, 24 Jan 2026 07:22:16 +0000

Microsoft Excel is one of the most widely used tools for basic data analysis. It is easy to learn for a beginner, requires no programming skills and is powerful enough to analyze, report and visualize everyday business data. In this article, we’ll explore how beginners can use Excel for data analytics step by step.

Microsoft Excel is a spreadsheet application that allows you to:

Store data
Organize information
Perform calculations
Analyze trends
Create visual reports

Excel data is arranged in Columns (vertical), Rows (horizontal) and
Cells (where a row and column meet).

Organizing data for analysis

It is important to note that for Excel to work well as an analytics tool, data must be structured properly.

Each column should have one clear header
Each row should represent one record
Avoid blank rows or columns inside your data

Example dataset:

Basic data cleaning in excel

Clean data leads to better analysis.

Common data-cleaning tasks:

Removing duplicates
Fixing data types (dates as Dates, numeric fields as numbers)
Handling missing values

Sorting and filtering data

Sorting and filtering help you focus on specific information or data.
Examples:

Sort salary from highest to lowest - highlight all data (Ctrl + A) and click data then sort

Basic calculations using formulas

Excel allows you to perform calculations using formulas and a formula always starts with an equals sign (=).

Common beginner formulas:

=SUM(E2:E10)      // Total salary
=AVERAGE(E2:E10)  // Average salary
=COUNT(E2:E10)    // Number of values
=MAX(E2:E10)      // Highest salary amount
=MIN(E2:E10)      // Lowest salary amount

Visualizing data with charts

Charts help turn numbers into visual insights.

Common chart types:

Column charts - compare values
Line charts - show trends over time
Pie charts - show proportions

How to create a chart:

Select your data
Click Insert then click Chart
Choose a chart type

For example, I will visualize salary by employee using a column chart and a line chart

Using pivot tables for data analysis

PivotTables are one of Excel’s most powerful features for analytics. They allow you to summarize large datasets without writing formulas.

Pivot tables can answer questions like:

Total salary by department
Salary by employee
Average salary by department

How to create a pivot table:

Select inside your data
Click Insert → pivot table
Drag fields into rows and values

Why Excel is great for beginner data analysts

Excel is ideal for beginners because:

No coding required
Easy to learn
Widely used in organizations
Strong analytical features for small and medium datasets

Many data professionals start with Excel before moving to tools like SQL, Power BI or Python.

In conclusion, Microsoft Excel is a powerful entry point into data analytics. By learning how to organize data, apply formulas, create charts and use PivotTables, beginners like me can gain valuable analytical skills and make data-driven decisions using Excel.

Feel free to comment & happy analyzing!!

Git for Beginners: Pushing, Pulling and Tracking Code Without the Stress

Analyst Kinywa — Mon, 19 Jan 2026 07:42:30 +0000

If you’re just getting into coding, chances are you’ve heard people casually throw around words like git, commit and github and thought:

“Yeah… I’ll figure that out later”

Good news: now is later and git isn’t as scary as it sounds.

So… What exactly Is git?

Git is a version control system. That's just a fancy way of saying:

Git keeps track of changes in your code over time

Think of git like a save system for your project, except:

Every save has a message
You can go back to any previous version
You can see what changed, when and why

Why version control is a big deal

Version control is important because:

Code breaks
Features change
Bugs appear out of nowhere
Teams work on the same files

With git, you can: Roll back to a working version, experiment without fear, collaborate without overwriting someone else’s work and understand how your project evolved.

Basically, git lets you make mistakes safely and that’s huge.

Installing git

First things first, install git:

👉 https://git-scm.com/

After installing, check that it works on cmd:

git --version

If you see a version number, you’re good to go 👍

How to push code to github

Let’s walk through a real-world flow.

Create a github repository

On github:

Click new repository
Give it a name (e.g. your-first-repository)
Click create repository

That’s your online home for the project.

Setting Up Your Git Username and Email

Before you start committing code, Git needs to know who you are. If you don’t set this up, Git will eventually stop you with an error like:

Please tell me who you are.

This information is attached to every commit you make, so GitHub and other developers can see who made each change.

Run these commands:

git config --global user.name
git config --global user.email

Set your git username

git config --global user.name "Your Name"
git config --global user.email "youremail@gmail.com"

Run & you are all set

git config --global --list

Initialize git in your project - This tells git: Hey, start tracking this project.

git init

Add files to git - This means: I want git to track all current changes.

git add .

Commit your changes - A commit message should explain what you changed not your life story, hehe lol.

git commit -m "Initial commit"

Connect your project to github - This links your local project to github.

git remote add origin https://github.com/your-username/your-first-repository.git

Push your code - Boom 💥 Your code is now on GitHub.

git push -u origin main

How to pull code from github

Pulling just means - “Give me the latest version from github.”

Run this inside your project:

git pull origin main

How to track changes with git

This is where git really shines. Check the status of your project

git status

If you ever feel lost, run

git status

See exactly what changed

git diff

This shows line-by-line changes before you commit.

View commit history

git log

This shows: past commits, messages, dates and authors

Git feels confusing at first because it’s different, not because it’s impossible.

Once you understand:
add - stage
commit - save
push - upload
pull - download

…it starts to click. If you’re learning git, you’re already doing the right thing. Stick with it.

Code! Add! Commit! 🚀