DEV Community: JOAN NJOKI

Connecting Power BI to an SQL database

JOAN NJOKI — Sat, 21 Mar 2026 12:05:09 +0000

In this article, we'll explore how to connect Power BI to a PostgreSQL database, a popular open-source relational database management system. We'll cover both local and cloud-based PostgreSQL connections, discuss data modeling, and highlight the importance of SQL skills for Power BI analysts.

What is Power BI and Why is it Used?
Power BI is a business analytics tool that allows you to analyze data, create compelling visualizations, and share insights across your organization. It's used for data analysis, business intelligence (BI), and creating interactive reports and dashboards. Power BI enables data professionals to connect to a wide range of data sources, transform and clean the data, and build data models.

Connecting Power BI directly to databases is essential for:

Real-Time Data Analysis using Dashboards
Efficiently storing and managing vast amounts of data
Centralized Data Management
Managing complex Data Relationships

SQL databases (like PostgreSQL) are fundamental for storing and managing analytical data because they provide:

Structured data that ensures data consistency and accuracy.
Efficient querying with SQL for powerful and efficient data retrieval and manipulation.
Scalability to accommodate growing datasets and increasing user demands.
Data integrity to maintain data reliability

Connecting Power BI to a Local PostgreSQL Database
1. Open Power BI
Launch the Power BI Desktop application and click on the "Get Data" button on the "Home" ribbon

2. Choose PostgreSQL
From the list of choices, click on more and select PostgreSQL Database and click on connect

3. Enter Server Details
Enter the local database details from your pgAdmin 4: server as localhost and the specific name of the database you are connecting to.

4. Provide Credentials
Enter your Username and Password as set up during your PostgreSQL installation.

5. Load Tables
After successfully authenticating, a "Navigator" window will appear, displaying all the tables and views in your database. Select the relevant tables (e.g., customers, products, sales, inventory) and click "Load."

After loading your data, choose the table view to view your data in table format or the model view to see the relationships and create schemas.

Connecting Power BI to a Cloud Database (Aiven PostgreSQL)
Aiven, a cloud database service, provides a secure PostgreSQL environment

1. Obtain Connection Details from Aiven
Log in to your Aiven account and locate your postgreSQL service

From the dashboard, you will find information on:

Host name
Port number
Database name
Username
Password

2. Download the SSL Certificate
In the Aiven console, under the "Overview" or "Connection Information" section for your service, you will see an option to download the "CA Certificate" or "SSL Certificate."

3. Establish the Connection in Power BI
Launch the Power BI Desktop application and click on the "Get Data" button on the "Home" ribbon

4. Choose PostgreSQL
From the list of choices, click on more and select PostgreSQL Database and click on connect

5. Enter Server Details
Enter the database details from your aiven service with the server details as Host:Port

Preview the data and load it

Read the data in table view to clean and remove duplicates or report view to establish relationships between the tables

Data Modeling
Data modeling in Power BI involves creating a logical structure for your data, ensuring that Power BI understands how different tables interact. The fundamental concept is establishing relationships between tables using common columns (keys).
To create a new relationship, open model view in power BI and click on manage relationships

Select +New relationship

Select the tables with similar primary keys that you want to join

Click on save and close to view the new relationship

The importance of data modelling in Power BI

Relationships are critical for calculating correct results in visualizations. When you create a chart showing sales by product category (where sales and product details are in different tables), Power BI uses the defined relationship to aggregate the sales data accurately according to the product category.
They allow for dynamic filtering. If you select a specific customer in one chart, all other linked charts and tables in your report will automatically filter to show data only for that selected customer.

Why SQL Skills are Important for Power BI Analysts
While Power BI provides a robust user interface for connecting to and importing data, proficiency in SQL is highly beneficial for Power BI analysts for several key reasons:

