DEV Community: Benta Okoth

Connecting Power BI to a SQL database

Benta Okoth — Sat, 07 Mar 2026 09:41:55 +0000

Power BI - Is a complete reporting solution that offers data preparation, data visualization, distribution and management through development tools and an online platform.

Power BI for Data Analysis and Business Intelligence

Helps track key performance indicators (KPIs).
Monitor business operations.
Identify trends.
Generate reports that help management make strategic decisions.

## Get Data In Power BI

The Get Data tab on the Power BI desktop helps use load data from various sources to our Power BI for the data visualization and report.
The various data sources are namely:
(i)Flat File
(ii) Relational data
(iii) NoSQL database
(iv) Online Service
(v) Azure Analysis Service

Connecting Power BI to SQL Database

We understand that the SQL database can be hosted on cloud or on premise.

On-Premise Database - is a database that is hosted on servers located inside a company’s physical infrastructure, such as their data center or office server room.
Example: a company installs Microsoft SQL Server on a server inside their office network.
Characteristics

Stored on local company servers
Managed by the internal IT team
Requires physical hardware
Accessible mainly through the company network

Cloud-Based Database - is hosted on remote servers managed by a cloud provider, and accessed via the internet.

Example:
Azure SQL Database
Amazon RDS
Google Cloud SQL
The infrastructure is maintained by the cloud provider.

Characteristics

Hosted in the cloud provider’s data centers
Accessible through the internet
Scalable on demand
Maintenance handled by the provider

Connecting Power BI to a Cloud SQL Database(Aiven PostgreSQL)

Steps :
1. Obtain Connection Details from Aiven

Ensure you have signed in to Aiven console using the link https://console.aiven.io/login Log in to the Aiven dashboard using your gmail.
Select your PostgreSQL service.
Navigate to the Overview or Connection Information section by clicking on the database to be connected.

2. Download the SSL Certificate

Cloud database services usually require encrypted connections for security. In Aiven, this encryption is enabled using SSL certificates.
To download the certificate:
In the Aiven dashboard, go to the Connection Information or Security section.
Locate the CA Certificate (Certificate Authority certificate).
Download the certificate file to your computer by clicking on the download arrow.

This certificate will be used to verify the identity of the database server and encrypt the connection between Power BI and the database.

3. Connect Power BI to the PostgreSQL Database
After obtaining the connection details and SSL certificate, you can connect the database to Power BI.

Steps:

Open Power BI Desktop.
Click Get Data.
Select PostgreSQL database.
Enter the following:
Server: host:port then click ok

Database: your database name.
Choose the connection mode (Import or DirectQuery).
When prompted, enter: Username Password

## Connecting Power BI to a Local PostgreSQL Database
Step
1: Open Power BI Desktop

Launch Power BI Desktop on your computer.
Go to the Home tab.
Click Get Data then choose PostgreSQL db.

Step 2: Enter the Server Name
A connection window will appear asking for the server details.
In the Server field, enter:
localhost
or
localhost:5432
Explanation:
localhost means the database is running on your local machine.
5432 is the default port used by PostgreSQL.

Step 4: Enter the Database Name

In the Database field:
Type the name of the database you want to connect to.

If you leave this field blank, Power BI will display all available databases.

Step 5: Choose Data Connectivity Mode
Power BI will ask how you want to access the data.
You can choose:
Import Mode
Data is copied into Power BI.
Faster for reporting.
DirectQuery
Queries run directly on the PostgreSQL database.
Shows real-time data.
For most local analysis, Import Mode is commonly used.

Step 6: Provide Database Credentials

Power BI will then request authentication details.
Enter: Username – your PostgreSQL user Password – the password for that user Then click Connect.

Step 7: Select Tables to Load
Once the connection is successful, Power BI opens the Navigator window.
Here you will see:
All tables
Views
Database schemas
Steps:
Select the tables you want to analyze.
You can preview the data on the right side.
Click Load to import the tables into Power BI.
Step 8: Data is Loaded into Power BI
After clicking Load:
Power BI imports the selected tables.

The tables appear in the Fields panel on the right side.
You can now start creating:
charts
dashboards
reports
data models

Learning SQL JOIN and Window Functions

Benta Okoth — Thu, 05 Mar 2026 20:29:26 +0000

SQL JOINS

JOIN clause is used to combine rows from two or more tables, based on a related column between them
To use joins we need to have a primary key and a foreign key
A Primary Key - uniquely identifies each row in a table. It must contain unique values. Cannot contain NULL. Only one primary key per table (but it can be made of multiple columns – composite key)

A Foreign Key is a column that references the Primary Key of another table. It creates a relationship between tables. Can contain duplicate values. Can sometimes contain NULL. Ensures referential integrity (you cannot reference a non-existing record)

We are going to use the two below tables to explain the concept of join.

select * from employees e;

select * from departments d ;

There are different types of JOINs in SQL:

(i). (INNER) JOIN: Returns only rows that have matching values in both tables

select e.name, d.department_name from employees e inner join departments d on e.department_id = d.department_id;

