DEV Community: tonny otieno

Connecting power BI to a SQL database

tonny otieno — Fri, 20 Mar 2026 14:26:26 +0000

POWER BI

Power BI stands for power business intelligence which is a collection of software services, tools and connectors. Power BI is a business analytics platform that helps transform data into actionable insights.

Uses of Power BI in data analytics and business intelligence

Data visualization and reporting

Power bi is used for communicating findings visually to stakeholders through reports and dashboards representing data.
Data integration

Power Bi helps in merging different sector datasets and coming to a conclusion. E.g. product data and market data.
Collaboration

Power Bi facilitates collaboration by providing a centralized platform for teams to build, share, and work on data insights securely. This is particularly enabled through role-based access control.
Data transformation

Power bi's query editor enables users to clean, shape and combine data from various sources such as excel, PostgreSQL and web.

Companies connects Power Bi to databases like SQL, PostgreSQL which can take real time tracking of Key Performance Indicators as the data changes. Artificial Intelligence and machine learning is also embedded to make business predictions based on data store in the connected databases. Databases such relational or not relational, allow large volume of data compared to excel sheet. Through functions such as triggers and events it's easy to update database for correct storage of data.

Connecting Power BI to a local PostgreSQL Database.

1 open the power BI desktop and click on get data on the tool bar.

2 click on more option and select databases.

3 select PostgreSQL database then click connect.

4 Enter your PostgreSQL details, select import then click OK.

5 Enter your credentials and click connect.

7 You have successfully connect to your local PostgreSQL database, choose your table of interest and load them for use.

The tables shown are loaded and Power Bi automatically finds foreign key to then and joins then creating schema through the relationships.

Connecting Power BI to cloud database e.g. aiven PostgreSQL

Just like the above steps ,
1 open the power BI desktop.
2 click on get data on the tool bar.
3 click on more option and select databases.
4 select PostgreSQL database then click connect.
5 Go to aiven and download the CA certificate.

The SSL certificate from aiven, is a trusted and encrypted for security purposes. Ensuring that only the computer having the certificate can connect to the cloud database.

Why do you need SQL skills for power BI analyst?

As we have seen above, power bi can be connected to different databases be it locally or in a cloud. It is essential for an analyst to have knowledge of SQL to apply them in data manipulation. Database performance by indexing and query optimization is important to achieve normalization in a vast fast changing environment.

SQl has variety of built-in functions that can be uses to retrieve, filter data and perform aggregation. We have conditional evaluation using WHERE to return dataset satisfying the given condition e.g.

SELECT first_name WHERE first_name like '%T'

SQL's window function offers an easy way in ranking rows, partitioning ranking and even grouping different entities. One of the powerful functions are GROUP BY and HAVING. Some of the common aggregate functions include count, sum, max, min etc

SQL JOINS AND WINDOW FUNCTIONS

tonny otieno — Mon, 02 Mar 2026 12:19:25 +0000

SQL Joins and Window Functions

SQL joins allows you to collate/add two or more tables together using a common identifier e.g. a foreign key. Due to normalization, one may not get all the required columns in a single table in order to reduce data redundancies.
The common identifier acts as a transportation between the two tables allowing columns from both tables be included in the query result set.

Sample Tables Used for Illustration

Student Table

student_id	name	course_id
1	Willis Kip	001
2	Edwin Sifuna	002
3	Tonny Oti	NULL

Course Table

course_id	course_name
001	Botany
002	Political_sci
004	English

Types of SQL Joins

1️⃣ INNER JOIN

Returns only the rows that exist in both tables.

SELECT name, course_name
FROM Student
INNER JOIN Course
ON Student.course_id = Course.course_id;

Output

Name	Course_name
Willis Kip	Botany
Edwin Sifuna	Political_sci

2️⃣ LEFT JOIN

Returns all rows from the left table and matching rows from the right table.

SELECT name, course_name
FROM Student
LEFT JOIN Course
ON Student.course_id = Course.course_id;

Output

Name	Course_name
Willis Kip	Botany
Edwin Sifuna	Political_sci
Tonny Oti	NULL

3️⃣ RIGHT JOIN

Returns all rows from the right table and matching rows from the left table.

SELECT name, course_name
FROM Student
RIGHT JOIN Course
ON Student.course_id = Course.course_id;

Output

Name	Course_name
Willis Kip	Botany
Edwin Sifuna	Political_sci
NULL	English

4️⃣ FULL JOIN

Returns all rows from both tables, matching where possible.

SELECT name, course_name
FROM Student
FULL JOIN Course
ON Student.course_id = Course.course_id;

Output

Name	Course_name
Willis Kip	Botany
Edwin Sifuna	Political_sci
Tonny Oti	NULL
NULL	English

Window Functions

A window function is a type of function that performs a calculation across a specific set of rows (the ‘window’ in question), defined by an OVER () clause. Window functions use values from one or multiple rows to return a value for each row, which makes them different from traditional aggregate functions, which return a single value for multiple rows

Syntax

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

Explanation

