DEV Community: Mercy Siele

Mastering Advanced PostgreSQL: A Comprehensive Guide to Hospital Data Intelligence

Mercy Siele — Sun, 18 Oct 2026 15:11:19 +0000

Introduction

Modern healthcare systems generate vast amounts of relational data daily, from patient admissions and doctor shift schedules to treatment costs and departmental expenses. Transforming this raw data into meaningful clinical and financial insights requires more than basic SQL queries. Database administrators and data analysts must leverage advanced querying techniques to uncover patterns, improve operational efficiency, and support data-driven decision-making.

This article explores four essential PostgreSQL concepts: Window Functions, Joins, Common Table Expressions (CTEs), and Subqueries. Using a realistic Hospital Management Dataset, each concept is demonstrated through practical SQL examples that illustrate how to analyze healthcare data and extract actionable insights.Each concept is explained using practical scenarios based on the hospital management database, which contains the following tables: doctors, department, diagnosis, wards, patients, procedures, and visits.

SQL Window Functions

SQL window functions perform calculations across a set of table rows that are related to the current row, without collapsing the result set into a single summary row. Unlike standard GROUP BY operations which merge rows to give you a single total, window functions preserve the identity of each individual row while appending the calculated aggregate or analytical value right next to it.

Understanding `OVER()`, `PARTITION BY`, `ORDER BY`, and window frames

These elements define how a window function evaluates rows.

The `OVER()` Clause

The OVER() clause specifies the window of rows used by a window function. Depending on the function and query, it can contain a partition, an ordering, and a window frame.

Result:

What it does: Calculates the average bill across all visits and displays that same average alongside every visit.
Why it is useful: Analysts can compare individual bills with the overall hospital average without losing the individual visit records.

The `PARTITION BY` Clause

PARTITION BY divides rows into groups for a window calculation without collapsing them into summary rows.

Result:

What it does: Calculates the average bill separately for each patient.
Result: Each visit remains in the output, with an additional column showing that patient's average bill across their recorded visits.
If you had used a standard GROUP BY patient_id, PostgreSQL would have collapsed your rows down to just one row per patient, losing the individual visit_id and total_bill_kes details.

The `ORDER BY` Clause

Within OVER(), ORDER BY determines the sequence in which rows are evaluated for functions such as ROW_NUMBER(), LAG(), and running totals.

Result:

What it does: Numbers each patient's visits from earliest to latest. visit_id provides a tie-breaker when visits have the same date.
Result: Each patient's first recorded visit receives number 1, the second receives number 2, and so on.

Window Frames

A window frame determines which rows SQL should include when performing a calculation for the current row. It is commonly used with functions such as SUM() and AVG() to calculate running totals and moving averages.
Think of it this way: as SQL moves through hospital visits one row at a time, a window frame tells it which rows to include in the calculation for each visit.
Example: Calculating running hospital revenue

For each row, SQL adds the current bill to all the bills before it.

ROWS specifies that the frame is based on rows.
UNBOUNDED PRECEDING means start from the first row.
CURRENT ROW means stop at the current row. Therefore, the first row includes only its own bill, the second includes the first and second bills, and the third includes all three bills. In summary: A window frame tells SQL how far back or forward to look when calculating a value for each row. It is particularly useful for running totals, moving averages, and calculations involving a specific number of preceding or following records.

Ranking window functions

Ranking functions assign positions to rows according to an ordering criterion. They are useful for identifying the most experienced doctors, highest-cost visits, or patients with the most visits.

ROW_NUMBER()

Purpose: Assigns a unique sequential number to each row within a partition.
Hospital scenario: Number each patient's visits in chronological order.

Result:

Result: A patient with four visits receives sequence numbers 1, 2, 3, and 4. Numbering restarts at 1 for the next patient.
Even when two visits share the same date, visit_id helps establish a consistent order.

RANK()

Purpose: Assigns the same rank to tied values but skips subsequent rank numbers.
Hospital scenario: Rank doctors by years of experience.

Result:

Result: If the highest experience values are 20, 15, 15, and 10 years, their ranks are 1, 2, 2, and 4.
This function is useful when tied doctors should share the same competitive position.

DENSE_RANK()

Purpose: Assigns equal ranks to ties without leaving gaps.
Difference between RANK() and DENSE_RANK(): RANK() skips numbers after ties, while DENSE_RANK() continues with the next consecutive rank

NTILE()

Purpose: Divides ordered rows into a specified number of approximately equal-sized groups.
Hospital scenario: Segment visits into four billing tiers.

Result:

Result: Visits are distributed among four groups. Group 1 contains the highest bills, while group 4 contains the lowest bills, assuming descending order.
This can help the hospital identify visits with comparatively high billing amounts.
NTILE(4) creates four groups of roughly equal numbers of rows, not four groups with equal billing values.

Value Window Functions

Value functions retrieve values from particular rows within a window. They are especially useful for analyzing changes in patients' visits and identifying first or last recorded events.

LAG()

Purpose: Retrieves a value from a preceding row without requiring a self-join.
Hospital scenario: Compare each patient's current bill with their previous visit bill.

Result:

Result: The first visit for each patient has NULL in previous_visit_bill because no earlier visit exists. Subsequent visits show the preceding bill.
For example, patient 1 has bills of KES 3,500 and KES 4,200, so the previous-bill values are NULL and 3,500.

LEAD()

Purpose: Retrieves a value from a following row in the window.
Hospital scenario: Examine the bill recorded for a patient's next visit.

Result:

Result: Each visit is displayed alongside the next recorded visit's bill for that patient. The last visit has NULL because no later visit exists.
Difference between LAG() and LEAD(): LAG() looks backward; LEAD() looks forward.

FIRST_VALUE()

Purpose: Returns the value from the first row in the window frame.
Hospital scenario: Display the bill from each patient's earliest visit alongside every visit.

Result:

LAST_VALUE()

Purpose: Returns the value from the last row in the window frame.
Hospital scenario: Display the bill from each patient's latest recorded visit alongside all their visits.

Result: Every visit for a patient displays the bill from that patient's latest recorded visit.
Important: LAST_VALUE() can be confusing because its result depends on the window frame. With an ordered default frame, it may return the current row's value or the last value among its peers rather than the final value in the entire partition. Specifying UNBOUNDED FOLLOWING ensures that the frame extends to the partition's final row.

Aggregate Window Functions

Aggregate window functions calculate summaries while preserving individual rows. They can support hospital revenue analysis, cost monitoring, and patient utilization reporting.

SUM()

Purpose: Adds values within a window.
Hospital scenario: Calculate the cumulative billing revenue generated by visits.

Result:

As shown in the results, starting with visit 1,001 (3,500 KES), the cumulative revenue accumulates chronologically row by row—adding visit 1,002 (2,500 KES) to reach 6,000 KES, visit 1,003 (28,000 KES) to reach 34,000 KES, and so on,allowing hospital management to track financial growth alongside individual visit bills.

