DEV Community: Ajani luke Kariuki

# Connecting Power BI to SQL Databases: A Complete Guide

Ajani luke Kariuki — Wed, 22 Apr 2026 14:00:11 +0000

Microsoft Power BI is one of the most widely used business intelligence tools available today. Organizations rely on it to analyze data, monitor performance, and build interactive dashboards that turn raw numbers into actionable insights. Rather than working with static spreadsheets, analysts can connect Power BI to live data sources — ensuring reports stay accurate and up to date in real time.

SQL databases are a natural partner for Power BI. They store structured data in well-defined tables, support powerful querying operations like filtering, sorting, and aggregation, and underpin most modern data systems. Together, SQL databases handle the data storage and Power BI handles the storytelling.

This guide walks you through connecting Power BI to both a local PostgreSQL database and a cloud-hosted database on Aiven.

Part 1: Connecting Power BI to a Local PostgreSQL Database

Step 1: Open Power BI Desktop

Launch Power BI Desktop on your machine.

Step 2: Get Data

Navigate to the Home tab
Click Get Data
Select PostgreSQL Database from the list

Step 3: Enter Connection Details

In the connection dialog, fill in the following:

Server: localhost
Database: your database name

Step 4: Authenticate

Select Database Authentication
Enter your username and password
Click Connect

Step 5: Load Your Tables

Once connected, you'll see a navigator pane listing your available tables. Select the ones you need — for example:

customers
products
sales
inventory

Click Load to bring them into Power BI.

Part 2: Connecting Power BI to a Cloud Database (Aiven PostgreSQL)

Cloud-hosted databases like those on Aiven follow a similar process, but with a few extra steps to handle remote access and secure connections.

Step 1: Install the PostgreSQL ODBC Driver

Before anything else, make sure the PostgreSQL ODBC driver is installed on your machine. Power BI needs this driver to communicate with PostgreSQL.

Download it from the official PostgreSQL FTP server:
https://www.postgresql.org/ftp/odbc/versions/

Step 2: Open Power BI and Get Data

Launch Power BI Desktop
Go to the Home tab
Click Get Data

Step 3: Select PostgreSQL Database

Choose Database → PostgreSQL Database
Click Connect

Step 4: Enter Your Connection Details

Server: hostname:port (from your Aiven console)
Database: your database name

Step 5: Authenticate

Select Database Authentication
Enter your username and password
Click Connect

Step 6: Install the SSL Certificate

For cloud databases, an SSL certificate is required to establish a secure connection. Download the CA certificate from your Aiven project dashboard and configure it in your connection settings.

Why SSL matters:

Encrypts all data in transit
Protects your login credentials
Blocks unauthorized access attempts

Step 7: Load Your Data

Once connected, select your tables from the navigator pane (e.g., customers, products, sales, inventory) and click Load.

Part 3: Data Modeling — Creating Relationships

With your tables loaded, head to Model View in Power BI to define relationships between them. This is what allows Power BI to filter and aggregate data correctly across tables.

Set up the following relationships:

From Table	To Table	Join Key
`customers`	`sales`	`CustomerID`
`products`	`sales`	`ProductID`
`products`	`inventory`	`ProductID`

Well-defined relationships ensure that slicers, filters, and visuals all work in sync — without them, your reports can produce misleading or incomplete results.

Part 4: Building Your Dashboard

Once your data model is in place, you can start building visuals. Here are some recommended charts by category:

Sales Performance

Line chart — Sales trends over time
KPI card — Total revenue
Bar chart — Sales broken down by region

Product Performance

Bar chart — Top-selling products
Pie chart — Revenue share by product category

Customer Insights

Table — Top customers ranked by revenue
Map visual — Geographic distribution of customers

Inventory Insights

Column chart — Current stock levels per product
KPI card — Low inventory alerts

Why SQL Skills Are Essential for Power BI Analysts

While Power BI has a point-and-click interface for building visuals, a solid understanding of SQL makes you significantly more effective. With SQL, you can:

Retrieve data efficiently — Pull exactly what you need, nothing more
Filter datasets — Apply conditions before data even enters Power BI
Aggregate data — Use functions like SUM, COUNT, and AVG at the query level
Join multiple tables — Combine data from different sources into a single clean dataset

