Forem: @waruikelvin

Connecting Power BI to an SQL Database for Data Analysis: Local & Cloud-Based Connections

@waruikelvin — Mon, 09 Mar 2026 05:30:00 +0000

Introduction

In the modern world, organizations rely heavily on tools that can transform raw data into meaningful insights. Power BI is one of the most popular self-service business intelligence and data visualization tools developed by Microsoft. It allows users to connect to multiple data sources, analyze datasets, and create interactive dashboards and reports that support better decision-making.

Power BI is widely used by businesses, analysts, and data professionals to monitor performance, identify trends, and gain insights from large volumes of collected data. Through visualizations such as charts, tables, and dashboards, Power BI makes it easier for stakeholders to understand complex data.

Many organizations store their operational and analytical data in SQL databases. SQL databases are structured systems designed to store, manage, and retrieve data efficiently. They allow organizations to maintain large datasets while ensuring data integrity and consistency. By connecting Power BI to a SQL database, analysts can access real-time or frequently updated information directly from the source, making it possible to build reports and dashboards based on accurate data.

In this article, we’ll see how Power BI connects to SQL databases, including both local PostgreSQL databases and cloud-hosted databases such as Aiven PostgreSQL, and how these connections enable effective data analysis.

Let’s get to it!

Connecting Power BI to a Local PostgreSQL Database

Power BI Desktop allows users to connect to many different types of databases. One of the commonly used open-source relational databases is PostgreSQL. When connecting to a local PostgreSQL database, Power BI retrieves tables directly from the database and imports them into the Power BI environment for analysis.

Step 1: Open Power BI Desktop

Launch Power BI Desktop on your computer. After the application opens, the main interface will appear with options to create reports or connect to data sources.

Click Blank Report to navigate to the home page.

Step 2: Select "Get Data"

On the Home ribbon at the top of Power BI Desktop, click Get Data. This option allows you to connect Power BI to various data sources such as Excel, SQL Server, PostgreSQL, web services, and more.

Step 3: Choose PostgreSQL Database

After clicking Get Data, a window listing available data sources will appear. From this list, select PostgreSQL Database, then click Connect.

Step 4: Enter the Server and Database Details

Next, Power BI will prompt you to enter connection details. For a local PostgreSQL database, the server name is usually:

localhost

You will also enter the database name that contains the tables you want to analyze.

For example, let’s do a mock connection using PgAdmin 4 and Power BI.

i. Open PgAdmin 4

ii. Access your server

To access your server, PgAdmin 4 will request your password. Use the password you created when you first installed and set up PgAdmin 4.

Once entered, you will have access to your server (for example, PostgreSQL 18).

iii. Locate your server connection details

Next, we need to find the connection details for this server.

Place your cursor on the server, right-click, then select Properties.

A window will pop up. Click on Connection.

Here, you’ll find information such as the Host name/address, which is needed to connect Power BI to your PostgreSQL server.

For this Power BI connection, we will use the following information:

Username: localhost
Database: company (use the name of your database)

Click OK.

NOTE: Ensure that the details match in both Power BI and PgAdmin 4.

Step 5: Provide Login Credentials

Power BI will request authentication details to access the database. Enter the following credentials:

Username
Password

From PgAdmin 4, copy your username and paste it into Power BI.

For the password, use the exact same password you used when setting up your PgAdmin user account.

Example:

Username: postgres
Password: xxxxxxxx

Then click Connect.

These credentials must match the PostgreSQL database user account.

Step 6: Select and Load Tables

After successful authentication, Power BI displays a Navigator window listing the available tables in the database.

For example:

assignment.customers
assignment.products
assignment.sales
assignment.inventory

You can preview each table before importing it. Select the tables you want, then click Load to import them into Power BI.

Once the data is loaded, you can begin analyzing and visualizing it in Power BI.

Connecting Power BI to a Cloud Database (Aiven PostgreSQL)

Most modern systems host databases in the cloud rather than on local machines. Cloud database providers such as Aiven allow organizations to run managed PostgreSQL databases that are accessible from anywhere.

To connect Power BI to an Aiven PostgreSQL database, you need specific connection details.

Step 1: Obtain Connection Details from Aiven

In your browser, search for Aiven, then select the official website.

Do this if you do not already have a service created.

After clicking Create Service, a pop-up will appear displaying several service types you can connect to. For this example, we will use the PostgreSQL service.

Select the free service plan, then create the service.

The dashboard will provide the following information:

Host name
Port number
Database name
Username
Password

These details are required to establish the connection from Power BI.

Step 2: Download the SSL Certificate

Cloud databases often require SSL encryption to secure connections between applications and the database server.

From the Aiven dashboard, download the SSL certificate file provided for your PostgreSQL service.

Step 3: Using the PostgreSQL ODBC Driver

i. Download and Install PostgreSQL ODBC Driver

The PostgreSQL ODBC driver is a software component that implements the Open Database Connectivity (ODBC) standard. It allows ODBC-compliant applications such as Microsoft Excel, Tableau, and Power BI to connect and interact with a PostgreSQL database using standard SQL without needing to understand the underlying database system.

You can download it from the psqlODBC website.

ii. Configure ODBC with Aiven

On your Windows computer, press:

Win + R

to open the Run dialog box.

Then type:

odbcad32

and click OK.

Navigate to System DSN, then click Add.

A window will appear. Scroll down and select PostgreSQL Unicode(x64), then click Finish.

Another window pop-up will appear requesting connection details.

Enter the following:

Description - Any name you prefer (e.g., Local Postgres)

Copy the following information from Aiven:

Database name - defaultdb
Server - Host from Aiven pg-96fb47b-kelvinwarui457-01c1.f.aivencloud.com
Port - 16552
User Name - avnadmin
Password - xxxxxxxxxxxx

NOTE: Ensure that the SSL Mode is set to Require.

Test the connection. If you receive a pop-up saying Connection Successful, click OK, then Save, and OK again.

Now navigate back to Power BI.

iii. Connect ODBC to Power BI to Access Your PostgreSQL Server in Aiven

Open Power BI and navigate to Home, then click Get Data.

Select ODBC, then click Connect.

A pop-up will appear.

Click the dropdown arrow and select PostgreSQL35W, then click OK.