AVG()

Purpose: Calculates the average of values within a window.
Hospital scenario: Compare every visit's bill with the average bill for that patient's visits.

Result:

As shown in the results, since Patient 1 has bills of 3,500 and 4,200, the average amount (3,850) is displayed across both of their rows, allowing hospital staff to track individual visit bills alongside each patient's average treatment cost.

COUNT()

Purpose: Counts rows or non-NULL values within a window.
Hospital scenario: Count the recorded visits for each patient.

Result:

As shown in the results, since Patient 1 has two recorded visits (1,001 and 1,011), the total count of 2 is repeated next to both rows, allowing hospital staff to view granular visit logs alongside lifetime patient visit frequency.

COUNT(*) counts rows, including rows containing NULL in individual columns. By contrast, COUNT(column_name) counts only rows where that column is not NULL.

MIN()

Purpose: Returns the minimum value in the window.
Hospital scenario: Identify the lowest recorded bill for each patient.

Result:

As shown in the results, Patient 1 has bills of 3,500 and 4,200, so the lowest amount (3,500) is displayed across both of their rows, allowing hospital staff to track individual bills alongside each patient's lowest overall cost.

MAX()

Purpose: Returns the maximum value in the window.
Hospital scenario: Identify the highest recorded bill for each patient.

Result:

As shown in the results, since Patient 1 has bills of 3,500 and 4,200, the maximum amount (4,200) is displayed across both of their rows, allowing hospital staff to track individual visit bills alongside each patient's peak treatment cost.

SQL Joins

SQL joins combine related data from two or more tables using a specified condition. Hospital databases commonly store patient details, doctors, departments, and visits separately to reduce duplication and maintain data consistency.
For example, the visits table contains identifiers such as patient_id, doctor_id, and department_id. Joins allow an analyst to retrieve meaningful names and department information instead of working only with identifiers.

INNER JOIN

Purpose: Returns only rows with matching values in both joined tables.
Hospital scenario: Retrieve visits with the corresponding patient details.

Result:

What it does: Matches every visit to the patient with the same patient_id.
Result: Visits with matching patient records appear in the output. Visits without a matching patient record are excluded.
This is useful when an analysis requires complete matches between the tables.

LEFT JOIN

Purpose: Returns every row from the left table and matching rows from the right table. Where no match exists, right-table columns contain NULL.
Hospital scenario: Find all registered patients, including those who have never visited.

Result:

Result: All patients are retained. Patients without visits have NULL for visit_id and visit_date.
This is useful for identifying patients with no recorded visits and checking the completeness of hospital records.

RIGHT JOIN

Purpose: Returns every row from the right table and matching rows from the left table.
Hospital scenario: Retain every visit record, including visits without a matching patient record.

Result:

Result: Every row from visits appears. Where a visit has no matching patient record, the patient columns contain NULL.
This can help audit inconsistent records, although a properly enforced foreign key should ordinarily prevent such orphaned visits.
A RIGHT JOIN can often be rewritten as a LEFT JOIN by reversing the table order.

FULL OUTER JOIN

Purpose: Returns all matching rows plus unmatched rows from both tables.
Hospital scenario: Audit patient records and visit records to identify unmatched entries on either side.

Result:

This is useful for data-quality investigations and reconciliation.

CROSS JOIN

Purpose: Returns every possible combination of rows from two tables, producing a Cartesian product.
Hospital scenario: Generate all possible combinations of departments and wards for planning or review.

Result:

This query performs a CROSS JOIN between the department and wards tables, producing a Cartesian product where every single department is paired with every available ward. As shown in the results, each of the four departments (Cardiology, General Medicine, Orthopedics, and Pediatrics) is combined with all four ward IDs (100, 200, 300, and 400), creating 16 total combinations that allow hospital planners to evaluate every possible department-to-ward pairing.
Caution: A cross join can produce a very large result when the tables contain many rows.

SELF JOIN

Purpose: Joins a table to itself using different aliases.
Hospital scenario: Find pairs of doctors with the same number of years of experience.

The query performs a SELF JOIN on the doctors table by aliasing it as doc1 and doc2, attempting to pair doctors who share the exact same number of experience years while eliminating duplicate reverse pairs using doc1.doctor_id < doc2.doctor_id. As shown in the results, because all four doctors in the dataset currently possess unique experience years (12, 9, 15, and 8 yearsrespectively), no records satisfy the matching condition, resulting in an empty result set with zero rows returned.

Common Table Expressions (CTEs)

What is a CTE?

A Common Table Expression (CTE) is a named result set defined using the WITH clause. It exists for the duration of the SQL statement and can make complex queries easier to understand, organize, and maintain.
In hospital analytics, CTEs are useful for calculating department revenue, identifying high-cost visits, summarizing patient activity, and breaking complex reports into smaller logical steps.

Basic CTEs

Purpose: Give a query result a temporary name so it can be referenced by the main query.
Hospital scenario: Identify visits with bills exceeding KES 10,000.

What it does: The CTE selects visits whose total bill exceeds KES 10,000. The main query retrieves those records from the named result set.
The CTE is not a permanent table. It is defined within this statement and can be referenced by the statement that follows it.

Multiple CTEs

Purpose: Break a complicated analysis into multiple named steps.
Hospital scenario: Calculate total billing revenue for each department and return departments with revenue above KES 10,000.

Result:

How it works:

department_visits joins department details to individual visit billing records.
department_revenue groups the records by department and calculates total revenue.
The final query filters the summary to departments exceeding KES 10,000 and sorts them by revenue. Result: A list of qualifying departments, with their total billing revenue ordered from highest to lowest. This structure is easier to maintain than placing every operation in one deeply nested query.

Recursive CTEs

Purpose: A recursive CTE repeatedly references its own result to process hierarchical data or generate sequences.
Hospital scenario:Generate a calendar containing every date between the hospital’s earliest and latest recorded visits. This helps identify days when patients visited the hospital and days when no visits were recorded

CTEs combined with joins and aggregations

CTEs can work with joins and aggregate functions to produce multi-stage hospital reports.
Hospital scenario: Count visits and calculate total billing revenue for each department.

Result:

What it does: The CTE summarizes visit activity for each department. The outer query joins those summaries to the department table.
Expected result: Every department is listed, including departments without visits. Departments without visits display zero for total_visits and NULL for the revenue and average-bill fields.
COALESCE() replaces a NULL value with a specified alternative; here, it ensures that departments without recorded visits display zero visits.

SQL Subqueries

What is a subquery?

A subquery is a query embedded within another SQL statement. It can appear in the SELECT, FROM, or WHERE clause and can return a single value, a single column of values, or a table-like result.
In hospital analytics, subqueries can identify patients with emergency visits, doctors with above-average workloads, and visits whose bills exceed specified benchmarks.

