DEV Community: phillip nzioka

Power BI Database Connections

phillip nzioka — Sun, 05 Jul 2026 16:31:37 +0000

Introduction

Power BI is a data visualization software offered by Microsoft. It allows users to connect to various types of data including databases to derive meaningful insights. There are various connectors for each type of database to establish a data source connection in Power BI, a few of which are:

SQL Server Database Connector
Access Database Connector
Oracle Database Connector
MySQL Database Connector
PostgreSQL Database Connector
Google BigQuery Connector
Snowflake Connector
Amazon Redshift Connector
ODBC Connector

Any of these connectors may be used, along with appropriate configurations, to connect to the desired database as a data source for Power BI.
This article will focus on connecting Power BI to a PostgreSQL database, both offline (local) and online.

Connecting to the Local PostgreSQL database

A local PostgreSQL server is a database instance installed and running directly on your personal computer, allowing you to work offline without relying on a remote or cloud-based service.
To connect to the PostgreSQL database, you must open Power BI Desktop and navigate to Get Data and click More in order to select the appropriate database connector.

Once you are redirected to the next window, type "postgres" in the search box, select the PostgreSQL database, and then connect.

Enter the server and database name details. The local PostgreSQL server runs on localhost:5432 or 127.0.0.1:5432 and the default database name is postgres.
Power BI offers two modes of connecting to our database:

Import
DirectQuery

The primary difference is that Import mode copies data into Power BI’s in-memory engine for fast performance and full DAX support, while DirectQuery mode maintains a live connection to the source database for real-time data with no internal storage.

After selecting Connect you will be prompted to enter details for postgres database in your local machine; the User name and the Password. Once completed, select Connect.

You will be redirected to a Navigator window where you select the data you want from the database and either Load or Transform Data based on your needs.

Loading data imports raw information directly into the Power BI data model for immediate analysis and visualization without modification, whereas transforming data uses the Power Query Editor to clean, shape, and prepare the data.

Connecting to a remote PostgreSQL database

Remote PostgreSQL servers are primarily used to centralize data storage, enable collaboration across distributed teams, and support cloud deployments where applications and databases are separated for better scalability and security.

Key benefits include:

Separation of Concerns: Applications can run on one server while the database resides on another, optimizing resources and security.
Accessibility: Teams can manage and query the database from multiple locations or machines without needing local installation.
Security and Control: Centralized administration allows for stricter IP whitelisting, encryption (SSL/TLS), and robust authentication policies (e.g., scram-sha-256) to protect sensitive data.

This article will focus on connecting to a remote PostgreSQL database on Aiven. Aiven is a cloud-agnostic, AI-ready open source data platform that provides fully managed database and data streaming services across major public clouds, including AWS, Google Cloud, and Microsoft Azure.

Login into your Aiven console, navigate to your project, locate your PostgreSQL service, ensure that it's running, and if not, power the service on.

Select your service, and you will be directed to the Connection Information page.

Locate and download the CA Certificate since our service requires SSL encryption to validate connections.

Search and navigate into Manage computer certificates on your local machine.

Select the Trusted Root Certification Authorities folder, navigate to the Certificates folder, right click on it, hover on All Tasks and select Import.

You will be prompted with the Certificate Import Wizard window in order to choose the storage location. Click Next to continue.

Select Browse on the prompted window, upload the CA Certificate that you downloaded, and click Next to proceed.

You will be prompted to select the storage for the certificate, then click Next.

You will be directed to the next window, where you will select Finish.

Repeat the steps involved in connecting Power BI to the local PostgreSQL database, but change the server details and credentials to those from our Aiven console PostgreSQL service.

Conclusion

Connecting Power BI to a PostgreSQL database (whether locally or remotely)is a foundational skill for any data professional looking to harness the full potential of their data. As we've explored, the process is straightforward when approached step by step.
For local connections, the setup is a matter of pointing Power BI to your localhost with the correct credentials, allowing you to work offline and iterate quickly on your reports. This is ideal for development, testing, or when you're working in isolated environments.

For remote databases like those hosted on Aiven, the additional layer of SSL certificate configuration is essential for security. While it introduces an extra step, the benefits of centralized data management, team collaboration, and enterprise-grade security far outweigh the initial setup effort. Properly importing the CA certificate ensures that your connection is encrypted and authenticated, protecting sensitive data from unauthorized access.

