Forem: susan njeri

Connecting Power BI to a SQL Database

susan njeri — Fri, 27 Mar 2026 09:16:24 +0000

Introduction: Power BI and the Role of SQL Databases

Power BI is used across industries retail companies use it to track product performance, healthcare organizations use it to monitor patient outcomes, financial firms use it for risk reporting, and logistics companies use it to optimize supply chains. It is one of the most widely deployed analytics tools in the world, and proficiency with it is one of the most sought after skills for data analysts and business intelligence professionals.

Connecting Power BI to a Local PostgreSQL Database

Before working with cloud databases, it is valuable to understand how to connect Power BI to a local PostgreSQL instance running on your own machine.
The process begins with opening Power BI Desktop. On the Home ribbon, you will see the Get Data button this is the entry point for connecting to any data source.

Step 1: Open Power BI Desktop and Click Get Data

When you launch Power BI Desktop, you are presented with the welcome screen. To begin connecting to your database, click the Get Data button in the Home ribbon at the op of the screen.

Step 2: Select PostgreSQL Database from the Get Data Dialog

After clicking Get Data, a dialog window appears listing all available data connectors. Power BI supports over 100 data sources from Excel files and CSVs to enterprise databases and cloud APIs. To connect to PostgreSQL, click on the Database category in the left panel, then scroll through the list and select PostgreSQL Database. Click Connect to proceed.

Step 3: Enter the Server Name and Database Details

Once you select PostgreSQL Database and click Connect, a connection dialog appears. This dialog requires two key pieces of information: the server name and the database name

Install the PostgreSQL ODBC Driver (Required for Both)

Go to: https://www.postgresql.org/ftp/odbc/versions/msi/
Download psqlODBC — get the x64 (64-bit) version
Install it like a normal Windows program Without this driver, Power BI won't even show PostgreSQL as a data source option.

Connecting to Localhost PostgreSQL

The database is connected and runs locally on my machine, the server is usually localhost.The database name will be postgres and you will see when the connection starts.The graphical tool, pg4Admin 4 is used in managing and development of the PostgreSQL databases.

The server name host will be – localhost
username – postgres

Connecting to Aiven PostgreSQL

While a local database is ideal for development, real world analytics almost always involves cloud databases. Cloud hosted databases offer critical advantages: they are accessible from anywhere, they scale automatically as data grows, they include built in backup and recovery, and they support multiple simultaneous connections from different tools and team members.

Connecting Power BI to Aiven PostgreSQL follows a similar process to a local connection, with one important addition: SSL certificate authentication.

Step 1: Get Your Aiven Connection Details
Log in to console.aiven.io, open your PostgreSQL service, and note:
• Host — pg-3ae2ac09-susanjeri4717-1f9e.c.aivencloud.com
• Port — 25837
• Database — defaultdb
• Username — avnadmin
• Password — from the Aiven console
• SSL Certificate — download the ca.pem file from the service page

Step 2: In Power BI Desktop

Home → Get Data → More… → PostgreSQL → Connect
Fill in the server field as:
Enter your Database name
Click OK
Choose Database authentication → enter Aiven username & password

Step 3: Handle SSL
Power BI's PostgreSQL connector supports SSL but may not ask you for the certificate directly in the GUI. If the connection fails with an SSL error, you have two options: using an ODBC DSN connection or Setting SSL mode via connection. We will use option A which is;

Use an ODBC DSN connection :

Open ODBC Data Source Administrator

Add a new System DSN → choose PostgreSQL Unicode

Fill in host, port, database, credentials — and point it to your ca.pem file under SSL settings

Back in Power BI → Get Data → ODBC → select your DSN

You will also see the Data Connectivity mode options: Import and DirectQuery. Import copies the data into Power BI's in memory storage, while DirectQuery sends live queries to the database every time a visual refreshes. For learning and development, Import mode is the best choice.

Step 4: Enter Your Database Credentials

After clicking OK, Power BI will prompt you to enter your credentials. Select the Database authentication tab, then enter your PostgreSQL username, commonly postgres, and your password. Power BI will store these credentials securely so you do not need to re enter them each time you connect.

Step 5: Select Tables in the Navigator

After successful authentication, the Navigator window opens. This window displays a tree structure of your database, showing all available schemas and tables. Expand the assignment schema to see the four tables from your database: customers, products, sales, and inventory.

Check the box next to each table you want to load into Power BI. The right panel shows a preview of the data in the selected table, allowing you to verify that you are connecting to the correct data before loading it. Once you have selected all four tables, click Load to import the data into Power BI.

After clicking Load, Power BI imports all selected tables into its in memory data model. You will see the data loading progress in the status bar at the bottom of the screen. Once complete, the tables are available in the Fields panel on the right side of the report canvas.

