DEV Community: Dennis Ogaka

Guide to Connecting Power BI to SQL Databases: Local & Cloud Workflows

Dennis Ogaka — Tue, 10 Mar 2026 17:29:51 +0000

What is Power BI and How is it Used?

Power BI stands at the forefront of transforming raw information into actionable strategy, which translates to ultimate competitive advantage. It is a collection of software services, apps, and connectors that work together to turn unrelated sources of data into coherent, visually immersive, and interactive insights.
Power BI is used by data analysts and businesses to bridge the gap between data and decision-making. Its primary functions include:

Data Preparation: Using Power Query to clean, transform, and shape messy data into a usable format.
Visual Exploration: Creating interactive reports and dashboards that allow users to check into specifics, such as sales performance by region or inventory turnover.
Data Integration: Pulling data from multiple sources, including spreadsheets, cloud services, and local databases.
Collaborative Intelligence: Sharing insights across an organization through the Power BI Service, ensuring everyone is looking at a "single version of the truth."

Why Companies Connect Power BI to Databases

While Power BI can import static files like Excel or CSVs, true enterprise-grade business intelligence relies on SQL databases (like PostgreSQL, SQL Server, or MySQL). SQL databases are built to handle millions of transactions while maintaining data integrity (the assurance that your data remains accurate and consistent over time)
Companies connect Power BI directly to these databases for several critical reasons:

Scalability: SQL databases are designed to store and manage millions (or billions) of rows of analytical data efficiently which is far beyond the capabilities of a spreadsheet.
Centralization: Databases act as a "Single Source of Truth," ensuring that all departments are pulling from the same validated data repository.
Real-Time Accuracy: By connecting to a live database, Power BI reports can be refreshed automatically, providing leadership with real-time insights.
Security and Governance: Databases offer robust permission levels, ensuring that sensitive financial or customer data is only accessible to authorized users.

Why SQL Databases are Vital for Analytics

For a company to move to a competitive edge, they need a robust system to manage its analytical data. SQL databases provide three critical pillars for this:

Data Security and Concurrency: Unlike a shared Excel file that might get "locked" when one person opens it, SQL databases allow hundreds of users (and Power BI reports) to access the data simultaneously. They also offer granular security, ensuring that a junior analyst can see sales trends but not specific employee salaries.
Relational Structure: SQL databases organize data into tables with defined relationships. For example, a "Sales" table can link to a "Customers" table via a unique ID. This prevents data redundancy; you don't need to type a customer’s address every time they buy something (the database simply "relates" the sale to the existing customer record.)
High-Performance Querying: When Power BI asks for "Total Revenue by Region for Q3," the SQL database doesn't just hand over every file it has. It uses powerful indexing and optimization to find exactly those rows, calculate the sum, and send back only the result. This makes reporting fast, even with massive datasets.

Connecting Power BI to SQL Databases for Analytics

To achieve data discovery, pattern recognition, and shared insights, fueling data-driven decision-making, Power BI excels at connecting to a diverse array of data sources, with SQL databases, like PostgreSQL, providing the reliable and organized foundation crucial for robust data analysis.

Connecting to a Local PostgreSQL Database

Connecting Power BI to your local PostgreSQL instance is a straightforward process.

**Launch Power BI Desktop: **Open your Power BI Desktop application and click “Blank report.”

Access "Get Data": On the "Home" ribbon, locate and click the "Get Data" icon.

Choose PostgreSQL: A dialogue box with various data source categories will appear. Select "Database" on the left panel, and then choose "PostgreSQL database" from the list.