Remember that the choice between Import and DirectQuery modes will significantly shape your reporting experience—balancing performance against real-time data needs. Experiment with both to understand which best fits your use case.

By following this guide, you've equipped yourself to connect Power BI to PostgreSQL in virtually any scenario. Whether you're building dashboards for personal projects or enterprise-wide business intelligence, this connectivity opens the door to powerful insights and data-driven decision-making.

Modeling, Joins, Relationships and Different Schemas In Power BI

phillip nzioka — Sun, 21 Jun 2026 10:16:39 +0000

Introduction

To master Power BI, you need to understand how data is structured, connected, and stored. Here is a comprehensive, structured guide to Modelling, Joins, Relationships, and Schemas in Power BI.

Data Modeling in Power BI

Data modeling is the process of identifying, organizing and defining the types of data a business collects and the relationships between them. It uses diagrams, symbols and textual definitions to visually represent how data is captured, stored and used. A well-designed data model helps:

Understand data requirements
Ensure proper structure for reporting
Align with business goals
Maintain data integrity

This is the golden rule of Power BI. While you can use a single flat table, it is inefficient and leads to poor performance. The recommended model is the Star Schema.

It consists of:

Fact Table (The Center): Contains quantitative data (numbers you want to aggregate). Examples: Sales Amount, Quantity Sold, Profit. Fact tables have foreign keys that link to dimension tables.

Dimension Tables (The Points): Contains descriptive attributes (text/categories you want to slice by). Examples: Customer Name, Product Color, Date, Location. Dimension tables should have a unique primary key.

Why?
Star schemas reduce memory usage, speed up calculations, and make DAX formulas (like CALCULATE) much easier to write.

Relationships (Connecting tables)

Relationships are the main feature of data modelling defining all data types. Relationships helps connect with multiple data sources using cardinality.
Relationships in Power BI define how tables filter each other. You manage them in the Model View.

Cardinality (The "One" and "Many")

Many-to-One (*:1): The most common and preferred. One row in the Dimension table relates to many rows in the Fact table (e.g., one ProductID appears many times in the Sales table).
One-to-One (1:1): Rare. Used when splitting a wide table for security or performance.
Many-to-Many (:): Avoid if possible. Used when bridging two fact tables or when dimensions are not unique (e.g., a product can belong to multiple categories). It creates ambiguity and slows down performance.

Cross-Filter Direction (The Arrow)

Single Direction (→): The default. Filters flow from the "One" side (Dimension) to the "Many" side (Fact).

Both Directions (↔): Use sparingly. This allows filters to flow from Fact to Dimension, which is useful for "Row-Level Security" but can create ambiguous path errors (circular dependencies).

Active vs. Inactive Relationships

You can have multiple paths between two tables, but only one can be active at a time.

To use an inactive relationship (dotted line), you must activate it in a DAX measure using the USERELATIONSHIP function (e.g., switching between Order Date and Ship Date).

Types of Joins (How Power BI combines data)

Unlike SQL, Power BI does not use JOIN in the query editor to combine tables for modelling (you use Relationships instead). However, when you merge queries in Power Query, you use standard joins:

Join Type	Description	Power BI Use Case
Left Outer	Keeps all rows from the left table; matches from the right.	Adding City names to a Customer list.
Right Outer	Keeps all rows from the right table; matches from the left.	Rarely used (just swap the tables).
Inner	Keeps only rows that exist in both tables.	Filtering out inactive customers by joining Sales to Customers.
Full Outer	Keeps all rows from both tables.	Merging two separate branch lists together.
Anti (Left/Right)	Left Anti keeps rows in the left that have no match in the right. Right Anti keeps rows in the right that have no match in the left.	Finding customers who haven't placed an order in 12 months.

Best Practice: Do as many merges/joins as possible in the source database (SQL), not in Power Query, as Power BI's merge engine is memory-intensive.

Different Schemas in Power BI

A "Schema" is the overall blueprint of your tables and relationships.

Star Schema (Recommended)

Structure: 1 Central Fact + Multiple Dimensions.
Pros: Fastest performance, easiest DAX, simplest filtering.
Cons: Requires time to normalize your data.

Snowflake Schema