(ii). LEFT (OUTER) JOIN: Returns all rows from the left table, and only the matched rows from the right table

Example: all departments and only the employees with department assigned to them.

select e.name, d.department_name from departments d left join employees e on d.department_id = e.department_id;

(iii). RIGHT (OUTER) JOIN: Returns all rows from the right table, and only the matched rows from the left table

Example: employees and all departments(Empty departments) The table that comes after the word right join

select e.name, d.department_name from employees e right join departments d on e.department_id = d.department_id;

(iv). FULL (OUTER) JOIN: Returns all rows when there is a match in either the left or right table

select e.name, d.department_name from employees e full outer join departments d on e.department_id=d.department_id;

(v). CROSS JOIN: Returns every combination of rows from both tables
-Example - Every employee with every project ie if one employee has 3 project we shall have the name repeated thrice and the projects will differ
select e.name, p.project_name from employees e cross join projects p;

(vi). SELF JOIN :- Is a regular join, but the table is joined with itself.

Example -Show employees and their managers select e.name as employee, m.name as manager from employees e join employees m on e.manager_id = m.employee_id;

(vii). NATURAL JOIN - it automatically joins all tables using columns that have the same names

department_id will be the unifying factor(all the rows and all the columns from both tables)

select * from employees e natural join departments d ;

## WINDOW FUNCTIONS

A window function in SQL is a function that performs a calculation across a set of rows that are related to the current row, without collapsing those rows into a single result.
This is the key difference from GROUP BY.
GROUP BY → combines rows and returns one row per group.
Window functions → keep every row but add a calculated value based on other rows in the "window".

#### Ranking window functions

a. The RANK() function - The RANK() function assigns a rank to each row based on the order specified within the window. Rows with the same values receive the same rank, and the next rank is skipped.

select title, price, rank() over (order by price desc) as price_rank from books;

b.The DENSE_RANK() function - The DENSE_RANK() function operates similarly to the RANK() function except it does not skip any ranks even if rows have the same values.

select title, price, dense_rank() over (order by price desc) as price_rank from books;

c.The ROW_NUMBER() function - The ROW_NUMBER() function assigns a unique sequential number to each row within a partition, regardless of the column values. It makes sure that no two rows can have the same row number within a division.

d.The NTILE() function - divides sorted partitions into n-number of equal groups. Each row in a partition is assigned a group number.
select c.first_name, c.last_name, sum(o.quantity) as total_quantity, ntile(3) over (order by sum(o.quantity) desc) as quantity_tile from orders o join customer c on o.customer_id = c.customer_id group by c.first_name, c.last_name;

## The Analytical Window Functions

The LEAD() function - The LEAD(column, n) function allows access of a value within a column from the
following nth-row relative to the current row. It is the counterpart of the LAG() function. The syntax for lead and lag are the same

select o.order_id, o.customer_id, o.quantity, lag(o.quantity) over ( order by o.order_id ) as next_quantity from orders o;

The LAST_VALUE() function- The LAST_VALUE() function allows the retrieval of the value of a column from the last row
within a window frame. Operates similar to FIRST_VALUE()

SELECT
Start_date,
Department,
First_name,
LAST_VALUE(First_name) OVER (
ORDER BY Start_date) AS Last_employee
FROM
Employee
ORDER BY
Department;

The Aggregate Window Function

SELECT
    c.first_name,
    c.last_name,
    o.quantity,
    SUM(o.quantity) OVER (PARTITION BY c.customer_id) AS totalquant_
FROM
    orders o
INNER JOIN
    customer c ON o.customer_id = c.customer_id;

select o.customer_id, c.first_name, c.last_name, sum(o.quantity) as total_quantity,
avg(b.price) as avg_price,
sum(o.quantity) over (partition by o.customer_id) as total_order_quantity
from customer c 
join orders o on c.customer_id = o.customer_id
join books b on o.book_id = b.book_id
group by c.first_name, c.last_name, o.quantity, o.customer_id;

SELECT
Department,
First_name,
Salary,
MIN(Salary) OVER (
PARTITION BY Department) AS Min_salary
FROM
Employee
ORDER BY
Department;

Translating Messy Data, DAX, and Dashboards into Action Using Power BI

Benta Okoth — Fri, 06 Feb 2026 17:42:50 +0000

Introduction

Power BI - Is a complete reporting solution that offers data preparation, data visualization, distribution and management through development tools and an online platform.

There are Three Major Components of Power BI

Power BI Desktop(desktop application application) - For creating semantic models and reports with visualization.
Power BI Service(online platform) - Dashboards from published reports and distribute content with aps
Power BI Mobile(cross-platform mobile app) - For on-the-go access to the PBI service content, designed for mobile.

Analytics Mindset

Analytic mindset encompasses using data to identify trends, ask relevant questions to inform decision making.
Analytical mindset is more evidence -based rather than using assumptions or intuition.

Analytics Mindset Helps:

Asks why something is happening, not just what happened.
Breaks down problems into measurable components.
Looks for trends, patterns, and outliers in data.
Uses data to make informed decisions and recommendations.

The Flow of Power BI is :

Connect to data with Power BI Desktop
Transform data with Power Query Editor
Model data with Power BI Desktop
Create Visualization and reports with Power BI Desktop
Publish Reports to Power BI Service
Distribute and Manage reports in Power BI service.

Building Blocks of Power BI

Semantic models - consist of all the connected data, transformation, relationships and calculations.
Visualization- when you create a visualization , you add it to the canvas for a report page.

1. Get Data In Power BI

Steps :
Open Power BI Desktop
Connect to different data sources under Get Data
Preview source data with Power Query

(i)Flat File- We can get data from different flat files and then as well be able to change the data source.

Open Power BI click on Get Data then under common data sources choose Excel Workbook or Text/csv.

(ii) Relational data- We can as well get data from relational data sources

Get data -> Click on from SQL Server(enter servers and database).
Data Connectivity mode > Import
Advanced Options to type your SQL Query
Change data source settings under Transform data Power BI uses a view, when it retrieves data, it participates in query folding , a feature of Power Query.

(iii) NoSQL database - You can get data from NoSQL database like Azure, Azure Cosmos db, data stored in JSON format.

(iv) Online Service - We can get data from online services eg SharePoint, OneDrive, Dynamic 365, Google Analytics then enter the URL.

(v) Azure Analysis Service - A fully managed PaaS. Its a similar approach as getting data from sql server.

authenticate to the server
pick the model you want to use
select

Power BI Storage Model

We have three modes of storage:
Import
Direct Query
Dual(composite)

2. Transform data with Power Query Editor

Power Query Editor helps with data cleaning and profiling.
The value or table in a column represents that the table has relationship with other tables and can be used to join tables.
At Power Query Editor helps profile data in PBI.

Profiling data is about studying the naunces of the data : determining anomalies, examining and developing the underlying data structures and querying data statistics eg row counts, value distribution, min and max values, average.
under View -> Data Preview
-> Check Column Quality
-> Column Profile
-> Column Distribution
-> Value Distribution
-> Column Statistics

When the Distinct = Unique it means the column contains unique values and this helps create 1-1 relationship.
Power Query Editor helps us with data cleaning, transformation and then load, that is,

(i) Unpivot column, pivot columns and transpose columns.

(ii) Resolve inconsistencies, unexpected or null values, data quality issues.

(iii) Apply user-friendly value replacement.

(iv) Profile data so you can learn more about a specific column before using it.

(v) Evaluate and transform column data types.

(vi) Apply data shape transformations to table structures.

(vii) Combine queries.

(viii) Apply user-friendly naming convention to columns and queries.

(ix) Edit M code in the Advanced Editor.

The Various Power BI Functions and their Categorization

DAX functions (Modeling & calculations)

Used for measures, calculated columns, and calculated tables.
Common categories

Aggregation SUM(), AVERAGE(), COUNT(), DISTINCTCOUNT()
Filter / context CALCULATE(), FILTER(), ALL(), ALLEXCEPT()
Time intelligence DATEADD(), TOTALYTD(), SAMEPERIODLASTYEAR() , DAY(), MONTH()
Logical IF(), SWITCH(), AND(), OR(), NESTEDIFS
Lookup / relationships RELATED(), LOOKUPVALUE(), USERELATIONSHIP()
Table functions VALUES(), SUMMARIZE(), ADDCOLUMNS()

Used when: you’re creating KPIs, ratios, YoY growth, rolling averages, etc

Power Query (M) functions (Data transformation) Used in Transform Data before the model loads.

Common categories

Text Text.Upper, Text.Lower, Text.Trim, Text.Contains
Date/Time Date.Year, Date.Month, Date.AddDays
Number Number.Round, Number.Abs
Table Table.SelectRows, Table.AddColumn, Table.Merge
List List.Sum, List.Distinct
Conditional logic if … then … else

Used when: cleaning data, shaping columns, merging tables, fixing formats.

Power BI visual-level “functions” Not written as code, but still important.

Visual aggregations (Sum, Avg, Min, Max)
Filters (Top N, Relative Date)
Drill-down / drill-through
Tooltips
Conditional formatting
Visual calculations (newer feature)

Used when: shaping how data appears on the report.

Joins and Relationships in Power BI

Joins - This is primarily achieved through Merging Queries in Power Query – Go Analytics Merge Queries in Power Query (Excel/Power BI) joins two tables based on common columns, similar to a SQL JOIN, to combine related data.
Found in the Home tab, it offers options to merge into an existing query or as a new one. It supports various join types, including Inner, Left Outer, and Full Outer.

*Relationships * - are the connections between tables that allow you to analyze data across multiple sources as if they were one. Think of them like bridges that link different datasets together so you can build meaningful reports and visuals.

Data modeling concepts and best practices

The data modelling process involves six steps:
Identifying business entities
Defining key properties
Creating a draft ER model
Identifying data attributes
Mapping attributes to entities
Finalizing and validating the data model

