DEV Community: Caroline Sikolia

Connecting Power BI to a SQL Database

Caroline Sikolia — Sat, 14 Mar 2026 11:16:47 +0000

Introduction

Power BI is a Microsoft business analytics service that transforms raw data from various sources (Excel, SQL, cloud services) into interactive dashboards and reports.

Businesses use it to track KPIs, identify trends, and make data-driven decisions without having to write complicated code.
A SQL database is the foundation of the majority of Power BI configurations. SQL databases, which provide organized, dependable, and scalable data management, are the foundation of corporate data storage. Businesses may have real-time access to their most important data by directly connecting Power BI to a SQL database, allowing for quicker and more precise analysis.

Importance of SQL Databases

Data Integrity: Enforces strict schemas and constraints (like keys), ensuring your data is reliable, accurate, and ready for decision-making.
Structured Management: Designed specifically for OLAP workloads, organizing historical data into tables that make large volumes easy to navigate.
Powerful Querying: Offers a robust language for complex analysis, using JOINs, GROUP BY aggregations, and WINDOW functions to slice through data.
Scalability & Performance: Built to handle massive datasets efficiently through indexing and cloud-based optimization, ensuring fast retrieval.
BI Tool Integration: Acts as a universal bridge to tools like Power BI and Tableau, turning raw queries into interactive stakeholder dashboards.

Connecting Power BI to a local PostgreSQL database

Step 1: Open Power BI Desktop.

Click on 'get data from other sources'

Step 2: Click on the source you want to get your data from

In our case we will select PostgreSQL Database, we will then click on connect.

Step 3: Enter your Server and Database

In our case we will use local host and our database will be postgres.

Import in Power BI loads a compressed copy of data into memory for high-performance, fast interactivity, and full DAX capabilities, but requires scheduled refreshes for updates. DirectQuery leaves data in the source, querying it in real-time, making it ideal for large datasets and live, up-to-the-second reporting, but it can be slower and limits transformation functionality. You can choose which option suits you best.

Step 4: Authentication

Enter your postgres username and password, this can be found in your connection details in dbeaver. The password is the one you used initially when installing postgreSQL.

Step 5: Load tables to Power BI

Select the tables you want to import to Power BI.

Connecting Power BI to a cloud database such as Aiven PostgreSQL

Instead of using local computers, organizations frequently host databases in the cloud. Aiven, which offers managed PostgreSQL services, is one illustration.

Although extra security settings are needed, connecting Power BI to a cloud PostgreSQL database is comparable to connecting to a local database.

Step 1: Log in to Aiven and navigate to your PostgreSQL service

In the Overview tab, find and copy the connection details: Host, Port, Database, Username (default is avnadmin), and Password.

Step 2:Download the SSL CA certificate file

Download the SSL CA certificate file(ca.pem or similar) from the Connection information section and save it to a secure, known location on your computer.

Step 3: Open Power BI

Open power BI like we did earlier and navigate to 'Get Data from other Sources'.
In the "Get Data" window, search for "PostgreSQL", select PostgreSQL database, and click Connect.
In the PostgreSQL database dialog, enter the Server (Host) and Database name from your Aiven details. Enter the server address and port number in the format

your-service-project.aivencloud.com:port

in the Server field, or use the Advanced Options for specific SSL parameters.

Step 4:Select a Data Connectivity mode

Import is recommended for most cases.

Step 5:Authenticate and Load Data

If prompted for credentials, select the Database authentication type. Enter your Aiven Username and Password. Click Connect. Power BI will attempt to establish a secure connection using the provided details and SSL certificate.

Step 6: Load Data

Once connected, the Navigator window will display available tables and views. Select the desired tables and choose Load to import the data directly, or Transform Data to use the Power Query Editor for cleaning and shaping the data before loading.

Data modeling is the act of specifying the relationships between various data tables in order to produce a cohesive, logical organization. Power BI knows how to filter and aggregate data from various sources by creating associations, usually utilizing a "Star Schema" in which a core fact table relates to multiple descriptive dimension tables. The program cannot correctly calculate totals or split data by categories without these stated paths, such as connecting a "Sales" table to a "Products" table via a Product ID. This results in broken visualizations or inaccurate insights. When you filter by a certain year or region, the full report will reply coherently and precisely thanks to proper modeling.