Loading Tables and Creating Relationships in Power BI

Once the four tables from the assignment schema are loaded customers, products, sales, and inventory they appear in Power BI's Fields panel.

Understanding the Four Tables

Before creating relationships, it helps to understand what each table contains and how they are logically connected:
• customers; Contains one row per customer, with details like customer id, first name, last name, email, and membership status. The customer id is the unique identifier (primary key).
• products; Contains one row per product, with product id, product name, category, price, supplier, and stock quantity. The product id is the primary key.
• sales; The central transactional table. Each row is a sale, with sale id as the primary key. Crucially, it also contains customer id and product id as foreign keys these are the columns that link each sale to a specific customer and product.
• inventory; A supporting table that tracks stock levels per product, linked to the products table via product id.

Creating Relationships in the Model View

To define relationships, switch to the Model View by clicking the relationship icon in the left sidebar . Power BI may automatically detect some relationships based on matching column names.

Conclusion

Power BI has become one of the most essential tools in a data analyst's toolkit, transforming raw database records into interactive dashboards and visual reports that drive business decisions. Its ability to connect directly to local databases like PostgreSQL is what makes it particularly powerful for analysts rather than exporting data manually to spreadsheets and risking outdated figures, a live local connection means your reports are always pulling from the actual source of truth.
For developers and students especially, working against a localhost database allows you to build, test, and refine reports in a fast, controlled environment without incurring cloud costs or depending on internet connectivity. This direct line between your database and your visualisation layer is what bridges the gap between raw SQL data and the polished, decision-ready insights that stakeholders actually consume.

JOINS AND WINDOW FUNCTIONS IN SQL

susan njeri — Mon, 02 Mar 2026 21:10:02 +0000

The JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Here are the different types of JOINs in SQL:

(INNER) JOIN: Returns only rows that have matching values in both tables
LEFT (OUTER) JOIN: Returns all rows from the left table, and only the matched rows from the right table
RIGHT (OUTER) JOIN: Returns all rows from the right table, and only the matched rows from the left table
FULL (OUTER) JOIN: Returns all rows when there is a match in either the left or right table

SQL INNER JOIN

INNER JOIN is used to retrieve rows where matching values exist in both tables. Combining records based on a related column. Returning only matching rows from both tables. Excluding non-matching data from the result set. Ensuring accurate data relationships between tables
Only returns rows where there's a match in BOTH tables. If a customer has no orders, they don't show up. If an order has no customer, it doesn't show up either.

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

The result of this will be; only the customers who placed an order will appear

LEFT(OUTER) JOIN

LEFT JOIN is used to retrieve all rows from the left table and matching rows from the right table. Returns ALL rows from the left table, plus any matching rows from the right table. If there's no match on the right side, you still get the left row just with NULL in the right columns.

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

The results of this is that the customers will appear and those that will have no order will have a NULL under 'product'.

RIGHT (OUTER) JOIN

RIGHT JOIN is used to retrieve all rows from the right table and the matching rows from the left table. It helps in:

Returns ALL rows from the right table, and matching rows from the left. In practice, most people rewrite this as a LEFT JOIN by swapping the table order it's easier to reason about.

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

The results; All orders will appear, even after deletion of customer from system.

FULL (OUTER) JOIN

FULL JOIN is used to combine the results of both LEFT JOIN and RIGHT JOIN. Returns ALL rows from BOTH tables. Where there's no match, you get NULLs on the missing side. Use this when you need a complete picture of both datasets regardless of matches.

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

Window Functions in SQL

A window function lets you perform calculations across a group of rows while still keeping every individual row in your result.

The Basic Syntax
The window function follows this pattern:

function_name()  OVER  (
    PARTITION BY  column - group rows (optional)
    ORDER BY      column -set row order  (optional)
)

PARTITION BY is like GROUP BY it resets the calculation per group. ORDER BY defines the order rows are processed within that group.

The 5 Most Useful Window Functions

ROW_NUMBER() Number Your Rows

Assigns a unique sequential number to each row within a partition. Restarts at 1 for each new group.

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

RANK() Handle Ties Fairly

Like ROW_NUMBER() but handles ties: two employees with equal salary both get rank 1, and the next gets rank 3 (skips 2). Use DENSE_RANK() if you don't want the gap.

SELECT name, score,
  RANK()        OVER (ORDER BY score DESC) AS rank_with_gap,
  DENSE_RANK()  OVER (ORDER BY score DESC) AS rank_no_gap
FROM students;

SUM() OVER Running Totals

Calculates a cumulative sum as it moves down the rows. One of the most used window functions in business analytics.

SELECT sale_date, amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

Result: each row shows the day's amount AND a growing total. By the last row it equals the grand total.