Scalar Subqueries

Purpose: Return a single value: one row and one column.
Hospital scenario: Display each visit alongside the average bill across all visits.

The inner query calculates the hospital-wide average bill. The outer query displays that value alongside each individual visit.
Result: Each visit retains its own bill and has an additional column containing the overall average.
A scalar subquery that returns more than one row causes an error when used in a scalar context.

Non-correlated Subqueries

Run independently of the outer query; they do not reference columns from that query.
Hospital scenario: Identify visits whose bills exceed the hospital-wide average.

How it works: The inner query calculates the average once conceptually as an independent expression. The outer query compares each visit's bill with that average.
Result: Only visits with bills greater than the overall average appear.

Correlated Subqueries

Purpose: Reference a column from the outer query, making the inner query dependent on the current outer row.
Hospital scenario: Count the total recorded visits for each doctor.

What it does: For each doctor, the subquery counts records in visits that have the same doctor_id.
Result: Every doctor appears with their recorded visit count. Doctors without visits receive a count of zero.
The reference d.doctor_id connects the inner query to the current doctor in the outer query. This is what makes the subquery correlated.

Nested Subqueries

Purpose: Place a subquery inside another subquery to perform multiple levels of filtering or calculation.
Hospital scenario: Find visits whose bills exceed the average bill for visits belonging to patients who have had at least two recorded visits.

How it works:

The innermost query groups visits by patient and identifies patients with at least two visits.
The middle query calculates the average bill across visits belonging to those patients.
The outer query returns visits whose bills exceed that calculated average. Result: A list of visits with bills above the average for the specified group of patients. This query demonstrates nested filtering, although a CTE might make the same logic easier to read in a larger reporting task.

Subqueries using `IN`

Purpose: Test whether a value matches any value returned by a subquery.
Hospital scenario: Find patients who have had an emergency admission.

What it does: The inner query identifies patients with an emergency visit. The outer query retrieves their demographic information.
Result: Patients with at least one matching emergency visit are returned. A patient with multiple emergency visits still appears only once in this outer query because the outer table is patients.

Subqueries using `EXISTS`

Purpose: Test whether a subquery returns at least one row.
Hospital scenario: Find patients who have at least one recorded visit.

What it does: For each patient, the subquery checks whether a matching visit exists.
Result: Only patients with at least one recorded visit are returned.

Subqueries using `ANY`

Purpose: Compare a value with the results of a subquery and return true if the comparison is true for at least one returned value.
Hospital scenario: Find visits whose bills exceed at least one bill recorded for a particular patient, here patient ID 1.

What it does: Returns visits whose bills are greater than at least one non-NULL bill belonging to patient 1.
Result: A visit qualifies if its bill exceeds at least one bill in the comparison set.
For example, if patient 1 has bills of KES 2,000 and KES 5,000, a bill of KES 3,000 satisfies > ANY because it exceeds KES 2,000, even though it does not exceed KES 5,000.
If the subquery returns no rows, the ANY comparison is false.

Subqueries using `ALL`

Purpose: Compare a value with every value returned by a subquery.
Hospital scenario: Find visits whose bills exceed every non-NULL bill recorded for patient 1.

What it does: Returns visits whose bills are greater than the highest bill in the comparison set, provided the set is nonempty.
Result: If patient 1's bills are KES 2,000 and KES 5,000, a visit with a bill of KES 6,000 qualifies, but a bill of KES 3,000 does not.
If the subquery returns no rows, the ALL comparison is true by definition. This is an important difference to consider when writing filters.

Subqueries in `SELECT`, `FROM`, and `WHERE`

Subqueries can be positioned in different parts of a SQL statement depending on the type of result.

Subquery in `SELECT`

A scalar subquery can provide an additional calculated column.

Subquery in `FROM`

A subquery in the FROM clause creates a derived table that the outer query can process.
Hospital scenario: Calculate each patient's total billing, then find the average total billing per patient.

What it does: The inner query calculates total bills for each patient. The outer query averages those patient-level totals.
Result: A single value representing the average total billing per patient among patients represented in the derived table.
This differs from the average individual visit bill because each patient is first represented by their cumulative billing total.

Subquery in `WHERE`

A subquery can filter records based on a comparison or membership condition.

Return visits meeting a condition derived from another query, such as exceeding the overall average bill.

Conclusion

Advanced PostgreSQL techniques enable healthcare analysts to transform relational data into meaningful operational and financial insights.
Window functions support ranking, comparisons, and cumulative calculations while preserving individual records.
Joins combine related information across tables and help identify unmatched records.
CTEs organize complex queries into manageable stages and support recursive processing. Subqueries allow filtering and calculations based on the results of other queries.
The most effective SQL solution depends on the question being asked, the structure of the data, and the expected output. Understanding not only how to write a query but also why it works is essential for producing reliable and useful analysis.

Power BI Data Modelling, Relationships and Joins.

Mercy Siele — Sun, 13 Sep 2026 15:13:00 +0000

Introduction

When I first started working with Power BI, I thought that importing data and creating charts was the main part of building a report. However, I quickly realized that before creating visuals, I needed to understand how my data was structured and how different tables were connected.

A Power BI report can contain beautiful dashboards, but if the underlying data model is poorly designed, the report can become difficult to maintain, DAX calculations can become unnecessarily complicated, and filters may not behave as expected.

This article explores three important areas of Power BI:

Data modelling and schema design
Relationships between tables
Joins in Power Query

Data Modelling in Power BI.

Data modelling is the process of organizing tables and defining how they relate to each other so that Power BI can correctly analyze the data.

Instead of putting every piece of information into one large table, we can separate information into logical tables and connect them using relationships.
For example, a sales business might have:
Customers

Products
Dates
Locations
FactSales

The FactSales table contains the actual sales transactions, while the other tables provide descriptive information about customers, products, dates, and locations.
A good data model is important because it affects:

- Reporting & Analytics: Keeps your data organized so finding and dragging fields into charts feels natural and quick.

- DAX Calculations: Allows you to write short, simple formulas instead of long, confusing code to connect messy tables.

- Performance: Helps Power BI run much faster, so charts load instantly instead of freezing or lagging.

- Scalability: Keeps your reports fast and stable, even as your data grows from thousands of rows to millions.

- Maintainability: Makes it easy to fix mistakes, update numbers, or share your work with teammates without breaking anything.

The Dataset Used in This Project

For this project, I used a small sales dataset.
The workbook contains the following tables:

Table	Purpose
Customers	Customer information
CustomerProfiles	Customer contact information
Segments	Customer segment information
Products	Product information
Locations	Location information
Dates	Date attributes
Orders	Order information
OrderDetails	Products contained in each order
FactSales	Central sales fact table

Data Modelling Approaches

There are several ways to organize data in a Power BI model.
The three approaches discussed in this article are:

Flat Table
Star Schema
Snowflake Schema

