DEV Community: leslie angu

Power BI Connects to a Database!

leslie angu — Sun, 05 Jul 2026 10:33:17 +0000

Introduction

Data analysts use Power Bi to build insightful dashboards. Where does this data come from? This led me through a rabbit hole, where I discovered that Power Bi has over 200 distinct data sources through its native connectors, ranging from local Excel files to cloud-based data lakes. The following are the built-in connectors:

Files: Excel, CSV, XML, JSON, PDF, and Parquet
Databases: SQL Server, Oracle, Snowflake, Google BigQuery, PostgresSQL, and Amazon Redshift
Cloud Services: Google Analytics, Salesforce, Dynamics 365, SharePoint, and Adobe Analytics
Online/Web APIs: OData feeds, REST APIs and unstructured web pages.
Power Platform: Dataverse and Power BI Datasets.

The Database Connection

How do you connect Power BI to a database? Their are different types of databases but we will focus on PostgreSQL for the local and cloud server option.

Option A: Connecting to postgreSQL database locally

To connect your Power Bi to a local postgreSQL database you will need to have the password, database name, username and port (default:5432) for your postgreSQL

Launch Power BI: Open Power BI Desktop and click on Get Data on the Home ribbon.

Select the connector: Type: PostgreSQL in the search box. Select PostgreSQL database and click Connect

Enter Server & Database Details:

Server: Enter localhost:5432 or 127.0.0.1:5432 if the database is on your machine. Set the port to 5432.
Database: Enter the specific name of your database
Data Connectivity Mode: Choose Import(loads data to memory, best for speed and offline work) or DirectQuery (queries the live database for real-time data)

Authenticate: Go to the Database tab on the left menu. Enter your postgreSQL Username and Password, then click Connect
Load Data: Check the boxes next to the tables you want to analyze and select Load (to pull data directly) or Transform Data (to clean and shape it in the Power Query Editor)

Option B: Connecting to PostgreSQL database remotely

A) Log in to your aiven console and navigate to your postgreSQL project. Ensure that your project is running.

B) Locate the Overview or Connection Information tab. Copy and save these individual parameters.

C) Locate the CA certificate option in the same section and click the download icon to save the file locally to your machine.

The CA(Certificate Authority) certificate from Aiven to connect to Power BI because Aiven requires mandatory SSL/TLS encryption for all external database connections.
The certificate serves two main purposes:

Data Encryption: It encrpts the connection so your credentials and data remain safe while traveling over the public internet.
Server Verification: It allows Power BI to verify that you are connecting to your actual Aiven database rather than a malicious imposter.

D) Install the SSL certificate in your local machine: search for the manage user certificates. Click the Trusted Root Certification Authorities.

Right click the folder and navigate to import. The certificate Import Wizard will pop up.

Load the downloaded certificate after ensuring all the files are selected.

E) Establish the connection in Power BI Desktop and add the server details from aiven similar to what was illustrated in the local database connection.

Conclusion

Connecting Power BI to both local and cloud-hosted databases is a straightforward process once the required configurations are in place. However, users may encounter common connection issues, such as using localhost instead of 127.0.0.1 when connecting to a local database, or failing to install the appropriate CA certificate required by a cloud database provider. Addressing these configuration requirements ensures secure and reliable connectivity.

In practice, data analysts are typically provided with the necessary database credentials and access permissions by database administrators. Once connected, they can retrieve, transform, and visualize data in Power BI to create meaningful reports and interactive dashboards that support informed business decision-making.

Let's discuss data modeling, Joins, Schemas and Relationships on Power BI

leslie angu — Tue, 23 Jun 2026 07:17:13 +0000

Introduction

Last week I had the opportunity to learn a few key skills that are used in Power BI, to make stories from data. I learnt that you can load different types of data to Power BI such as excel, csv, parquet and you can also pull data from databases like postgresql. So what is power BI and what does it do?

Power BI is a business intelligence and data visualization platform. It takes the data collected and stored in different formats and which the use of DAX (power BI querying language), you can unify scattered financial, sales, operational data and track key performance indicators (KPI).

What is data modeling?

Data modeling is the process of identifying, organizing and defining the types of data a business collects and the relationships between them. For example a school system has a spreadsheet of classes, teachers and performance score. Individually they are just records but we can combine them to tell us Math, taught by John Doe is the best performing across different class streams.
Modeling involves:

Creating relationships between tables
Defining calculated columns and measures
Optimizing data structure for performance
Ensuring data accuracy and consistency.

What are Relationships in Power Bi?