SELECT → clause defines the columns you want to select from the table_name table.
function() → The window function you want to apply.
OVER() → Defines the window.
PARTITION BY → divides rows into partitions based on the specified partition_expression; if the partition_expression is not specified, the result set will be treated as a single partition..
ORDER BY → Defines row order within each partition.
output_column_name → Name of the calculated column.

Example

SELECT dem.first_name,
       dem.last_name,
       gender,
       AVG(salary) OVER (PARTITION BY gender) AS avg_salary_by_gender
FROM employee_demographics dem
JOIN employee_salary sal
    ON dem.employee_id = sal.employee_id;

This calculates the average salary for each gender while still displaying individual employee records.

Commonly Used Window Functions

ROW_NUMBER()
RANK()
DENSE_RANK()
LAG(expression, offset)
FIRST_VALUE()
LAST_VALUE()
Aggregate functions used as window functions:
- SUM()
- AVG()
- MAX()
- MIN()
- COUNT()

CONCLUSION

Use JOINs when combining data from multiple tables.
Use Window Functions when you need row-level results alongside aggregated calculations.

# How Analysts Translate Messy Data, DAX, and Dashboards into Action Using Power BI

tonny otieno — Mon, 09 Feb 2026 13:17:46 +0000

Businesses interact with data periodically as part of their day-to-day operations. Raw data is often messy and needs polishing to extract value and ultimately create insights on the actions needed to be performed for improvement.

One powerful tool is Power Query in PowerBI helps a great deal in transforming data for decision making.

As a data analyst, working with raw data can be broadly classified into three stages:

Data Cleaning
Data Analysis
Data Visualization

Data Cleaning

Here you have to state the problem statement that is specific, measurable and achievable. The problem statement will be answered in the different KPI’s to be shown in the dashboard. Thereafter, perform a preliminary analysis on the data like:

-Asigning consistent correct data types.
-Removing duplicates.
-Fixing misspellings/typos.
-Checking for biases.

Data Analysis

This is the core stage of the process and must be executed carefully.
After understanding the problem statement, adjust and format the data using DAX (Data Analysis and Expression). DAX is a very critical part in creating measures or columns representing calculation like aggregates, filters, time and logic.

The choice of data modelling is also vital for performance and accurate reporting. It is advisable to adopt star schema as it is organized and allows easy creation of large data volumes.

Data Modeling

Best Practices for Creating Models

Always connect dimension tables to fact tables, not the other way around.
Use one-to-one relationships only where necessary.
Keep filter direction single unless there is a strong reason otherwise.
Avoid unnecessary inactive relationships.
Always validate relationships by testing visuals and slicers.

Once the data achieves high integrity, joins can be introduced to combine or filter data from multiple tables based on relationships (matching columns).

Essentially, joins enrich data and checks quality. Based on the required outcome one can either choose inner join, left join, right join, left-anti join, right-anti join etc.

Data Visualization

After completing the first two stages, a dashboard is created to visualize key KPIs. A dashboard is a well-organized visual representation of data designed for presentation and sharing.

Data visualization is the graphical representation of information and data. By using visual elements like charts, graphs, tables and maps.
Data visualization tools provide an accessible way to see and understand trends, outliers, and patterns in data. Here is where non-technical people understand the KPI’s e.g. trend over time, relationship between different factors. This is possible by filtering through slicers on a given metric.

Once visual data is analyzed, informed decisions can be made to improve business performance and focus on specific areas—backed by data rather than guesswork.

Simply no flying blind in decision-making.

Without taking action, results are bound to fail. This is often the tipping point that differentiates good organizations from great ones.

Principles of a Good Dashboard

A well-designed dashboard should emphasize:

Simplicity
Clarity
Contextualization

It should be clean, well-organized, and not overdone.

POWER BI- Schema and Data Modelling.

tonny otieno — Mon, 02 Feb 2026 14:10:56 +0000

Schema and Data Modelling in Power BI

We will explore schema and data modeling in Power BI, and why good modeling is critical for performance and accurate reporting. Key terms covered include Star Schema, Snowflake Schema, Relationship Table, Fact Table, and Dimension Table.

What is Data Modelling?

Data modelling is the process of creating structured visual representation of a given data to support analysis and eventually make informed decisions.Its simply focuses on how your data should make sense.

Star Schema

A star schema is a multi-dimensional data model used to design data warehouse and relational database so that it is easy to understand and analyze.It mainly denormalizes data into dimentions and fact. Fact being the centre of star schema with foreign keys to other tables joining it.

Benefits of Star Schema

Usability: Clean, organized data makes reports intuitive
Query Performance: Fewer joins enable faster queries on large datasets vs. normalized models
Simple Queries: Minimal join dependency compared to snowflake schemas

Snowflake Schema

A snowflake schema extends the star schema by normalizing dimension tables into sub-tables, creating branching structures. It's like multiple star schemas interconnected.

Benefits of Snowflake Schema

Fast data retrieval
Enforces data quality
Common data warehousing model
Storage efficient

Fact Table

A Fact Table is the central table in a data warehouse schema e.g. in star and snowflake schemas. It stores quantitative data (measures/metrics) or facts about a business process and links to dimension tables through foreign keys. Example of data stored in a fact table is unit sold, amount etc.