Flat Table

A flat table contains most or all of the information required for analysis in a single table.Instead of having separate Customer, Product, Location and Sales tables, the information is stored together.

Advantages of a Flat Table
A flat table is relatively easy to understand, especially for beginners.
Advantages include:

Simple structure
No relationships required
Easy to create simple visuals
Easy to export
Useful for small datasets
Suitable for straightforward analysis

Disadvantages

The biggest problem is data redundancy.
For example, if a customer makes 100 purchases, their name, city, county and other descriptive information may be repeated 100 times.

This can result in:

Larger models
Repeated data
More difficult maintenance
More opportunities for inconsistent information
Less scalable models

A flat table is therefore appropriate when the dataset is small, the analysis is simple, the data already comes in a clean denormalized format, there are no complex relationships, or the report is temporary or exploratory.

Star Schema

A star schema separates the business event from the descriptive information.
At the center is a fact table, surrounded by dimension tables.
For this project, the model can be structured as:

Advantages of a Star Schema
A star schema has several advantages:

Easy to understand
Clear separation between facts and dimensions
Simple relationships
DAX is generally easier to write
Good report performance
Easy filter propagation
Scales well
Easier to maintain
Reduces unnecessary duplication

Disadvantages
There are some disadvantages:

More tables need to be managed
Relationships must be correctly configured
Beginners may initially find the model more complicated than a flat table
Poor relationship design can produce incorrect results

When Should a Star Schema Be Used?
A star schema is particularly useful for:

Business intelligence
Sales reporting
Financial reporting
Inventory analysis
Customer analytics
Operational dashboards
Large analytical models

Snowflake Schema

A snowflake schema is similar to a star schema, but dimension tables are further divided into additional related tables.

Advantages
A snowflake schema can:

Reduce repeated descriptive data
Normalize dimensions
Improve consistency in some situations
Be useful when dimensions have complex hierarchies

Disadvantages
The main disadvantage is increased complexity.
This includes:

More tables
More relationships
More joins/relationship paths
Potentially more complicated DAX
More complicated filter propagation

It can be therefore be approriate when:

Dimensions are very large
Hierarchies are complex
Data is already highly normalized
Reducing redundancy is important
There are reusable sub-dimensions

Understanding Fact Tables and Dimension Tables

One of the most important concepts in data modelling is understanding the difference between fact tables and dimension tables.
A fact table stores information about business events or transactions.It contains numerical values that can be aggregated.
In my data set the Fact Sales is the fact table containing OrderID,OrderDate,CustomerID,ProductID,LocationID,Quantity,Price, Revenue,Cost,Profit and Status.
Dimension tables contain descriptive attributes used to describe and filter the business events in the fact table. Examples include:CustomerID,CustomerName,City,County and SegmentID.
In simple terms:
Dimensions answer questions such as:

Who?
What?
Where?
When?

Facts answer questions such as:

How many?
How much?
What revenue?
What cost?
What profit?

Understanding Granularity (Grain) in Data Modeling
When designing a data model, one of the first questions you should ask is:
What does one row in this table represent?
The answer to this question is called the grain of the table.

Grain defines the exact level of detail stored in each row of a fact table. In other words, it tells us what a single record represents.

Practical Example: Building a Star Schema for a Retail Business

Imagine a retail company that sells clothing through several branches. The business wants to use Power BI to analyze its sales performance.Instead of putting all the information into one large table, we can organize the data using a star schema.

At the center of the model is the Fact Sales table. This table contains the sales transactions and the numerical information we want to analyze, such as quantity sold, sales revenue, and discounts.

Around the fact table are several dimension tables that provide additional information about the sales, such as Dim Customer, Dim Product, Dim Store, and Dim Date.

Connecting the Tables Using Keys

We can use the Fact Sales and Dim Product tables to see how the connection works.
The Product_Key in Dim Product is the primary key. It uniquely identifies each product in the table.

The Product_Key in Fact Sales is the foreign key. It is used to link each sale to the correct product in the Dim Product table.

For example, we could have:

Dim Product

Product_Key	Product_Name	Category
P001	Classic Shirt	Shirts
P002	Wide Leg Pants	Trousers

Fact Sales

Sale_ID	Product_Key	Quantity	Sales
1001	P001	2	8,000
1002	P002	1	5,000
1003	P001	3	12,000

These two tables are connected through Product_Key.

If Power BI sees P001 in the Fact Sales table, it can use that key to find P001 in the Dim Product table and identify the product as Classic Shirt.

The relationship is:

Dim Product (1) → Fact Sales (*)

This means that one product can appear in many sales transactions.

Understanding the Keys

Primary Key: A column that uniquely identifies each record in a table. In our example, Product_Key uniquely identifies each product in Dim Product.
Foreign Key: A column that refers to the primary key of another table. In our example, Product_Key in Fact Sales links each sale to a product in Dim Product.

The same approach can be used to connect other dimension tables, such as Dim Customer, Dim Store, and Dim Date, to the Fact Sales table. Together, these tables form a star schema.

Relationships in Power BI

A relationship tells Power BI how two tables are connected.Relationships are necessary because analytical information is often distributed across multiple tables.

One-to-Many Relationship (1:*)

The most common relationship in a Power BI star schema is one-to-many.A single unique key in one table (the "One" side) matches multiple instances of that same key in another table (the "Many" side).
Practical Example: Connecting DimCustomer[CustomerID] to FactSales[CustomerID]. A customer registers once (unique on the "One" side), but can make multiple purchases over time (appears multiple times on the "Many" side).

When to use: This is the gold standard for Power BI data modeling. Use it to connect all dimension tables to your central fact table.

When NOT to use: Never force a $1:* relationship if the key on the "One" side contains duplicates; Power BI will throw an error or default to a Many-to-Many relationship.

One-to-One Relationship(1:1)

Each unique value in the primary key column of Table A corresponds to exactly one unique value in Table B, and vice versa.
Practical Example: Connecting DimCustomer to CustomerContactInfo, where both tables use CustomerID as a unique primary key.

When to use: **Use sparingly when splitting a very wide table into two for security reasons (e.g., separating sensitive HR data) or organizational performance.

When NOT to use: In almost all standard Power BI scenarios, 1:1 relationships add unnecessary model complexity. It is far better to merge the two tables into a single table using Power Query before loading the model.

Many-to-Many Relationship(:)

Neither column in the relationship contains unique values. Multiple records in Table A match multiple records in Table B.

Practical Example: Connecting FactSales directly to a Promotions table where a single sale can qualify for multiple promotions, and a single promotion applies to multiple sales transactions.

When to use: Use only when modeling complex, non-additive business scenarios that cannot be restructured in Power Query (such as target vs. actuals at higher aggregation levels).

When NOT to use: Avoid using direct : relationships between standard facts and dimensions. They introduce ambiguous filter paths, create unpredictable DAX calculation results, and significantly reduce query performance. Instead, resolve them by introducing a Bridge Table in between to create two 1:* relationships.