Imagine you are the data analyst working for Naivas supermarket, with multiple branches all over the country, and each of the branches has its own database where they store important business data like the customer information, sales, and stock. Data from these different branches was collected, and you were asked to combine all this data together and generate insights from it. You will need to create a relationship between these different datasets so as to link them together before you start exploring the data.
To understand how rows in one table is related to another table, we need to discuss cardinality.

Cardinality specifies how the rows in one table are related to the rows in another table.

To create a relationship in Power BI, you need to link columns that contain the same information and the same column headers together eg Customer_id in the customer table with customer_id in the sales table.

The one-to-many cardinality relationship in Power BI is a connection between two tables in a data model where one unique value in the one table can be associated with multiple values in the many table. For example customers and products: each customer can make multiple purchases of a product, so there is a one to many relationship between them but each purchase(many) is associated with only one customer(one).

Many-to-one relationship: The many to one cardinality in PowerBI refers to a relationship between two tables where multiple records in one table can be associated with a single record in another table. For example in the sales table, one customer can be involved with multiple purchases over time. Multiple purchases associated with one customer.

One-to-one relationship: The one-to-one cardinality in PowerBI is a relationship setting that describes how two tables are related to each other.

Tables have rows that have unique identifies such as Primary Key (PK). Foreign Key (KY): links records across two tables together.

What are schemas?

A schema refers to the structure and organization of data within a data model. Schemas define how data is connected and related within the model, influencing the efficiency and performance of data queries and reports.

Understanding schemas helps in designing best data models that support comprehensive analysis

Types of Schemas in Power BI

1. STAR schema

The star schema consists of a central fact table surrounded by dimension tables, forming a star-like pattern.
The central fact table contains quantitative data(eg sales), while the dimension tables hold descriptive attributs related to the facts.(eg customers, products)

2. Snowflake Schema

Snowflake schema is a normalized version of the star schema. In this design, dimension tables are further divided into related tables, resulting in a more complex structure.

The normalization process eliminates redundancy by splitting dimension tables into multiplr related tables. This results in a web-like structure, resembling a snowflake.
Usage: Snowflake schemas are used in scenarios requiring detailed data models and efficient storage. They are beneficial when dealing with large datasets where data redundancy needs to be minimized.

Galaxies Schema(or fact constellation schema)

The galaxies schema involves multiple fact tables that share dimension tables, creating a complex, interconnected data model.

The schema consists of multiple fact tables linked to shared dimension tables, enabling the analysis of different business processes within a single model.
Usage: Galaxies schemas are suitable for large-scale enterprise environments where multiple related business processes need to be analyzed. They support complex queries and detailed reporting across various domains.

What are joins?

Power BI supports all major joins that are available in SQL. To perform joins, you need to access the Power Query Editor which is appears after clicking Transform data. The following join types are standard join types in Power BI and SQL.

Inner Join: Returns the rows present in both left and right table only if there is a match.
Joining the sales table to the customer table. The common column is customer_id.

The expected result after the join:

Full Outer Join: It returns all the rows present in both the left and right table.
Joining the sales table with the products table. The common column is the product_id

The expected result after the join:

Left Outer Join: It returns all the rows present in the Left table and matching rows from the right table(if any)
Joining the sales table with the store table. The common column is the store_id.

The expected result after a left outer join:

Right Outer Join: Returns matching rows from the left table(if any) and all the rows present in the SQL Right Table.

The expected result from the right outer join:

Anti Join: a filtering operation that returns only the rows from the first table(the left table) that do not have matching value in the second table(the right table).

The expected result from the left anti join:

Note: The null values observed after the join are a result of the absence of matching rows between the two tables.

Conclusion

Power BI is more than just a visualization tool; it is a complete business intelligence platform that enables organizations to transform raw data into meaningful insights. Understanding concepts such as data modeling, relationships, cardinality, schemas, and joins forms the foundation for building efficient and reliable reports.

By creating proper relationships between tables and designing models using schemas such as the star and snowflake schemas, analysts can ensure consistency, improve query performance, and simplify data analysis. Additionally, mastering joins in Power Query allows data from multiple sources to be combined effectively, making it possible to uncover patterns and answer business questions with confidence.

As I explored these concepts, I realized that creating dashboards is only one part of the process. The real value lies in designing a robust data model that enables accurate analysis and storytelling. This learning experience provided me with a deeper appreciation of how Power BI bridges the gap between raw data and informed decision-making, and it has given me a solid foundation for building more advanced reports and analytical solutions in the future.

Git & Github Fundamentals For developers