Writing optimized SQL queries upstream means cleaner data models, faster dashboards, and more reliable analysis. SQL and Power BI aren't competing skills — they're complementary ones.

# ETI and ELT

Ajani luke Kariuki — Wed, 22 Apr 2026 13:56:10 +0000

ETL vs ELT: Which One Should You Use and Why?

If you're exploring a career in data engineering or analytics, you've almost certainly stumbled across these two acronyms: ETL and ELT. At first glance they look interchangeable, but they represent fundamentally different philosophies for moving and processing data. Let's break both down clearly.

What Is ETL?

ETL stands for Extract, Transform, Load — and it does exactly what it says on the tin, in that exact order. It's the traditional backbone of data integration: pull raw data from various sources, clean and reshape it, then load the polished result into a destination system.

1. Extract

This is the data collection phase. Engineers pull raw, often messy data from a wide range of sources, including:

Relational databases
APIs (Application Programming Interfaces)
Web content
Economic and financial feeds
Real estate and weather data
Surveys and interviews

The list isn't exhaustive — any system that produces data can be a source.

2. Transform

This is where the real work happens. Raw data is rarely ready for analysis straight out of the source, so transformation cleans, restructures, and enriches it. Common transformation tasks include:

Data Cleaning — Removing errors, duplicates, and null values
Structural Conversion — Adding or removing columns, standardizing formats, and imposing structure on unstructured data
Destructive Transformation — Dropping irrelevant or outdated fields that would otherwise clutter the dataset
Feature Engineering (Attribution) — Deriving new fields from existing ones, such as calculating age from a date of birth, or combining revenue and cost into a single profit/loss column

Popular tools for this phase include:

Microsoft Excel & Power BI
dbt (Data Build Tool) for SQL-based transformations
Pandas (Python)
Informatica

Data cleaning is arguably the most critical step in any data pipeline — garbage in, garbage out.

3. Load

Once transformed, the data is loaded into a target system for storage, analysis, or reporting. Common destinations include:

Data warehouses
Data lakes
Staging areas
Analytics and reporting repositories

What Is ELT?

ELT flips the middle two steps: Extract, Load, Transform. Instead of transforming data before it reaches the destination, you load it in its raw form first and transform it inside the target system — typically a modern cloud data warehouse.

This approach has grown rapidly in popularity for several reasons:

Scalability — Cloud platforms like BigQuery, Snowflake, and Redshift are purpose-built to handle massive datasets and can run transformations at high speed and relatively low cost.
Agility — Since raw data is already available in the warehouse, analysts and data scientists can build and iterate on transformation models on the fly as business needs evolve, without touching the ingestion pipeline.
Simplified Pipelines — Fewer intermediate steps means fewer tools, less infrastructure to maintain, and a leaner overall workflow.
Faster Ingestion — Data reaches the target system almost immediately after extraction, which is essential for real-time or near-real-time reporting use cases.
Unstructured Data Support — ELT is particularly well-suited for unstructured formats like JSON, images, and video files, which can be stored raw and only processed when a specific analysis demands it.

ETL vs ELT: When to Use Which?

There's no universal answer, but here are some common scenarios that favour one approach over the other:

Scenario	Recommended Approach
Strict compliance or data governance requirements	ETL
Real-time or near-real-time analytics	ELT
Working with IoT sensor streams	ETL
Unstructured or semi-structured data	ELT
Experimenting with new data sources	ETL
Large-scale cloud analytics	ELT

IoT Applications

IoT use cases — think sensor networks or connected devices — tend to lean toward ETL because data often arrives in proprietary protocols that need converting to standard formats before they're useful. Deduplication, filling missing values, and filtering high-frequency noise are all easier to handle before the data hits the cloud.

Experimentation & Discovery

When data engineers are exploring new sources or testing hypotheses, ETL's multi-tool pipeline offers a granular view of data at each stage, making it easier to debug and validate assumptions.

Complex, Multi-Source Analytics

In large organisations, it's common to run both — ETL for certain source systems or legacy databases, and ELT for cloud-native workloads. The two aren't mutually exclusive.

The Bottom Line

Choosing between ETL and ELT ultimately comes down to your specific context. Key factors to weigh include:

Cost — Cloud ELT can be more economical at scale, but compute costs for in-warehouse transformations add up
Speed — ELT wins on ingestion speed; ETL can be more efficient for targeted, pre-defined transformations
Flexibility — ELT gives analysts more freedom to iterate; ETL offers tighter control
Security — ETL keeps sensitive data out of the warehouse until it's been cleaned; ELT stores raw data that may include sensitive fields
Team skills — The right tool is also the one your team knows how to use well

Neither ETL nor ELT is inherently superior. Used correctly, both are powerful — and in practice, many modern data stacks use a combination of the two.

Connecting powerbi to postgresql

Ajani luke Kariuki — Wed, 18 Mar 2026 06:21:32 +0000

connecting power bi to sql databases: a summary

introduction

Connecting power bi directly to a database creates a reliable data pipeline. instead of manually importing files, data can be refreshed automatically, allowing analysts to focus more on insights rather than data handling.

connecting power bi to a local postgresql database

the process of connecting to a local database follows a few simple steps:

open power bi and click "get data"
select postgresql database from the database options
enter server details (e.g. localhost or localhost:port)
input database name
authenticate using your username and password
select tables to load or transform data using power query

this allows you to import and work with your database tables directly in power bi.

connecting to a cloud database (aiven postgresql)

cloud platforms like :contentReference[oaicite:0]{index=0} allow databases to be hosted online instead of locally.

to connect:

log in and obtain connection details
download the ssl certificate for secure communication
use power bi "get data" → postgresql database
enter the host, port, and database name
authenticate and load tables

ssl certificates ensure secure data transfer over the internet when connecting to cloud databases.

building the data model

once data is loaded:

power bi detects relationships using primary and foreign keys
relationships can also be created manually
proper modeling connects fact tables (e.g. sales) with dimension tables (e.g. customers, products)

this structure allows power bi to generate meaningful insights.

why sql skills matter for power bi analysts

sql is essential for effective data analysis in power bi:

data retrieval: fetch only relevant data using queries
full control: access exactly the data needed without relying on exports
handling complexity: perform advanced logic and cleaning more efficiently
industry standard: widely used across all data platforms
data preparation: simplify joins, transformations, and calculations before loading into power bi

conclusion

Strong data modeling and sql knowledge enhance performance, improve data handling, and enable deeper insights. sql acts as the foundation that allows analysts to efficiently interact with databases before visualization in power bi.

PostgreSQL Joins and Window Function

Ajani luke Kariuki — Mon, 02 Mar 2026 12:12:26 +0000

Understanding JOINS in PostgreSQL

Joins let you merge data from multiple tables (or views) by linking them through related columns.
The choice of join type depends mainly on:

Which rows you want to keep (including unmatched ones)
How the tables relate to each other

Main Join Types

CROSS JOIN

Creates the Cartesian product - every row from the first table pairs with every row from the second.
No ON clause needed.

Example result: 5 rows × 5 rows = 25 rows.SQLSELECT p.project_name, e.name, e.salary
FROM sales_data.projects p
CROSS JOIN sales_data.employees e;

INNER JOIN

Returns only matching rows from both tables. Non-matching rows are excluded
SELECT emp.name, dep.department_name
FROM sales_data.employees emp
INNER JOIN sales_data.departments dep
ON emp.department_id = dep.department_id;

LEFT JOIN (LEFT OUTER JOIN)

Keeps all rows from the left table, plus matching rows from the right.

SELECT *FROM sales_data.projects p
LEFT JOIN sales_data.employees e
ON p.employee_id = e.employee_id;

RIGHT JOIN (RIGHT OUTER JOIN)

Keeps all rows from the right table, plus matching rows from the left.
SELECT *FROM sales_data.projects p
RIGHT JOIN sales_data.employees e
ON p.employee_id = e.employee_id;

FULL JOIN (FULL OUTER JOIN)

Returns all rows from both tables. Places NULL where no match exists.SELECT *
FROM sales_data.projects p
FULL JOIN sales_data.employees e
ON p.employee_id = e.employee_id;

Enhancing Joins

Add filtering, sorting, etc.:
SELECT *
FROM sales_data.projects p
FULL JOIN sales_data.employees e ON p.employee_id = e.employee_id
WHERE e.employee_id < 4
ORDER BY e.employee_id ASC NULLS LAST,
p.project_name ASC;

Window Functions in PostgreSQL