LAG() Look at the Previous Row

Grabs a value from a previous row without needing a selfjoin. Perfect for month-over-month or day-over-day comparisons.

SELECT month, revenue,
  LAG(revenue, 1) OVER (ORDER BY month)              AS prev_month,
  revenue LAG(revenue, 1) OVER (ORDER BY month)    AS change
FROM monthly_sales;

LEAD() does the opposite it looks at the next row. Useful for forecasting or "days until next event" calculations.

AVG() OVER Compare to Group Average

Shows each row's value alongside the average for its group something impossible with plain GROUP BY.

SELECT name, department, salary,
  AVG(salary) OVER (PARTITION BY department)  AS dept_avg,
  salary AVG(salary) OVER (PARTITION BY department) AS vs_avg
FROM employees;

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

susan njeri — Mon, 09 Feb 2026 10:35:28 +0000

Organizations collect massive amounts of information from various sources sales transactions, customer interactions, operational metrics, and more. However, raw data alone doesn't drive decisions. The real value emerges when this data is transformed into clear, actionable insights that guide strategic choices. Power BI has become an essential tool in this transformation process, enabling analysts to bridge the gap between numbers and meaningful business outcomes.

The Data Preparation
The first challenge in any analytics project is dealing with unstructured data. Raw datasets often contain duplicates, missing values, inconsistent formatting, and irrelevant columns. Power BI's Power Query editor addresses these issues, allowing analysts to clean and reshape data before it ever reaches a dashboard.

Building a Solid Foundation with Data Modeling
Once data is cleaned, the next step involves creating relationships between different data tables. Power BI's data modeling capabilities allow analysts to establish connections that reflect real world business relationships. For instance, linking sales transactions to customer information, products, and time dimensions creates a cohesive structure that supports flexible analysis.

A well-designed data model using a star schema approach with fact tables containing transactional data and dimension tables providing context ensures optimal performance and makes complex calculations more manageable. This foundation enables users to slice and dice data from multiple perspectives without rebuilding queries from scratch.

Adding Intelligence with DAX
Data Analysis Expressions (DAX) is where Power BI truly shines. This formula language allows analysts to create custom calculations that go beyond simple sums and averages. DAX enables sophisticated metrics like year over year growth rates, running totals, market share calculations, and customer lifetime value.

The power of DAX lies in its context awareness. Calculations automatically adjust based on how users interact with visuals whether they're viewing company wide performance or drilling down into a specific region or product category. This dynamic behavior transforms static numbers into interactive insights that respond to business questions in real time.

Designing Dashboards
Effective visualization is about more than choosing attractive charts. The best Power BI dashboards guide users through a narrative, starting with high-level KPIs that answer "how are we doing" before enabling deeper exploration into "why" and "what should we do about it"

Strategic use of visual elements KPI cards for quick status checks, trend lines for pattern recognition, and interactive filters for focused analysis helps stakeholders grasp complex information quickly. The goal is clarity, not complexity. Each visual should serve a purpose and contribute to understanding the bigger picture.

Transforming Raw Data into Actionable Insights with Power BI

susan njeri — Mon, 09 Feb 2026 10:32:47 +0000

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

susan njeri — Mon, 09 Feb 2026 10:26:46 +0000

The Data Preparation

The first challenge in any analytics project is dealing with unstructured data. Raw datasets often contain duplicates, missing values, inconsistent formatting, and irrelevant columns. Power BI's Power Query editor addresses these issues, allowing analysts to clean and reshape data before it ever reaches a dashboard.

Building a Solid Foundation with Data Modeling

Once data is cleaned, the next step involves creating relationships between different data tables. Power BI's data modeling capabilities allow analysts to establish connections that reflect real world business relationships. For instance, linking sales transactions to customer information, products, and time dimensions creates a cohesive structure that supports flexible analysis.

Adding Intelligence with DAX

Data Analysis Expressions (DAX) is where Power BI truly shines. This formula language allows analysts to create custom calculations that go beyond simple sums and averages. DAX enables sophisticated metrics like year over year growth rates, running totals, market share calculations, and customer lifetime value.

Designing Dashboards

Effective visualization is about more than choosing attractive charts. The best Power BI dashboards guide users through a narrative, starting with high-level KPIs that answer "how are we doing" before enabling deeper exploration into "why" and "what should we do about it"

Understanding Schemas and Data Modelling in Power BI: Building the Foundation for Performance and Accuracy

susan njeri — Mon, 02 Feb 2026 13:19:44 +0000

Power BI has revolutionized how organizations connect to data, transform it, and create interactive visualizations. However, the true power of Power BI lies not just in its stunning visuals, but in what happens behind the scenes the data model. A well-designed data model is the backbone of every successful Power BI solution, directly impacting performance, accuracy, and user experience.