Conclusion

Why SQL skills are important for Power BI analysts

Efficient Filtering: Uses WHERE clauses to extract only necessary data, preventing Power BI from slowing down with millions of redundant rows.
Pre-emptive Aggregation: Summarizes data at the source (using GROUP BY), delivering smaller, faster datasets to your dashboard.
Complex Prep: Handles intricate joins and transformations more cleanly than a GUI, keeping your data model lean and organized.
Performance Optimization: Enables Query Folding through native queries, pushing the heavy processing to the database for faster refreshes.

From Tables to Trends: Understanding Joins and Window Functions in SQL

Caroline Sikolia — Sun, 01 Mar 2026 12:45:54 +0000

What is SQL used for?

SQL (Structured Query Language) is primarily used for managing and manipulating data in relational databases. It is the common language for communicating with databases such as Oracle, Microsoft SQL Server, PostgreSQL, and MySQL, allowing users to carry out a variety of operations from simple data management to intricate analysis.

JOINS

In SQL, a JOIN clause is used to combine rows from two or more tables based on a related column between them. The primary purpose of joins is to retrieve data that has been spread across multiple tables in a relational database, providing a comprehensive, unified view in a single result set. It helps answer business questions like which customer placed an order or which product generated revenue.

Window Functions

SQL window functions perform calculations across a set of table rows related to the current row, without merging those rows into a single output like a GROUP BY clause would. They return a result for each individual row, which is useful for tasks such as calculating running totals, rankings, and moving averages.

In real-world databases, data rarely lives in one table. Joins help us combine related data, while window functions allow us to analyze patterns without losing row-level detail.

Types of JOINS

1.INNER JOIN

It is used to combine rows from two or more tables based on a specified common column with matching values. It returns only the set of records that have a match in all the tables involved, effectively acting as an intersection of the data sets
Rows that do not have a corresponding match in the other table(s) are excluded from the result set.

Example

SELECT customers.name, orders.order_date
FROM customers
INNER JOIN orders
    ON customers.customer_id = orders.customer_id;

This query returns only customers who have placed orders because INNER JOIN keeps matching records from both tables.

2.LEFT JOIN

The SQL LEFT JOIN (or LEFT OUTER JOIN) is a fundamental operation that retrieves all rows from the left (first) table and matching rows from the right (second) table. If a row in the left table has no corresponding match in the right table based on the join condition, the result will contain NULL values for the columns of the right table.

Example

SELECT 
    customers.name,
    orders.order_date
FROM customers
LEFT JOIN orders
    ON customers.customer_id = orders.customer_id;

This query keeps all customers, if a customer has an order → the order date appears. If a customer has never placed an order → order_date will be NULL.

3.RIGHT JOIN

The SQL RIGHT JOIN (or RIGHT OUTER JOIN) returns all records from the right-hand table and only the matching records from the left-hand table. If a row in the right table has no match in the left table, the columns from the left table in the result set will contain NULL values.

Example

SELECT 
    customers.name,
    orders.order_date
FROM customers
RIGHT JOIN orders
    ON customers.customer_id = orders.customer_id;

This query It keeps all orders, if an order has a matching customer → the name appears. If there’s no matching customer → customers.name will be NULL.

4.FULL OUTER JOIN

A FULL OUTER JOIN (or FULL JOIN) in SQL returns all rows from both the left and right tables, combining matching records and using NULL values for columns where no match is found.

Example

SELECT 
    customers.name,
    orders.order_date
FROM customers
FULL OUTER JOIN orders
    ON customers.customer_id = orders.customer_id;

This query returns Customers with orders → matched rows.
Customers without orders → order columns are NULL.
Orders without customers → customer columns are NULL.

WINDOW FUNCTIONS

Window functions allow us to calculate values like rankings, running totals, and averages across a group of rows while still keeping each individual row visible.
Core components of windows functions include:

Order by

The SQL ORDER BY clause is used to sort the result set of a SELECT query in a specific order based on one or more columns. Without this clause, there is no guaranteed order for the returned rows.
Example

