Forem: Lawrence Murithi

Connecting Power BI to SQL Databases: A Practical Guide for Data Analysts

Lawrence Murithi — Tue, 17 Mar 2026 12:03:47 +0000

Introduction

In most modern organizations, data is one of the most valuable assets. Companies collect large amounts of information from sales systems, websites, customer platforms, and operational databases. To make sense of this information, businesses use tools that can transform this raw data into clear insights. One of the most widely used tools for this purpose is the Microsoft Power BI platform.

What is Power BI

Power BI is a business intelligence and data visualization tool developed by Microsoft. It allows users to connect to different data sources, analyze data, and create interactive dashboards and reports. These reports help organizations monitor performance, understand trends, and support decision-making among other uses.

Power BI is commonly used by data analysts, business managers, and decision makers because it can present complex data in simple visual forms such as charts, tables, maps, and dashboards.

Most organizations store their operational and analytical data in SQL databases. SQL databases are designed to store large amounts of structured data in tables. They allow users to query, filter, update, and analyze data efficiently using Structured Query Language (SQL). SQL databases are reliable, secure, and scalable, hence they are widely used in business systems such as sales platforms, customer management systems, and inventory systems.

Connecting Power BI to a database allows analysts to access this stored data directly. Instead of manually exporting data into spreadsheets, Power BI can retrieve the data automatically, refresh it when the database changes, and build dashboards that always reflect the latest information.

This article explains how Power BI connects to SQL databases, how to connect to a local PostgreSQL database, how to connect to a cloud database such as Aiven PostgreSQL, and how the loaded data is modeled for analysis.

Understanding the Power BI Interface

Before connecting to a database, it is helpful to understand the Power BI Desktop interface. Power BI Desktop is the main application used for building reports and dashboards.

The Power BI Desktop interface includes several sections such as:

Ribbon (Top Menu) – Contains commands and tabs such as Get Data, Transform Data, and Publish.
Report Canvas – The workspace where charts and dashboards are created.
Visualizations Pane – Used to select and customize charts.
Fields Pane – Displays the tables and columns loaded into Power BI.

You can download Power BI desktop app here.

Connecting Power BI to a Local PostgreSQL Database

PostgreSQL is one of the most popular open-source relational databases used in data analytics. Many organizations run databases locally on their own servers.
The steps below explain how to connect Power BI to a local PostgreSQL database.

Step 1: Open Power BI Desktop

Start by opening Power BI Desktop on your computer.
When the application opens, a blank report canvas appears. This is where you will build your report after loading the data.

Step 2: Get Data

On the Home tab of the ribbon, click Get Data.
This button opens a list of available data sources. Power BI supports many data sources including:

Excel
SQL Server
PostgreSQL
Web APIs The Get Data feature is the starting point for connecting Power BI to any data source. Other data sources are as shown on the image.

Step 3: Select PostgreSQL Database

From the list of available data connectors, click more to view more options. Scroll down, select PostgreSQL Database and click Connect.

Step 4: Enter the Database Connection Details

After selecting PostgreSQL and clicking connect, Power BI opens a connection window that requires connection details for the connection to go through.

Server – The location of the database server. If the database is on your computer, use localhost.

Database – The name of the database you want to connect to.

Step 5: Provide Login Credentials

After a connection is made, Power BI will ask for authentication details.
You will need to provide:
Username
Password

These credentials were set up during installation of the PostgreSQL and allows Power BI to securely access the database.

Once the credentials are entered, click Connect.

Step 6: Select Tables to Import

After connecting successfully, Power BI opens the Navigator Window which displays all available tables in the database.
You can preview the contents of each table before loading them.
There are two options:
Load – Import the data directly.
Transform Data – Clean or modify the data before loading it.

Connecting Power BI to a Cloud Database (Aiven PostgreSQL)

Many organizations now store their databases in the cloud. Cloud databases are accessible through the internet and provide benefits such as scalability, backups, and easier management.
Aiven is a cloud platform that provides managed PostgreSQL databases.
Connecting Power BI to a cloud database is not different to connecting to a local database, only that additional security steps are required.

The steps below explain how to connect Power BI to an Aiven PostgreSQL database.