Visualizations overview in Power BI

Power BI offers a wide variety of built-in visuals, each designed for specific scenarios and data types.
Visuals in a report interact with each other through cross-filtering and cross-highlighting, creating a fully interactive and dynamic experience. You can also drill through to other pages or even other reports to explore related data in more detail.

Charts for comparison and trends

Bar and column charts Bar and column charts are the standard for comparing specific values across different categories. Use column charts for time-based comparisons and bar charts when category names are long.

Line charts Line charts emphasize the overall shape of values over time, making them ideal for showing trends and patterns. They work best with continuous data along the X axis.

Area charts Area charts are based on line charts with the area between the axis and line filled in. They emphasize the magnitude of change over time and can show cumulative totals when stacked.

Combo charts Combo charts combine a column chart and a line chart into one visual, allowing you to compare multiple measures with different value ranges. They can have one or two Y axes.

Ribbon charts
Ribbon charts show which category has the highest rank over time, with the highest value always displayed on top for each period. They're effective for visualizing rank changes.
Waterfall charts
Waterfall charts show a running total as values are added or subtracted. They're useful for understanding how positive and negative changes affect an initial value, such as tracking contributions to net income.

Charts for part-to-whole relationships

Pie and donut charts Pie and donut charts show the relationship of parts to a whole. Donut charts have a blank center that allows space for a label or icon. Use these charts when you have a small number of categories.

Treemaps Treemaps display hierarchical data as nested rectangles, with size representing value. They're excellent for showing large amounts of hierarchical data and the proportions between parts and the whole.

Funnel charts Funnel charts visualize sequential processes with stages, where items flow from one stage to the next. They're commonly used for sales pipelines and conversion tracking.

Charts for distribution and relationships

Scatter, bubble, and dot plot charts Scatter charts display data points at the intersection of two numerical values, revealing correlations and clusters. Bubble charts add a third dimension through bubble size, while dot plots can use categorical data on the X axis.

Tables and matrices These visuals display detailed data in rows and columns.

Tables
Tables present related data in a grid of rows and columns. They're ideal when you need to see exact values and make quantitative comparisons across many values for a single category.

Matrix visuals
Matrix visuals support stepped layouts and make it easier to display data across multiple dimensions. They automatically aggregate data and enable drilling down into hierarchies.

Map visualizations

Map visuals help you display geographic and spatial data. Power BI offers several map types for different scenarios.

Cards or callouts, KPIs, and gauges

These visuals help you highlight key values and track progress toward goals and key metrics.

Card visuals Card visuals display a single fact or data point prominently. The card visual supports both single-card and multi-card layouts, and can include images and detailed reference values within the visual. Use cards when a single number, such as total sales or market share, is the most important thing to track.

KPI visuals
Key performance indicator (KPI) visuals communicate progress made toward a measurable goal. They're ideal for measuring progress and distance to a metric.
Gauge charts
Radial gauge charts display a single value measuring progress toward a goal. The needle represents the target value, and shading shows progress. They're great for showing the health of a single measure.
Goals visual
The goals visual displays metrics and scorecards that help teams track progress toward business objectives. You can set targets, track current values, and visualize status with color-coded indicators.

AI-powered visuals

These visuals use artificial intelligence to help you explore and understand your data. Decomposition tree Decomposition tree visuals let you visualize data across multiple dimensions. They automatically aggregate data and enable drilling down in any order, with AI helping identify the next dimension to explore.

Key influencers
Key influencer charts display the major contributors to a selected result or value. They help you understand which factors influence a key metric.

Anomaly detection
Anomaly detection automatically finds anomalies in line chart data. It highlights unexpected spikes and dips, helping you identify outliers that need investigation.

Filtering visuals
Power BI provides multiple ways to filter data in your reports. All visuals can be filtered using the Filters pane, which lets you apply filters at the visual, page, or report level. For more direct interaction, slicers provide on-canvas filtering controls.

Slicers
Slicers come in multiple formats including button, list, dropdown, and date range. They display commonly used filters on the report canvas for easier access.

Other visuals

Image visual
Text box and shapes
Buttons and navigators
Paginated report visual
Q&A visual
R and Python visuals
Power Apps visual
Custom visuals

Choosing the right visual

Your data type: Categorical, numerical, time-based, or geographic
Your goal: Compare values, show trends, display relationships, or track progress
Your audience: What level of detail do they need?
Available space: Some visuals work better in smaller or larger areas

Dashboard design and layout principles

Consider your audience
Tell a story on one screen
Make use of full-screen mode
Accent the most important information
Place the most important information
Use the right visualization for the data

**Data storytelling and translating insights into action

Data storytelling is very similar to human storytelling but provides the added benefits of deeper insights and supporting evidence through graphs and charts.
Through data storytelling, complicated information is simplified so that your audience can engage with your content and make critical decisions quicker and more confidently.

Making sure your data story is valuable

Think about your theory - What do you want to prove or disprove? What do you think the data will tell you?
Collect data. Collate the data you’ll need to develop your story.