Enter Server Details: In the PostgreSQL connection window:
- For your local database, enter localhost (or your machine's IP address) in the "Server" field.
- Provide the "Database" name where your data resides.

Authenticate: After clicking "OK," you'll be prompted for your PostgreSQL database credentials. Ensure you have the necessary username and password and enter them into the "Database" authentication method.

Select and Load Tables: Once authenticated, the Navigator window appears, displaying the databases and schemas within your PostgreSQL server. Expand your database and select the tables you need (e.g., assignment.customers, assignment.products, assignment.sales, assignment.inventory) by checking their respective boxes. A preview of the selected table's data will appear on the right. Finally, click "Load" to bring the data into Power BI.

Connecting to a Cloud PostgreSQL Database (e.g., Aiven)

Many organizations store their data in cloud services like Aiven PostgreSQL, which requires specific security considerations, particularly SSL certificates. SSL (Secure Sockets Layer) ensures that all data transmitted between Power BI and your cloud database is encrypted, protecting sensitive information from potential interception.

To connect to an Aiven PostgreSQL database:

Retrieve Connection Details: In your Aiven dashboard, navigate to your PostgreSQL service and copy the following connection information:
- Host
- Port
- Database Name
- User Name
- Password

Download the SSL Certificate: Power BI requires the service's SSL certificate to establish a secure connection. Look for the "Download" or "SSL Certificate" option on your Aiven service dashboard and save the certificate file locally.

Initiate Power BI Connection: Follow steps 1-3 from the local PostgreSQL section. When entering the connection details for your Aiven database, provide the retrieved Host, Port, and Database Name.
Incorporate the SSL Certificate: Power BI might prompt you about encryption during the initial connection or authentication. Depending on your driver configuration (PostgreSQL ODBC or ADO.NET), you may need to import the SSL certificate into your system's certificate store or provide the path to the certificate file. The exact method can vary slightly. Ensure your connection properties specify using SSL and potentially point to the downloaded CA certificate file.

Once properly configured with SSL, Power BI will securely connect to your cloud-based Aiven PostgreSQL database, allowing you to select and load tables as previously described.

Conclusion

In essence, knowing SQL gives Power BI analysts a strong toolkit that allows them to work with data sources more directly and complete data preparation chores more quickly, producing analytics solutions that are richer, more accurate, and more effective. Any data professional can benefit greatly from combining the powerful data retrieval and manipulation capabilities of SQL with the data exploration and visualization capabilities of Power BI.

Expanding the Dataset: A Comprehensive Guide to SQL Joins and Window Functions

Dennis Ogaka — Mon, 02 Mar 2026 20:25:33 +0000

Joins and Window Functions in SQL

Joins and Window Functions in SQL are two concepts that significantly elevate querying skills. These tools allow you to combine datasets and perform advanced calculations without collapsing your data.

PART 1: JOINS

A Join combines rows from two or more tables based on a related column between them (usually a primary key and a foreign key).

Common Types of Joins

1. INNER JOIN
Returns only matching records from both tables.

Customers
customer_id | name
1           | Alice
2           | Bob

Orders
order_id | customer_id | amount
101      | 1           | 500
102      | 3           | 300

Query
SELECT c.name, o.amount
FROM Customers c
INNER JOIN Orders o
    ON c.customer_id = o.customer_id;

Result
Alice | 500

Bob is excluded because he has no matching order.

2. LEFT JOIN (LEFT OUTER JOIN)

Returns all records from the left table, and matching records from the right table.

SELECT c.name, o.amount
FROM Customers c
LEFT JOIN Orders o
    ON c.customer_id = o.customer_id;

Result
Alice | 500
Bob   | NULL

Bob appears with NULL because he has no orders.

3. RIGHT JOIN

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

Customers
customer_id name
1           Alice
2           Bob
3           Carol

Orders
order_id  customer_id         amount
101        1             500
102        1             200
103        4             300

Query
SELECT 
    c.name,
    o.order_id,
    o.amount
FROM Customers c
RIGHT JOIN Orders o
    ON c.customer_id = o.customer_id;

Result
name    order_id  amount
Alice     101      500
Alice     102      200
NULL      103      300

Orders 101 and 102 match Alice.
Order 103 has customer_id 4 → no match in Customers.
Because it's a RIGHT JOIN, all Orders are kept.
Since there’s no customer for id 4 → name becomes NULL.

4. FULL JOIN

Returns all records when there is a match in either table.

Query
SELECT 
    c.name,
    o.order_id,
    o.amount
FROM Customers c
FULL JOIN Orders o
    ON c.customer_id = o.customer_id;

Result
name      order_id           amount
Alice       101                500
Alice       102                200
Bob           NULL         NULL
Carol       NULL             NULL
NULL        103                300

Alice matches orders.
Bob and Carol have no orders → NULL for order columns.
Order 103 has no customer → NULL for name.
A full join keeps everything from both tables.

PART 2: WINDOW FUNCTIONS

A Window Function performs calculations across a set of rows related to the current row without grouping the result into fewer rows.
While Joins combine different tables, Window Functions perform calculations across a set of rows that are related to the current row. Window functions don't "collapse" your data into a single row; they keep the individual row detail intact while adding an analytical layer.
The secret sauce is the OVER () clause, which defines the "window" of data the function looks at. You can further refine this with:

PARTITION BY: Breaks the data into logical groups (like buckets).
ORDER BY: Defines the sequence within those buckets.

Basic Syntax
FUNCTION_NAME() OVER (
    PARTITION BY column
    ORDER BY column
)

Common Window Functions

1. ROW_NUMBER ()

Assigns a unique sequential number to rows.

SELECT 
    name,
    department,
    ROW_NUMBER() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS rank_in_department
FROM Employees;

This ranks employees by salary within each department.

2. RANK()

Similar to ROW_NUMBER, but handles ties differently.
If two employees have the same salary; Both get rank 1, and the next rank becomes 3

3. DENSE_RANK()

Like RANK but without skipping numbers.

4. SUM() as a Window Function

Running totals example:

SELECT 
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
    ) AS running_total