Step 1: Get the Database Connection Details from Aiven

Login to Aiven and inside the dashboard, you will find the connection information for your PostgreSQL service. These details are used by Power BI to locate and connect to the database.

Step 2: Download and install the SSL Certificate

Cloud database providers often require SSL encryption to secure the connection.
An SSL certificate ensures:

Data transferred between Power BI and the database is encrypted
Unauthorized users cannot intercept the connection
The database server identity is verified

In Aiven, download the certificate file(CA Certificate) from the Connection Information section of the service dashboard.
Rename the downloaded file from ca.pem to ca.crt and install the Certificate on your PC.

Choose Local Machine as the location of the installation and click next.

Choose place all certificates in the following store and browse certificate store to Trusted Root Certification Authorities.
Click ok and finish.

Step 3: Connect Power BI

Open Power BI desktop as before, click Get Data, click more, scroll down and select PostgreSQL Database.
Copy the Server Name from the service URL(host_name:port_number) on Connection Information and paste on Power BI.
Input the name of your database and click ok.

Copy the username and password from Aiven, input them on the Power BI credentials window that opens and click connect.
Once the connection is successful, a navigator window opens and displays all tables in the database.
Select the tables you want to work with and click on load/transfrom data depending on what you wish to do with the data.
Transform data option is used to clean raw data e.g delete any duplicates and address null values using the most appropriate method.

Successfully loaded data displays on the data pane as shown on figure below.

Creating Relationships Between Tables

Once loaded, Power BI automatically detects relationships between tables based on matching columns using primary and foreign keys. Relationships not created can be manually created by dragging a column from one table onto the matching column in another table.
These relationships allow Power BI to combine information across multiple tables.
For example:

In the Model View, the tables appear as connected boxes. The relationships show how data flows between tables.

Data Modeling and Why its Important

Data modeling is the process of defining how data is stored, structured, and related within a database. It ensures that Power BI understands how different tables are related.

Good data modeling allows Power BI to:

Filter data correctly
Calculate totals accurately
Create meaningful visualizations
Avoid duplicated values

For example, when analyzing sales:

The sales table stores transaction records.
The customers table provides customer information.
The products table describes the items sold.

why SQL skills are important for Power BI analysts

Power BI is a powerful tool for building reports and dashboards, but it does not replace the need for strong data handling skills. Most business data is stored in SQL databases, and before that data can be visualized in Power BI, it must first be retrieved, cleaned, and structured properly.
SQL skills give Power BI analysts a real edge by providing an easier way to grab just what you need without pulling everything into Power BI.
Without SQL, analysts may rely too much on raw data, which can lead to slow reports, incorrect results, and inefficient workflows.
SQL allows analysts to:

1. Retrieve Data
Analysts can write queries to select specific rows and columns relevant to their analysis from a database.

-- selecting only products name and price columns from products table

SELECT product_name, price
FROM products

Why this matters in Power BI:

Reduces the amount of data imported
Improves performance
Makes the model easier to manage
Avoids unnecessary columns

2. Filter Data
In real-world scenarios, not all data is useful for analysis. Analysts often need to focus on specific time periods, regions, or business conditions. SQL thus makes it easy to filter datasets based on a specific criteria before loading them into Power BI.

-- Retrieving only sales from 2024 onwards.

SELECT *
FROM sales
WHERE sale_date >= '2024-01-01'

Why this matters:

Reduces dataset size
Speeds up report loading
Focuses analysis on relevant data
Avoids unnecessary processing inside Power BI

3. Perform Aggregations
Aggregation is the process of summarizing data. In business analysis, analysts often need totals, averages, counts, and other summary metrics. SQL can summarize large datasets quickly by using functions such as GROUP BY, SUM, COUNT, and AVG.

-- Calculating total sales per product

SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id;

Why aggregation in SQL is important:

Reduces data volume before loading
Improves Power BI performance
Simplifies data models
Avoids heavy calculations in DAX

Preparing data for Analysis

Raw data must be cleaned or transformed before it is ready for visualization.
SQL can be used to:

Joining Tables and Combining Data