Other Important Relationship Concepts

Primary Keys and Foreign Keys

A primary key is a column that uniquely identifies each record in a table. In the Customers table, CustomerID acts as the primary key because each customer has a unique ID.
A foreign key is a column that references a key in another table. In FactSales, CustomerID acts as a foreign key.
Unlike the primary key, the foreign key does not have to be unique.

Cardinality

Cardinality describes how many records in one table can be associated with records in another table.
Power BI supports several types of cardinality, with three being particularly important when designing a model.

Unique Values and Referential Integrity

For a column to be used on the one side of a 1:* relationship, its values must be unique.

For example:
Customers[CustomerID] C001 C002 C003 C004 C005 C006
There should not be multiple records with C001 in this table.

The corresponding foreign key in FactSales, however, can contain repeated values:
FactSales[CustomerID] C001 C002 C003 C001

Referential integrity refers to the consistency between the values in the related tables. Ideally, every foreign-key value in the fact table should have a corresponding key in the dimension table.For example:
FactSales[CustomerID] → Customers[CustomerID]

Active and Inactive Relationships

Power BI relationships can be active or inactive.
An active relationship is used automatically when filters and calculations are evaluated.

For example:DimDate[Date] ─────── FactSales[OrderDate] Active
If a report user selects January 2026 from DimDate, the active relationship allows Power BI to filter the relevant sales records.
An inactive relationship exists in the model but is not automatically used.This can be useful when a fact table contains multiple date columns, such as:
FactSales --------- OrderDate ShipDate DeliveryDate

A model could contain relationships from a Date dimension to both OrderDate and ShipDate, while only one is active. The inactive relationship can then be used when a calculation specifically needs the alternative date.

Filter Direction in Power BI

Relationships determine not only how tables are connected but also how filters move between them.
Consider the following relationship:

If a user selects Bags from DimProduct, the filter travels to FactSales.

Single-Direction Filtering

In a typical star schema, filters flow from the dimension tables toward the fact table.

For example, selecting: Product Category = Bags,
filters the products in DimProduct, which then filters the related records in FactSales.

Single-direction filtering is generally preferred because the direction of filter propagation is clear and predictable.

Bidirectional Filtering

With bidirectional filtering, filters can flow in both directions between related tables.

Bidirectional filtering can be useful for specific modelling requirements, but it should not automatically be enabled throughout a model.
onsider a model containing several interconnected tables:

There may now be multiple paths through which a filter can travel.

This can result in:

Ambiguous filter paths
Unexpected filtering
More difficult DAX calculations
Harder-to-understand report behaviour
Unnecessary model complexity

For this reason, my default approach would be single-direction filtering from dimension tables to fact tables, unless there is a clear business requirement for bidirectional filtering.

Joins in Power Query

A join combines information from two tables based on one or more matching columns.
In Power Query, joins are performed using Merge Queries.
For example, consider two tables from the practice dataset.

The common column is CustomerID.
Different join types produce different results depending on which records we want to retain.

Left Outer Join

A Left Outer Join keeps all records from the left (First) table and any matching records from the right (Second) table.

I used Customers as the left table and Orders as the right table. The Left Outer Join retained every customer, including C006, even though this customer had no corresponding order. The order information was added where a matching CustomerID existed.

Right Outer Join

Keeps every row from the right (second) table, plus matches from the left( First) table where they exist.

The Right Outer Join retained every record from the Orders table. As a result, O1008 remained in the output even though its CustomerID, C999, did not exist in the Customers table.

Inner Join

Combines two tables by keeping only the rows that have matching values in a common column.

The Inner Join returned only records where CustomerID existed in both tables. Therefore, customers without orders and orders with invalid customer IDs were excluded.

Full Outer Join

Combines two tables by keeping all rows from both tables, whether they match or not.

The Full Outer Join retained all records from both tables. This made it possible to see both types of mismatch: a customer with no order and an order whose customer does not exist in the Customers table.

Left Anti Join

Returns only the rows from the first (left) table that do not have a match in the second (right) table.

The Left Anti Join returned records that existed in Customers but had no matching CustomerID in Orders. In this dataset, the result was C006, Faith Chebet. This type of join can be used to identify customers who have not placed an order.

Right Anti Join

Returns only the rows from the right-hand (secondary) table that have no matching rows in the left-hand (primary) table.

The Right Anti Join returned records from Orders that had no corresponding CustomerID in Customers. The result identified O1008 with CustomerID C999, revealing a referential-integrity issue in the dataset.

Power Query Joins vs. Power BI Relationships

A Power Query merge and a Power BI relationship both connect tables, but they work differently and are used at different stages of the Power BI workflow.

Does a Power Query Merge Physically Combine Data?

Yes.

A merge in Power Query combines columns from two tables into one table based on a matching key.

For example, if we merge Customers with Orders using CustomerID, customer information such as name and location can be added directly to the Orders table.

This happens during the data preparation stage, before the data is loaded into the Power BI data model.

Does Creating a Relationship Combine the Tables?

No.

A relationship keeps the tables separate.

For example, DimCustomer and FactSales remain as two separate tables in the Power BI model.

The relationship simply tells Power BI that the tables are connected through a common key.

For example:

DimCustomer[CustomerID] → FactSales[CustomerID]

Power BI can then use this relationship to connect customer information with sales when creating reports and calculations.

When Does Each Operation Happen?

The two operations happen at different stages:

Merge: Done in Power Query during data preparation and transformation.
Relationship: Created in the Power BI Data Model after the tables have been loaded. A simple way to remember this is:

Power Query prepares and transforms the data, while the Data Model connects the tables for analysis.

When Would You Choose a Merge Instead of a Relationship?

A merge can be useful when you genuinely need to create one combined table.

For example, you might need a single flat table for a specific export or for a system that does not support relationships.

However, for most business intelligence reporting, especially when building a star schema, relationships are generally preferred.

How Can Excessive Merging Affect a Data Model?

Merging everything into large tables can reintroduce the redundancy that a star schema is designed to avoid.

For example, if customer information is merged into a sales table, the same customer name, location, and other details may be repeated across every transaction made by that customer.

As the dataset grows, this can create wider and more difficult-to-maintain tables.

It can also make the overall data model harder to understand because the clear separation between facts and dimensions is lost.

Why Keep Fact and Dimension Tables Separate?

Keeping the tables separate has several advantages:

Less redundancy: Customer information is stored once in DimCustomer rather than repeated for every sale.
Cleaner data model: Fact and dimension tables have clearly defined purposes.
Simpler analysis: Measures can be created from the fact table while dimensions provide the context for analysis.
Easier maintenance: If a customer's location changes, you update it in one place instead of updating many transaction rows.
Better scalability: A well-designed star schema is easier to work with as the amount of data increases.