Schema vs. Data Model: Understanding the Distinction

Before diving into the technical details, it's important to understand the relationship between schemas and data models. A schema is the blueprint or conceptual design that defines how your data will be structured for analysis. Think of it as the architectural plan for your data warehouse. A data model, on the other hand, is the actual implementation of that schema within Power BI the tables, relationships, and calculations you create.

In simple terms, the schema is your plan, and the data model is what you build based on that plan.

Essential Terminology: Building Your Data Modeling Vocabulary

Fact Tables

Fact tables are the heart of your data model, storing quantitative, measurable business data that changes frequently. These tables contain metrics like sales revenue, quantities sold, transaction amounts, or order counts. They typically hold the largest volume of data in your model and represent the "business events" you want to analyze.

Key characteristics of fact tables include numeric measure columns for calculations and foreign key columns that link to dimension tables. For example, a Sales fact table might contain OrderID, CustomerID, ProductID, DateID, Quantity, and Revenue.

Dimension Tables

Dimension tables store descriptive, contextual information that helps you slice, filter, and group your fact data. These tables are relatively static and contain attributes used for filtering and organizing your analysis. Common examples include Customer, Product, Date, and Geography tables.

Dimension tables contain a primary key column that uniquely identifies each record, along with descriptive attributes. A Customer dimension might include CustomerID, CustomerName, Email, City, and Country.

Normalized vs. Denormalized Data

Normalized data is structured to minimize redundancy by splitting information across multiple related tables. Each piece of information is stored only once, reducing duplication. Denormalized data combines related information into fewer tables, even if it means repeating data across rows. While this creates redundancy, it can improve query performance by reducing the number of joins required.

Power BI's data modeling approach strategically balances these concepts to optimize for both storage efficiency and query performance.

Star Schema: The Gold Standard for Power BI

The star schema is the most widely recommended approach for Power BI data modeling, and for good reason. In this design, a central fact table is surrounded by multiple dimension tables, creating a visual structure that resembles a star.

Structure of a Star Schema

In a star schema, the fact table sits at the center containing all your measurable data and foreign keys. Surrounding it are dimension tables that are denormalized meaning all attributes for a dimension are contained in a single table, even if it creates some redundancy. Each dimension table connects directly to the fact table through a one-to-many relationship.

For example, imagine a retail sales model where the central FactSales table connects directly to DimCustomer, DimProduct, DimDate, and DimStore tables. Each dimension contains all relevant attributes: the Product dimension includes ProductID, ProductName, Category, Subcategory, and Brand all in one table.

Why Star Schema Excels in Power BI

Performance Optimization: Power BI's VertiPaq engine is specifically optimized for star schema designs. The engine uses columnar storage and advanced compression techniques that work best with flat dimension tables. Fewer relationships mean faster filter propagation, and the reduced number of joins significantly improves query performance. Organizations typically see queries run 5-10 times faster with properly implemented star schemas compared to poorly structured models.

Simplicity and Usability: Star schemas are intuitive for business users and report developers. The straightforward structure makes it easy to understand how data relates, reducing the learning curve for new team members. When creating reports, users can easily drag and drop fields without worrying about complex join paths.

DAX Efficiency: Data Analysis Expressions (DAX) formulas work more efficiently in star schema models. Measures and calculated columns perform better because filter context flows predictably from dimensions to facts. This predictability also makes DAX code easier to write and debug.

Microsoft's Recommendation: Microsoft explicitly recommends star schemas for Power BI semantic models as a best practice. The entire Power BI ecosystem from the VertiPaq engine to DAX calculations is designed with star schema principles in mind.

Snowflake Schema: When Normalization Extends Further

A snowflake schema extends the star schema concept by normalizing dimension tables into multiple related sub tables. Instead of keeping all dimension attributes in a single table, dimensions are broken down into smaller, more granular tables.

Structure of a Snowflake Schema

In a snowflake schema, the fact table remains at the center, but dimension tables are further divided. For instance, a Product dimension might be split into Product, ProductSubcategory, and ProductCategory tables, each connected through relationships. This creates "branches" that extend from the central fact table, resembling a snowflake.

When to Consider Snowflake Schema

Highly Normalized Source Data: If your source data warehouse already uses a snowflake design, it might be easier to import it as-is rather than denormalizing during the ETL process.

Complex Hierarchies: When you have intricate hierarchical relationships across dimensions that genuinely benefit from separation into multiple tables.

Data Integrity and Storage: In scenarios where maintaining normalized structures is critical for data governance, or when you need to minimize storage in very large dimensions ,though Power BI's compression often makes this less relevant.