Secure Connection Using SSL

When connecting Power BI to a cloud-hosted PostgreSQL database such as Aiven, SSL encryption is required to secure the connection.

The SSL certificate provided by Aiven helps verify the identity of the database server and ensures that communication between Power BI and the database is encrypted.

In Power BI Desktop, the PostgreSQL driver automatically establishes an SSL-secured connection when connecting to a server that requires SSL. Users only need to provide the server address, database name, and authentication credentials, while the SSL encryption is handled by the underlying database driver.

SSL encryption ensures that:

Data transferred between Power BI and the database is encrypted.
The database server’s identity is verified.
Sensitive information cannot be intercepted during transmission.

Loading Tables into Power BI

Once the connection is established, Power BI displays the database tables in the Navigator window.

In this example, the dataset contains four main tables:

Customers - stores customer information
Products - contains product details
Sales - records sales transactions
Inventory - tracks product stock levels

Select these tables and click Load to import them into Power BI.

After loading, the tables appear in the Fields pane, where they can be used to create visualizations and reports.

NOTE: Always ensure that your service is running in Aiven for a successful connection.

Creating Relationships Between Tables

After importing the tables, the next step is data modeling. Data modeling defines how different tables relate to each other so that Power BI can correctly analyze the data.

For example:

Customers --> Sales
Connected through customer_id

Products --> Sales
Connected through product_id

Products --> Inventory
Connected through product_id

These relationships are created in the Model View in Power BI.

By defining these relationships, Power BI understands how data from multiple tables should interact. For instance, when analyzing sales by product or by customer, Power BI uses these relationships to combine data from different tables correctly.

Proper data modeling improves:

Accuracy of reports
Performance of queries
Consistency of analysis

Without correct relationships, reports may display incorrect totals or duplicate values.

Why SQL Skills Are Important for Power BI Analysts

Although Power BI provides powerful visual tools for analysis, SQL skills remain essential for data analysts. SQL (Structured Query Language) allows analysts to interact directly with databases and retrieve the exact data they need.

SQL helps analysts:

Retrieve Data - Analysts can write queries to select specific columns or rows from large datasets.
Filter Data - SQL allows filtering using conditions such as dates, categories, or customer segments.
Perform Aggregations - Functions like SUM, COUNT, AVG, and GROUP BY allow analysts to summarize large datasets efficiently.
Prepare Data for Analysis - SQL can clean, transform, and structure data before it is imported into Power BI, making reporting faster and more efficient.

By combining SQL querying skills with Power BI visualization capabilities, analysts can build more accurate dashboards and extract deeper insights from data.

Conclusion

Power BI is a powerful business intelligence tool that enables organizations to analyze data and create meaningful visual reports. By connecting Power BI to SQL databases such as PostgreSQL, analysts can directly access structured data stored in relational databases.

This article explained how to connect Power BI to both a local PostgreSQL database and a cloud-hosted Aiven PostgreSQL database. It also described how to load tables, create relationships between them, and understand the basics of data modeling.

Finally, the article highlighted the importance of SQL skills for Power BI analysts, as SQL enables efficient data retrieval, filtering, aggregation, and preparation. When SQL and Power BI are used together, analysts can build powerful dashboards that support informed business decisions.

SQL Beyond the Basics: Mastering Joins and Window Functions

@waruikelvin — Mon, 02 Mar 2026 13:41:45 +0000

Introduction

SQL mastery comes from diving into advanced concepts that allow you to query, manage, and optimize databases with precision. While these concepts may add a layer of complexity to your existing data management and analysis skills, learning to apply them is both rewarding and essential for anyone serious about becoming a power user in SQL.

Today, we’ll explore two of those powerful concepts: Window Functions and Joins.

Let’s dive in.

SQL Window Functions

What Are SQL Window Functions?

SQL window functions are calculation functions that perform operations across a set of table rows defined by an OVER() clause.

They are similar to aggregate functions. However, unlike traditional aggregate operations such as GROUP BY, window functions do not collapse rows into a single result. Instead, they retain individual rows while performing calculations across a defined window of data.

Window functions use values from one or multiple rows to return a value for each row. This is what makes them different from traditional aggregate functions, which return a single value for multiple rows.

General Syntax

SELECT column_1, column_2, column_3,
       function() OVER (
           PARTITION BY partition_expression
           ORDER BY order_expression
       ) AS output_column_name
FROM table_name;

Explanation

function() → The window function (e.g., ROW_NUMBER, RANK, SUM)
OVER() → Defines how the window is constructed
PARTITION BY → Splits rows into groups
ORDER BY → Defines the logical order of rows inside each partition
AS output_column_name → Alias for the calculated result

Important Note: Window functions are processed after WHERE, GROUP BY, and HAVING, but before the final ORDER BY. This means you cannot use a window function directly in a WHERE clause. You must use a subquery or CTE instead.

Dataset Used in This Article

For demonstration purposes, we’ll use the following practice tables.

Students Table

CREATE TABLE students (
student_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
gender VARCHAR(50),
math_score FLOAT,
english_score FLOAT
);

INSERT INTO students (student_id, first_name, last_name, gender, math_score, english_score)
VALUES
(1, 'Sascha', 'Bolden', 'Female', 99.65, 86.72),
(2, 'Pyotr', 'Pepperd', 'Male', 14.72, 81.92),
(3, 'Konrad', 'Benedite', 'Male', 24.16, 35.11),
(4, 'Eduino', 'Habershaw', 'Male', 63.27, 54.66),
(5, 'Henka', 'Grellier', 'Female', 99.96, 65.71),
(6, 'Devonna', 'Cesaric', 'Female', 49.18, 93.29),
(7, 'Reggy', 'Marson', 'Male', 73.1, 19.25),
(8, 'Richmond', 'Kennealy', 'Male', 54.66, 50.52),
(9, 'Jo', 'Sea', 'Female', 52.1, 74.27),
(10, 'Gerard', 'Murphey', 'Male', 2.3, 77.36);

Stream table

CREATE TABLE stream (
student_id INT, 
class_stream VARCHAR(50)
);

INSERT INTO stream (student_id, class_stream)
VALUES
(2, 'North'),
(8, 'South'),
(3, 'East'),
(7, 'North'),
(5, 'North'),
(6, 'South'),
(7, 'East'),
(11, 'West'),
(9, 'North'),
(10, 'East');