Data Retrieval: Analysts can write custom SQL queries (using Power BI's "Advanced options" or "SQL statement" field during the connection phase) to retrieve only the required data. This avoids importing irrelevant or excessive data, significantly improving report performance.
Filtering: complex filtering conditions can be applied directly in the SQL query, moving the data filtering load to the database server itself, which is often more efficient.
Aggregations: SQL allows for pre-aggregating data (e.g., calculating daily sales summaries instead of importing every individual sales transaction). This creates a smaller, more optimized dataset for Power BI to handle.
Data Transformation: Basic data cleaning, transformations (like formatting dates or combining columns), and calculations can be done using SQL functions before the data even enters Power BI. This leads to a cleaner and simpler data model within Power BI itself.
Query Performance Tuning: If a Power BI report is slow to refresh, understanding the underlying SQL queries can help analysts identify and optimize inefficient database access patterns.
DirectQuery Mode: Power BI's "DirectQuery" mode sends queries directly to the database in real-time. Crafting optimal SQL queries is absolutely essential for acceptable performance when using this mode with large datasets.

Conclusion
Connecting Power BI to PostgreSQL, whether local or cloud-based, empowers you with real-time data access and the ability to analyze complex datasets. By understanding data modeling principles and leveraging SSL certificates for security, you can build reliable and impactful business intelligence solutions. While Power BI streamlines the analysis process, mastering SQL skills remains a critical differentiator for analysts, enabling them to optimize data retrieval, enhance performance, and deliver deeper, more accurate insights from their database connections

JOINS AND WINDOW FUNCTIONS IN SQL

JOAN NJOKI — Fri, 06 Mar 2026 10:40:19 +0000

JOINS
The JOIN clause is used to retrieve data from multiple tables based on logical relationships between them. Joins are fundamental to relational database operations and enable you to combine data from two or more tables into a single result set.

TYPES OF JOINS
1. Inner Join
It returns only rows that have matching values in both tables.
In SQL the join and inner join clause are the same

2. Right Join
It returns all rows from the right table, and only the matched rows from
the left table

3. Left Join
It returns all rows from the left table, and only the matched rows from the right table

4. Full Join
It returns all rows when there is a match in either the left or right table

WINDOW FUNCTIONS
A window function performs a calculation across a set of table rows that are somehow related to the current row. Unlike aggregate functions, which return a single value for a group of rows, window functions retain the identity of individual rows.
The basic word that turns a regular function into a window function is OVER.

LEAD FUNCTION
It provides access to a row at a specified physical offset that follows the current row.

LAG FUNCTION
It provides access to a row at a specified physical offset which comes before the current row.

NTILE FUNCTION
It allows you to break the result set into a specified number of approximately equal groups, or buckets by assigning each group a bucket number starting from one. For each row in a group, the NTILE() function assigns a bucket number representing the group to which the row belongs.

PARTITION BY FUNCTION
This clause divides the result into partitions to which the window function is applied

Ranking Functions: ROW_NUMBER, RANK, and DENSE_RANK
Ranking is one of the most common use cases. Imagine you have a list of students and their scores, and you want to see who came in first.

SELECT 
    student_name, 
    score,
    ROW_NUMBER() OVER(ORDER BY score DESC) as row_num,
    RANK() OVER(ORDER BY score DESC) as rank_val,
    DENSE_RANK() OVER(ORDER BY score DESC) as dense_rank_val
FROM students;

ROW_NUMBER(): Assigns a unique, sequential number to each row (1, 2, 3, 4).
RANK(): If two rows have the same score, they get the same rank, but the next rank is skipped (1, 2, 2, 4).
DENSE_RANK(): Similar to rank, but it doesn’t skip any numbers (1, 2, 2, 3).

SCHEMAS AND DATA MODELLING IN POWER BI.

JOAN NJOKI — Mon, 02 Feb 2026 14:12:53 +0000

Effective data modelling is the cornerstone of powerful and performant Power BI reports. It involves structuring your data in a way that optimizes for analysis, reporting, and efficient data processing.

Fact and Dimension Tables: The Building Blocks
The foundation of most analytical data models, including those in Power BI, rests on two primary types of tables:

1. Dimension (Lookup) tables
These tables describe the "who, what, where, when, and how" of your business. They contain descriptive attributes that provide context to data such as:

Customer Dimension: Customer Name, Address, City, Region
Product Dimension: Product Name, Category, Sub-Category, Brand
Date Dimension: Year, Quarter, Month, Day of Week
Location Dimension: Store Name, City, State, Country

Dimension tables are typically wider (more columns) but have fewer rows than fact tables. They are often normalized to reduce data redundancy.

2. Fact (Event) Tables
These tables record the "measurements" or "metrics" of your business processes. They contain numerical values that can be aggregated and analyzed. Fact tables also contain foreign keys that link them to dimension tables. Examples include:

Sales Fact: Order Quantity, Sales Amount, Discount, Unit Price (along with foreign keys to Customer, Product, Date, and Store dimensions)
Web Traffic Fact: Page Views, Session Duration, Bounce Rate (along with foreign keys to User, Page, and Date dimensions)

Fact tables are typically deeper (more rows) but narrower (fewer columns) than dimension tables. They can grow very large as they record every event or transaction.

Star Schema
The star schema is the simplest and most widely used schema design in data warehousing and business intelligence. It gets its name because the central fact table is surrounded by its related dimension tables, resembling a star. When you click a filter on a Product (Dimension), Power BI only has to make one "jump" to filter the Sales (Fact)

Key Characteristics:

Central Fact Table: Contains all the measurable data and foreign keys to all dimension tables.
Directly Linked Dimension Tables: Each dimension table is directly linked to the fact table. There are no intermediate dimension tables.
Denormalized Dimensions: Dimension tables contain all the attributes related to that dimension in a single table, even if some attributes could logically belong to a separate, more granular dimension.

Advantages:

Easy to understand, design, and implement.
Excellent for query performance due to fewer joins required to retrieve data. Most queries only need to join the fact table with a few dimension tables.
Business users can easily navigate the model and understand the relationships.
Power BI's VertiPaq engine is highly optimized for star schemas, leading to faster aggregations and calculations.

Disadvantages:

Denormalized dimension tables can lead to some data redundancy.
Can be less efficient for handling very complex or deep hierarchical relationships within dimensions

Snowflake Schema
The snowflake schema is an extension of the star schema where dimensions are normalized into multiple related tables. This means that a dimension table might have its own "sub-dimension" tables, creating a hierarchical structure that resembles a snowflake.

Key Characteristics:

Dimension tables are normalized, breaking them down into multiple related tables to reduce data redundancy.
Dimensions can have multiple levels of sub-dimensions. For example, a "Product" dimension might have a linked "Product Category" dimension and a "Product Subcategory" dimension.
Involves more tables than a star schema due to the normalization.

Advantages:

Normalization minimizes redundant data storage, which can be beneficial for very large dimension tables with repeating attributes.
Improved data integrity due to normalization.
Better suited for handling complex and deep hierarchical dimensions.

Disadvantages:

More complex to design, understand, and maintain due to the increased number of tables and joins.
Queries often require more joins to retrieve data, which can negatively impact performance, especially with large datasets.
Can be more difficult for business users to navigate and understand the relationships between tables.

Relationships: Connecting the Dots
Relationships define how tables are connected in your data model. In Power BI, relationships are crucial for filtering and aggregating data correctly across different tables. The most common relationship type is one-to-many, where one record in the dimension table relates to multiple records in the fact table.
For example, one customer (in the Customer Dimension) can place many orders (in the Sales Fact).

The Importance of Good Data Modelling

Performance Optimization: A well-structured model significantly reduces the amount of data Power BI needs to process for each query translating to faster report loading times, quicker interactions, and a smoother user experience. Poorly modelled data can lead to slow reports, long refresh times, and even crashes.
Accuracy and Consistency: A clear data model ensures that calculations and aggregations are performed correctly and consistently across all reports thus minimizing ambiguity and reducing the likelihood of incorrect insights due to flawed data relationships.
Ease of Use and Maintainability: A logical and intuitive data model makes it easier for report developers to find and use the correct data fields. It also simplifies maintenance and updates, as changes in the underlying data sources can be managed more efficiently.
Scalability: As your data volumes grow, a well-designed model can scale more effectively, preventing performance bottlenecks and ensuring that Power BI remains a viable solution for your expanding analytical needs.
Data Storytelling and Insight Generation: Ultimately, good data modelling enables you to tell compelling data stories. By presenting data in a logical and easy-to-understand format, you empower users to extract meaningful insights and make informed decisions

The Beginner's Guide to Ms Excel

JOAN NJOKI — Sun, 25 Jan 2026 16:46:09 +0000

Microsoft Excel is a spreadsheet program that allows users to organize, format, and calculate data in a spreadsheet and perform detailed analysis across both personal and professional settings.
Excel supports a wide range of tasks, including:

Data entry and organization
Creating charts and visualizations
Automating tasks with Excel macros
Advanced calculations with built-in functions
Managing large datasets with Pivot Tables
Time tracking and project management

Excel for Data Analytics
The first step to analyzing data on Excel is cleaning the data.

1. Removing Duplicates
ctrl+A to select the data in the spreadsheet > Click Data from the ribbon > select the 'remove duplicates" icon

2. Filtering Data/Conditional Formatting
Highlight the data to be filtered> click data>filter> select the desired field

For conditional formatting, click home>conditional formatting>highlight cell rules> select the desired criteria> select text color

3. Performing Calculations
Basic Excel functions include:

=SUM(E2:E15) computes the total for all the numbers in a column
=AVERAGE(E2:E15) computes the average for all the numbers in a column
=MAX(E2:E15) computes the maximum number in a column
=MIN(E2:E15) computes the minimum number in a column
=COUNT(E2:E15) computes the count for all the cells in a column

Conditional functions
Same as basic functions, but they only work if the given condition is met.
Example: =SUMIF(E2:E877,">100,000") returns the sum of salaries from column E greater than 100,000
=SUMIFS(I2:I877,O2:O877,"Intern",P2:P877,"Berlin") returns the total performance score for interns in Berlin
Other functions include:

AVERAGEIF
COUNTIF
COUNTIFS

Text functions
They're used to manipulate text data and include:

=UPPER(B2) converts the text in a cell into uppercase
=LOWER(B2) converts the text in a cell into lowercase
=PROPER(B2) capitalizes the first letter of text in a cell 
=TRIM(B2) removes any excess characters e.g space before the text
=LEN(B2) counts the number of characters in a cell
=LEFT(B2,x) extracts the first "x" characters from the text in a cell
=RIGHT(B2,x) extracts the last "x" characters
=CONCATENATE(B2," ",C2) joins text from multiple cells into one e.g combining the first and last name
=VLOOKUP finds data vertically
=HLOOKUP finds data horizontally

4. Pivot Tables
They allow one to turn large datasets into clear summaries, grouped insights, and simple side‑by‑side comparisons.
How to create one:

select all the data from the original dataset
Click insert, select pivot table, choose to start on a new worksheet

drag fields into rows, values and columns

One can also add slicers to filter out more data faster.

5. Visualizing data with charts/graphs.
Data visualization is commonly done using pie charts, line graphs and column charts

6. Summary
While advanced coding languages like Python and SQL handle massive datasets, Excel remains the industry standard for rapid data exploration, ad-hoc cleaning, and quick visualization. Its powerful features, specifically Power Query for data transformation and Pivot Tables for instant summarization, allow analysts to spot errors, test ideas, and turn raw numbers into simple data.

🚀 Getting Started with Git and GitHub: A Beginner’s Guide

JOAN NJOKI — Sun, 18 Jan 2026 05:50:08 +0000

🛑 The "What" and "Why": Git vs. GitHub 🧐
Git is a distributed version control system that tracks code changes, and allows you to:

go back to earlier versions if something breaks
work with teammates without overwriting each other's work
see who made what changes and when.

GitHub is a cloud-based platform that hosts Git repositories, providing additional tools for collaboration, issue tracking, and code review. GitHub offers remote repository hosting and integrates with Git for easier teamwork.
It's like the "showcase" for your code, enabling collaboration, public visibility for your portfolio, and secure backups of your work.
If Git is the "camera" taking snapshots of your work, GitHub is the "online gallery" where those snapshots are stored and shared.

🛠 Your First Steps: Setting Up a Project 📂
1. Tracking changes
To start tracking a project, you first need to initialize Git in your project folder using your terminal: git init
Once Git is watching your folder, tracking changes follows a simple three-step "Save" process:

check your status (git status): see files that are untracked (new) or modified
stage (git add): Pick which changes you want to include in your next "snapshot."
commit (git commit): take the snapshot and give it a descriptive label.

2. Pushing code to GitHub
"Pushing" is the act of sending your local commits (the snapshots on your computer) to a remote server like GitHub. This makes your code accessible to others and acts as a backup.
The steps are:

Create a new repository on GitHub.com.
Copy the URL of the repository (e.g., https://github.com/your-username/my-project.git).
Link your local folder to GitHub (you only do this once): git remote add origin https://github.com/your-username/my-project.git
Push code git push -u origin main

3. Pulling code from GitHub
Pulling is essentially a combination of fetching the latest version from GitHub and merging it into your local files using the command git pull origin main

Basic Workflow Summary:

Create a new repository on GitHub using the web interface
Clone it to your machine with git clone [url] (this creates your local repository)
Make some changes to the files in your working directory
Stage changes with git add . (moving them to the staging area)
Commit with git commit -m "Describe your changes"
Push to GitHub with git push -u origin main

Happy Coding and may your code always be perfectly version-controlled! ✨💻