A Practical Example

Suppose Alice Wambui has made two orders:

Order_ID	CustomerID	Sales
101	C001	5,000
104	C001	8,000

In the DimCustomer table, Alice's information is stored once:

CustomerID	Customer_Name	Location
C001	Alice Wambui	Nairobi

If we merge the customer table into the sales table, Alice's name and location would be repeated on both orders.

If we instead create a relationship, the customer information stays in DimCustomer, while FactSales only needs the CustomerID.

DimCustomer                 FactSales

CustomerID                  CustomerID
Alice Wambui                C001
Nairobi                     C001
     |                          |
     └──────── 1 : * ──────────┘

Power BI uses the relationship between CustomerID and CustomerID to connect the two tables when analyzing the data.

Key Takeaway

The main difference is simple:

A Power Query merge combines data into a table, while a Power BI relationship connects separate tables within the data model.

For a well-structured star schema, keeping fact and dimension tables separate and connecting them with relationships is usually the better approach.

Recommended Power BI Model

For a typical business intelligence project, I would recommend using a star schema as the preferred data model.

A star schema provides a good balance between performance, simplicity, scalability, and maintainability. It separates business transactions from descriptive information, making the model easier to understand and use when building Power BI reports.

A typical model could look like this:

                    DimProduct
                         |
                         |
DimCustomer ─────── FactSales ─────── DimStore
                         |
                         |
                      DimDate

The FactSales table would contain the business events and numerical values we want to analyze, such as sales amount, quantity, cost, and profit. The dimension tables would contain descriptive information such as product details, customer information, store details, and dates.

Why Choose a Star Schema?

1. Better Query and Report Performance

A star schema keeps dimension tables relatively small and the fact table focused on transactions and measures. This structure allows Power BI to efficiently filter and aggregate data when users interact with reports.

Instead of storing customer or product information repeatedly in every sales transaction, the information is stored in the relevant dimension table and connected through relationships.

2. Simpler DAX

Star schemas also make DAX easier to understand.
For example, a measure can calculate total sales from the FactSales table:

Total Sales = SUM(FactSales[Sales_Amount])

Users can then analyze this measure by product category, store, customer type, or month using fields from the dimension tables.
The relationships allow the filters from those dimensions to affect the fact table automatically.

3. Better Model Readability

A star schema has a clear structure.
The fact table sits at the center, while the dimension tables surround it. This makes it easier for another analyst to understand the model and identify where different types of information are stored.

For example:

FactSales → What happened?
DimProduct → What was sold?
DimCustomer → Who bought it?
DimStore → Where was it sold?
DimDate → When did it happen?

4. Scalability

A business may start with thousands of transactions and eventually grow to millions.
Keeping fact and dimension tables separate provides a structure that can scale more easily as the amount of data increases.
It also makes it easier to add new dimensions or attributes without completely restructuring the fact table.

5. Less Data Redundancy

A star schema avoids unnecessarily repeating descriptive information.

For example, instead of storing "Alice Wambui, Nairobi" on every transaction made by Alice, that information can be stored once in DimCustomer.
The fact table only needs the CustomerID to identify the customer.This reduces redundancy and keeps the model organized.

6. Easier Maintenance

If a customer's location changes, we can update the information in DimCustomer rather than changing it across thousands of sales records.

Similarly, if a product is moved to a different category, the relevant information can be maintained in DimProduct.
This makes the model easier to manage over time.

Recommended Relationships

For a typical star schema, I would normally use one-to-many (1:*) relationships between dimension tables and the fact table.

For example:

DimProduct[Product_Key]     1 ───── * FactSales[Product_Key]

DimCustomer[Customer_Key]   1 ───── * FactSales[Customer_Key]

DimStore[Store_Key]         1 ───── * FactSales[Store_Key]

DimDate[Date_Key]           1 ───── * FactSales[Date_Key]

The dimension table is on the "one" side, because each key should uniquely identify a record in that dimension.

The fact table is on the "many" side, because the same product, customer, store, or date can appear in many transactions.

Filter Direction

I would normally use single-direction filtering, with the filter flowing from the dimension table to the fact table:

DimProduct
    ↓
FactSales

For example, if a user selects Shirts from DimProduct, the filter flows to FactSales and Power BI calculates the sales for shirts.

Single-direction filtering is generally preferable because it keeps the model's behavior predictable and reduces unnecessary complexity.

Bidirectional filtering can be useful in specific scenarios, but I would not use it by default. Using it unnecessarily can create ambiguous filter paths and make a model more difficult to understand and troubleshoot.

What About a Flat Table or Snowflake Schema?

A flat table can be useful for simple datasets or small reporting tasks because everything is contained in one table. However, as the dataset grows, it can create a lot of repeated information and become difficult to maintain.

A snowflake schema normalizes dimensions into additional related tables. This can reduce redundancy, but it also introduces more relationships and complexity into the model.

For most Power BI business intelligence projects, I would therefore choose a star schema because it provides a practical middle ground:

Simple enough to understand, efficient enough to perform well, and flexible enough to scale.

Final Recommendation

For a typical Power BI project, my preferred design would be:

Star schema
One central fact table
Multiple dimension tables
One-to-many relationships from dimensions to the fact table
Single-direction filter propagation from dimensions to facts
Clearly defined grain for the fact table
Measures stored and calculated from the fact table
Descriptive attributes stored in dimension tables
Avoid unnecessary merging of fact and dimension tables

Conclusion

The goal is not simply to create relationships between tables, but to create a model that is easy to understand, efficient to query, simple to maintain, and flexible enough to support future reporting needs.

Ultimately, a good Power BI model should make it easier for both the developer and the end user to answer business questions without introducing unnecessary complexity.

Jumia Product Analysis with Excel

Mercy Siele — Sat, 05 Sep 2026 21:13:03 +0000

Introduction

When shopping onine, I usually find myself looking at two things before making a purchase: Product ratings and reviews since I cannot examine the product physically.Products with high ratings and reviews tend to make the product trustworthy.This motivated me to explore how these factors using a sample dataset from Jumia.
I analyzed a dataset of products listed to investigate whether pricing, discounts, ratings and review counts directly influence one another. By transforming this raw e-commerce data into an interactive Excel dashboard this project uncovers how strategic pricing directly impacts customer engagement.

Data Inspection

Before data cleaning, I inspected all the data to find missing values, duplicates, inconsistent formats, and values that could affect the accuracy of the analysis.

Data Cleaning

Before beginning the analysis, I cleaned and standardized the dataset to ensure that the values were accurate, consistent, and suitable for analysis in Excel. The main cleaning steps involved correcting data formats, handling missing values, and identifying duplicate records.

Correcting Data Formats

I first reviewed each column and converted the values into appropriate data types.