Class Stream table

CREATE TABLE class_stream (
student_id INT, 
class_stream VARCHAR(50)
);

INSERT INTO class_stream (student_id, class_stream)
VALUES
(1, 'North'),
(2, 'South'),
(3, 'East'),
(4, 'West'),
(5, 'North'),
(6, 'South'),
(7, 'East'),
(8, 'West'),
(9, 'North'),
(10, 'East');

NOTE: The datasets used here are purely for practice purposes.

Types of SQL Window Functions

Window functions can generally be grouped into three categories:

Aggregate Window Functions
Ranking Window Functions
Value Window Functions

1. Aggregate Window Functions

Examples include:

AVG()
MAX()
MIN()
SUM()
COUNT()
STDEV()
VAR()

These functions perform calculations across a window of rows without collapsing the result set.

2. Ranking Window Functions

ROW_NUMBER()
RANK()
DENSE_RANK()
PERCENT_RANK()

These assign ranking positions to rows based on specified ordering logic.

3. Value Window Functions

LAG()
LEAD()
FIRST_VALUE()
LAST_VALUE()
NTH_VALUE()
CUME_DIST()

These functions allow comparisons between rows within a defined window.

Practical Window Function Examples

1. ROW_NUMBER()

Assigns a unique sequential number to each row.

SELECT first_name, last_name, math_score,
       ROW_NUMBER() OVER (ORDER BY math_score DESC) AS row_number
FROM students;

Each student receives a unique ranking based on their math score.

2. RANK()

Assigns ranks with gaps when ties occur.

SELECT first_name, last_name, math_score,
       RANK() OVER (ORDER BY math_score DESC) AS rank
FROM students;

If two students tie, they receive the same rank, and the next rank is skipped.

3. DENSE_RANK()

Similar to RANK() but without gaps.

SELECT first_name, last_name, math_score,
       DENSE_RANK() OVER (ORDER BY math_score DESC) AS dense_rank
FROM students;

4. PERCENT_RANK()

Calculates the relative rank between 0 and 1.

SELECT first_name, last_name, english_score,
       ROUND(PERCENT_RANK() OVER (ORDER BY english_score DESC)::numeric, 2)
       AS percentile_rank
FROM students;

5. SUM() with PARTITION (Running Total)

SELECT student_id, first_name, last_name, gender, english_score,
       SUM(english_score) OVER (
           PARTITION BY gender
           ORDER BY student_id
       ) AS cumulative_scores
FROM students;

PARTITION BY gender divides the rows into groups.
ORDER BY student_id defines the order inside each partition.
SUM() calculates the running total.

SQL Joins

Joins combine rows from two or more tables based on related columns.
Understanding joins is essential when working with normalized databases and relational systems.

Types of Joins

Inner Join
Left Join
Right Join
Full Outer Join
Cross Join
Self Join

1. Inner Join

Returns only matching rows from both tables.
students

Stream

SELECT s.student_id, s.first_name, s.last_name, c.class_stream
FROM students s
INNER JOIN stream c
ON s.student_id = c.student_id;

Students without a matching stream are excluded.

2. Left Join

Returns all rows from the left table.

SELECT s.student_id, s.first_name, s.last_name, c.class_stream
FROM students s
LEFT JOIN stream c
ON s.student_id = c.student_id;

Students without a stream appear with NULL.

3. Right Join

Returns all rows from the right table.

SELECT s.student_id, s.first_name, s.last_name, c.class_stream
FROM students s
RIGHT JOIN stream c
ON s.student_id = c.student_id;

Streams without students appear with NULL values.

4. Full Outer Join

Returns all rows from both tables.

SELECT s.student_id, s.first_name, s.last_name, c.class_stream
FROM students s
FULL OUTER JOIN stream c
ON s.student_id = c.student_id;

Unmatched rows from either side are included.

5. Cross Join

Returns every possible combination of rows.

SELECT s.first_name, s.last_name, c.class_stream
FROM students s
CROSS JOIN class_stream c;

This produces a Cartesian product.

6. Self Join

A self join joins a table to itself.

SELECT 
    s.student_id,
    s.first_name AS student_first_name,
    s.last_name AS student_last_name,
    t.student_id AS teacher_id,
    t.first_name AS teacher_first_name,
    t.last_name AS teacher_last_name
FROM students s
LEFT JOIN students t
    ON s.teacher_id = t.student_id
ORDER BY s.student_id;

This allows us to relate rows within the same table.

Complete SQL Queries Used in This Article

Below is a structure you can use to group all queries together for readers who want to test everything at once:

-- Create students table
-- Insert student data
-- Create stream table
-- Insert stream data
-- Window function examples
-- Join examples
-- Self join example

Providing a complete runnable script makes your article more beginner-friendly and practical.

Conclusion

Window functions and joins are two of the most powerful tools in SQL.

Window functions allow you to perform advanced analytical calculations without collapsing rows, making them ideal for ranking, running totals, and row comparisons.

Joins allow you to connect related tables and extract meaningful insights from structured relational databases.

When combined, these concepts unlock a new level of SQL capability, enabling you to build reports, perform advanced analytics, and solve real-world database problems efficiently.

Mastering them takes practice, but once you understand how they work, your confidence and SQL problem-solving skills will increase significantly.

Keep practicing. Keep querying!

Cleaning, Calculating, and Communicating: The Analyst’s Power BI Workflow

@waruikelvin — Sat, 07 Feb 2026 18:12:44 +0000

Have you ever opened a raw dataset and immediately felt overwhelmed? How easy was it for you to understand the data? My guess is you often found it messy and probably wondered to yourself, “How is this kind of data supposed to relay insights and help in business decision-making?”

Other than Excel, there is another tool called Power BI that allows you to load messy data, clean it, model it, visualize it, and transform it into business insights that decision-makers can understand and act on.

This article will show you what the analysis workflow in Power BI looks like as we unlock our analyst minds and showcase our skills of not only coming up with visually engaging yet insight-driven dashboards, but also being able to turn chaos into clarity.
Let’s begin!

Terms you will find in this article.

