Modelling, Joins, Relationships and different Schemas

Pepsy-11y — Sun, 21 Jun 2026 12:14:25 +0000

Data Modelling is the invisible architecture that structures your data so it is fast accurate and scalable.
It is the process of identifying, organizing and defining the types of data a business collects and the relationship between them.

Joins
1.Left OuterJoin
Keeps all rows from the first(left) table and adds the matching rows from the second (right) table.
If there is no match,it returns null for the right table's column.

2.Right Outer Join
It returns all the rows from the right table that are specified in the right outer join clause, not just the rows in which the columns match.

3.Inner Join
Also known as a simple join, returns rows from joined tables that have matching rows.

4.Full Outer Join
It returns all joined rows from both tables, plus one row for each unmatched left row(extended with nulls on the right), plus one row for each unmatched right row(extended with nulls on the left).

5.Cross Join
A cross join returns all possible combinations of rows of two tables(also called a Cartesian product).

Relationships
1.Cardinality

One-to-Many
On the one-side of the relationship the column must have unique values; on the many-side the value can(and usually does) contain duplicates.
One-to-One
On both sides of the relationship the columns need to have unique values. A more accurate name would be "zero-or-one"-to-"zero-or-one" relationship because the presence of a row in one table does not imply the presence of a corresponding row in the other table.
Many-to-Many
On both sides of the relationship the columns can have duplicates.
These occur when neither table has unique values, or when you are trying to directly connect two fact tables.

2.Active vs Inactive relationships
Active: Represented by a solid line; used automatically by Power BI visuals.
Inactive: Represented by a dashed line ; ignored by default unless explicitly invoked.

Different Schemas

Star Schema It is a straightforward and commonly used data modeling approach in Power BI. It consists of a central fact table connected to multiple dimension tables, forming a star-like structure. Advantages are:

Simplicity
Query Performance
User-Friendly

2.Snowflake Schema
It is an extension of the star schema, where dimension tables are further normalised into multiple related tables, forming a snowflake-like structure.
Advantages are:

Reduced Redundancy
Scalability
Detailed data organization.

How Excel is Used in Real-World Data Analysis

Pepsy-11y — Sun, 07 Jun 2026 12:55:23 +0000

Excel is a spreadsheet software that allows you to collect, organize, analyze, calculate and visualize data efficiently. It is basically a table consisting of rows and columns. Rows are a horizontal group of cells whereas columns are a vertical group of cells.
A cell is an individual block within a table which you can enter values such as words or numbers.

Excel data analysis involves taking raw datasets and transforming them into actionable insights. It empowers users to clean, summarize and visualize trends without needing advanced programming skills.
Excel is a go-to software for professionals across industries.

Ways Excel is Used in Real World Data Analysis
1. Financial Analysis-Banking and finance professionals use Excel for budgeting, forecasting, risk analysis and investment tracking.

Marketing Analysis-Marketers use Excel to track and analyze campaign performance, customer segmentation and ROI calculations.
Supply Chain and Inventory Management-Excel is widely used for inventory tracking, demand forecasting and supply chain optimization.

** Excel Features and Formulas**

Formulas

a. VLOOKUP-This function searches vertically the first/leftmost column of a table until a value that matches or exceeds the one you are looking up is found.

Syntax is:
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]).

b. HLOOKUP-This function stands for horizontal lookup. It searches for a value horizontally in the first row of a table and returns a value from the same column in a row you specify.

Syntax is:
=HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup]).

c. IF-The IF function is often used when you want to sort your data according to a given logic.

Pivot Tables

A pivot table is an interactive way to quickly summarize large amounts of data, to analyze numerical data in detail and to answer unanticipated questions.

Sorting and Filtering

Sorting allows for alphabetic, numeric, colour and even multi-level organization.
Filtering allows data to be limited to only display data which meets the criteria of the filter.

Conclusion
Excel has become an essential tool by creating financial models, helping businesses in making informed decisions, improving operational efficiency and optimizing workforce strategies throughout various sectors.

DEV Community: Pepsy-11y

Modelling, Joins, Relationships and different Schemas

How Excel is Used in Real-World Data Analysis