FROM Orders;

This calculates cumulative sales over time.

5. AVG() Over Partition

SELECT 
    department,
    name,
    salary,
    AVG(salary) OVER (
        PARTITION BY department
    ) AS department_avg
FROM Employees;

Each row shows the employee salary and department average without grouping.

Key Difference: GROUP BY(GB) vs Window Function(WF)

GB - Collapses rows, WF - Keeps all rows
GB - One result per group, WF - One result per row
GB - Used for summaries, WF - Used for ranking, running totals, comparisons

Sample Combined Example (Join + Window Function)

SELECT 
    c.customer_name,
    o.order_date,
    o.amount,
    SUM(o.amount) OVER (
        PARTITION BY c.customer_name
        ORDER BY o.order_date
    ) AS customer_running_total
FROM Orders o
JOIN Customers c
    ON o.customer_id = c.customer_id;

Joins customers to orders
Calculates cumulative spending per customer
This is powerful for customer lifetime value analysis.

Conclusion

Joins merge different tables based on a shared column. They change the width of your data.
Window Functions calculate values (like rankings or running totals) based on other rows in the same result set. They change the depth of your insight without losing row-level detail.
Joins are for relationship building; Window Functions are for trend and comparative analysis.
If Joins are about combining data, Window functions are about analyzing data intelligently.

Jumia Product Performance Dashboard: Analyzing Pricing, Discounts, and Customer Reviews.

Dennis Ogaka — Sat, 07 Feb 2026 23:33:30 +0000

The dataset had 115 rows and six columns. Customer rating was out of 5. I carried out data cleaning and analysis and created the dashboard. This is how I categorized the rating and discount:
Rating Category:

Poor for ratings below 3
Average for ratings between 3 and 4.4
Excellent for ratings of 4.5 and above
Discount Category:
Low Discount for discounts below 20%
Medium Discount for discounts between 20% and 40%
High Discount for discounts above 40%

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

Dennis Ogaka — Sat, 07 Feb 2026 23:10:47 +0000

Data professionals are always given data sets with missing numbers, inconsistent formats, duplicate records, and unclear business definitions. Organizations anticipate reliable insights and timely decisions from the data presented.
Any data professional must be able to give comprehensive, clear, and instructive data insights to support decision-making in the face of such organizational expectations, and Power BI is the solution to help do this. Strong data modeling and DAX logic in Power BI allow analysts to transform disorganized raw data into useful dashboards that impact organizational choices.

- Understanding the Business Question Before the Data
Proactive analysts align on the decision to be made, the audience, and the metrics that define success. Without this context, even the cleanest dashboard can fail.
For example:

Sales want to know why revenue is declining.
The executive wants to reduce cost leakages.