Power Query - A data transformation and preparation engine from Microsoft that enables users to import, clean, reshape, and combine data from various sources without needing advanced coding skills.
Data Analysis Expressions (DAX) - A formula expression language used in Microsoft Power BI, Power Pivot in Excel, and Analysis Services to create custom calculations, measures, and calculated columns.
*ETL (Extract, Transform, Load) process - * A data integration methodology that collects raw data from multiple sources, cleans and reshapes it, and loads it into the Power BI engine for analysis.

What is Power BI?

Power BI is a business intelligence tool developed by Microsoft that enables analysts to transform raw data into interactive, decision-ready dashboards. It allows analysts to clean raw data, calculate meaningful metrics, and communicate insights in a way decision-makers can actually use.

Before opening Power BI, great analysts ask one critical question What decision is this analysis meant to support? Many dashboards tend to fail because they start with visuals instead of purpose. A good analysis begins with clarity (What do we really expect at the end of this analysis?):

Are we trying to increase revenue?
Reduce costs?
Improve customer retention?
Identify underperforming products?
Understand customer engagement?
Increase safety in air travel?
Plan for reasonable maintenance schedules?
Decrease flight departure and arrival delays?

When the goal is clear, everything else becomes easier. The data you work on, the metrics you calculate, and the visuals you design should all serve that one objective. Power BI is just the vessel. The destination is always an impactful business decision.

NOTE - The general workflow in Power BI follows a logical order, often summarized by the ETL process (Extract, Transform, Load).

Loading data in Power BI

Loading data is the first step in analyzing data in Power BI. When you open Power BI, this is the first thing you will see. To load data, click on get data, then depending on your file type, choose the appropriate file to load. It may take some time depending on your data size.

A pop-up will then appear where you can Load data, Transform Data, or even Cancel if you maybe uploaded the wrong data set. Now, since we have to now clean the data, our next button to click is Transform Data(black button). This will now take us to our next step, **Power Query**, where we will prepare our data for analysis.

*NOTE - * In an instance where you have already cleaned your data using Excel and now want to visualize your data using Power BI, you will click the Load (Red) button. This will take you directly to your worksheet area to start your calculations and visualizations.

Preparing Data with Power Query

Real-world data is rarely analysis-ready. That’s why the first technical step in Power BI is almost always Power Query. This is what analysts use Power Query for:

Data connection
Transforming and shaping data (e.g., fixing incorrect data types such as text vs. numbers vs. dates)
Combining Data (e.g., merging related tables)
Automating processes
Loading data

For example, an airline safety and management dataset might have:

Flight dates stored as text
Revenue with currency symbols
Flight crew names are spelled differently across files
Duplicate Flight IDs

If you don’t fix these issues first, your analysis will be wrong, no matter how good your DAX or visuals are. Think of Power Query as the foundation of a house. If the foundation is weak, everything built on top will crack and eventually crumble.

Data Modelling in Power BI

Power BI will almost always automatically create relationships between the tables in your dataset, based on common fields across tables. Now, this brings us to our next step in Power BI workflow analysis, Data Modelling.

It is good practice not to fully trust Power BI’s automatic modeling, as it can sometimes create incorrect relationships. Before working on your calculations, ensure that your modeling is correct, with your relationships well identified. To understand the schemas and modeling in Power BI, check out this article that will help you understand it in detail Schemas and Data Modelling in Power BI

Turning Data into Metrics with DAX

Once the data is clean and the models are correct, analysts move to the next phase, which is calculating insights using DAX (Data Analysis Expressions). DAX is the language Power BI uses to create:

Measures (dynamic calculations like totals, averages, ratios)
Time intelligence (month-to-date, year-to-date, growth %)
Rankings and comparisons

For example, instead of just showing Total Flight Trips, an analyst might calculate:

Average Travel Delays
Profit Margin
Month-over-Month Growth
Top 10 flight accidents by cause

This is where raw data becomes business intelligence. A table of numbers tells you what happened. DAX measures help explain how well it happened and why it matters. Good analysts also keep DAX readable and logical. Complex formulas are powerful, but only if they can be understood and maintained.

Designing Dashboards That Drive Decisions

At this stage, we have successfully loaded our messy data into Power BI, transformed our data in Power Query, modelled our data, calculated metrics to get a deeper understanding of our data, and finally, we are at visualizing our data. The workflow becomes structured and logical.
This step, communication, is now a step closer to finalizing our analysis in Power BI, turning calculations into dashboards that tell a story. A strong Power BI dashboard:

Answers specific questions
Highlights what matters most
Makes insights obvious at a glance

This means:

Choosing the right visuals (not just the fancy ones)
Using layout and hierarchy to guide the viewer’s eye
Limiting clutter
Using consistent formatting and colors (Best advisable to use colors relating to the type of dataset you have, e.g. in an agricultural dataset, you can use green to represent nature and maybe a touch of brown)

Instead of asking users to search for insights, great dashboards present them. For example:

A KPI card for Total Flight Trips
A trend line showing growth in flight safety over time
A bar chart ranking the top causes of flight accidents
A slicer to explore by region or cause

The goal isn’t to impress, but to inform and facilitate great business decision-making.

What Great Analysts Do Differently

The best analysts don’t stop at insights. They go one step further and translate them into recommendations, not just Flight safety was increased in Region B, but Flight safety improved in Region B, with major accidents decreasing by 78% after targeted safety measures were implemented based on identified root causes. This suggests the measures were effective. We should review how they were implemented and consider replicating the approach in other regions.. This is where Power BI becomes a decision engine, not just a reporting tool. Here’s an example of action-oriented analysis:

Suppose an airline recorded 12,000 flights last year. After cleaning and modeling the data:
• You discover that 62% of delays happen during peak weather months.
• 45% of major incidents are linked to maintenance scheduling gaps.
Instead of simply reporting these numbers, the recommendation becomes:
**“Reallocate maintenance checks before peak weather months and reinforce crew training during high-risk periods.”**

NOTE - Insight without action is just information. Action is what creates value.

Common Power BI Mistakes (and How to Avoid Them)

Making mistakes while working with Power BI — or any tool — is part of the learning process. Even experienced analysts make mistakes. Mistakes are part of the learning process. What matters is identifying them early and improving from them. Some of the most common include:

Building dashboards before understanding the business problem
Always start with the question, not the visuals.
Overloading dashboards with too many charts
Fewer visuals = clearer message.
Writing complex DAX that no one understands
Keep formulas readable and documented.
Ignoring performance and refresh issues
Optimize models and ensure reliable data updates.
Designing for yourself instead of the user
Always think about who will use the dashboard and how, assuming they are not tech-savvy.
Not using a star schema.
Poor model design can significantly reduce performance. Always aim for a clean fact table connected to dimension tables.

The Skills That Separate Good Analysts from Great Ones

Power BI is a technical tool, but great analysis is a blend of technical and human skills. Great analysts combine:

Power Query + DAX + Data Modeling
Business thinking
Communication and storytelling

They understand that:

Data is about people
Dashboards are about decisions
And analytics is about impact

Pros and Cons of Power BI

Just like every other software you can use out there, Power BI has its own pros and cons.

      1. Pros

Cost-Effective - Offers a free, functional desktop version, with affordable Pro licensing compared to competitors.
Microsoft Ecosystem Integration - Seamlessly connects with Excel, Azure, SQL Server, and Office 365.
Powerful Visualization & Data Handling - Features a wide range of interactive, custom visual tools and strong data modeling capabilities.
Constant Updates - Microsoft frequently updates the platform with new, AI-driven features.
Large Community Support - Extensive online resources, forums, and documentation are available.
```
 2. Cons
```
Steep Learning Curve - While easy for basic tasks, mastering DAX (Data Analysis Expressions) and complex data modeling requires significant technical expertise.
Performance Issues - Can become slow when processing, loading, or refreshing large or complex datasets.
Limited Customization - Flexibility for customizing visuals can be restrictive compared to tools like Tableau.
Dependency on Windows/Cloud - Primarily designed for Windows, with limited or no native, full-featured editing capabilities on other operating systems.
Licensing Complexity - The licensing structure can be confusing, and, for some, the cost of scaling (Premium) is high.

Conclusion

Finally! We now have a clear understanding of the Power BI analysis workflow. The workflow is structured and logical — and with consistent practice, it becomes second nature. Platforms like Kaggle provide high-quality practice datasets to sharpen your skills.

The analyst’s workflow can be summarized in three powerful steps:
Load -> Clean -> Model -> Calculate -> Communicate

Power BI supports every stage of the modern analytical workflow:

Power Query cleans the chaos
Data modeling creates structure
DAX builds intelligence
Dashboards communicate insight

Tools alone don’t create impact — analysts do.
The real value lies not in reporting what happened, but in guiding what should happen next.

ALL THE BEST IN YOUR POWER BI EXPLORATION

SCHEMAS AND DATA MODELLING IN POWER BI

@waruikelvin — Fri, 30 Jan 2026 20:40:25 +0000

Introduction to Power BI and Data Modelling

Power BI is a widely used business intelligence and data visualization tool that enables organizations to analyze data and make informed, data-driven decisions. To fully leverage its capabilities, it is essential to understand how data is structured and organized within the Power BI environment.

Data modelling plays a central role in Power BI by defining how data tables are designed, connected, and optimized for analysis. Concepts such as fact and dimension tables, star and snowflake schemas, and table relationships form the foundation of an effective data model. A well-structured data model not only improves report performance and accuracy but also simplifies analysis and enhances the overall user experience.

This article explores key schemas and data modelling concepts in Power BI, including fact and dimension tables, star and snowflake schemas, and the importance of proper relationships, while highlighting why good data modelling is critical for performance and accurate reporting.

Terms You Will Find in This Article

Data Modelling – The process of structuring data into tables and relationships to support efficient analysis and reporting.
Fact Table – A table that stores measurable, quantitative data such as sales, revenue, or quantities.
Dimension Table – A table that stores descriptive attributes used to filter, group, and categorize fact data.
Star Schema – A data model design where a central fact table is directly connected to multiple dimension tables.
Snowflake Schema – A normalized version of the star schema where dimension tables are split into additional related tables.
Relationships – Logical connections between tables that control how data is filtered and aggregated.
Cardinality – The definition of how rows in one table relate to rows in another table.
Cross-Filter Direction – The direction in which filters flow between related tables. 9 DAX (Data Analysis Expressions) – A formula language used in Power BI to create calculations and measures.

What Is Data Modelling in Power BI?

Power BI data modelling is the process of structuring data in a logical and organized way to support accurate analysis and reporting. This process includes loading and cleaning data, defining relationships between tables, and creating DAX calculations.

Using the right data modelling techniques ensures clean relationships, reliable KPIs, and a strong foundation for self-service analytics across an organization. When discussing data modelling, the most commonly referenced approach is the star schema, which is also the recommended modelling technique in Power BI.

Understanding Fact and Dimension Tables

A fact table contains measurable values that can be summarized and aggregated, such as sales amount, quantity, or revenue. It also includes one or more keys that link the fact table to related dimension tables.

For large or complex data models, it is recommended to use numeric keys rather than text strings, as numeric keys generally improve model performance. Fact tables should be narrow, containing only essential measurable fields.

Columns that describe attributes, such as ProductName or ProductCategory, should be stored in dimension tables. This separation process, known as data normalization, reduces redundancy and improves performance.

Dimension tables describe the features of the data. They contain unique values and are responsible for filtering, grouping, and labeling data in Power BI reports.

The Star Schema in Power BI

The star schema is the most commonly used and recommended schema in Power BI.

Benefits of the Star Schema

Usability: Produces a clean and easy-to-understand data model.
Performance: Fewer joins result in faster queries and more efficient DAX calculations.

How a Star Schema Works

A star schema consists of a central fact table with dimension tables directly connected to it. While it is ideal to have a single fact table, multiple fact tables can be used when required.

Use Case: Star schemas are ideal for dashboards and summary reports where fast query performance is critical.

Note: A star schema does not need to visually resemble a star. Some layouts are preferred because they make filter propagation easier to understand.

The Snowflake Schema

The snowflake schema is an extension of the star schema. In this approach, dimension tables are further normalized into additional related tables. For example, a Products table may be split so that product categories are stored in a separate table. This reduces redundancy but increases the number of joins.