SELECT * FROM Customers ORDER BY CustomerName;

Partition by

It is used to divide a query's result set into partitions (groups). The window function then performs calculations, such as aggregation or ranking, within each partition independently, without collapsing the individual rows of the result set.
Example

SUM(amount) 
OVER (PARTITION BY customer_id ORDER BY order_date) 
AS cumulative_sales

This query calculates a running total of sales for each customer, ordered by date. The sum resets for each new customer ID.

Over()

The OVER clause determines the partitioning and ordering of a rowset before the associated window function is applied.
Example

SELECT 
    employee_name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

This query ranks employees within each department based on salary without grouping the table.

Other key window functions

ROW_NUMBER() - It assigns a unique, sequential integer (starting at 1) to each row within a result set based on a specified ORDER BY clause. It is commonly used for pagination, ranking, and identifying top-N results.

RANK()- It assigns a rank to each row within a result set.

DENSE_RANK()- It assigns a rank to each row within a result set, with tied values receiving the same rank and no gaps in the ranking sequence.

SUM() OVER() (running total)- It is used to calculate the sum of values across a set of table rows related to the current row, without collapsing the individual rows themselves. This differs from a standard SUM() with GROUP BY, which returns only a single summary row per group.

AVG() OVER()- It is used to calculate the average value of a numeric column across a specific "window" or set of related rows, without collapsing the individual rows of the result set into a single summary row.

CONCLUSION

For practical data analysis, SQL joins and window functions are vital tools. Joins enable us to comprehend connections between various datasets, such customers and their orders, and to merge relevant tables. On the other hand, window functions let us to carry out sophisticated computations like running totals and rankings without losing track of specific row information.

When combined, these ideas help us go beyond simple questions and provide more profound analytical understanding. While mastering window functions enables more sophisticated data analysis, mastering joins enables us to connect data.

Beyond Pretty Charts: How Power BI + DAX Create Real Business Impact.

Caroline Sikolia — Sun, 08 Feb 2026 11:52:29 +0000

Every Monday morning, Sarah opens her laptop to face the same nightmare:
47 Excel files, 12 different data formats, and a CEO asking for "quick insights"
by noon. Sound familiar? If you've ever felt overwhelmed by messy data that
needs to become executive-ready dashboards, you're not alone. Today, I'll show
you exactly how Power BI, DAX, and smart dashboard design can transform you
from a data wrangler into a strategic analyst who drives real business decisions.

The Data Journey: From Chaos to Clarity

Before any analysis begins, reality hits.

Most datasets come with problems like:

Multiple data sources that don’t align
Inconsistent date and number formats
Missing values
Duplicate records
Poorly named columns

A dataset can contain duplicate customers, empty revenue fields, and product categories written in different formats (“Electronics”, “electronics”, “Elec”).

Trying to build visuals on top of this produced misleading totals and broken charts.

This is where many beginners get stuck — jumping straight into dashboards without preparing the data first.

The lesson?

Good dashboards start with clean data.
Below is an example of raw data... it contains some blanks from first glance.

Once I acknowledged the mess, I moved into Power Query to start shaping the data properly.

Data Preparation & Power Query

Power Query is where the real magic begins.

Inside the Power Query Editor, I performed several essential transformations:

✅ Removed duplicates

To avoid double-counting farmers.

✅ Handled null values

Replacing blanks with zeros or meaningful defaults.

✅ Split and merged columns

Separating full names into first/last names and combining date fields where necessary.

✅ Converted data types

Ensuring dates were dates, numbers were numbers, and text stayed text.

Power Query records every step, making your process repeatable and transparent.

Here’s a simple M example for removing duplicates:

= Table.Distinct(Source)

For dealing with blanks I used the replace value functions which replaces blanks with null to clean the table.
Once the data foundation was solid, I moved on to creating metrics that actually mattered.

Creating Meaningful Metrics

DAX (Data Analysis Expressions) is what allows Power BI to go beyond simple totals.
One of my first measures was calculating Total Planted Area.

Total Planted Area= SUM(Kenya_Crops_Cleaned_Final[Planted Area (Acres)])

I was able to view the results in the report view page under the cards feature.