Prices- The price columns were initially stored as text because they included currency symbols (KSh), commas, and in some cases, price ranges such as KSh 1,620 - KSh 1,980. I used Find and Replace (Ctrl + H) to remove the KSh text and other unnecessary characters then converted the values to numerical format. For products with price ranges, I calculated the average of the minimum and maximum prices and used this value for further analysis. I then recalculated the discount percentages based on the standardized prices.
Ratings- Ratings were stored as text in formats such as 4 out of 5. I used Find and Replace (Ctrl + H) to remove out of 5 and converted the remaining values into numerical ratings.These were also formated as texts i.e 4 out of 5.
Review- All the reviews counts were recorded as negative values. Since review counts represent the number of customer reviews and should be non-negative integers, I converted these values to their absolute values to ensure consistency.

Discounts- Discount values were standardized and formatted as percentages, allowing them to be compared consistently across products.
Handling Missing Values- The missing values were mostly from the Ratings and Reviews Columns which I replaced using CTRL+H with Nulls.
Duplicates- To handle potential duplicates, I applied Conditional Formatting on the product column to highlight duplicate product names. I then manually cross-examined all columns across the flagged records to verify true duplicates:

-Three True Duplicates: Confirmed as identical records sharing exact matching product names, old prices, current prices, and discount percentages. These were permanently removed to prevent dataset skew.

-Three Distinct Items: Identified varied product configurations, as they exhibited different pricing structures and discount levels despite sharing similar names. These were retained in the dataset.

Data Enrichment

To enhance data analysis, I created four key derived columns in the dataset.

Rating Category.

</=3 – Poor
>3 – 4.5 – Average
>4.5 – 5 – Excellent
Null – Not Provided

Discount Category

</=20% – Low Discount
>20% – 40% Medium Discount
>40% – High Discount

Review Category.

1–23 – Low
24–46 – Medium
>46 – High
Null – Not Provided

Pricing Category.

</= KSh 1,000 – Low Price
>KSh 1,000 – KSh 2,000 Medium Price
>KSh 2,000 – High Price

Absolute Discount
=D3-B3 = Old Price -Currect Price

I used the following IF functions to come up with the threshholds:
-Reviews
=IF(H2="null","Null",IF(H2>46,"Excellent",IF(H2>23,"Average","Poor")))
-Ratings
==IF(K2="null","Null",IF(K2>4.5,"Excellent",IF(K2>3,"Average","Poor")))
-Price Category
=IF(B2>2000,"High Price",IF(B2>1000,"Medium Price","Low Price"))
-Discount
=IF(F2>40%,"High Discount",IF(F2>20%,"Medium Discount","Low Discount"))

Data Analysis

Using Pearson's Correlation Coefficient (r), I tested three core hypotheses regarding how pricing, discounts, ratings and reviews influence one another:

Key Insights
-As shown above,Pearson correlation analysis of the discount and reviews (r = -0.131), confirming that offering deeper price cuts does not lead to higher customer engagement.
-The correlation between rating and reviews is very weak (r = +0.057) because product rating has almost no impact on how many reviews a listing receives.This happens because Poor products and Moderate products generate almost the exact same average engagement.
-The correlation between price and product ratings is very weak positive (r = +0.110) because price increases do not reliably drive higher customer ratings.

Dashboard Analysis

I did the final analysis using a dashboard that had the following KPIs:

Total Products -112 products
Average Current Price -KSh 1,187
Average Discount-37%
Average Rating -4/5
Total Reviews -723 reviews

The Final Dashboard :

Key Recommendations

Rethink the use of discounts to drive customer engagement up. Since the relationship between discounts and reviews was weak (r = -0.131), sellers should test different discount levels rather than assuming that larger discounts will generate more reviews.
Capitalize on "Medium Price" products. Price and rating show a weak positive correlation (r = +0.110), with ratings rising in the Medium Price tier (4.1 rating) while High (3.88) and Low (3.78) lag behind.
Improve Products with High Engagement but Poor Ratings
The analysis identified products that received a high number of reviews despite having poor ratings. For example, the 120W Cordless Vacuum Cleaner received 69 reviews but had a rating of 2.8. This suggests strong customer engagement but potentially low customer satisfaction.

Conclusion

This analysis provided insights into how ratings, reviews, prices, and discounts relate to product performance on Jumia. I initially assumed that larger discounts would lead to more reviews and stronger customer engagement. However, the weak relationship between discounts and reviews challenged this assumption. The analysis also showed that higher-priced products were not necessarily rated more highly, while products with many reviews were not always highly rated. These findings highlight the importance of considering product quality, customer satisfaction, and overall value, rather than relying on price, discounts or reviews alone.

Getting Started with Excel for Data Analytics: From Basics to Data Cleaning

Mercy Siele — Sun, 30 Aug 2026 14:33:13 +0000

Introduction

Microsoft Excel is one of the most powerful and beginner friendly tool for data analytics. It helps you to organize,store, calculate, clean,analyze and visalize data.
Data analytics is the process of collecting, cleaning and examinig raw data to find useful insights.

Excel Basics

Before diving into the specific data cleaning techniques I learnt, we can get into the basics of Excel to avoid feeling overwhelmed by all the gridlines.

Excel ribbon

This are tabs at the top of Excel interface that helps users to navigate and locate commands when using Excel.

The following are the core tabs under the Excel ribbons and the use of each:

1. Home tab-this is the everyday button that contains different formating options such, font type (colour and size), alignment options,number formating (Currency,date, accounting,date and time),styles, cells and editing.
2. Insert- This is used to insert charts, pivot tables, symbols, tables,shapes, slicers, comments and links.
3.Page Layout- It contains a list of commands that determine how the pay appears.
4.Formulas- It contains mathematical formulars used for calculation and formula auditing tools.
5.Data- This is a section used ti import data, sort, filter and analyse data.
6.Review-Has tools used to check spelling, manage accessibility and to check the performance of the workbook.

Excel Terms

Cell- This is a single box in which column and rows meet.
Column- This is a vertical group of cells labelled with letters (A,B,C).
Row-A horizontal group of cells labelled with numbers (1,2,3)
Worksheet- A single sheet inside Excel used to enter data.
Workbook-A file in excel containing one or more worksheets.

Data Cleaning in Excel

Data cleaning is the processs of identifying and correcting errors, inconsistencies, duplicates and missing values to ensre it is accurate, reliable and ready for analysis.

Steps Taken During Data Cleaning :

1. I inspected the dataset I was checking on to check for the any issues.

Below are the issues I ecountered:

Missing values
Duplicates
Inconsistent formating

2.Removing duplicates
There were data that appeared more than once as shown.

To identify and remove duplicates, I used the Employee ID as the unique identifier since each employee should have a different ID.
I selected the dataset,under data tab on the ribbon, I selected remove duplicates under data tools. I selected the Employee ID column to allow Excel to identify duplicates and remove them.

3.Removing Missing Values
The missing values appeared on most of my column and I replaced the text types by using "Unknown" value.