Structure: Dimensions are further normalized into sub-dimensions.
Pros: Saves storage space (less data duplication).
Cons: Slower in Power BI. It creates more relationships, forcing the engine to traverse multiple tables to apply a single filter. Power BI recommends against snowflaking; denormalize your dimensions into a single flat table if possible.

Flat Schema (Single Table)

Structure: A single massive table with all columns (facts and dimensions) combined.
Pros: No relationships needed; simple for beginners.
Cons: Massive file size. DAX measures become complex (you can't use CROSSFILTER). Lack of date tables makes time intelligence impossible. Avoid for enterprise reports.

Best Practices for Power BI Modeling

Hide Foreign Keys: In the Model view, hide the foreign key columns in your Fact table so report builders don't accidentally use them as filters.
Create a separate Date Table: Never use a "datetime" column as your date. Create a dedicated calendar table (with Year, Month, Quarter, Weekday) and mark it as "Date Table" in Power BI. Connect it to your fact table(s) using a single-direction relationship.
Sort By Column: If you have a text month (Jan, Feb), use the "Sort by Column" feature to sort it by a numeric Month Number column.
Referential Integrity: Ensure your Foreign Keys in the Fact table do not contain values that don't exist in the Dimension table. Power BI handles this by creating a blank "Unknown" row, which can skew totals.
Set the Datatype: Always set numeric columns to Decimal or Whole Number (never Text), and set date columns to Date (not Date/Time) unless you need timestamps.

Linux Fundamentals for Data Engineering

phillip nzioka — Sun, 07 Jun 2026 19:11:58 +0000

Introduction

Linux is a free, open-source Unix-like operating system (created by Linus Torvalds in 1991) that powers most cloud infrastructure and big data tools. It's essential for data engineers due to its stability, performance, and automation capabilities.

Why Linux Matters for Data Engineering

Cloud & Tool Dominance: Runs on AWS, Azure, GCP, Docker, Kubernetes, Spark, Kafka, and Airflow
Automation: Enables ETL pipeline automation via Bash scripting and CRON jobs
Command Line Skills: Requires mastery of navigation, process management, and text editing
File System & Permissions: Critical for secure data handling with chmod and chown

Linux Essentials

Basic SSH connection

SSH (Secure Shell) lets you open an encrypted terminal session to a remote machine, typically with ssh @<server_address>.

ssh <username>@<server_address>

Creating a user to a Linux server

To create a new user, all you have to do is ‘useradd‘ or ‘adduser‘ with ‘username’. The ‘username’ is a user login name, that is used by user to login into the system.

Once you’ve created the user, you can switch to the created user

su - phillipN

Once logged in it's important to make sure the server is upto date.

Once in a while you can upgrade the server.

File Operations

ls
Lists directory contents

cd
Navigates through directories

cd <directory_path> → Moves to directory
cd .. → Goes up one level
cd → Returns to home

pwd
Prints working directory

mkdir
Creates a directory

mkdir <directory_name> → Makes directory
mkdir -p <path> → Creates parent directories

mv
mv does two jobs depending on what you give it as the destination. If you give it a new path, it moves the file there. If you give it a new name in the same folder, it renames it. Either way, the original is gone.

cp
Used to copy files and directories from a source to a destination. The basic syntax is cp [options] source destination.

Common Usage

Copy a single file: cp file1.txt file2.txt creates a copy named file2.txt.
Copy to a directory: cp file.txt /backup/ copies the file into the specified directory.
Copy directories: Use the -r or -R (recursive) flag to copy folders: cp -r dir1 dir2

rm
Deletes files or folders permanently.

rm <file> → Delete file
rm -r <dir> → Delete recursively
rm -i <file> → Confirm before delete

touch
Creates empty files.

System Monitoring & Performance

top / htop
Process monitoring.

kill
Terminate/end process.

free
Shows memory usage.

df / du
Disk usage.

uptime
Shows system uptime.

Vim Editor Essentials

Modal editing: Normal mode (navigation) and Insert mode (typing)
Key shortcuts: i (insert), Esc (exit mode), :wq (save & quit), :q! (quit without saving)
Navigation: h/j/k/l (arrows), gg (top), G (bottom), /pattern (search)

Critical Security & Maintenance

Use SSH keys instead of passwords for authentication
Create non-root users with sudo privileges
Regular system updates: apt update && apt upgrade

PostgreSQL Installation & Configuration for Data Engineering

1. Install PostgreSQL (Only If Not Already Installed)

# Check if PostgreSQL is already installed
psql --version

# If not installed, update package list and install
sudo apt update
sudo apt install -y postgresql postgresql-contrib

# Verify installation
systemctl status postgresql

2. Configure PostgreSQL to Allow Remote Traffic

# Edit the main PostgreSQL configuration file
sudo nano /etc/postgresql/*/main/postgresql.conf

# Find and change the listen_addresses line:
# From: #listen_addresses = 'localhost'
# To:   listen_addresses = '*'

# Save and exit (:wq in vim or Ctrl+X then Y in nano)

# Edit the client authentication configuration file
sudo nano /etc/postgresql/*/main/pg_hba.conf

# Add this line at the end to allow connections from any IP
host    all             all             0.0.0.0/0               md5

# For specific subnet (more secure), use:
# host    all             all             192.168.1.0/24         md5

3. Configure Firewall to Allow PostgreSQL Traffic (Port 5432)

# Allow PostgreSQL default port through firewall
sudo ufw allow 5432/tcp

# Reload firewall to apply changes
sudo ufw reload

# Verify firewall rules
sudo ufw status verbose

4. Restart PostgreSQL and Check Status

# Restart PostgreSQL to apply configuration changes
sudo systemctl restart postgresql

# Check service status
sudo systemctl status postgresql

# Check if PostgreSQL is listening on all interfaces
sudo netstat -tulpn | grep 5432
# OR
ss -tulpn | grep postgres

5. Create Database and Schema

# Switch to postgres system user
sudo -i -u postgres

# Create database 'phillipdb'
CREATE DATABASE phillipdb;

# Connect to the new database
\c phillipdb

-- Inside psql, create schema 'staging'
CREATE SCHEMA staging;

-- Verify schema was created
\dn

6. Add Data to the Staging Schema

-- Create a sample table in staging schema
CREATE TABLE staging.customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample data
INSERT INTO staging.customers (name, email) VALUES 
    ('John Doe', 'john.doe@example.com'),
    ('Jane Smith', 'jane.smith@example.com'),
    ('Phillip N', 'phillip.n@example.com');

-- Query to verify data
SELECT * FROM staging.customers;

-- Import data from CSV file (common data engineering task)
COPY staging.customers (name, email)
FROM '/data/customers.csv'
DELIMITER ','
CSV HEADER;

SCP(Secure Copy Protocol)

It is a Linux command-line utility used to securely transfer files and directories between a local host and a remote host, or between two remote hosts. It operates over an SSH (Secure Shell) connection, ensuring that all data is encrypted during transit.

Key Syntax and Usage

The general syntax is scp [options] source destination. The source and destination can be local paths or remote paths formatted as [user@]host:/path.

Local to Remote: scp file.txt user@remote_host:/remote/path/
Remote to Local: scp user@remote_host:/remote/file.txt ./local/path/
Remote to Remote: scp user@host1:/path/file.txt user@host2:/path/

Common Options

-r: Recursively copy entire directories.
-P port: Specify a non-standard SSH port (note the capital P).
-p: Preserve file modification times and permissions.
-q: Quiet mode; suppresses progress meter and warnings.
-C: Enable compression to speed up transfers.

To copy files from a Linux server to a Windows machine using SCP, you must run the command from the Windows machine to "pull" the file, as Windows typically does not run an SSH server by default.

Using Built-in OpenSSH (Windows 10/11)

Modern Windows versions include a native SCP client. Open Command Prompt or PowerShell and use the following syntax:

scp username@linux_server_ip:/path/to/remote/file C:\path\to\local\destination

username: The Linux user account.
linux_server_ip: The IP address or hostname of the Linux server.
/path/to/remote/file: The absolute path on the Linux server.
C:\path\to\local\destination: The local Windows directory where the file will be saved.

Copying files from Windows machine to a specific user on a Linux server using scp

Conclusion

Linux is not just an optional skill for data engineers—it's the foundational operating system upon which modern data engineering is built. Mastering Linux commands and concepts directly translates to the ability to build, deploy, and maintain robust data pipelines in production environments.