Dimension Table

Dimentional table is almost same as fact table, only that it stores descriptive data like time, product etc. They use a unique surrogate key (often an integer) that acts as a primary key, which is referenced by a corresponding foreign key in the fact table.

Conclusion

Good Power BI data modeling optimizes memory usage, speeds up DAX query processing, reduces memory footprint, and ensures accurate aggregations. Poor models cause slow performance and scalability issues as datasets grow.

Introduction to MS Excel for Data Analytics

tonny otieno — Sat, 24 Jan 2026 21:20:51 +0000

Computing has advanced over time and is now essential across industries to help organizations make better decisions and boost profits through data analysis. A simple tool like Microsoft Excel serves as an excellent starting point for analyzing various datasets, including medical records, sales data, student performance, and social media metrics.

Data Analysis Basics

Data analysis involves examining, cleaning, organizing, and interpreting data to uncover useful insights, identify patterns, and support decision-making.

Why Clean Your Data?

Ensures high integrity by removing biases.
Enables correct use of formulas.
Aligns with the problem statement.
Maximizes use of complete sample size.

Types of Data Analysis

Prescriptive - Uses historical data to recommend actions.
Predictive - Analyzes past patterns to forecast the future.
Diagnostic - Explains why something is happening.

Common Excel Terms

Workbook - The entire Excel file containing multiple sheets.
Cell - The smallest "box" where data is entered (e.g., A1).
Column - Vertical groups of cells, labeled A, B, C... at the top.
Row - Horizontal groups of cells, labeled 1, 2, 3... on the left.

Dataset - A collection of related data organized for analysis.

Basic Excel Functions

Formulas must start with =. They can include numbers, cell references (A1, B2), operators (+, -, *, /), and functions.

MAX & MIN - Find maximum/minimum values in a range.
LEN - Returns the length of text.
TRIM - Removes leading/trailing spaces.
CONCATENATE (or CONCAT) - Joins text strings.
AVERAGE - Calculates the mean of a range.

Logical Functions

Logical functions test conditions and return results based on TRUE/FALSE.

1. IF & IFS

IF: =IF(logical_test, value_if_true, value_if_false)
IFS: Multiple conditions =IFS(condition1, result1, condition2, result2, ...)

Examples:

=IF(G2>30,"OLD","YOUNG")
=IFS(G2>50,"OLD", G2>40,"ABOVE-MEDIUM", G2>30,"MEDIUM","YOUNG")

2. AND & OR

AND: All conditions must be true.
OR: Any condition can be true.

Examples:

=IF(AND(G2>40,E2>70000),"H--R","OTHERS")
=IF(OR(G2>40,E2>70000),"H--R","OTHERS")

3. NOT

Negates a condition: =NOT(logical_test) or use <>.

Other operators: <, >, =, <>, <=, >=.

Data Filtering & Sorting

Sorting: Arranges data (A-Z, newest-oldest, etc.).
Filtering: Shows data meeting specific conditions.

Filter Function Example:

=FILTER(A2:F10, D2:D10="HR")

Pivot Tables

Pivot tables summarize, sort, count, and reorganize data. They convert rows to columns and perform calculations (Max, Min, Average, etc.).

Data Visualization

Data visualization uses charts (bar, line, pie, scatter plots) to reveal patterns and relationships.

Benefits:

Quick pattern identification
Better storytelling with data
Enhanced decision-making

Excel Dashboards

An Excel Dashboard consolidates key visuals (PivotTables, charts, slicers) into one interactive screen for at-a-glance insights.

Key Components:

PivotTables for dynamic summaries
Charts for visual impact
Slicers for interactivity
Clean layout design

Getting started with GitHub

tonny otieno — Sat, 17 Jan 2026 19:57:16 +0000

GitHub is a version control tool that provides software as a service used to store and track changes for an individual or a team collaborating on a project. There are also other tools for the same purpose such as Gitlab,Bitbucket that also incorporates GUI.

Setting up Git locally

Below is how to configure your git to connect to your remote account.
For linux/macOS users input the commands to your terminal while windows users after installing gitbash, will use it to write the commands. Verification is done by git config --global --list

git config --global user.name "Name" git config --global user.email "email-address"

Navigate to your local project directory and initialize git using git init .

Create a README.md file using touch README.mdor other files e.g a python file called users.py. The README.md file is used to tell what the project is all about and how one can use it.

Pushing Code

After working on your project, you have to add the changes to the staging area using git add . (for everything).
Commit the changes having a short message of what is committed e.g git commit -m "signup function"
Push your code to the remote account using git push -u origin main.

NOTE Without commiting, the changes won't be effected to the remote repository and will only remain in the local repository.First push may prompt for authentication (set up SSH keys or a Personal Access Token for HTTPS).

Pulling updated Code from GitHub

It is best practice to check the status of your code using git status to ensure no uncommitted changes exist. Then do a pull using git pull which is a combination of git fetch (downloads the latest commits) and git merge(merges the downloaded commits)