Window functions compute values over a "window" of rows related to the current row — without collapsing rows like GROUP BY does.

Ranking Functions

ROW_NUMBER() — assigns unique, consecutive numbers (1,2,3,… no ties)
RANK() — same values get the same rank, but skips numbers after ties (1,1,3,…)
DENSE_RANK() — same values get the same rank, no skips (1,1,2,…)
NTILE(n) — divides rows into n roughly equal buckets (good for quartiles, percentiles)

SQLSELECT *,
RANK() OVER (ORDER BY salary DESC NULLS FIRST) AS rank_col,
DENSE_RANK() OVER (ORDER BY salary DESC NULLS FIRST) AS dense_rank_col,
ROW_NUMBER() OVER (ORDER BY salary DESC NULLS FIRST) AS row_num
FROM sales_data.working_hub;

Aggregate Functions in Windows
Run aggregates (SUM, AVG, COUNT, MIN, MAX…) across the window without grouping.
SQLSELECT *,
SUM(e.salary) OVER () AS total_salary_company_wide
FROM sales_data.projects p
FULL JOIN sales_data.employees e ON p.employee_id = e.employee_id;
Navigation / Value Functions
Compare rows within the ordered window:

LAG(col) — value from previous row
LEAD(col) — value from next row
FIRST_VALUE(col) — first value in window
LAST_VALUE(col) — last value in window

SQLSELECT *,
LAG(salary) OVER (ORDER BY salary DESC) AS prev_salary,
LEAD(salary) OVER (ORDER BY salary DESC) AS next_salary
FROM sales_data.working_hub;

PARTITION BY Splits data into groups (like GROUP BY), but keeps all rows intact. SQLSELECT *, SUM(e.salary) OVER (PARTITION BY p.project_name) AS salary_per_project FROM sales_data.projects p FULL JOIN sales_data.employees e ON p.employee_id = e.employee_id;

How Analysts Turn Messy Data into Action with Power BI

Ajani luke Kariuki — Mon, 09 Feb 2026 05:53:16 +0000

The Analyst’s Everyday Challenge

Data analysts are constantly tasked with converting chaotic, unstructured data into insights that decision-makers can actually use. The real value of business intelligence lies in this translation—moving from raw data, to clear visuals, and finally to actions that improve business outcomes. Power BI plays a key role in enabling this end-to-end journey.

The Reality of Real-World Data

In most organizations, data is far from perfect. Analysts often deal with:

Data coming from multiple, unconnected systems (CRMs, ERPs, Excel files)
Inconsistent naming conventions and formats
Missing values and duplicate entries
Uneven or irregular time-based data
Semi-structured or unstructured sources

Before analysis can begin, this data must be cleaned and aligned.

Phase 1: Data Preparation with Power Query

Power Query acts as Power BI’s ETL engine, allowing analysts to connect to external data sources and reshape them into analysis-ready datasets.

Typical workflow:
Get Data → Choose source → Connect

At this stage, analysts profile the data to understand its quality and structure.

Common Transformation Techniques

Standardization: Making dates, currencies, and categories consistent
Pivoting / Unpivoting: Reshaping data for better analysis
Fuzzy Matching: Merging datasets with imperfect keys
Custom Columns: Creating calculated fields during import
Parameters: Building flexible, refreshable data connections

Phase 2: Answering Business Questions with DAX

DAX (Data Analysis Expressions) is what turns prepared data into meaningful insights. Without DAX, reports remain descriptive; with it, they become analytical and actionable.

Key DAX Concepts

Measures: Calculations evaluated dynamically (e.g., Total Profit)
Calculated Columns: Values computed and stored at the row level

DAX shifts analysis from what happened to why it happened and what should happen next.

Why Context Matters

DAX automatically adapts calculations based on filters and selections:

Viewing a specific category recalculates metrics for that category only
Drilling into a specific month updates results accordingly

Time Intelligence

One of DAX’s strongest capabilities includes:

Year-over-year comparisons
Cumulative totals
Moving averages (e.g., 30-day sales trends)

Phase 3: Designing Dashboards People Actually Understand

This phase focuses on clarity. A good dashboard should communicate insights in under half a minute.

The Pyramid Layout

Top: Core KPIs
Middle: Trends and comparisons
Bottom: Detailed data for deeper analysis