Detailed, Enterprise-Scale Models: Large organizations with complex data warehouses might use snowflake schemas where normalization is required upstream.

Performance Considerations

While snowflake schemas can work in Power BI, they come with performance trade-offs. Power BI must navigate longer relationship chains with more joins, potentially slowing query performance. The model becomes more complex with additional tables and relationships, which can confuse users and make maintenance harder. The VertiPaq engine's compression benefits are reduced because normalization limits the effectiveness of columnar compression.

Best Practice: Even when working with snowflaked source data, many Power BI developers flatten the structure during data transformation in Power Query to create a star schema in the final model.

Relationships in Power BI: Connecting Your Data

Relationships are the connections between tables based on common columns. They enable data from multiple sources to flow together seamlessly, ensuring that filters, slicers, and visuals interact correctly across your entire model.

Types of Relationships

One-to-Many (1:M): This is the most common and recommended relationship type. One record in the dimension table relates to many records in the fact table. For example, one Customer can have many Orders. In a well-designed star schema, all relationships from dimensions to facts should be one-to-many.

One-to-One (1:1): Each record in one table matches exactly one record in another table. This is rare and often suggests that the tables should be merged. One-to-one relationships might be used for splitting very wide tables or for security purposes.

Many-to-Many (M:M): Multiple records in one table relate to multiple records in another. While Power BI supports this, it should be used cautiously as it can create ambiguous filter paths and performance issues. Many-to-many relationships often require bridge tables and careful model design.

Creating Relationships

Power BI offers two methods for establishing relationships:

Auto-detect: When loading multiple tables, Power BI automatically attempts to find and create relationships based on matching column names and data types. While convenient, always verify these auto-detected relationships to ensure accuracy.

Manual Creation: For precise control, create relationships manually by going to Model View, selecting Manage Relationships > New, choosing the tables and columns, and setting the appropriate cardinality and cross-filter direction.

Understanding Cardinality

Cardinality defines the nature of the relationship between tables how many rows in one table relate to rows in another. Setting the wrong cardinality can lead to duplicated data, incorrect aggregations, and misleading reports. Power BI usually auto detects cardinality correctly, but it's crucial to verify it matches your data structure.

Cross-Filter Direction: Controlling Filter Flow

Cross-filter direction determines how filters propagate between related tables. This is one of the most important yet often misunderstood aspects of Power BI relationships.

Single Direction (Default and Recommended): Filters flow in one direction only, typically from the "one" side of a one to many relationship to the "many" side. For example, selecting a customer filters their orders, but selecting an order doesn't filter the customer table. This is the default behavior and what Power BI's engine is optimized for.

Both (Bi-directional): Filters can flow in both directions between tables. While this seems flexible, bi-directional filtering should be used sparingly. It can create ambiguous filter paths, introduce circular dependencies, negatively impact performance, and make the model harder to understand and debug.

When to Use Bi-directional Filtering: Bi-directional relationships are appropriate in specific scenarios like many to many relationships using bridge tables, certain Row-Level Security (RLS) implementations, or when you need to filter one fact table by another through a shared dimension. Even in these cases, consider using the CROSSFILTER DAX function to enable bi-directional filtering only for specific calculations rather than permanently on the relationship.

Best Practice: Start with single-direction filtering and only introduce bi-directional filtering when there's a clear, well-understood requirement that cannot be met any other way.

Active vs. Inactive Relationships

Power BI allows only one active relationship between any two tables for automatic filter propagation. However, you can create multiple inactive relationships that can be activated in specific DAX calculations using the USERELATIONSHIP function. This is particularly useful for role-playing dimensions like Date tables that might relate to a fact table multiple times (OrderDate, ShipDate, DeliveryDate).

Why Good Data Modeling Is Critical

The quality of your data model directly determines the success of your Power BI solution. Here's why investing time in proper data modeling is essential:

Performance Optimization

A well-designed data model particularly using a star schema dramatically improves query performance. Fewer joins reduce the computational work required for each query. The VertiPaq engine can compress and cache data more efficiently in star schemas. Filter propagation happens faster through simple, single-direction relationships. Dashboards and reports load in seconds instead of minutes, transforming the user experience.

Real-world results show that organizations moving from poorly structured models to optimized star schemas often see 5-10x performance improvements. When reports load in under 2 seconds instead of 15-30 seconds, it fundamentally changes how users interact with data.

Accurate and Reliable Reporting

Proper relationships between fact and dimension tables ensure that calculations aggregate correctly. Filters and slicers work as expected across all visuals, and data integrity is maintained throughout the model. Incorrect relationships or poorly designed models lead to double counting, missing data, or misleading totals errors that can result in costly business decisions.

Scalability and Maintainability