leslie angu — Sun, 14 Jun 2026 20:43:08 +0000

Introduction

The week that had passed, I was curious about how data engineers use git and github to perform their tasks. I managed to learn a few core usages and I broke them down so that a novice programmer could understand them.

Understanding Git

So basically git is used for version control. What is version control and who cares about what it does...
Version control is a system that tracks and manages changes to files over time. According to the research I did, it acts like a time machine: allowing you to take specific snapshots of your work, compare differences, and instantly revert back to an older working version if something goes wrong. To put it into a simpler percpective, imagine having a shopping list and pulling a trolley, when you get into the supermarket, you check what is on your list and pick them as you add them to your shopping cart. As time goes buy and the ice cream you picked melted, you can return it into the freezer and pick another one.

Why is git useful

Total accountability: You can see exactly who made a change, what they changed, when it happened, and why.
Pain-Free Collaboration: Teams can work on the same files simultaneously without accidentally overwriting each other's work.
Safe Experimentation: You can test out new ideas in a separate workspace eg a branch without affecting the main project until you are ready
Automated Backups: Your project's history is typically stored on a remote cloud server, protecting your work from local machine failures. #### How does Git work? Git eradicates the need for manually saving messy copies of your files eg resume1.doc, resume2.doc and resume3.doc -all these might contain the same content with minimal changes. Version control does the organizing for you. You make edits to your files, and when you are satisfied with a set of changes, you save a permanent snapshot of the project. These snapshots are stored in a database called repository. ## What is Github? Git hub is a cloud-based platform and hosting service where developers store, share, and collaborate on software code. It is like Google Drive and Icloud.

Let's get to learn how version control works

We need to track the work down in the working directory by using git init.

ADMIN@uSER MINGW64/Documents/Project/ git init

We list the contents in the directory using ls, then we check the status of the directory. Git status works by comparing the differences in three distinct states: HEAD Commit, staging area, and working directory.

Note: Most developers use a flag with git status to improve on readability and to save on time. Eg git status -s : short format of the whole message generated.
The directory doesn't have any files so we will create one using touch and then we will add it to the staging area to be tracked using git add .

The files in the staging area needs to be committed so that we can permanently save them. This is like a permanent save point in a video game.

Pushing the tracked file that was saved to Github

Did you notice the master at the end of the file path in the git bash. That is the branch that we are currently in, it's similar to root.
Note: Master is the older way of naming branches. The new way is using main
We change the branch from master to main using: git branch -M main.

We need to push the tracked file to github. We log into our github account, create a repository and expect some instructions to push the tracked file. use command git remote add origin https://github.com/USER/project.

Finally ensure you can upload the tracked file using git push -u origin main.

.
When you refresh your repository, the tracked file will be uploaded.

Note: In case the output from the terminal is fatal, check the repo you connected to using git remote -v and if your repo url had an issue use this command to reset it:git remote set-url origin https://github.com/USERNAME/REPOSITORY.git

What is a Git branch?

A git branch is an independent workspace or "parallel universe" within a code repository that allows developers to make changes without affecting the main project.

Creating a branch use git branch and then switching to the branch use git checkout branch-name then alter the file.

When you print out the contents of main.py, they will be completely different if you changed the data in main.py file while still in the local branch.

Let's merge the two branches.

When you want to merge the two files, use git merge but ensure they are in the main branch. Incase you meet any conflict, ensure that you use vim editor and choose which content you would like to remain.

Next concepts to be covered:Git pull, Git clone, git rebase

When you want to collaborate, use ensure you use ssh (secureshell) when cloning the repo. Moreover, use git pull (download any changes from a remote repository and immediately integrate them into your current local working branch)

Summary of Git and Github

When you start a project always use git init, ensure you add and commit any changes made to a project periodically.

Linux Fundamentals for Data Engineering

leslie angu — Sun, 07 Jun 2026 13:05:05 +0000

What is data Engineering

Data engineering is the work of building and maintaining the data pipelines that allows organizations to collect, store, process and use data effectively.
Data engineers use different tools to store data such as digital ocean, google cloud platform, azure and Aws. These Saas run on Linux Operating Systems, thus data engineers have to use the terminal to connect, manage and monitor data flows.

Linux Essentials

Data engineers use different operating systems to connect to virtual private servers (VPS). The most common method that is used - secure shell(SSH).
SSH works on windows after installation of wsl (windows subsystem for linux), however MacOs and Linux operating systems will work with it out of the box.
Since I use windows, I installed wsl, and used ssh @ip - the username and Ip were provided. The VPS had a password for security purpose and inputting it you get access to an Ubuntu Operating System.
>ssh root@159.65.222.96
root@159.65.222.96's password:

When data engineers can't access a particular server because of company restrictions, they use jump servers to connect to a different server.
To check if all the files have the same name contained in all the folders we use find -name 'file_name' as shown below.

To move files to and from a local machine to the server we use secure copy protocol (SCL)
a) Moving files from local machine to the server:

scp C:/Users/Admin/Downloads/Rental_property root@159.65.222.96 /root/<folder>

b) Moving files from server to local machine:
scp /root/<folder>/rental_property.csv C:/Users/Admin/Desktop

To open a file in the server you can use vim editor or nano editor. Once the file is open, you can edit it by clicking esc and using I for inserting. Click escape then full colon then type wq(write-quit) to permanently save the changes that have been added to the file. Adding ! to the command wq!, means that your changes will overide any changes that were previously added/currently added.
root@class:~# vim <filename>

To remove files from a folder you can use rm
rm -r <filename>

To view the contents of a file you can use more or less
root@ip~:# more <filename>

To check the first 5 lines of a file use head and to check the last 5 lines of the code use tail.
root@ip~:# head <filename>
To update the linux operating system currently running in the virtual private server we use sudo apt update

Working with databases in the server - Postgresql

Once you have access to the server, install postgres using the following code: sudo apt install postgresql postgresql-contrib -y. check the status of the server and the version of the psql. The status check type: sudo systemctl status postgresql and the version is psql --version.

To add a user to a server use:sudo adduser <user>. If the server rejects the format of the username that you want to add, use: sudo adduser --force-badname username.

To check if the user was added use more/etc/passwd. Ensure you check the last line of the list displayed.

To access the database using psql shell we access the postgres instance in the server: sudo -i -u postgres.then we use psqlcommand to access the psql shell where we can create a database.

After creating the database of your choice, the original postgres super user is the only one who has write and read privileges which makes it a challenge to connect the created database and the new user added to manage the databases.

The solution I opted for is altering the postgres password since I didn't set it up when the postgres was installed.

The command to alter postgres password in the psql shell is: ALTER USER postgres WITH PASSWORD <newpassword>.

After setting up the postgres database and schema, you need to change a few files in postgresql found in the root folder. The first file is the postgresql.conf that has the 'listenaddress' which should be uncommented after using vim to edit it. The next file to be edited is the pg_hba.conf file that has the firewall setup. Add the following configuration: 'host all all 0.0.0.0 md5' on the last line.
Restart the servers using:sudo systemctl restart postgresql. This will ensure you don't get the same fault as you saw on dbeaver.

To check if the database has data, we access the psql shell and type the following commands: \l : List all databases, \c database_name : Connect to a different database, \dt : List all tables in the current database, \du : List all the database users and their roles, \conninfo: display current connection details, \q : Exit

\l : List all databases,

\c database_name : Connect to a different database

\dt : List all tables in the current database

\du : List all the database users and their roles

\conninfo: display current connection details

Venturing into the data space

leslie angu — Sat, 30 May 2026 12:20:50 +0000

Week 1: Basic fundamentals

Day 1: Introduction to Data Analytics, Data Science, Data Engineering and AI.

Data is raw unorganized collection of facts, observations or symbols.
Types of data analytics and the data careers in this fields.

Prescriptive Analytics: What should we do about it? This is a branch of data that deals with prediction of future outcomes but also recommends the optimal course of action to take.
Descriptive Analytics: What happened? Summarizes raw historical data into easily readable metrics, charts and reports.
Predictive Analytics: What is likely to happen? Use historical data alongside statistical models and machine learning algorithms to identify trends and to forecast future probabilities.
Diagnostic Analytics: Why did it happen? Involves drilling down into historical data to identify the root causes of specific trends, anomalies, or performance dips.

Tools that were installed and their purpose in data.

DBeaver : This is a data base management tool.
Vscode: This is an intergrated development environment (IDE), this is where the coding takes place.
Python: This is the most preffered language for data analysis.
Github & Git: Git is a tool used to keep track of the changes made to a file and Github is the repository that has a copy of the files that are in the IDE.

Day 2: Dealing with DBMS & Git

I managed to connect two databases to DBeaver. The first was aiven- I had set up postgresql and the credentials were generated which was straighforward. The second was postgresql which was running locally on my pc, which required the database name - postgres, password and port: 5432

I already had a github account and it was connected to the git on my local machine. I managed to learn a few more git commands that I hadn't used before like git status.