Other essential concepts I used:

CALCULATE for modifying filter context
DIVIDE instead of / to avoid errors
Time intelligence for monthly and yearly comparisons
Understanding row vs filter context

DAX felt intimidating at first, but once I understood how context works, everything clicked.
Instead of just showing numbers, I was now answering business questions like:

Are sales growing?
Which products drive the most revenue?
What periods perform best? That’s when the project moved from reporting to insight.

From Visuals to Insights

A dashboard isn’t about squeezing in as many charts as possible.
It’s about telling a story.
I followed a few simple design principles:

Start with KPI cards (Revenue, Total Planted Area, Profit)
Use line charts for trends
Bar charts for comparisons
Slicers for interactivity
Keep colors consistent and minimal
Each visual answered one clear question.
I also added filters so users could explore performance by crop type, county, or time period.

The result? A dashboard that didn’t just look good but it also helped stakeholders make decisions.

Conclusion

Data doesn’t arrive perfect.
But with the right process — Power Query for cleaning, DAX for logic, and thoughtful dashboard design — you can turn raw chaos into actionable insights.
If you’re learning Power BI, my advice is simple:

Start small. Practice often. Build real projects.

That’s how you grow.

Schemas and Data Modelling in Power BI

Caroline Sikolia — Sun, 01 Feb 2026 13:31:00 +0000

Introduction to Power BI

Power BI is a cloud-based business analytics tool from Microsoft that connects to, cleans, and converts raw data from several sources into dashboards and reports that are interactive and visually appealing. In order to facilitate decision-making, it allows users to examine data, identify patterns, and disseminate insights within an organization.

Data Modelling

Data modeling in Power BI is the essential process of structuring and relating your data tables to create a logical, effective semantic model that enables accurate analysis and powerful reports. By defining relationships, generating DAX calculations (measures), and organizing data—often using star or snowflake schemas within Power Query and Model View in Power BI Desktop. This blueprint promotes scalability for well-informed decision-making, enhances report performance, and guarantees data integrity.

Schema

A schema in Power BI is the logical structure and arrangement of tables, defining how data is organized, related, and modeled to support efficient reporting and analysis.

Types of Schemas

1. Star Schema: The central, most efficient model for Power BI, featuring a central fact table surrounded by directly related dimension tables. This structure minimizes joins, resulting in faster performance and easier usability.

Components of Star Schema
Fact Tables: The center of the star, containing foreign keys to dimension tables and numerical measures (e.g., Sales Amount, Quantity, Boxes).
Dimension Tables: Surround the fact table, providing descriptive attributes (e.g., Product Name, Customer Region, Date).
Relationships: Typically 1-to-many ((1:N)) from dimension tables to the fact table.
Surrogate Keys: Unique integer identifiers often used in dimension tables to enhance performance.

Importance of Star Schema

Performance: Optimized for read-heavy operations, making data retrieval faster and more efficient.
Simplicity: Reduces the need for complex joins, facilitating easier report and dashboard creation.
Scalability: Allows for easy addition of new dimensions or facts, making it a flexible choice for growing businesses.

2.Snowflake Schema:A snowflake schema in Power BI is a normalized data modeling approach where dimension tables are broken down into multiple, related tables (e.g., separating Product Subcategory from Product), creating a snowflake-like shape. While it reduces data redundancy and aids data integrity, it is generally less optimal for Power BI performance than a star schema, causing slower filter propagation and larger model sizes.

Components of Snowflake Schema

Central Fact Table: Stores measurable, quantitative data (e.g., Sales, Revenue) and foreign keys to connect to primary dimension tables.
Primary Dimension Tables: Direct lookup tables connected to the fact table, containing descriptive attributes (e.g., Customer, Product).
Normalized Sub-dimension Tables: Further branched tables that normalize data, such as splitting a "Product" table into "Product," "Subcategory," and "Category" tables.
Primary and Foreign Keys: Crucial for creating one-to-many (1:N) relationships between the fact table and dimensions, and between primary and sub-dimensions.
Hierarchical Structures: The branching of dimensions enables deep, hierarchical filtering, such as mapping Product (\rightarrow ) Subcategory (\rightarrow ) Category.