Slicers are added to allow users to filter data easily, and every visual should answer a specific business question.

Phase 4: Turning Insights into Action

The final step is where dashboards create real impact. Power BI can trigger actions such as:

Sending alerts when KPIs cross thresholds
Creating tasks in Teams or Outlook
Updating CRM systems
Exporting data to downstream tools

Success is no longer measured by report views, but by decisions made and actions taken.

Conclusion: Analysts as Decision Enablers

The true purpose of analytics is not advanced formulas or polished visuals. Decision-makers care about outcomes, not the technical steps behind them. An analyst’s job is complete only when a stakeholder can confidently say:

“I know what action to take next.”

Schemas and Data modeling in Power bi

Ajani luke Kariuki — Mon, 02 Feb 2026 07:32:29 +0000

Star schema
Snowflake schema
Relationships
Facts
Dimention Tables

star schema vs snowflake schema

A star schema is defined as the simplest data warehouse schema where one or more fact tables reference any number of dimension tables in a star-like structure.

snowflake schemaIs a more normalized version of the star schema where dimension tables are broken down into further tables.

Fact Tables: Tall and narrow, containing measurable, quantitative data (e.g., unit price, quantity).

_*Dimension Tables: *_Short and wide, containing descriptive attributes used for filtering and grouping (e.g., product name, location).

Relationships: Power BI typically uses 1-to-many relationships, where dimension tables connect to the fact table, ensuring data integrity.

Schemas organize data into a central, narrow fact table (containing measures/keys) connected to surrounding dimension tables (describing entities) using one-to-many relationships.

Power BI: Beginner’s Guide to Data Modeling and Schemas — Summary

Overview

Modern organisations rely heavily on insights derived from raw data to guide decision-making and remain competitive. Business Intelligence (BI) tools play a crucial role in transforming this data into meaningful insights, and Power BI stands out as a powerful, visualisation-friendly solution.

Power BI enables both technical and non-technical users to understand data through intuitive visuals such as charts, graphs, and dashboards. Central to its effectiveness are data models and schemas, which structure data for efficient analysis.

What is Business Intelligence (BI)?

Business Intelligence refers to tools, techniques, and processes used to analyse organisational data and support strategic and operational decisions.

Power BI supports BI by providing:

Descriptive analytics
Interactive reports
Dashboards for decision-makers

Well-designed data models ensure that insights are accurate, timely, and cost-effective.

What is a Schema in Power BI?

A schema defines how data is structured and how tables relate to each other within a data model.

Schemas:

Improve query performance
Enhance reporting efficiency
Enable better data analysis

The two main schemas used in Power BI are:

Star Schema
Snowflake Schema

Star Schema

The Star Schema is the most common and beginner-friendly schema in Power BI. It consists of:

One central fact table
Multiple surrounding dimension tables

The structure resembles a star.

Key Features

Simplicity: Easy to understand and use
Flexibility: New dimensions or facts can be added easily
Performance: Fewer table joins result in faster queries

Dimension Tables vs Fact Tables

Dimension Tables

Dimension tables store descriptive attributes that provide context to data.

Characteristics:

Contain primary keys
Include descriptive fields (e.g., product name, category)
Used for filtering and grouping data

Example attributes:

Product Name
Category
Price

Fact Tables

Fact tables store quantitative, measurable data related to business activities.

Characteristics:

Contain foreign keys linking to dimension tables
Store numerical measures (e.g., sales amount, quantity sold)
Each row represents a transaction or event

Key Differences

Aspect	Dimension Table	Fact Table
Purpose	Provides context	Records transactions
Structure	Fewer rows, more attributes	Many rows, fewer attributes
Data Type	Descriptive	Numerical

Snowflake Schema

The Snowflake Schema is an extension of the star schema where dimension tables are further divided into sub-dimensions.

Strengths

Faster data retrieval in some cases
Improved data integrity
Better data normalization

Weaknesses

Higher initial setup cost
More complex structure
Less flexible for future changes

Importance of Good Data Models

Well-designed data models are critical for:

Accurate reporting
High-performance dashboards
Reliable KPI tracking
Effective decision-making

Without proper data modeling, organisations risk inaccurate insights and poor strategic execution.

Conclusion