4.Correcting Inconsistent Text
The same information was written in the same format.Some with different capitalization, extra spaces and spelling variation. Which will cause inconsistencies during analysis.

To correct this ,I used find and replace: Find what> Replace With> Replace All.
This ensured that all the data remained consistent.
I also used Excel text functions to standardize the data:

TRIM-removes extra spaces from texts.
PROPER-changes the texts to proper case.
UPPER-changes the texts to upper case.
LOWER-changes the texts to lower case.

5.Vaidating Cleaned Data
After completing the data cleaning process, I validated the data to ensure the changes had been made.
I used Filters and Sorting to check for any incorrect and unusal values. I also used Conditional Formating to check for any available duplicate values and missing values.

I also checked for the data types to ensure that columns were being stored under the correct data types such as dates under dates,numerical values as numbers.

After doing this, I confired that the data was ready for the next step which is analysis.

Conclusion

Through the entire process, I realised that data cleaning is not just about deleting blanks, removing blanks and correcting obvious errors. It is about understanding the data, investigating inconsistencies and making informed decisions before analysis. Every missing value, duplicate, unusual value can tell a story and removing it can lead to misleading insights.

My First Github Project:From a Local Folder to GitHub Using Git and SSH

Mercy Siele — Sun, 23 Aug 2026 04:48:36 +0000

Introdction

I always knew that GitHub is where developers keep and share their projects but I have always wondered how developers get the projects from their computers onto GitHub.When I started learning Git aeverything started falling into place. I recently worked on my first GitHub project using Git, Git bash, GitHub and SSH key.
Before I explain what I did, I first wanted to understand what each of these terms mean.

Key Terms

- Git
It is a software tool that tracks changes locally in your computer.
- GitHub
It is an online platform where repositories are posted online for collaboration.
- SSH Key
This is a secure digital credential that is used to connect the local Git software to the online GitHub account without typing a password everytime.
- Git Bash
This is a program used to interact with Git using commands

Setting up my Project Folder

I started by creating a folder for my project on the Computer.My project was called Kenya-Hospitals-Records-Analysis.

Creating Project Folder

I used mkdircommand to create the folder.This is a command used to create a new folder.
mkdir Kenya-Hospitals-Records-Analysis

Moving into the Project Folder.

I used the cd command to move to the project folder.
The cd Kenya-Hospitals-Records-Analysis allowed me to move into the folder itself.

Creating Files and Folders

I created another folder inside Kenya-Hospitals-Records-Analysis using command:
mkdir Data
and a file using command:
touch README.md
The touchcommand is used to create a new file.
I used README.md file to document my project.
The Datafolder contained my project data.

Checking Folders

After creating folders and files, I used lscommand to check what was inside my project folder. The command listed Data folder and README.md file confirming that they had been successfully created.

Initializing Git

I initialized Git using git init command. This created a hidden folder inside my project folder which initialized a new Git repository.

Stagging and Commiting Files

After initializing my project as a Git repository, I first checked the status of my project to know the files that were not being tracked using git status.
I then used git add .command to add all the files in my project to the staging area for commit.
After staging my files, I used :
git commit -m"First commit" . The git commit -m command saves the staged changes in my local git repository.
I used "First commit" as the commit message because it was the first time saving the project as a git.

Connecting My local Repository to GitHub

After making the first commit, I used git branch to check on the branch I was working on. The branch I was working on was the main branch.
Before starting the project, I had already set up an SSH key and added the public key to my GitHub. The SSH key allowed my computer to securely authenticate with Github when working with my remote repository.
I then connected my local repository with the GitHb repository using the command git remote add origin git@github.com:username/Kenya-Hospitals-Records-Analysis.git.
The origin being the name given to GitHub remote repository.
I then checked the connection using git remote -v which displayed the GitHub repository where my local repository was connected to. Once the connection was done, I used the command git push -u origin main. This command pushes the committed changes on my local repository to GitHub.

Verifying the Project on GitHub

Finally, I opened my GitHub to verify that my project had been uploaded.The Data and README.mdwere displayed verifying that I had successfuly uploaded my project.

Conclusion

This practical experience gave me the foundation on how to use Git,GitHub,Git bash and SSH key.I learnt how to create a local project,initialize it as a local repository,commit changes,connect it to GitHub and push the project to GitHub using SSH key.What seemed like complicated process became easier to understand once I learnt the use of the commands and the different stages in the process.

DEV Community: Mercy Siele

Mastering Advanced PostgreSQL: A Comprehensive Guide to Hospital Data Intelligence

Introduction

SQL Window Functions

Understanding OVER(), PARTITION BY, ORDER BY, and window frames

The OVER() Clause

The PARTITION BY Clause

The ORDER BY Clause

Window Frames

Ranking window functions

ROW_NUMBER()

RANK()

DENSE_RANK()

NTILE()

Value Window Functions

LAG()

LEAD()

FIRST_VALUE()

LAST_VALUE()

Aggregate Window Functions

SUM()

AVG()

COUNT()

MIN()

MAX()

SQL Joins

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

CROSS JOIN

SELF JOIN

Common Table Expressions (CTEs)

What is a CTE?

Basic CTEs

Multiple CTEs

Recursive CTEs

CTEs combined with joins and aggregations

SQL Subqueries

What is a subquery?

Scalar Subqueries

Non-correlated Subqueries

Correlated Subqueries

Nested Subqueries

Subqueries using IN

Subqueries using EXISTS

Subqueries using ANY

Subqueries using ALL

Subqueries in SELECT, FROM, and WHERE

Subquery in SELECT

Subquery in FROM

Subquery in WHERE

Conclusion

Power BI Data Modelling, Relationships and Joins.

Introduction

Data Modelling in Power BI.

Data Modelling Approaches

Flat Table

Star Schema

Snowflake Schema

Understanding Fact Tables and Dimension Tables

Connecting the Tables Using Keys

Understanding the Keys

Relationships in Power BI

One-to-Many Relationship (1:*)

One-to-One Relationship(1:1)

Many-to-Many Relationship(:)

Other Important Relationship Concepts

Primary Keys and Foreign Keys

Cardinality

Unique Values and Referential Integrity

Active and Inactive Relationships

Filter Direction in Power BI

Single-Direction Filtering

Bidirectional Filtering

Joins in Power Query

Left Outer Join

Right Outer Join

Inner Join

Full Outer Join

Understanding `OVER()`, `PARTITION BY`, `ORDER BY`, and window frames

The `OVER()` Clause

The `PARTITION BY` Clause

The `ORDER BY` Clause

Subqueries using `IN`

Subqueries using `EXISTS`

Subqueries using `ANY`

Subqueries using `ALL`

Subqueries in `SELECT`, `FROM`, and `WHERE`

Subquery in `SELECT`

Subquery in `FROM`

Subquery in `WHERE`