Advantages of Good Modelling in Power BI
Better Performance: A well-designed data model, especially one that makes use of a star schema (fact and dimension tables), drastically cuts down on query time and memory consumption, which speeds up report rendering.
Data Integrity and Accuracy: By removing redundant data and guaranteeing consistent relationships, it lowers errors, which is essential for reliable, accurate reporting.
Simplified Complex Calculations: By eliminating the need for unduly complicated DAX formulas, a well-structured model facilitates the writing and upkeep of measures.
Facilitates Data Exploration: It makes it possible to create drill-down routes and hierarchies, which aid users in navigating data, spotting trends, and carrying out in-depth analysis.
Scalability and Maintenance: Reports with a strong data model are more manageable and scalable, enabling them to accommodate increasing data quantities as business requirements change.

In conclusion, Data modeling in Power BI is essential for creating efficient, accurate, and scalable reports. Without proper data modeling, Power BI reports may suffer from poor performance, incorrect data aggregation, and difficult-to-maintain, complex calculations.

Introduction to MS Excel for Data Analytics

Caroline Sikolia — Sun, 25 Jan 2026 16:39:48 +0000

Within the Microsoft Office suite, Microsoft Excel is a robust spreadsheet application that uses rows and columns (cells) to organize, analyze, visualize, and compute data. With formulas, functions, charts, and pivot tables to transform raw data into usable insights, it can be used for anything from personal budgets and contact lists to sophisticated financial modeling and data analysis. It is accessible online and across devices for teamwork.

Excel Interface

This consist of things you see when you open excel. They include:

Quick Access Toolbar: The Quick Access Toolbar is located above the ribbon and contains buttons for commands that you'll use often, such as save and undo.
Ribbon:The Excel Ribbon is the command center at the top of the window, organized into Tabs (like Home, Insert, Page Layout) that contain logical Groups (like Font, Alignment) of related Commands (buttons, icons) for performing actions.
Name Box:The Name Box in Excel is the small box left of the formula bar that shows the active cell's reference (e.g., A1) but also lets you assign meaningful names to cells or ranges, making them easier to find and use in formulas.
Formula Bar:The Excel formula bar is a toolbar below the ribbon used to enter, view, and edit data or formulas in cells.
Worksheet & Workbook:An Excel worksheet is a grid of columns and rows forming a single page for data entry, analysis, and visualization. One or multiple worksheets make a Workbook.

Formatting and Cleaning Data

This section covers how to clean data in your work sheet using tools such as Formatting Cells, Conditional Formatting and removing duplicates.

Formatting Cells & Rows: You can format the cells and Rows in your data according to their data type to improve data integrity, readability and assist in calculations. This can be done through number section in the home tab part of the ribbon or right clicking a cell.

Removing Duplicates: Duplicates in data sets can be removed via the Data tab in the ribbon under the remove duplicates button.

Conditional Formatting:Excel conditional formatting automatically changes cell appearance (color, icons, data bars) based on rules or formulas, helping you visualize trends, spot outliers, and highlight key data like duplicates .

Sorting, Filtering and Data Validation

Sorting: This changes the order of rows based on the chosen columns eg. From largest to smallest for numbers and A to Z for texts.
Filtering:Excel filtering lets you show only the data that meets specific criteria, hiding the rest.
Data Validation:Data validation in Excel restricts data entry in cells to specific types, values, or formats (like whole numbers, dates, or list items) to ensure accuracy and consistency.

Basic Calculations and Functions

A formula in excel is a command for Excel to calculate something. They usually begin with an equal sign.(=)
You can do basic calculations directly eg. =B2 + B3 or you can use the SUM function. SUM is usually for addition, AVERAGE
for finding average , MIN for finding minimum value, MAX for maximum value and COUNT for counting cells with values.

AUTOSUM in Excel quickly adds numbers in a range using the =SUM function; you can use the AutoSum button (Σ) on the Home tab, or the Alt + = keyboard shortcut, by selecting the cell where the total should appear and pressing Enter.