Define the purpose of your story- Using the data you gathered, you should be able to write what the goal of your story is in a single sentence.

Think about what you want to say- Outline everything from the intro to the conclusion.

Ask questions. Were you right or wrong in your hypothesis?- How do these answers shape the narrative of your data story?

Create a goal for your audience- What actions would you like them to take after reading your story?

The key components of data storytelling

Use data as the foundation of your story
Create visuals to make insights clear
Craft a narrative that connects data and visuals

Microsoft Power BI

Benta Okoth — Mon, 02 Feb 2026 17:39:23 +0000

Introduction

Power BI - Is a complete reporting solution that offers data preparation, data visualization, distribution and management through development tools and an online platform.

There are Three Major Components of Power BI

Power BI Desktop(desktop application application) - For creating semantic models and reports with visualization.
Power BI Service(online platform) - Dashboards from published reports and distribute content with aps
Power BI Mobile(cross-platform mobile app) - For on-the-go access to the PBI service content, designed for mobile.

The Flow of Power BI is :

Connect to data with Power BI Desktop
Transform data with Power Query Editor
Model data with Power BI Desktop
Create Visualization and reports with Power BI Desktop
Publish Reports to Power BI Service
Distribute and Manage reports in Power BI service.

Building Blocks of Power BI

Semantic models - consist of all the connected data, transformation, relationships and calculations.
Visualization- when you create a visualization , you add it to the canvas for a report page.

1. Get Data In Power BI

Steps :
Open Power BI Desktop
Connect to different data sources
Preview source data with Power Query

(i)Flat File- We can get data from different flat files and then as well be able to change the data source.

Open Power BI click on Get Data then under common data sources choose Excel Workbook or Text/csv.

(ii) Relational data- We can as well get data from relational data sources

Get data -> Click on from SQL Server(enter servers and database).
Data Connectivity mode > Import
Advanced Options to type your SQL Query
Change data source settings under Transform data Power BI uses a view, when it retrieves data, it participates in query folding , a feature of Power Query.

(iii) NoSQL database - You can get data from NoSQL database like Azure, Azure Cosmos db, data stored in JSON format.

(iv) Online Service - We can get data from online services eg SharePoint, OneDrive, Dynamic 365, Google Analytics then enter the URL.

(v) Azure Analysis Service - A fully managed PaaS. Its a similar approach as getting data from sql server.

authenticate to the server
pick the model you want to use
select

Power BI Storage Model

We have three modes of storage:
Import
Direct Query
Dual(composite)

2. Transform data with Power Query Editor

Power Query helps with data cleaning and profiling.
The value or table in a column represents that the table has relationship with other tables and can be used to join tables.
At Power Query Editor helps profile data in PBI.

When the Distinct = Unique it means the column contains unique values and this helps create 1-1 relationship.
Power Query Editor helps us with data cleaning, transformation and then load, that is, (i) Unpivot column, pivot columns and transpose columns.

(ii) Resolve inconsistencies, unexpected or null values, data quality issues.

(iii) Apply user-friendly value replacement.

(iv) Profile data so you can learn more about a specific column before using it.

(v) Evaluate and transform column data types.

(vi) Apply data shape transformations to table structures.

(vii) Combine queries.

(viii) Apply user-friendly naming convention to columns and queries.

(ix) Edit M code in the Advanced Editor.

The Various Power BI Functions and their Categorization

DAX functions (Modeling & calculations)

Used for measures, calculated columns, and calculated tables.
Common categories

Aggregation SUM(), AVERAGE(), COUNT(), DISTINCTCOUNT()
Filter / context CALCULATE(), FILTER(), ALL(), ALLEXCEPT()
Time intelligence DATEADD(), TOTALYTD(), SAMEPERIODLASTYEAR()
Logical IF(), SWITCH(), AND(), OR()
Lookup / relationships RELATED(), LOOKUPVALUE(), USERELATIONSHIP()
Table functions VALUES(), SUMMARIZE(), ADDCOLUMNS()

Used when: you’re creating KPIs, ratios, YoY growth, rolling averages, etc

Power Query (M) functions (Data transformation) Used in Transform Data before the model loads.

Common categories

Text Text.Upper, Text.Lower, Text.Trim, Text.Contains
Date/Time Date.Year, Date.Month, Date.AddDays
Number Number.Round, Number.Abs
Table Table.SelectRows, Table.AddColumn, Table.Merge
List List.Sum, List.Distinct
Conditional logic if … then … else

Used when: cleaning data, shaping columns, merging tables, fixing formats.

Power BI visual-level “functions” Not written as code, but still important.

Visual aggregations (Sum, Avg, Min, Max)
Filters (Top N, Relative Date)
Drill-down / drill-through
Tooltips
Conditional formatting
Visual calculations (newer feature)

Used when: shaping how data appears on the report.

Different Schemas In Power BI

A schema refers to the structure and organization of data within a data model.
Schemas define how data is connected and related within the model, influencing the efficiency and performance of data queries and reports.
Understanding schemas helps in designing best data models that support comprehensive analysis