- Translating Messy Data into Structured Models
Messy data includes missing values, duplicates, incorrect data types, and inconsistent naming. Power Query is used to clean, standardize, and stage data for analysis.
Data Type Handling: One of the first steps in data cleaning is ensuring that each column has the correct data type. A column containing text should have the data type “text”, and so on. Power Query provides intuitive options to change data types.

Removing Duplicates: Duplicate records can skew analysis results and lead to inaccuracies. Power Query simplifies identifying and removing duplicates with just a few clicks, ensuring your dataset remains in perfect condition.

To remove duplicates, right-click on the column of interest and choose “Remove Duplicates.”

- Data Modeling: Turning Tables into Insight Engines
Using star schema principles, analysts design fact and dimension tables with proper relationships to ensure accurate calculations and performance.

- DAX: Turning Data into Business Logic
DAX encodes business rules such as profit, margin, growth, and performance comparisons using context-aware measures. With DAX, dashboards become analytical
Examples:

Gross Profit = Revenue – Cost
Selling Price = Yield*Market Price

- From Numbers to Narratives: Dashboard Design
Dashboards focus on KPIs, trends, and exceptions, guiding decision-makers toward action rather than overwhelming them with data. Analysts achieve this by removing unnecessary visuals, conflicting colors, and redundant labels.

- Translating Dashboards into Action
Effective dashboards influence real decisions such as pricing changes, cost control, and resource allocation. This is where data becomes a decision-making tool, not just a mere report.

Conclusion
Power BI bridges the gap between raw data and decisions for the company. The analyst's job is to convert facts into recommendations that lead to outputs.
As a result, a Power BI analyst's real worth is found in converting unstructured data into useful metrics, facilitating better decision-making, and effectively presenting insights. Organizations can only maintain their leadership in corporate advancement and decision-making by doing this.

An Overview of Schemas and Data Modelling in Power BI

Dennis Ogaka — Mon, 02 Feb 2026 19:04:38 +0000

Data modeling in Power BI refers to how data tables are organized, connected through relationships, and optimized for calculations and reporting.
In simple language, a data model is the engine in Power BI. Efficient data modeling transforms raw, messy data into a high-performance analytical structure. This article explains key concepts such as fact tables, dimension tables, star schema, snowflake schema, relationships, and why good modelling is critical for performance and accurate reporting.

Fact and Dimension Tables
Before choosing a schema, you must categorize your data into two distinct table types.

Fact Tables
A fact table contains quantitative, measurable data related to business events such as Sales, Orders, Revenue, and Discount.
Usually contain millions of rows, many foreign keys, and numeric columns meant for aggregation (Sum, Average).
Example: A Sales table containing OrderID, DateKey, ProductKey, and SalesAmount.
Fact table answers these questions: “how much” or “how many”

Dimension Tables
A dimension table has descriptive attributes. It provides the context for the facts on the who, what, where, and when.
The dimension tables are characterized by a smaller size, text, or categorical data used for filtering and grouping, and apply one record per entity.
Example: A product table containing productName, Category, and Color.
Dimension tables answer these questions ”who,what,where,when.”

Types of Schemas in Power BI

The Star Schema
The Star Schema is the recommended modeling pattern for Power BI. In this setup, a central Fact table is surrounded by multiple Dimension tables.

Dimension tables are not connected to each other.
It is the best option for Power BI due to the simple relationships, ease of understanding and maintenance, and its compatibility with DAX calculations.

Snowflake Schema
A Snowflake schema occurs when a dimension table is normalized, meaning a dimension table connects to another dimension table rather than the fact table.
Example: A Product(Fact) table connects to Product(Dim), which then connects to a separate Category(Dim) table.

While this saves a tiny amount of storage by reducing redundancy, it creates complex relationship chains. This forces Power BI to work harder to filter data, often leading to slower report performance. In short they are not ideal for Power BI reporting.

Managing Relationships
Relationships define how data flows between tables. In Power BI, you must pay attention to two specific settings because correct relationships ensure that filters and slicers behave correctly across visuals.
These are the relationships in Power BI:

One-to-Many
Many-to-One
One-to-One