Business data is usually stored in multiple tables.
SQL allows analysts to combine these tables using joins. Joined datasets in SQL can simplify the data model.

SELECT c.customer_name, s.sales_amount
FROM customers c
JOIN sales s
ON c.customer_id = s.customer_id;

Why this is important:

Combines related data into one dataset
Reduces the need for complex relationships in Power BI
Makes analysis easier
Prevents duplication errors

Data Cleaning and Preparation

Raw data is often messy. It may contain:

Missing values
Duplicate records
Incorrect formats

SQL helps clean and prepare the data before it is loaded into Power BI hence leading to better insights.

-- Eliminating duplicates

SELECT DISTINCT customer_id
FROM customers;

--Handling missing values

SELECT 
  COALESCE(phone_number, 'Not Provided') AS phone
FROM customers;

Why data cleaning matters:

Ensures data accuracy
Improves report reliability
Reduces cleaning work in Power BI
Prevents errors in calculations

Creating Calculated Fields

SQL allows analysts to create new columns based on existing data.

-- Calculate total sales

SELECT 
  product_name,
  price,
  quantity,
  price * quantity AS total_sales
FROM sales;

Why calculated fields are useful:

Prepares key metrics before loading
Reduces need for DAX calculations
Keeps logic centralized in the database

Supporting Advanced Analysis

SQL also supports more advanced operations such as:

Window functions (running totals, ranking)
Subqueries
Common Table Expressions (CTEs)
Data transformations