Conditional Functions
These calculate only for rows that meet certain conditions.
SUMIF: Adds values in a range that meet a single, specified condition, using the syntax =SUMIF(range, criteria, [sum_range]), where range is checked for criteria, and if true, corresponding cells in sum_range are added.

SUMIFS is used when there is more than one criteria.
COUNTIF,AVERAGEIF,MAXIF and MINIF can be used the same way depending on what calculation needs to be done.

Tables in Excel

You can convert your data to a table under the insert tab in the ribbon or click anywhere in your data and select Ctrl + T.

PivotTables
PivotTables in Excel are formula-free tools designed for instantly summarizing, analyzing, and exploring large datasets. You can create them through selecting anywhere in your data and navigating to the insert tab.
You can edit the contents of the pivot table using the PivotTable section.

Charts & Slicers

Excel charts and slicers create interactive, visual reports by allowing users to filter data dynamically. Slicers act as visual buttons to filter PivotTables, Tables, and connected Charts instantly.

Dashboards

An Excel dashboard is a visual report that consolidates key business metrics (KPIs), charts, and tables into a single view for easy tracking and analysis, offering a dynamic, at-a-glance understanding of performance, often built using PivotTables, PivotCharts, Slicers, and Timelines for interactivity.

Conclusion

Excel is a great starting point for anyone getting into data analysis. With basic formulas, pivot tables, slicers, charts, and dashboards, you can turn messy raw data into clear, useful insights. These tools make it easier to spot trends, answer questions faster, and support better decisions. With practice, Excel becomes more than just a spreadsheet, it becomes a powerful tool for understanding data.

A Beginner’s Guide to Git Bash, GitHub, and Version Control

Caroline Sikolia — Fri, 16 Jan 2026 11:25:55 +0000

Introduction

As a beginner in data science and data analytics, learning how to manage and track code is an important skill. Git and GitHub are tools that help developers and analysts keep track of changes in their projects and collaborate efficiently.

In this article, I'll break down what Git, GitHub, and Git Bash actually are in simple terms, and walk you through the basics like pushing and pulling code, tracking your changes, and understanding on version control.

What is Version Control?

Version control is a system that helps you keep a history of changes made to files over time. Instead of saving multiple copies of the same file, version control allows you to track edits, go back to previous versions, and see what changed and when. This comes in handy when working on projects or collaborating with others.

What is Git?

Git is a version control system that helps track changes in your code or files. It allows you to save different versions of a project, undo mistakes, and work on projects safely without losing progress.

What is GitHub?

GitHub is an online platform that hosts Git repositories. It allows users to store their projects in the cloud, collaborate with others, and access their code from anywhere.

What is Git Bash?

Git Bash is a command-line tool for Windows that allows users to run Git commands using a terminal. It helps users to manage files, track changes and connect to GitHub.

Linking Git Bash to GitHub

After installing Git Bash, you can connect it to your GitHub account by configuring your username and email and authenticating your account. This connection allows you to push local code to GitHub and pull updates from remote repositories.

Basic Git Commands for Beginners

mkdir (folder name)This command creates a new folder.
touch (file name)This command creates a new file.
git initThis command sets up a new Git repository in your project folder.
git add .This adds files to the staging area, preparing them to be committed.
git commitThis saves your changes with a message describing what was done.
git statusThis command shows the current state of your files and displays any changes made.
git push origin mainThis command uploads your local commits to a GitHub repository.
git pullThis command fetches and updates your local project with changes from GitHub.

How Git tracks changes

Git keeps track of changes in three basic steps:

Modified – files that have been changed but not saved.
Staged – files ready to be committed.
Committed – files safely stored in Git history.

These help users manage changes in an organized way.

Common Beginner Mistakes

Forgetting to run git add before committing
Not writing clear commit messages
Trying to push without connecting to a remote repository These mistakes are normal when starting out and improve with practice.

Conclusion

Git, GitHub, and Git Bash are essential tools for beginners in tech fields such as data science and data analytics. Understanding how to track changes, push code, and pull updates makes project management easier and more reliable. With regular practice, these tools become an important part of any developer’s workflow.

First Technical Article

Caroline Sikolia — Tue, 13 Jan 2026 18:29:41 +0000

heading 1

heading 2

Today this is the first try
k = 5