Types of Schemas in Power BI

1. STAR SCHEMA

Definition: The star schema is a simple and commonly used schema in data warehousing. It consists of a central fact table surrounded by dimension tables, forming a star-like pattern.

Structure: The central fact table contains quantitative data (e.g., sales), while the dimension tables hold descriptive attributes related to the facts (e.g. Employee, Date, Territory).

Use Cases: Star schemas are ideal for straightforward reporting and querying. They are efficient for read-heavy operations, making them suitable for dashboards and summary reports.

The star schema does not necessarily have to be in the shape of a star. Below is also a star schema and is a preferred arrangement for many experts as in this arrangement is easier to visualize filter propagation from dimension to fact tables.

2. SNOWFLAKE SCHEMA

Definition: The snowflake schema is a normalized version of the star schema. In this design, dimension tables are further divided into related tables, resulting in a more complex structure.

Structure: The normalization process eliminates redundancy by splitting dimension tables into multiple related tables. This results in a web-like structure, resembling a snowflake.

Use Cases: Snowflake schemas are used in scenarios requiring detailed data models and efficient storage. They are beneficial when dealing with large datasets where data redundancy needs to be minimized.

3. GALAXIES SCHEMA(OR FACT CONSTELLATION SCHEMA)

Definition: The galaxies schema, also known as the fact constellation schema, involves multiple fact tables that share dimension tables, creating a complex, interconnected data model.

Structure: This schema consists of multiple fact tables linked to shared dimension tables, enabling the analysis of different business processes within a single model.

Use Cases: Galaxies schemas are suitable for large-scale enterprise environments where multiple related business processes need to be analyzed. They support complex queries and detailed reporting across various domains.

How Schemas Impact Power BI Performance

a. Data Storage Efficiency

b. Query Performance

c. Data Refresh Performance

Implementing Schemas in Power BI

a. Creating a Star Schema
Set Up Fact and Dimension Tables: Identify and create the central fact table and surrounding dimension tables.
Link Tables: Establish relationships between the fact table and dimension tables using foreign keys.
Optimize for Performance: Index key columns and use efficient data types to enhance query performance.

b. Implementing a Snowflake Schema
Normalize Dimension Tables: Split dimension tables into related sub-tables to reduce redundancy.
Create Relationships: Define relationships between sub-tables and the main dimension tables, ensuring referential integrity.
Optimize Storage: Use appropriate storage and indexing strategies to manage complex joins efficiently.

c. Setting Up a Galaxies Schema
Identify Fact Tables: Determine the various fact tables needed for different business processes.
Share Dimension Tables: Create shared dimension tables to link multiple fact tables.
Ensure Efficient Querying: Design the schema to support complex queries and optimize performance through indexing and data partitioning.

Microsoft Excel in Data Analytics

Benta Okoth — Sat, 24 Jan 2026 07:29:00 +0000

Microsoft Excel is a spreadsheet program from Microsoft that lets you organize, calculate, analyze, and visualize data using rows, columns, formulas, and charts.
It’s widely used for everything from simple lists to complex financial mode

Key Features and Functions

Data Organization: Users can enter data into cells, which are organized by columns (letters) and rows (numbers).
Calculations & Formulas: It uses formulas and functions (e.g., SUM, VLOOKUP) to perform complex calculations and data manipulation.
Data Visualization: Excel includes tools to create charts, graphs, and pivot tables to visualize data trends and insights.
Automation: Visual Basic for Applications (VBA) allows users to create macros to automate repetitive tasks.
Data Analysis: Features like PivotTables, sparklines, and conditional formatting help turn raw data into actionable information

Excel is Commonly used for :

Financial Modeling: Creating budgets, forecasts, and financial reports.
Data Management: Sorting, filtering, and organizing large datasets.
Reporting & Analytics: Analyzing data to drive business decision-making.

The Excel Structure

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

At the very top of the Excel window is the Ribbon. The Ribbon is a toolbar that organizes commands into tabs such as Home, Insert, Page Layout, Formulas, Data, Review, and View.
Each tab contains groups of related operations — for example, the Home tab includes formatting tools, while the Insert tab provides options for charts, tables, and illustrations.
The Ribbon makes it easy to find and use Excel’s features without having to memorize complex commands

Excel Data Types

There are varies data types used in excel:

Text (String)
Number
Date
Time
Boolean (Logical)
Percentage
Currency

Common Mathematical and Logical Operations

These are used to calculate values, totals, averages, and numeric comparisons.

a) Basic Arithmetic Operations
Excel uses standard math symbols.

Operation Symbol Example
Addition + =A1+B1
Subtraction - =A1-B1
Multiplication * =A1*B1
Division / =A1/B1

b) Common Mathematical Functions
These are built-in Excel functions.

Function Purpose
SUM() Adds multiple values
AVERAGE() Finds the mean
MAX() Highest value
MIN() Lowest value
ROUND() Rounds a number
ROUNDUP() Rounds up
ROUNDDOWN() Rounds down
ABS() Absolute value
COUNT() Counts numbers
COUNTA() Counts non-empty cells