Structured models built on solid design principles are far easier to extend as business needs grow. When you need to add new fact tables, dimensions, or measures, a well designed star schema makes this straightforward. Clean models support reusable DAX measures and consistent calculation logic across reports. They're also easier to troubleshoot when issues arise and simpler for new team members to understand and work with.

Enhanced User Experience

A clear, intuitive data model makes it easier for report authors and business users to work with the data. Users can confidently create their own reports without constantly seeking help or making errors. The Data pane shows a logical, understandable structure rather than a confusing web of tables. This increases adoption and empowers self-service analytics across the organization.

Efficient Resource Utilization

Good modeling practices reduce memory consumption through appropriate data types, eliminating unnecessary columns, and using measures instead of calculated columns where appropriate. Optimized models refresh faster, consume less capacity, and cost less to run in Power BI Premium or Fabric.

Alignment with Best Practices

Following Microsoft's recommended practices ensures you're working with the platform as intended rather than against it. Your models will be more compatible with future Power BI features and updates. You'll be able to leverage the full power of DAX and the VertiPaq engine, and your skills will be transferable across different Power BI projects and organizations.

Practical Tips for Building Better Models

Start with Star Schema: Make the star schema your default approach. Only deviate when you have specific, well understood requirements that truly demand a different structure.

Use Power Query for Transformation: Leverage Power Query to transform your source data into the desired schema before loading it into the model. Denormalize snowflaked sources, clean and filter data, and create proper key columns.

Establish a Date Table: Always create a dedicated Date dimension table with all date related attributes. Mark it as a date table in Power BI and use it for all time-intelligence calculations.

Choose Appropriate Data Types: Select the most efficient data type for each column. Use integers instead of text for ID columns, use Date instead of DateTime when time isn't needed, and avoid unnecessary precision in decimal columns.

Hide Unnecessary Fields: Hide foreign key columns and other technical fields from the report view to simplify the user experience and prevent confusion.

Use Measures, Not Calculated Columns: Prefer measures for calculations as they're computed at query time, reducing model size and improving flexibility. Only use calculated columns when the result needs to be used for slicing, filtering, or in relationships.

Document Your Model: Add descriptions to tables, columns, and measures to help others (and your future self) understand the model's structure and logic.

Test and Monitor Performance: Use Performance Analyzer in Power BI Desktop to identify bottlenecks. Regularly review query performance, refresh times, and model size as your solution evolves.

Introduction to MS Excel for Data Analytics - A Beginner's Guide

susan njeri — Sun, 25 Jan 2026 12:22:22 +0000

So I recently started learning data analytics, and everyone kept telling me that i needed to learn excel first. I'll be honest, at first I thought Excel was just for making boring spreadsheets.

Excel is actually super powerful for analyzing data, and the best partis that you don't need to know programming to use it. In this guide, I'm going to walk you through everything I learned about using Excel for basic data analysis.

Before we dive into Excel, let me explain what I mean by "data analytics" in simple terms.

Data analytics is basically looking at a bunch of information (data) and trying to find patterns, answers, or insights. For example:

Which product sells the most?
What's the average score of students in a class?
How do sales change month by month?
Excel helps us answer these questions without doing everything manually!

Part 1: Getting Started with My Data

Opening the Dataset

First thing I did was open Excel and load my HR dataset file.

I saw columns with data about employees: Employee ID, Age, Department, Gender, Job Role, Monthly Income, Years at Company, and more. Honestly, it was a bit overwhelming at first!

Basic Navigation

I learned that:

Rows go horizontally (each row = one employee)
Columns go vertically (each column = one type of information)
Cells are the individual boxes where rows and columns meet

Freezing Headers

One thing that annoyed me was scrolling down and forgetting which column was which! So I froze the top row:

Clicked on any row
View tab → Freeze Panes → Freeze Top Row

Now when I scroll, the headers stay visible!

Part 2: Sorting and Filtering Data

Sorting by Salary

I wanted to see who had the highest salary:

Clicked anywhere in my data
Data tab → Sort
Sort by: MonthlyIncome, Order: Largest to Smallest

Using Filters

To see only specific data, I turned on filters:

Data tab → Filter
Clicked the dropdown arrow in Department column
Selected only "Sales"

Part 3: Basic Formulas

Calculating Average Salary

I clicked on an empty cell and typed:

=AVERAGE(E2:E877)

This calculated the average salary instantly - around Ksh. 74066.533

Other Useful Formulas

Counting employees:

=COUNT(A2:A877)

Conditional Counting

To count how many employees left the company:

=COUNTIF(J2:J1001,"Yes")

To sum salaries for just the Sales department:

=SUMIF(D2:D1001,"Sales",I2:I1001)

[Boost]