SELECT 
  sale_date,
  SUM(sales_amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

Conclusion

Power BI is a powerful tool that helps organizations transform raw data into meaningful insights. By connecting directly to SQL databases, Power BI allows analysts to access structured data stored in business systems and convert it into interactive dashboards and reports.
SQL prepares the foundation, and Power BI builds the story on top of it. Strong SQL skills allow analysts to work more efficiently, produce accurate reports, and deliver better insights for decision-making.
When SQL and Power BI are used together, they provide a powerful combination for modern data analysis and business intelligence.

Mastering SQL Joins and Window Functions

Lawrence Murithi — Tue, 03 Mar 2026 10:36:56 +0000

Introduction

SQL (Structured Query Language) is a powerful tool used to search, manage, and analyze large amounts of data. It is widely used by data enthusiasts, software developers and even marketing professionals.
In real-world databases, data is not stored in one large table. It is divided into multiple related tables. This makes storage efficient and avoids duplication. To work effectively with such data, you must understand SQL joins and window functions. These two features allow you to combine data correctly and perform advanced analysis without losing important details.

SQL Joins

A JOIN in SQL is used to combine rows from two or more tables based on a related column. This relationship is usually created using:

A primary key (unique identifier in one table)
A foreign key (reference to that key in another table) Joins are essential when working with relational databases because data is often split across multiple tables.

Importance of Joins

Combining related data from multiple tables
Maintaining relational integrity
Supporting one-to-many and many-to-many relationships
Building meaningful reports and analytics
Preventing unnecessary duplication of data

The type of join you use directly affects:

The number of rows returned
Whether NULL values appear
How business logic is interpreted NB: Choosing the wrong join can lead to missing data, duplicated records, or incorrect analysis.

Types of SQL Joins

INNER JOIN

The INNER JOIN returns only the rows that have matching values in both tables.

Combines records based on a related column
Returns only matching rows
Excludes non-matching rows INNER JOIN is used when:
You only need matched data
You want to exclude incomplete relationships

LEFT (OUTER) JOIN

The LEFT (OUTER) JOIN returns:

All rows from the left table
Matching rows from the right table
NULL values if no match exists LEFT JOIN is used when:
You want all records from the main table
You want to identify missing matches
You need complete reporting from one side

RIGHT (OUTER) JOIN

The RIGHT (OUTER) JOIN returns:

All rows from the right table
Matching rows from the left table
NULL where no match exists on the left NB: RIGHT JOIN works like LEFT JOIN but from the opposite direction.

FULL (OUTER) JOIN

The FULL JOIN returns:

All rows from both tables
Matching records where possible
NULL values where no match exists The FULL JOIN is used when:
Comparing two datasets
Identifying differences between systems
Performing reconciliation tasks

CROSS JOIN

A CROSS JOIN returns all possible combinations of rows thus can create very large results.
If Table A has 5 rows and Table B has 10 rows:
Result = 50 rows.
It does not use a matching condition.

A CROSS JOIN is used to:

Generate combinations
Create calendar expansions
Test scenarios

SELF JOIN

A self join joins a table to itself. Aliases are used to refer to the same tale
Example:
Employee table:
| EmployeeID | ManagerID |
To show each employee and their manager name, the table is joined to itself.

Self joins are useful for hierarchical data.

NATURAL JOIN

A natural join Joins all tables using columns that have the same name.

Performance Considerations for Joins.

Joins can affect performance, especially in large databases.
Best practices:

Index join columns (primary and foreign keys)
Avoid unnecessary joins
Filter data early using WHERE
Understand execution plans
Be careful with joins that multiply rows unintentionally

Improper joins can cause:

Duplicate results
Data inflation
Slow query execution

Window Functions

Window functions allow us to perform advanced calculations across a group of related rows while keeping the original data. They are useful for ranking, running totals, moving averages, and analytical reporting.
Window functions often remove the need for complex self-joins and provide an analytical layer within SQL.
Window functions:

Keep every row
Add calculated values to each row

Structure of a window function:

SELECT column_1,
       function() OVER (
           PARTITION BY column
           ORDER BY column
       ) AS output_column
FROM table_name;

1. OVER()

The OVER() clause defines how the window function operates and controls:

Partitioning
Ordering
Optional frame boundaries

2. PARTITION BY()

The PARTITION BY divides rows into logical groups. If omitted, the entire dataset is treated as one group.

3. ORDER BY()

ORDER BY defines the sequence of rows inside each partition.

It is essential for:

Ranking
Running totals
Time-based comparisons

If ORDER BY is omitted, row processing order is undefined.

4. Frame Clause (ROWS vs RANGE)

Used to define a range of rows(boundary) and commonly used for moving averages and cumulative calculations.
In the ROWS subclause, the frame is defined by beginning and ending row positions while in the RANGE subclause, the frame is defined by a value range.

ROWS BETWEEN lower_bound AND upper_bound
RANGE BETWEEN lower_bound AND upper_bound

Types of SQL Window Functions

Window functions fall into three main categories.

1. Aggregate Window Functions

These include:

AVG() - Calculates moving averages.
SUM() - Creates running totals.
COUNT() - calculates the number of items found in a group.
MIN() - returns the minimum value.
MAX() - returns the maximum value.

Some use cases of Aggregate window functions include:

Department totals
Running totals
Moving averages
Cumulative metrics

2. Ranking Window Functions

They are used to assign position or rank.

ROW_NUMBER() - Assigns a unique number to each row.
RANK() - Assigns rank with gaps when ties exist.
DENSE_RANK() - Similar to RANK but does not skip numbers and better for ranking reports where gaps are not desired.
PERCENT_RANK() - calculates the relative rank of a row within a group of rows.

Some use cases of Ranking window functions include:

Top N per group
Performance ranking
Leaderboards
Percentile analysis

3. Offset (Value) Window Functions

They are used to access data from other rows.

LAG() - shows previous row value and used in time-based analysis.
LEAD() - shows next row value and used in time-based analysis.
FIRST_VALUE() - returns the first value in an ordered set of values within a partition.
LAST_VALUE() - returns the last value in an ordered set of values within a partition.
NTH_VALUE() - Divides rows into equal groups and useful in performance analysis and segmentation.

Some use cases of Offset window functions are:

Month-over-month growth
Time-series comparison
Trend detection
Sequential analysis

Conclusion

SQL joins and window functions are core tools for designing efficient and powerful queries.
Joins allow you to combine data from multiple tables using defined relationships while Window functions provide an advanced analytical layer in SQL.

The Power of BI; Translating Messy Data, DAX, and Dashboards into Action

Lawrence Murithi — Mon, 09 Feb 2026 17:59:06 +0000

Introduction

In the real world, data rarely comes in a clean and perfect format. Most of the time, it comes from multiple systems, created by different people, and maintained with different rules. It may have missing values, inconsistent naming, or outdated records. Databases may store the same information in different ways. This is where analysts come in. Their work is not just about building reports, but about turning raw, messy data into clear insights that drive real business actions. Power BI is one of the main tools that helps them do this effectively.

Understanding the Business Before Touching the Data

Good analysts first try to understand what problem they want to solve before embarking on the data analysis journey. They ask questions like: What decision needs to be made? Who will use the report? What actions should the dashboard support? Without this context, even the best-looking dashboard can fail.
Understanding who your target is and what their needs are could help analysts decide what data to use, what calculations matter, and what level of detail is required.

Making Sense of Messy Data

Most data comes in a rough and messy state, therefore, a majority of an analyst’s time is spent cleaning and preparing the data to make sure it is reliable. Power BI’s Power Query tool is designed for this task. Analysts use it to load data from many sources such as Excel files, SQL databases, APIs, and cloud platforms. Power Query allows analysts to apply repeatable step-by-step transformations to clean the data.

Common data problems analysts handle

Missing values
Duplicate records that inflate totals
Different spellings or codes for the same category
Incomplete dates or incorrect data types
Columns that mix multiple values in one field

Building a Strong Data Model

After cleaning the data, analysts focus on building a proper data model. This includes defining relationships between tables, choosing the correct granularity, and organizing data in a way that supports accurate analysis.

Characteristics of a well-designed data model

Improves performance
Makes DAX calculations easier
Reduces confusion for report users

Analysts often use star schemas, separating fact tables from dimension tables, to keep the model simple and efficient. This step is invisible to most users, but is critical for reliable results.

Using DAX to Add Meaning to the Data

Raw data alone does not answer business questions. DAX(Data Analysis Expressions), therefore, helps analysts turn raw numbers into useful metrics/insight. Analysts use DAX to create measures that reflect real performance. For example, instead of showing total sales, DAX can show how sales compare over different months or years thus leading to better decision-making.

Examples of Insights obtained using DAX

Year-over-year growth
Employee turnover rate
Running totals and averages
Comparison of current performance to past periods
Percentages, ratios, and growth rates

Building Dashboards That Tell a Story

Once the data and calculations are ready, analysts design dashboards with the end user in mind. Dashboards are not just charts but are tools for communication. They should guide users toward key insights without overwhelming them. The goal is clarity, not complexity. A good dashboard should have the right visuals, avoid clutter and highlight the most important numbers hence telling a clear story. Filters and slicers allow users to explore the data without needing technical skills.
A good dashboard should have:

The right chart type for each metric
Logical layout and flow of information
Clear labels, titles, and tooltips

Good Dashboards help users:

Quickly see what is going well
Spot problems early
Ask better questions
Drill down to find the cause

Turning Insights into Real Business Actions

The real success of a dashboard is determined by the actions it supports. A good dashboard helps teams respond faster and make better decisions. This could include setting targets, tracking performance, or identifying risks early.
Since dashboards can refresh automatically, decisions are based on up-to-date information rather than static reports. Alerts and shared reports also ensure that insights reach the right people at the right time.

Measuring Impact and Improving Over Time

Analyst work does not end after publishing a dashboard. They gather feedback, monitor usage, and refine reports over time. As business needs change, dashboards must evolve with them.

Over time, Power BI reports can show measurable impact, such as reduced costs, improved performance, faster reporting cycles, or better accountability. These outcomes demonstrate how technical skills translate into real business value.

Conclusion

Analysts act as a bridge between raw data and business decisions. They clean messy data, use DAX to add meaning, and design dashboards that help people understand what is happening and what to do next. While Power BI provides the tools, it is the analyst’s understanding of data and business that turns information into action.

The value of Power BI, therefore, is not in the visuals or formulas alone but lies in how analysts use it to support smarter decisions and create measurable impact across the organization.

Schemas and Data Modelling in Power BI

Lawrence Murithi — Tue, 03 Feb 2026 15:19:33 +0000

Introduction

Power BI is a business intelligence tool used to turn raw data into meaningful reports and dashboards. It allows organizations to analyze data, track performance, and make informed decisions. However, the quality of insights produced by Power BI depends heavily on how the data is structured behind the scenes. Good visuals and advanced calculations cannot compensate for poorly designed data.

What are Schemas and Data Modelling

Data modelling is the process of organizing data into tables and defining how those tables relate to each other. A schema is the structure or design of this data model.
Schemas and data modelling define how data is organized, connected, and interpreted in Power BI. They determine how tables relate to each other, how filters flow across the model, and how calculations are performed.

Characteristics of a data model.

A good data model:

Makes reports faster and more responsive
Produces correct totals and calculations
Is easy to understand and maintain
Works naturally with DAX measures and visuals.

A bad data model:

Slows down reports
Produces wrong or inconsistent numbers
Forces complex and hard-to-read DAX formulas
Confuses report users and developers

This article explains schemas and data modelling in Power BI, focusing on:

Star schema
Snowflake schema
Fact and dimension tables
Relationships
Importance of good modelling for performance and accurate reporting

Understanding Data Modelling in Power BI

Data modelling happens after data is loaded from sources such as Excel, databases, or cloud systems. The model is built in the Model view, where tables and relationships are defined.
A Power BI data model usually includes:

Fact tables (business events or measurements)
Dimension tables (descriptive information)
Relationships between tables
A schema design (such as star or snowflake)

Fact Tables and Dimension Tables

Fact tables store measurable business data (i.e) numerical data that you want to analyze and measure.
Characteristics of fact tables:

Very large, with many rows
Contain numeric values used in calculations
Contain keys that link to dimension tables
Grow over time as new transactions are added.

Examples of fact data:

Sales amount
Quantity sold
Profit.

Dimension tables store descriptive information that helps explain the facts. Dimension tables are used for filtering, grouping, and slicing data in reports.

Characteristics of dimension tables:

Smaller than fact tables
Mostly text and categorical data
Used for filtering, grouping, and slicing
Change less frequently than facts.

Examples of dimension data:

Product name
Customer name
Region

Relationships in Power BI

Relationships in Power BI define how tables are connected and how data flows between them. A relationship is usually created between a key column in one table and a matching column in another table. These keys allow Power BI to link descriptive data from dimension tables to numerical data in fact tables. Relationships are mainly defined by cardinality, direction, and status.

Types of Relationships

One-to-Many - This is the most common and recommended relationship where one record in a dimension table matches many records in a fact table.
One-to-One - One row in one table matches one row in another table.
Many-to-One - Many rows in the fact table match one row in the dimension table.
Many-to-Many - multiple rows in one table match multiple rows in another table and is often used when there is no unique key.

Why Relationships Matter

Good relationships:

Ensure correct totals and aggregations
Control how slicers and filters behave
Improve report performance
Make DAX measures simpler and easier to maintain

On the contrary, poorly defined relationships often result in:

Wrong numbers
Missing data in visuals
Confusing filter behavior
Slow reports

Best Practices for Relationships in Power BI

Use one-to-many relationships wherever possible
Connect dimension tables to fact tables, not dimension to dimension
Use numeric surrogate keys instead of text
Avoid unnecessary many-to-many relationships
Use single-direction filtering by default
Keep the model simple and clear

Star Schema

A star schema is the recommended data model in Power BI and consists of:

One central fact table
Multiple dimension tables connected directly to the fact table. A star schema structure looks like a star, with the fact table in the center and dimension tables branching out around it.

Example:

        Date
         |
Product — Sales — Customer
         |
       Region

Benefits of Star Schema

Simple and easy to understand
Faster query performance
Fewer relationships
Easier DAX calculations
Better compatibility with Power BI’s engine.

Power BI can process queries more efficiently because dimension tables are not connected to each other.

Snowflake Schema

A snowflake schema is a more complex version of the star schema. In this structure:

Dimension tables are normalized
Dimension tables are connected to other dimension tables.

Example:

Sales → Product → Category → Department

Benefits of Snowflake Schema

Reduces data redundancy
Useful for very large or complex dimensions

Challenges of Snowflake Schema:

More complex relationships
Slower performance due to extra joins
More complex DAX measures
Harder for users to understand

Why Good Data Modelling is Critical

Performance - Power BI uses an in-memory engine. A clean star schema reduces joins and improves query speed. Poor models can cause reports to load slowly or even fail.
Accurate Reporting - Correct relationships and proper table design ensure that filters and totals behave correctly. Bad modelling often leads to duplicated values or missing data.
Simpler DAX - DAX formulas are easier to write and maintain when the model is simple. Complex schemas often require complicated formulas, which are harder to debug.
Better User Experience - Business users prefer models that are easy to understand. Clear table names, logical relationships, and simple structures help users create reports without confusion.

Common Modelling Mistakes

Using many-to-many relationships unnecessarily
Mixing transactional and lookup data in one table
Using bi-directional relationships everywhere
Not creating a proper date dimension
Loading unnecessary columns

NB: Avoiding these mistakes improves both performance and reliability.

Conclusion

Good data modelling is the foundation of effective Power BI reporting. Visuals and calculations only work well when the underlying model is designed correctly. A clean star schema with clear fact and dimension tables leads to faster performance, accurate results, and reports that are easy to build and maintain.
Good data modelling improves performance, ensures accurate reporting, simplifies DAX, and makes reports easier to use. Without proper modelling, even the best visuals cannot deliver correct insights.

Introduction to Linux for Data Engineers

Lawrence Murithi — Mon, 26 Jan 2026 16:28:35 +0000

Introduction

Linux is one of the most important tools for data engineers. Most data systems today run on Linux servers, including cloud platforms, databases, and big data tools like Hadoop and Spark. Understanding Linux basics is, therefore, a key skill for anyone starting a career in data engineering.

This article introduces Linux in a simple way. It explains why Linux is important for data engineers, shows basic Linux commands, and demonstrates how to create and edit files using Vi and Nano, which are common Linux text editors.

Why Linux for Data Engineers

Linux is important for data engineers for several reasons:

Most data pipelines run on Linux servers
Cloud platforms like AWS, Azure, and Google Cloud use Linux
Tools such as Hadoop, Spark, Airflow, and Kafka are built for Linux
Linux is stable, secure, and efficient for large data processing
Data engineers often work with Log files, Configuration files and Scripts written in Python, SQL, or Bash. Linux makes it easy to manage these files directly from the terminal.

Basic Linux Commands For Beginners

Linux commands are instructions typed in the terminal to tell the operating system what to do, such as creating files, moving between folders, or running programs. They allow users to interact directly with the system in a fast and efficient way. Linux commands help manage files, automate tasks and work effectively on servers, which is essential in data engineering and software development.
Below are some of the beginner linux commands.

ssh root@IP - connects to the server
pwd - Shows current directory
ls - shows all files and folders in the current directory.
cd - Changes directory and cd .. moves back one level
mkdir- Creates a new directory
touch - Creates an empty file
cp - Copies files and cp -r copies folder
mv - Moves or renames files
rm - Deletes a file and rm -r deletes a folder

-cat - display file content

Linux Vi and Nano Text Editors
Linux editors are programs used to create, open, and edit text files directly from the terminal. They are important because many configuration files, scripts, and logs in Linux are text-based. Data engineers and developers often use Linux editors when working on servers where graphical tools are not available.
Some of the common Linux text editors are Vi and Nano.
1. Nano Editor
Nano is a simple and beginner-friendly editor.
To open or create a file with Nano:

nano filename.txt

The command opens the window below.

Other nano commands

Command What it does

Ctrl + O Saves the file
Ctrl + X Exits Nano
Ctrl + G Shows help
Ctrl + W Searches for text
Ctrl + K Cuts (removes) a line
Ctrl + U Pastes a cut line
Ctrl + A Moves cursor to start of line
Ctrl + E Moves cursor to end of line
Ctrl + C Shows current line and column
Ctrl + _ Go to a specific line number

2. Vi/Vim Editor
Vi is a powerful editor and widely used in professional environments.
Vi has 3 main modes:

Normal mode - navigation and commands
Insert mode - typing text
Visual mode - selecting text To open a file in Vi editor:

vi filename.txt

The below window opens when the command is prompted.

Other vi commands

Entering Insert Mode

i - insert before cursor
a - append after cursor
o - open new line below
I - insert at beginning of line
A - append at end of line

Saving and Exiting/quiting

:w - save (write)
:q - quit
:wq or ZZ - save and quit
:q! - quit without saving
:w filename - save as new file

Navigation Commands

h - Move left
l - Move right
j - Move down
k - Move up
gg - Go to start of file
G - Go to end of file
0 - Start of line
$ - End of line

Editing Commands

x - delete character
dd - delete line
yy - copy line
p - paste below
P - paste above
u - undo
Ctrl+r - redo

Conclusion
Linux is a core skill for data engineers because it is used in servers, cloud platforms, and data tools. Basic Linux commands help you move around the system and manage files.
Learning Linux early makes it easier to work with data pipelines, scripts, and production systems.

A Simple beginners Guide to Git & GitHub

Lawrence Murithi — Sun, 25 Jan 2026 12:37:27 +0000

What is Git, GitHub and why version control is important

1. What is Git & GitHub
Git is a distributed version control system (VCS)/tool that helps you save, track, and manage changes in your code. It keeps a history of your work so you can see what changed, when it changed, and who changed it.
Think of Git like a save system for your code, but much better than “Save As”.
GitHub is an online platform where you store Git projects.
GitHub allows you to:

Back up your code
Share code with others
Work on the same project from different computers.

What is version control?
Version control is a system that tracks and manages changes to software code and files over time.

Why is version control important?
Version control helps:

Track changes in your work
Go back to an older version if something breaks
Work safely without fear of losing files
Collaborate with other people on the same project

2. Set Up the Git & GitHub Environments
Step 1: Create a GitHub Account
Sign up on GitHub using you credentials.

Step 2: Install Git
Download Git.
Install the application using the downloaded file.
Open Git Bash (Windows) or Terminal (Mac/Linux).
Configure your identity(Name and Email) to help Git Identify who is making the changes any time the changes are made.
NB: Use the email address used to sign up on GitHub.

git config -global user.name "your name"

git config -global user.email "youremail@example.com

Check to ensure your configuration has been set up.

git config -global --list

Step 3: Connect Git to Your GitHub Account
One of the easiest ways to connect your Git to your GitHub Account is using an SSH key which provides(digital identity) a secure password-less way to connect both to avoid the need for inputing a password every time.
To generate the SSH key on GitBash, run the command:

ssh-keygen -t ed25519 -C "youremail@example.com"

Now, you need to generate an agent; a helper program that holds your key in memory.

eval "$(ssh-agent -s)"

Add the key generated to the agent.

ssh-add ~/.ssh/id_ed25519

Lastly, print the public key and use it to connect your GitHub account.

cat ~/.ssh/id_ed25519.pub

Copy the key, navigate to the SSH and GHG keys on settings in you GitHub account and add a new SSH key.

Finally, authenticate your GitHub account in Git by running this command:

ssh -T git@github.com

3. Set up a Repository in GitHub and Project folder in Git

Create a repository on GitHub
Create a project folder in Git

mkdir Myproject

Initialize Git in your project. Tells Git to start tracking this project.

git init

Add files in your folder. Tells Git which files to track.

git add

Save changes (commit)

git commit -m "your message"

4. Connect your project to GitHub)

git remote add origin https://github.com/yourusername/repositoryname.git

Push code to GitHub (send code from your computer to GitHub) Pushing refers to uploading local commits to a remote server to make your code accessible to others.

git push -u origin main

Pull code from GitHub (send code from GitHub to your computer).

git clone https://github.com/username/repositoryname.git

git pull origin main

clone creates/downloads a new copy on your local computer while pull updates your local files with any changes made.

Check the status of your file.

git status

Checking status shows:

Modified files
New files
Files ready to be committed

Summary Cheatsheet

Conclusion
Git, GitHub, and version control help developers and data professionals manage their work in a clear and organized way. While Git allows users to track changes, save progress through commits, and return to earlier versions when needed, GitHub provides an online platform to store projects, share code, and collaborate with others. Together, they reduce the risk of losing work, make teamwork easier, and support professional development by keeping code history clear and well documented.