c) Logical Operations in Excel
i. Comparison Operators
Used to compare values.
Operator Meaning
= Equal to
<> Not equal

      Greater than
< Less than
= Greater than or equal
<= Less than or equal

ii. Logical Functions

Used for conditional logic. Function Purpose IF() Conditional result AND() All conditions must be TRUE OR() At least one condition TRUE NOT() Reverses logical result

Error-Handling Logical Functions

Used to avoid Excel errors. Function and the Purpose IFERROR() Handles errors gracefully ISBLANK() Checks empty cells ISNUMBER() Checks numeric values ISTEXT() Checks text ISERROR() Detects errors

Common Errors:

Error Meaning

#DIV/0! Division by zero
#N/A Value not found
#VALUE! Wrong data type
#REF! Invalid cell reference

CORE CAPABILITIES

Data Preparation
Importing data from CSV, databases, and external sources.
Cleaning datasets (removing duplicates, handling missing values).
Validating and standardizing formats.

Data Analysis
Functions like SUMIFS, VLOOKUP, INDEX-MATCH for calculations and lookups.
Pivot Tables for summarizing and aggregating large datasets.
What-If Analysis tools (Goal Seek, Scenario Manager) for forecasting.

Data Visualization
Charts and graphs (bar, line, scatter, pie, combo).
Conditional formatting to highlight trends and anomalies.
Interactive dashboards using slicers and pivot charts.

Decision Support
Turning raw numbers into actionable insights.
Tracking KPIs, forecasting sales, and evaluating performance.
Supporting finance, marketing, operations, and research

Main Types of Lookups in Excel

Vertical Lookup (VLOOKUP)
Looks down a column.
When to use
Data arranged vertically
Lookup value is in the first column
Horizontal Lookup (HLOOKUP)

Looks across a row.

Exact Match vs Approximate Match

Exact Match
Finds an exact value.

1. DATA PREPARATION

Data preparation is the process of cleaning, organizing, and transforming raw data into a usable format.
It ensures accuracy, consistency, and reliability, making analysis faster and insights more meaningful.

Data Cleaning

This can be achieved by creating a staging table of data you are working on then on a separate sheet and name it staging.
(i). Remove duplicates

You start by Freezing the column headers - Go to the View Tab on the Ribbon then in the Window Group click Freeze Pane then Freeze Top Row
Select the column with unique identifier for example CustomerID, OrderID. Then Go to Data tab in the ribbon then in the Data Tools group, click Remove Duplicates, a dialog box will appear-choose the columns you want Excel to check for duplicates then click on Remove Duplicate then click on OK as below.

Work on column Data Types

Click on the column header that contains date then right click and choose format cell and under category select date and choose the date type of your preference as below

Select the column with numeric values then right click and choose format cells and under category select number and choose the decimal places of your preference and ensure the date place is uniform for a given column, do the same for the columns containing text.
For columns that are expressed as a percentage you can format them to percentage and choose the preferred number of decimal places.
You can add Data Validation as well to certain columns. Select the column then Data>> Data Validation dropdown >>Data Validation >> Under settings Allow choose the preferred eg List and then under source list the entries you want Then click OK.( a dropdown appears at that column)

Handling of Missing Values
You start by filtering the column to check if missing values do exist then proceed to insert a column beside it .

If say a column like FName has missing data _Apply a filter to the city column to know the columns with unfilled cities then proceed to replace we say words likes unknown before you start your analysis

Perform Calculations
Performing calculations is one of Excel’s core functions. It allows users to quickly compute sums, averages, percentages, and more without manual work.

How It Works

Every calculation in Excel starts with a formula, which begins with =.

You can use cell references instead of typing numbers, which means formulas automatically update when data changes.

Examples

Adding Numbers

=A1 + B1

Adds values in cells A1 and B1.

Summing a Range

=SUM(A1:A10)

Adds all numbers in cells A1 through A10.

Calculating Percentages

=A1/B1

Converts a number into a fraction of another, which can be formatted as a percentage.
Screenshot Suggestion: Show a small table with sample numbers and a formula in the formula bar, highlighting the result.

Analyze Trends
Excel allows users to identify patterns and trends in data over time. This is useful for spotting growth, declines, or seasonal patterns in sales, performance, or other metrics.

How It Works

Use functions like AVERAGE(), MAX(), and MIN() to summarize data.

Use conditional formatting to highlight trends, such as increases or decreases.

Use formulas like =A2-A1 to calculate changes over time.

Examples

Monthly Sales Change

=B2-B1

Calculates the difference in sales between months.

Highlight Growth

Select your data → Home → Conditional Formatting → Color Scales

Excel colors cells based on value, making trends visible at a glance.

Average Performance

=AVERAGE(B2:B13)

Calculates the average sales over a year.

Create Visual Reports

Excel allows you to turn raw data into visual reports that are easier to interpret and present.

How It Works