Use Case: Snowflake schemas are useful when storage efficiency is important, but they may reduce performance due to additional joins.

Relationships in Power BI Data Models

Relationships define how tables are connected within a Power BI model. These logical connections control how filters and calculations behave across tables and visuals. A well-designed data model relies on correctly defined relationships to ensure accurate and reliable reporting.

Creating Relationships in Power BI

Relationships can be created in two ways:

Drag a field from one table onto a related field in another table.
Use Manage Relationships from the ribbon to define relationships manually.

Although Power BI attempts to automatically detect relationships, these should always be reviewed to ensure correctness.

When setting up relationships in Power BI, there are two important options to pay close attention to: cardinality and cross-filter direction. These settings control how tables interact and how filters move through your data model. Getting them right makes a noticeable difference in report accuracy and performance. Let’s take a closer look at each of them.

Cardinality

Cardinality defines the type of relationship between two tables.

Common cardinality types in Power BI include:

One-to-Many (1:*) – The most common and recommended relationship, where one dimension value relates to many fact rows.
One-to-One (1:1) – Used when both tables contain unique values.
Many-to-Many (*:*) – Used in advanced scenarios but generally avoided due to increased complexity.

Use Case: One-to-many relationships are well-suited to star schema designs.

Cross-Filter Direction

Cross-filter direction determines how filters propagate between related tables.

Options include:

Single Direction: Filters flow from the dimension table to the fact table (recommended).
Both Directions: Filters flow in both directions and should be used cautiously.

Use Case: Single-direction filtering is ideal for star schemas. Both-direction filtering is recommended only for specific analytical scenarios where filter propagation must flow both ways.

Importance of Good Data Modelling for Performance and Accuracy

Good data modelling is the backbone of effective Power BI reporting. The way data is structured, connected, and optimized directly affects how fast reports load, how accurate calculations are, and how easy dashboards are to maintain over a period of time.

1. Performance Optimization

A well-designed data model minimizes unnecessary complexity. Using schemas such as the star schema, keeping fact tables narrow, and avoiding redundant columns reduces the number of joins Power BI must process. This results in faster visuals, smoother interactions, and quicker data refreshes, especially when working with large datasets.

Real-world example (Sales): In a sales dashboard, a star schema with a single Sales fact table connected to Date, Product, and Customer dimensions allows totals and trends to calculate instantly. In contrast, mixing product attributes directly into the fact table often leads to slower visuals and inconsistent aggregations.

2. Accuracy and Consistency

Good data modelling ensures calculations return accurate and consistent results. Correct relationships, proper cardinality, and well-defined filter directions prevent issues such as duplicated values, missing records, or incorrect totals.

Real-world example (Finance): In financial reporting, an incorrect many-to-many relationship between transactions and accounts can cause revenue or expense figures to be overstated. A properly modelled one-to-many relationship ensures that balances reconcile correctly with accounting records.

3. Simpler DAX and Easier Maintenance

When the data model is clean, DAX measures become simpler and more readable. Dimension tables naturally filter fact tables, reducing the need for complex DAX logic.

Real-world example (HR): In an HR report, separating employee attributes (department, role, location) into dimension tables allows metrics such as headcount, attrition, and tenure to be calculated with straightforward measures instead of complex filters.

This simplicity makes models easier to maintain, troubleshoot, and hand over to other developers or analysts.

Common Power BI Modelling Pitfalls

Poor data modelling often leads to:

Slow report performance due to unnecessary joins
Incorrect totals caused by wrong cardinality or filter direction
Overly complex DAX measures trying to “fix” modelling issues
Difficulty scaling the model as new data or requirements are added NOTE: Many Power BI performance and accuracy problems are not caused by visuals or DAX, but by issues in the underlying data model.

4. Scalability and Reusability

A strong data model is easier to scale as data volumes grow or business needs change. New visuals, measures, or even additional datasets can be added without redesigning the entire model. Well-modelled datasets can also be reused across multiple reports, saving development time and ensuring consistency.

Conclusion

Schemas and data modelling form the foundation of effective Power BI reporting. Understanding fact and dimension tables, choosing the appropriate schema, and defining correct relationships directly impact performance, accuracy, and scalability.

The star schema remains the preferred approach for most Power BI solutions due to its simplicity and efficiency, while the snowflake schema serves specialized use cases. Ultimately, good data modelling enables reliable analytics, scalable reporting, and better decision-making.

In short, good data modelling is not just a technical best practice; it is essential for building fast, accurate, and trustworthy Power BI solutions that support confident, real-world decision-making.

Great reports are built on great data models!

MS Excel for Data Analytics: A Beginner-Friendly Introduction

@waruikelvin — Fri, 23 Jan 2026 16:00:12 +0000

Howdy! Ever heard of Microsoft Excel? Ever wondered what its use is? Worry not, let me simplify it for you. Microsoft Excel is one of the most poowerful and beginner-friendly tools for data analytics. Before people move to advanced tools like Python or SQL, they often start with Excel, and for good reason. It helps you organize data, clean it, analyze it, and visualize it all in one place. How cool is that?

Whether you are tracking sales, survey responses, expenses, or social media performance, Excel can turn your raw numbers (data) into meaningful insights.

What You Will Learn from This Article

By the end of this article, you will be able to:

Understand what Microsoft Excel is and how it is used in data analytics
Identify rows, columns, and cells in a worksheet
Enter and organize data in table format
Use basic Excel functions like SUM, AVERAGE, COUNT, and IF
Clean data by sorting, filtering, and removing duplicates
Create Pivot Tables to summarize large amounts of data
Build simple charts to visualize trends and comparisons
Understand how Excel can be used to create basic dashboards for reporting
Gain a strong foundation to start working with data confidently, even as a beginner.

Excel Terms You Will Encounter (Excel & Data Analytics)

Here are simple explanations of key terms used in this article:

Cell - A single box in Excel where a row and column meet (example: A1).
Column - A vertical group of cells labeled with letters (A, B, C…).
Row - A horizontal group of cells labeled with numbers (1, 2, 3…).
Worksheet - One page inside an Excel file where you enter and analyze data.
Workbook - An Excel file that contains one or more worksheets.
Dataset - A collection of related data arranged in rows and columns.
Formula - An equation used in Excel to perform calculations (example: =A1+B1).
Function - A built-in formula in Excel that performs a specific task (example: SUM, AVERAGE).
Range - A group of selected cells (example: A1:A10).
Filter - A tool used to display only the data that meets certain conditions.
Sort - Arranging data in a specific order (A–Z, smallest to largest, etc.).
Pivot Table - A tool that summarizes and analyzes large datasets by grouping and calculating data automatically.
Chart - A visual representation of data, such as a bar chart or line graph.
Dashboard - A single page that shows key data insights using charts, numbers, and summaries.
Data Cleaning - The process of fixing or organizing data so it is accurate and ready for analysis.

All right, let’s get into understanding MS Excel.

1. Introduction to Excel

Definition

Microsoft Excel is a spreadsheet software developed by Microsoft that helps people store, organize, calculate, and analyze data using tables made of rows and columns.
It allows users to:

Perform calculations with formulas and functions
Sort and filter information
Create charts and graphs
Summarize data using tools like Pivot Tables

Simply put, Excel turns raw numbers into useful information that helps with decision-making.

Excel is a spreadsheet program made up of:

Rows – horizontal lines (1, 2, 3…)
Columns – vertical lines (A, B, C…)
Cells – where rows and columns meet (like A1, B2)

Each cell can contain:

Text (Names, Categories)
Numbers (Sales, Scores)
Dates (1/1/2026, 02-11-2026)
Formulas (calculations), among many others.

Example Dataset

Let’s say we have this simple sales dataset:

Date	Product	Quantity	Price	Total Sales
1/1/2026	Pen	10	15	150
1/2/2026	Book	5	70	350
2/3/2026	Pencil	7	25	175
1/4/2026	Rubber	3	10	30

_NOTE: In this table, the Total Sales column is calculated by multiplying Quantity × Price.

This kind of table is the starting point for data analysis.

2. Excel Functions (Basic Calculations)

Functions are built-in formulas that help you perform calculations easily.

1. SUM – Add Numbers

Adds a range of numbers.

=SUM(C2:C5)

This adds all values from cell C2 to C5.

Use case: Total sales, total expenses, total quantity sold.

AVERAGE – Find the Mean

Calculates the average of numbers.

=AVERAGE(D2:D5)

Use case: Average score, average daily sales.

COUNT – Count Numbers

Counts how many cells contain numbers.

=COUNT(A2:A5)

Use case: Number of transactions recorded.

COUNTA – Count Non-Empty Cells

Counts cells that are not empty (text or numbers).

=COUNTA(B2:B5)

Use case: Number of customers or products listed.

IF – Logical Function

Performs a test and returns different results depending on the outcome.

=IF(C2>10, "High", "Low")

This checks if the value in C2 is greater than 10.
** Use case** Categorizing sales as High/Low, Pass/Fail.

3. Data Cleaning in Excel

Before analysis, data often needs cleaning.
Common cleaning tasks:

Removing duplicates Data -> Remove Duplicates
Sorting data Data -> Sort A to Z
Filtering data Data -> Filter

Filtering lets you view only certain data, like:

Only sales for “Pen.”
Only records from January

4. Pivot Tables (Powerful Data Summaries)

Pivot Tables help you summarize large data quickly without complex formulas.

You can answer questions like:

Total sales per product
Sales per month
Quantity sold by category

Steps to create a Pivot Table:

Select your data
Go to Insert -> PivotTable
Choose where to place it
Drag fields into Rows, Columns, and Values

Example:

Rows -> Product
Values -> Sum of Total Sales

Excel will automatically calculate total sales for each product.

5. Charts in Excel

Charts help turn numbers into visuals that are easier to understand.

Common chart types:

Column Chart – Compares categories
Line Chart – Shows trends over time
Pie Chart – Shows proportions

How to insert a chart:

Select your data
Click Insert
Choose a chart type

Example: Sales by product displayed as a column chart.

6. Dashboards in Excel

A dashboard is a single page that shows key insights using charts, numbers, and summaries.

A simple Excel dashboard might include:

Total Sales (big number)
Sales by Product (chart)
Sales by Month (line chart)
Top Product (text or card)

Dashboards help decision-makers understand performance quickly.

Tools used in dashboards:

Pivot Tables
Charts
Slicers (interactive filters)

Conclusion

Excel is a powerful starting tool for data analytics because it helps you:

Organize data in tables
Perform calculations using functions
Clean and filter messy data
Summarize information with Pivot Tables
Visualize trends using charts
Build dashboards for reporting

Mastering Excel gives you a strong foundation before moving into advanced analytics tools like Power BI, Python, or SQL.

ALL THE BEST IN YOUR ANALYTICS JOURNEY!

Getting Started with Git: Track Your Code and Use GitHub with Confidence

@waruikelvin — Fri, 16 Jan 2026 07:51:38 +0000

Introduction

Hey! Are you learning to code? Have you heard people talk about Git and GitHub? How did you feel when you first heard those terms? Why do developers need them? What problems do they actually solve? And how do commands like push and pull fit in?

This article is written for absolute beginners who want a simple, practical introduction to Git and GitHub. You don’t need any prior experience with version control, and you don’t need to be an expert developer.

By the end of this article, you’ll understand:

What Git is and why version control is important
How to track changes in your code using Git
How to push your code to GitHub
How to pull code from GitHub

Think of Git as a safety net for your code. It helps you keep track of changes, avoid losing work, and collaborate with others confidently. Let’s break it down step by step, in plain language.

Common Git Terms You Will See

When learning Git and GitHub, you’ll come across new terms. Don’t worry, here are simple explanations to help you understand them.

Repository (Repo) – A folder that contains your project and its full change history.
Commit – A saved snapshot of your project at a specific point in time.
Branch – A separate version of your project used to work on features or fixes safely.
Main (or Master) – The primary branch containing the stable version of the project.
Remote Repository – A version of your project stored online (for example, on GitHub).
Staging Area – A place where files are prepared before committing.
Push – Sending local commits to a remote repository like GitHub.
Pull – Downloading the latest changes from a remote repository.
Clone – Creating a local copy of a remote repository.
Status – git status shows the state of your files (new, modified, or staged).

What Is Git?

Git is a distributed version control system used to track changes in source code during software development.