susan njeri — Fri, 23 Jan 2026 13:50:10 +0000

My First Time Using Git and GitHub - A Complete Beginner's Guide

susan njeri ・ Jan 17

#github #git #datascience #data

My First Time Using Git and GitHub - A Complete Beginner's Guide

susan njeri — Sat, 17 Jan 2026 18:36:25 +0000

So I just finished learning how to use Git and GitHub, and wow, it was a journey! A few weeks ago, I had no idea what version control even meant. Now I'm pushing code to GitHub like it's no big deal (okay, I still get a little excited every time it works ).

I wanted to write this guide while everything is still fresh in my mind because I remember how confusing it all seemed at first. If you're just starting out like I was, hopefully this helps!

Quick explanation of what these things are:

Git = a tool that tracks all the changes you make to your code. It's like having unlimited undo buttons!
GitHub = a website where you can store your code online and share it with others

That's literally all I knew when I started. Let's dive in!

What You Need

Just make sure you have:

A computer (I'm on Windows but I'll try to cover Mac and Linux too)
A GitHub account - go to github.com and sign up, it's free
Don't worry if you're not super comfortable with the command line, I wasn't either

Step 1: Installing Git Bash

Okay, so first thing - I needed to install Git on my computer.

If You're on Windows (like me)

I went to git-scm.com
Clicked the big "Download for Windows" button
Ran the installer (it's like installing any other program)
Here's the thing - there are a LOT of options during installation. I honestly just kept clicking "Next" with the default settings and everything worked fine
- One thing I made sure to check: "Git Bash Here" - this lets you right-click in any folder and open Git Bash there, super convenient!
Clicked through the rest and finished

If You're on Mac

I asked my friend who uses Mac and apparently Git might already be installed! Open Terminal and type:

git --version

If it's not there, Mac will ask if you want to install it. Pretty straightforward from what I understand.

If You're on Linux

My friend who uses Ubuntu told me to run:

sudo apt-get update
sudo apt-get install git

For other Linux versions, you might need different commands but I think the idea is the same.

Did It Work?

Open Git Bash (on Windows I just searched for "Git Bash" in the start menu) and type:

git --version

If you see something like git version 2.40.0, you're good! If not... uh... maybe try installing again?

Step 2: Connecting Git to GitHub

This part confused me at first but it's actually not too bad.

Tell Git Who You Are

Git needs to know your name and email so it can tag your work. I opened Git Bash and typed:

git config --global user.name "Your Name"
git config --global user.email "your.email@example.com"

Obviously use YOUR actual name and email . And make sure the email matches whatever you used for GitHub!

To check if it worked:

git config --global --list

Setting Up SSH Keys (This Part Seemed Scary But Wasn't)

So apparently you need something called an SSH key to connect securely to GitHub. I watched a YouTube video to understand this better, but here's what I did:

Generate the key:

ssh-keygen -t ed25519 -C "your.email@example.com"

It asked me where to save it - I just hit Enter to use the default location.

Then it asked for a passphrase. I added one for security but you can just hit Enter twice to skip if you want.

Start something called the SSH agent:

eval "$(ssh-agent -s)"

I don't fully understand what this does but it needs to be running

Add the key:

ssh-add ~/.ssh/id_ed25519

Copy the key:

cat ~/.ssh/id_ed25519.pub

This spits out a bunch of random characters starting with ssh-ed25519. I copied THE WHOLE THING.

Put it on GitHub:

Went to GitHub
Clicked my profile picture in the top right → Settings
Found "SSH and GPG keys" on the left side
Clicked "New SSH key"
Gave it a name (I called mine "My Laptop")
Pasted that long string of characters
Clicked "Add SSH key"

Test if it worked:

ssh -T git@github.com

I got a message saying "Hi [my username]! You've successfully authenticated" and I literally did a little celebration 🎉

Step 3: Understanding What Git Actually Does

Before I started using commands, I tried to understand WHY I needed this.

You know how you save files like:

my_code.py
my_code_v2.py
my_code_final.py
my_code_final_FINAL.py
my_code_final_FINAL_I_SWEAR.py

Yeah... Git makes this unnecessary. Instead, you keep one file and Git remembers every version you save. You can go back to ANY previous version whenever you want.

Words I Had to Learn

Repository (or repo): Basically just a folder that Git is watching
Commit: Saving a snapshot of your project at a moment in time
Branch: Like a copy of your project where you can try stuff without breaking the original (I haven't really used this yet though)
Remote: Your project stored online on GitHub

Step 4: Making My First Repository

Okay here's where it got real. Time to actually USE Git!

Creating a Project

I made a new folder for my project:

mkdir my-first-project
cd my-first-project

Then I initialized Git (basically telling Git to start watching this folder):

git init

That's it! Git is now tracking this folder. There's a hidden .git folder created but you don't really need to touch it.

Checking the Status

I learned this command is super important:

git status

Use this ALL THE TIME. It tells you what's going on with your files.

Making a File

I created a simple README file:

echo "# My First Project" > README.md

Then checked status again:

git status

Git saw the file but said it was "untracked" - meaning Git noticed it but isn't saving its history yet.

Adding Files (Staging)

To tell Git to track the file:

git add README.md

Or if you want to add everything:

git add .

The . means "everything in this folder".

Checked status again:

git status

Now it said the file was "staged" and ready to commit.

My First Commit!

This is like taking a snapshot:

git commit -m "Add README file"

The -m part is for adding a message. ALWAYS add a message explaining what you did! Future you will thank you.

Good messages:

"Add login feature"
"Fix typo in homepage"
"Update installation steps"

Bad messages:

"update"
"fixed stuff"
"asdfjkl"

Viewing History

To see all your commits:

git log

Or for a cleaner version:

git log --oneline

Pretty cool seeing your project history like this!

Step 5: Putting My Code on GitHub!

This was the part I was most excited about - making my code visible online!

Creating a GitHub Repository

Went to GitHub
Clicked the "+" button in the top right
Clicked "New repository"
Named it my-first-project (same as my local folder)
Did NOT check "Initialize with README" because I already have one
Clicked "Create repository"

Connecting Everything

GitHub showed me some commands. I copied the SSH URL (the one starting with git@github.com).

Then I ran:

git remote add origin git@github.com:YOUR_USERNAME/my-first-project.git

The word "origin" is just a nickname for this GitHub repository.

Double-checked it worked:

git remote -v

Pushing My Code Online

This was the moment of truth:

git branch -M main
git push -u origin main

What these commands do:

git branch -M main - names your main branch "main"
git push -u origin main - sends your code to GitHub and remembers this as the default place to push

I refreshed GitHub and SAW MY CODE ONLINE. I'm not gonna lie, I genuinely did a dance because of how excited I was.

Step 6: The Daily Workflow

Now that everything was set up, here's what I do when I'm actually coding:

1. Make some changes to my files

echo "Learning Git is easier than I thought!" >> README.md

2. Check what changed

git status

Or see the actual differences:

git diff

This shows you exactly what lines changed. Super useful!

3. Add and commit

git add .
git commit -m "Add my thoughts to README"

4. Push to GitHub

git push

Since I set up the default earlier, I can just type git push now.

Step 7: Getting Changes FROM GitHub

This is important if you're working on multiple computers or with other people.

I Tested This By Editing on GitHub

Went to my repository on GitHub
Clicked on README.md
Clicked the pencil icon to edit
Added a line: "This was edited directly on GitHub!"
Scrolled down and clicked "Commit changes"

Pulling Those Changes

Back in Git Bash:

git pull

Checked my file:

cat README.md

And there was the line I added on GitHub! Magic!

Step 8: Commands I Use All The Time

I literally keep this list open in a notepad:

# Starting stuff
git init                    # Start tracking a folder
git clone <url>             # Download someone else's repository

# Daily workflow
git status                  # What's changed?
git add .                   # Stage everything
git commit -m "message"     # Save a snapshot
git push                    # Send to GitHub
git pull                    # Get changes from GitHub

# Checking things
git log --oneline           # See commit history
git diff                    # What changed?

# When I mess up
git restore <file>          # Undo changes to a file
git restore --staged <file> # Unstage a file

Step 9: Mistakes I Made (So You Don't Have To)

Mistake #1: Forgot to pull before pushing
I got an error saying my push was rejected. Turns out someone else had pushed changes. Solution: Always git pull before git push!

Mistake #2: Terrible commit messages
My first few commits were like "updated file" and "changes". Now I can't remember what I did! Write clear messages!

Mistake #3: Added sensitive files
I almost committed a file with my API key in it . Use .gitignore files to tell Git to ignore certain files.

Mistake #4: Panicked when I saw a merge conflict
These happen when Git can't automatically combine changes. It's scary at first but not the end of the world. Git marks the conflicts in your files and you just need to choose which version to keep.

What I'm Learning Next

Now that I've got the basics down, I want to learn:

How to use branches properly (I keep hearing about them)
What pull requests are and how to do code reviews
More advanced Git commands
How to contribute to open source projects

Git was intimidating at first. The commands felt weird, and I didn't understand why I needed all this complexity just to save my code.
Start small. Make a repository. Practice the basic commands: add, commit, push, pull. Don't worry about advanced stuff yet. I've been using Git for a few weeks and I still only use like 5 commands regularly!

You've got this! And hey, if I figured it out, anyone can