Use charts and graphs to show relationships between data points.

Use PivotTables to summarize large datasets.

Add interactive elements like slicers to filter data dynamically.

Examples

Bar Chart for Sales Comparison

Select data → Insert → Bar Chart

Excel generates a visual comparing monthly sales or product performance.

Line Chart to Show Trends

Select data → Insert → Line Chart

Displays growth or decline over time clearly.

PivotTable Summary

Select data → Insert → PivotTable

Drag fields into Rows, Columns, and Values to summarize sales by region, product, or customer.

Git Essentials for Beginners

Benta Okoth — Sat, 17 Jan 2026 18:41:00 +0000

Overview

This guide introduces beginners to the core concepts of Git, the leading version control system.
You’ll learn what version control is, why it matters, and how to track file changes.
Step‑by‑step, we’ll cover staging and committing work to build a clear project history.
You’ll also see how to push code to GitHub and pull updates to stay in sync.
By the end, you’ll master the everyday Git workflow: track → commit → push → pull.

Git Bash vs GitHub

Git Bash - A command-line tool on your computer used to run Git commands (clone, commit, push). It works locally, basically where you type commands.
GitHub - An online platform (website) used to store, share, and collaborate on Git repositories in the cloud. Basically where your code is stored and shared online

Importance of Git

Version control – Tracks changes and allows rollback to earlier versions
Cloud backup – Code is safely stored online
Collaboration – Multiple people can work on the same project
Portfolio building – Showcases skills to employers
Code review – Improves quality through pull requests and comments
Industry standard – Widely used across tech and IT roles

Git Bash Installation

Go to the Git website: https://git-scm.com/install/windows and install the git bash
Go to the search bar and search for git bash, to check if the installation is complete.
Click on the git bash icon and the git command line will be opened.

Connecting Git Bash to a GitHub Account

1.Checking the installed Git Bash version using a command

git –version

2.Configure identity on git bash

we config name and email as below
git config --global user.name "name"
git config --global user.email xxxx@gmail.com

3.Confirmation of the name and email by check the list
git config --global –list

4.Using SSH Key and How to Generate an SSH Key

- >> ssh-keygen -t ed25519 -C "email@gmail.com"
- >> Enter file in which to save the key (/c/Users/Lenovo/.ssh/id_ed25519):
-(dont enter anything just press enter and then enter again, ssh key will be populated)
We need an agent (the key holder) - this is what holds the key generated we use the command is

eval "$(ssh-agent -s)"

eval (builtin command and its role is check and helps to push a text command ie in python it’s the string)
ssh-agent = a helper program that holds your keys in memory
-s = output commands in a format the shell can use
$(...) = “run this command first, and insert its output here”
eval = “execute the text that comes out”

5.Add your SSH private key (id_ed25519) into the SSH authentication agent (ssh-agent)

This helps Git (or any other tool using SSH) to use it automatically without asking for your passphrase every time. The command is below :

ssh-add ~/.ssh/id_ed25519

These tells us the identity has been added as below Identity added: /c/Users/Lenovo/.ssh/id_ed25519 (email@gmail.com)"

6.When need to see our public key then use it to connect to github

We need to print it using the command (when using the ssh) cat ~/.ssh/id_ed25519.pub

7.Add the Key to GitHub and Test the Connection

Go to GitHub on your browser > Settings > SSH and GPG Keys and then name and then copy the key plus the email from git bash and add it.

ssh -T git@github.com

GITBASH AND GITHUB WORKFLOW

Create and Move to a Project Folder

You start by making a directory and get to the directory using the commands below

mkdir my-first-data-project

cd my-first-data-project

2.Git Tracking/Initialize the Folder using the command

git init
(this helps turn your folder into git repository)

3.Create a New file in Folder created earlier on using the command
touch Week1.html - creates an empty file

echo "print python" > Week1.txt - This creates a file called Week1.txt with the text print python inside.

nano readme.md or >> vim script.js - This opens the file in a terminal-based editor. Save and exit when done.

4.Confirm the file exists using the command
ls

5.File Tracking, Modifications and Changes Saving
We use the command

git add - to help save the changes (moves the changes to the staging area) done

git commit - to help actually creates the snapshot of your project history. Takes everything you staged with git add and saves it permanently in Git’s timeline, along with the message you write. Commit message are usually the words in quote after the -m which acts as a label helps to be able to identify what changed and at what time.

git add > select changes to include
git commit > save those changes into history

6.Checking History of Snapshot
We use the command
git log
Shows all commits in detail (author, date, commit message, commit ID) as below.
Press q to quit the log view.

7.Publish your work to the repository / Upload your code to GitHub

Go to the github and create the repo with the exact name of the one created on gitbash ie my-first-data-project

Then ensuring we are in the folder on the git bash side
Connect your local project to the github using the command

git remote add origin https://github.com/your-username/my-first-python-project.git

Then Push your work using
git branch -M main
git push -u origin main

Then going forward you will use
git push

8.Get Changes from Others/Another Computer (Pull)
git pull