Instead of saving multiple copies of your project, like:

project-v1
project-v2
project-final-version

Git automatically keeps a complete history of your work.

Each time you make a meaningful change, you can save a snapshot called a commit. If something breaks later, you can easily go back to a previous version that worked.

Git vs GitHub

Git

Runs on your local computer
Tracks changes and manages versions locally

GitHub

An online platform
Stores Git repositories in the cloud
Helps developers collaborate and share code

NOTE: You can use Git without GitHub, but GitHub makes collaboration and backups much easier.

Why Is Version Control Important?

Version control (also called source control) is the practice of tracking and managing changes to code.

It helps you:

Avoid losing your code
See what changed and when
Experiment without fear
Collaborate with others
Go back to earlier versions if something breaks

Tracking Changes Using Git

Let’s look at the basic Git workflow.

1. Initialize a Git Repository

Inside your project folder, run:

git init

This tells Git to start tracking your project.

2. Check the Status of Your Files

git status

This shows which files are new, modified, or ready to be committed.

3. Add Files to the Staging Area

Before saving changes, you need to stage them:

git add .

This stages all changed files.

4. Commit Your Changes

A commit saves a snapshot of your project:

git commit -m "Add initial project files."

Creating a GitHub Repository

To push your code online, you’ll need a GitHub repository.

Go to github and sign in or sign up
Click New Repository
Choose a repository name and visibility (public or private)
Create the repository

NOTE: Don’t initialize with a README if your project already exists.
GitHub will give you a repository URL.

How to Push Code to GitHub

1. Connect Your Local Project to GitHub

Replace the URL with your own repository URL:

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

2. Push Your Code

git push -u origin main

After this, your code will appear on GitHub.

How to Pull Code from GitHub

When working with others or switching computers, you’ll need to pull the latest changes:

git pull origin main

Always pull before starting new work to avoid conflicts.

Common Beginner Mistakes

Making mistakes while learning Git is completely normal; even experienced developers make them.

Common mistakes include:

Forgetting to commit before pushing
Confusing Git (local) with GitHub (online)
Not pulling before making changes
Writing unclear commit messages

Learning Git takes practice, so be patient with yourself.

Useful Git commands to remember

git init        # Start a Git repository
git status      # Check file status
git add .       # Stage changes
git commit -m   # Save changes
git push        # Upload to GitHub
git pull        # Download updates
git log         # View commit history

Conclusion

Git is one of the most important tools you’ll learn as a developer. It helps you track changes, protect your work, and collaborate with confidence.

Start small, practice often, and don’t be afraid to make mistakes. Git is designed to help you recover from them.

Happy coding!

Semantic And Accessibility

@waruikelvin — Thu, 07 Nov 2024 12:11:16 +0000

Definition

Semantic HTML refers to the practice of using HTML tags that accurately reflect the meaning of the content they encapsulate. This approach not only aids developers in understanding code but also enhances user experience, making the web more accessible and easier to navigate. Essentially, semantic HTML acts as a liaison between the content and its intended meaning, facilitating improved interpretation by both web browsers and human readers.

Types of Semantic Elements

Semantic HTML can be divided into two primary categories:

Non-Semantic Elements: These elements do not provide a clear indication of their content’s meaning. Examples include <div> and <span>, which serve general purposes without delivering explicit information about the content they contain.
Semantic Elements: These elements clearly articulate their intended purpose, providing more informative structures. Key semantic elements include:

<header>: A container for introductory content or a group of navigational links, offering context typically found at the top of a webpage.
<nav>: Specifically designed for defining a set of navigational links, assisting users in navigating a website’s structure.
<main>: Identifies the primary content of a document, isolating significant material from other sections.
<article>: Ideal for independent and self-contained content such as blog posts and news articles.
<figure>: Represents self-contained content like illustrations, diagrams, or photos, often with accompanying captions.
<p>: The paragraph element denotes a block of text, enhancing readability and coherence.
<section>: Defines a distinct section within a document, typically used to group related content.
<aside>: Indicates content that is tangentially related to the main content, such as side notes or additional information.
<mark>: Used to highlight or emphasize specific text within a document.
<footer>: Represents the footer for a document or section, often containing copyright information or links to related content.
<summary>: Provides a visible heading for a <details> element, summarizing additional information available.
<time>: Defines a specific date or time, adding temporal context to the content.

The Importance of Using Semantic HTML Tags

Using semantic HTML tags is not only advantageous for developers but also essential for creating a web environment that is accessible and optimized for search engines. Here are three key reasons to adopt this practice:

1. Enhancing Accessibility

Accessibility in web development means ensuring that content is usable and understandable to all users, irrespective of their abilities. Semantic HTML is fundamental to this goal.

For example, assistive technologies like screen readers rely on semantic HTML to interpret and articulate content for visually impaired users. When developers use a <button> tag, a screen reader can identify it as an interactive element, clearly conveying its function. Furthermore, buttons are inherently accessible via keyboard navigation, allowing users who cannot use a mouse to engage with the site effectively.

By prioritizing accessibility through semantic HTML, developers promote inclusivity, ensuring all users can interact with online content.

2. Boosting Search Engine Optimization (SEO)

Integrating semantic HTML can significantly improve a website’s SEO, which is crucial for enhancing visibility on search engines. The key benefits include:

Improved Indexing and Ranking: Search engines use semantic tags to understand better and organize webpage structures, leading to accurate indexing and ranking that helps content reach its intended audience effectively.
Relevance and Quality: Utilizing standardized names for semantic elements enriches the consistency of content across the web, allowing browsers to interpret information with greater precision. This results in more relevant and high-quality search results, enhancing user experiences.
Indirect Impact on SEO: Although not a direct ranking factor, well-structured semantic HTML enhances user experience and engagement. A user-friendly layout encourages visitors to explore more of the website, signalling to search engines that the content is valuable, which can positively influence rankings over time.

Conclusion

In summary, embracing semantic HTML is more than just a best practice; it is vital for improving accessibility and optimizing search engine performance. Web developers should focus not only on the visual dimensions of their designs but also on the structural integrity of the content they produce. By thoughtfully incorporating semantic tags, they can enhance the relevance, clarity, and inclusivity of their websites.