Power BI leverages strong data modeling principles—particularly star and snowflake schemas—to make data analysis accessible, efficient, and impactful. Creating good data models is essential for building reliable dashboards and enabling organisations to make informed, data-driven decisions.

Ms.Excel for beginners

Ajani luke Kariuki — Sun, 25 Jan 2026 16:51:41 +0000

Microsoft Excel is a spreadsheet program made by Microsoft that helps you store, organize, calculate, and analyze data in a clear, structured way.

Uses of Excel

Data entry & organization

You enter information in rows and columns

Calculations

Excel can automatically do math using formulas and functions (e.g. add totals, calculate averages, percentages).

Data analysis

You can sort, filter, and summarize large amounts of data.

Charts & graphs

Excel turns numbers into visual charts like bar charts, pie charts, and line graphs.

COMMON FEATURES OF EXCEL

Excel has many featuers,used to make it easier to get output from the spreadsheets.
##Common features include##
Formulas (e.g. =A1+A2)

Functions (e.g. SUM, AVERAGE, IF)

Formatting (colors, borders, fonts)

Sorting & filtering data

PivotTables for advanced analysis

Decisions with Logical Functions

Logical functions help you categorise data automatically based on rules.

The IF Function The IF function performs a test: it returns one value if the test is true, and a different value if it is false.

Example: Imagine you want to categorise salaries. If the salary in cell E2 exceeds 12040, it is "High"; otherwise, it is "Low".

Formula: =IF(E2 > 12040, "High", "Low").

Nested IFs. If you have more than two categories (e.g., Old, Middle-aged, Young), you can use a Nested IF, which places a second IF function inside the first one.

AND / OR Logic You can combine IF with AND (where both conditions must be met) or OR (where at least one condition must be met).

AND Example: Assign a bonus only if experience > 30 years AND projects > 10.

Formula: =IF(AND(P2 > 30, D2 > 10), "Assign Bonus", "Do not Assign Bonus").

##Pivot Tables##
Pivot tables are the ultimate tool for summarising data. They allow you to aggregate thousands of rows into a clear summary table without writing complex formulas.

How to create one:

Click a single cell inside your data range (avoid selecting the whole sheet).

Go to Insert > Pivot Table.

Drag and Drop fields:

Rows: For categories (e.g., Department).

Values: For numbers to calculate (e.g., Sum of Salary, Count of Employees).

Interactive Slicers: To make your report interactive, insert a Slicer. This is a visual button menu that filters your Pivot Table instantly when clicked.

A Pivot Table with a Slicer for 'Department' next to it.

Use of Git to push/pull code, track changes and version control

Ajani luke Kariuki — Sat, 17 Jan 2026 06:58:01 +0000

Setting up Git and using it for version control involves a few key steps, including installing Git, creating a repository, making commits, and using push and pull commands to sync your work with a remote repository. Here's a detailed guide to help you get started:
Firstly

Push and pull

Push-Refers to sending your local changes(code or data) to a remote system or_ repository_(git hub)
Pull -Refers to fetching and intergrating changes from the remote system back to your local environment.

Set Up Git

After installing Git, you need to configure it with your usre information. Using comands like: Git config --global user.name"y/n"

git config --global user.email "your email"

Next are the linus commands.
###First we make a directory###
with this comand: mkdir project1
(project1 being the name of the directory)

Next change directory
with: cd project1

Creating repository

After jumping into the project1 or test1, initialise a repository
:git init

Making commits

Add files to the repository

Use the (git add .) command to prepare the file for commitment

Next commit
using: git commit -m "Add readme.md"

Pushing changes

To push commits to a remote repository, one must first create a repository and link it to your local repostory

##pulling changes##
To update your local repository with new chaneges from the reote repository:

Write a clear, beginner-friendly article on Dev.to explaining how to push and pull code, track changes, and understand version control using Git.

Ajani luke Kariuki — Tue, 13 Jan 2026 16:47:36 +0000

luxdev Markdown Language Class

Ajani luke Kariuki — Tue, 13 Jan 2026 11:01:00 +0000

How to write a markdown language

This is the first markdown language the students have learned and they can now write an article on dev.to

The first thing that students learned

The students learned about the heading

lastly but not least

she told me she loves tech and i told her i enjoy it

Morning class
Evining class

name= "Aj"

here is where you will find mevisit my git hub acc on