Why Modeling is Critical
A poor model isn't just a technical annoyance; it leads to broken data and unwanted errors.
Why Good Data Modelling Is Critical:

Performance A well-designed model reduces memory usage, improves query speed and makes visuals load faster
Accurate Reporting Good modelling ensures filters propagate correctly and measures calculate as expected
Simpler DAX Measures Clean models lead to shorter DAX formulas, easier debugging and fewer calculation errors
Scalability and Maintenance A good model is easy to extend with new data and easier for other analysts to understand. It supports long-term reporting needs.

Best Practices for Power BI Data Modelling

Use star schema whenever possible
Separate facts and dimensions clearly
Avoid bi-directional relationships unless necessary
Create a dedicated Date dimension
Remove unused columns
Keep column names business-friendly
Validate totals against source systems

Conclusion
Effective Power BI reporting is built on data modeling. Understanding fact tables, dimension tables, relationships, star schemas, and snowflake schemas allows you to construct models that are fast, accurate, and easy to manage. In Power BI, good visualizations start with good models. Performance, dependability, and confidence in your reports are all enhanced by devoting time to accurate data modeling.

Introduction to MS Excel for Data Analytics

Dennis Ogaka — Sun, 25 Jan 2026 11:35:20 +0000

1. What Is MS Excel?
Microsoft Excel is a spreadsheet application used to store, organize, analyze, and visualize data. It is widely used in data analytics because it is easy to learn and capable of performing basic to intermediate data analysis tasks.

In Excel, data is stored in:

Rows (horizontal)
Columns (vertical)
Cells (intersection of rows and columns)

Each cell can store text, numbers, or formulas.

2. Understanding the Excel Interface

Before analyzing data, it is important to understand the main parts of Excel:

Ribbon – Contains tabs like Home, Insert, Data, and Formulas
Worksheet – The working area where data is entered
Formula Bar – Shows formulas entered in a cell
Sheet Tabs – Used to navigate between worksheets

3. Entering and Organizing Data

Steps:

Open Excel
Enter column headers in Row 1
Enter data in the rows below Good data organization is important for analysis.

4. Basic Calculations Using Formulas
Excel allows you to perform calculations using formulas.
Calculating Total:
Add a new column called Total.
Formula:
=Units * Unit Cost
Example:
=E2*F2
Press Enter, then drag the formula down to apply it to other rows.

5. Using Basic Excel Functions

SUM Function

Used to add values.
Example:
=SUM(G2:G44)
This calculates total sales for all products.

AVERAGE Function

Used to calculate the average value.
Example:
=AVERAGE(G2:G44)

6. Sorting and Filtering Data

Sorting and filtering help you analyze data more easily.
Sorting

Select the dataset
Go to Data → Sort
Sort by units or Total

Filtering
Select the header row
Click Data → Filter
Use dropdown arrows to filter specific products

7. Creating Simple Charts
Charts help visualize data and identify patterns.
Creating a Column Chart

Select Item and Total columns
Go to Insert → Column Chart
Choose a simple column chart

Excel will automatically generate a chart.

8. Using Conditional Formatting
Conditional formatting highlights important information automatically.
Example:
Highlight high total values
Steps:

Select Total column
Go to Home → Conditional Formatting
Choose Color Scales This example shows the top 5 totals in yellow.

9. Why Excel Is Useful for Data Analytics

Excel is useful for beginners because:

Easy to learn
No programming required
Supports calculations, charts, and summaries
Widely used in businesses

Excel is often the first step before moving to advanced tools like SQL, Python, or Power BI.

10. Conclusion

Microsoft Excel is an essential tool for basic data analytics. By learning how to enter data, use formulas, apply functions, sort and filter information, and create charts, beginners can quickly start analyzing data and making informed decisions.
For anyone new to data analytics, Excel is an excellent starting point.

Git for Beginners

Dennis Ogaka — Sat, 17 Jan 2026 19:36:47 +0000

If you are learning programming or working with code, you will hear the word Git everywhere. Git can feel confusing at first, but once you understand the basics, it becomes one of the most powerful tools in your developer toolkit.

This article explains:

What Git and version control are
How Git tracks changes
How to push and pull code
A simple Git workflow you can follow confidently

You don't need any prior experience.

1. What Is Version Control?

Version control is a system that helps you track changes made to files over time.

Think of it like this:

You wrote some code today
Tomorrow you change it
Next week, something breaks, and you want the old version back

Version control solves this problem by:

Keeping a history of every change
Allowing you to go back to previous versions
Making it easy for multiple people to work on the same project

2. What Is Git?

Git is a version control system used to track code changes.

Key things Git does:

Tracks file changes
Records who made a change and when
Allows collaboration without overwriting each other’s work
Works locally on your computer (even without internet)

Git is different from GitHub:

Git → the tool

GitHub / GitLab / Bitbucket → online platforms that store Git repositories

3. What Is a Repository?

A repository (repo) is a project folder that Git tracks.

It contains:

Your project files (code, images, docs)
A hidden .git folder that stores version history

There are two types:

Local repository → on your computer
Remote repository → online (for example, on GitHub)

4. How Git Tracks Changes

Git tracks changes in three main areas:

1. Working Directory

This is where you edit files normally. Example:

index.html
style.css

At this stage, Git sees changes but hasn’t recorded them yet.

2. Staging Area

The staging area is where you prepare changes before saving them permanently.

Command:

git add filename

Or add everything:

git add .

Think of this as saying:

"These are the changes I want Git to remember."

3. Commit History

A commit is a snapshot of your project at a specific time.

Command:

git commit -m "Describe what changed"

A commit includes:

The changes
Your message
Date and author

Git Change Flow (Very Important)

Edit files Stage changes Commit

5. Installing Git

Check if Git is installed:

git --version

If not installed:

Windows: Download from git-scm.com
macOS: Install via Homebrew or Xcode tools
Linux: Use your package manager

6. Creating Your First Git Repository

Navigate to your project folder:

cd my-project

Initialize Git:

git init

Now your folder is a Git repository.

7. Checking File Status

Use this command often:

git status

It tells you:

Which files are modified
Which files are staged
What is ready to commit

8. Making Your First Commit

Step 1: Add files to staging

git add .

Step 2: Commit changes

git commit -m "Initial project setup"

You have now saved your first version.

9. Understanding Push and Pull

What Is Push?

Push sends your local commits to a remote repository (like GitHub).

Example:

git push origin main

Meaning:

Push → send changes
origin → remote repository
main → branch name

You use push when:

You want to upload your work
You want others to see your changes

What Is Pull?

Pull downloads the latest changes from the remote repository to your local machine.

Example:

git pull origin main

You use pull when:

Someone else updated the code
You want the latest version before working

Important Rule

Always pull before you start working to avoid conflicts.

10. Understanding Branches (Beginner Level)

A branch is a separate line of development.

Main (or master) → stable code
Feature branches → new features or experiments

Create a new branch:

git branch feature-login

Switch to it:

git checkout feature-login

Or both in one command:

git checkout -b feature-login

Branches let you:

Experiment safely
Avoid breaking main code
Work in parallel with others

11. A Simple Daily Git Workflow

Here is a beginner-friendly workflow you can follow every day:

Pull latest changes

git pull origin main

Make code changes
Check status

git status

Stage changes

git add .

Commit changes

git commit -m "Add login validation"

Push to remote

git push origin main

12. Viewing Commit History

See past commits:

git log

This helps you:

Understand project history
Identify when bugs were introduced
Revert changes if needed

13. Common Beginner Mistakes

Forgetting to commit changes
Writing unclear commit messages
Not pulling before pushing
Editing directly on the main branch
Panicking when seeing merge conflicts (they are normal)

14. Why Git Is Essential

Git is used by:

Solo developers
Large companies
Open-source projects

It helps you:

Work confidently
Recover from mistakes
Collaborate professionally
Build real-world projects

15. Final Thoughts

Git may feel overwhelming at first, but you only need a few commands to be productive:

git status

git add

git commit

git push

git pull

Master these, and you already understand the core of Git and version control.

The best way to learn Git is to use it daily. Make mistakes, explore, and keep practicing.