DEV Community: Ajani luke Kariuki

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