DEV Community: Jason Ndalamia

Building an Interactive Excel Dashboard for E-commerce Product Analysis: A Case Study of Jumia Products

Jason Ndalamia — Wed, 30 Sep 2026 06:19:13 +0000

E-commerce data can look simple at first: product name, price, discount, reviews and rating. But turning a raw export into something a seller can actually use requires much more than making a few charts.

In this project, I used Excel 2010 and 2021 to clean, analyse and build a dashboard from a raw Jumia product export. The goal was to understand how discounts, prices and customer feedback relate to product performance.

The business question

The project brief asked four practical questions:

Are higher discounts leading to higher customer engagement?
Do highly rated products have higher or lower prices?
Which products show strong performance or need pricing attention?
What actions can sellers take from the evidence?

The original export contained 115 listings. After cleaning and removing exact duplicate rows, the final analytical dataset contained 112 records.

Figure 1: Raw export before cleaning.

Step 1: Cleaning the raw data

I kept the original data untouched and created a separate cleaned dataset.

The main cleaning tasks were:

removing exact duplicate rows;
converting price text into numeric values;
handling price ranges by using their midpoint;
converting negative review counts to positive counts;
converting ratings such as 4.6 out of 5 to numeric ratings;
retaining missing ratings as blank rather than treating them as zero; and
recording the decisions in a cleaning log.

Three duplicate rows were removed, leaving 112 records.

Figure 2: Cleaned dataset.

Step 2: Enriching the dataset

I added four fields needed for the analysis.

Discount Amount

Discount Amount = Old Price - Current Price

Rating Category

The project brief left a gap between 4.0 and 4.5, so I explicitly added a Good category:

below 3 → Poor
3 to 4 → Average
above 4 to 4.5 → Good
above 4.5 → Excellent
blank → Not Rated

Discount Category

below 20% → Low
20% to 40% → Medium
above 40% → High

Price Category

I used current-price quartiles:

Low: up to KSh 493
Medium: above KSh 493 to KSh 1,669.50
High: above KSh 1,669.50

Figure 3: Example formulas and enrichment fields.

Step 3: Descriptive statistics

The cleaned dataset contains:

Metric	Result
Products	112
Rated products	57
Unrated products	55
Total reviews	723
Average current price	KSh 1,186.89
Average old price	KSh 1,811.11
Average discount	about 36.8%
Average rating	3.89 / 5

The current price ranged from KSh 38 to KSh 3,750.

Step 4: PivotTables and product rankings

PivotTables were used to compare categories and create ranking tables without changing the cleaned source data.

Figure 4: PivotTables used for category analysis and rankings.

The Top 10 by Reviews were led by the 120W Cordless Vacuum Cleaner with 69 reviews, followed by the 137 Pieces Cake Decorating Tool Set with 55 and the Electronic Digital Display Vernier Caliper with 49.

The Top 10 by Rating contained seven products rated 5.0 and three rated 4.8.

Step 5: Testing the relationships

Three scatter plots were used to test the numeric relationships:

Discount % vs Reviews
Rating vs Reviews
Current Price vs Rating

Figure 5: Relationship analysis.

Discount vs Reviews

The correlation was 0.0122 with R² of 0.00015.

That means there was essentially no linear relationship between discount percentage and review count in this sample.

Rating vs Reviews

The correlation was 0.0572 with R² of 0.0033.

This is a very weak positive relationship.

Current Price vs Rating

The correlation was 0.1101 with R² of 0.0121.

The relationship is weak, although the category-level averages showed that high-price products had a higher average rating than low-price products (4.08 vs 3.64).

Figure 6: Category PivotCharts used in the dashboard.

Step 6: Outliers and exception groups

I also looked at combinations that could be more useful for sellers than a single metric.

High discount + poor rating

There were 10 products in this group.

For example:

120W Cordless Vacuum Cleaner — 49% discount, 69 reviews, 2.8 rating
Intelligent LED Body Sensor Wireless Lighting Night Light USB — 52%, 15 reviews, 2.7
5-PCS Stainless Steel Cooking Pot Set — 55%, 13 reviews, 2.1

High discount + little engagement

There were 32 products with a High Discount and zero reviews.

Strong customer demand

Using the upper quartile of reviews (7) as the threshold, 32 products had 7 or more reviews.

Strong demand + average rating

Five products had 7 or more reviews but only an Average rating. This is an important group because engagement was present, but customer satisfaction was not in the top categories.

Step 7: Building the dashboard

The final dashboard was designed as a visual canvas rather than as another analysis sheet.

It contains:

five KPI cards;
three Top-10 summary tables;
three relationship scatter plots;
three category PivotCharts; and
slicers for Rating Category, Discount Category and Price Category.

I kept the underlying PivotTables and analysis on separate sheets and used the dashboard for presentation.

Figure 7: Final Jumia Product Performance Dashboard.

What did the data tell us?

Are higher discounts leading to higher engagement?

Not in this sample. The correlation between discount and reviews was only 0.0122. In fact, the Medium discount group had the highest average review count at 10.97, compared with 5.39 for High discounts.

This means a bigger discount should not automatically be assumed to produce more customer engagement.

Do highly rated products have higher or lower prices?

There was a weak positive relationship between price and rating (r = 0.1101). High-price products averaged 4.08 compared with 3.64 for low-price products, but the relationship was weak at the individual-product level.

Which products need the most attention?

The strongest attention signals were the combinations rather than any single ranking:

10 products had both High Discount and Poor Rating.
32 had High Discount and zero reviews.
2 had High Price and Poor Rating.

Three practical recommendations

1. Test discount levels instead of assuming deeper discounts are better

The relationship between discount percentage and reviews was essentially zero in this sample.

Use controlled pricing tests and compare engagement outcomes rather than automatically increasing discounts.

2. Investigate product experience before increasing promotions on poorly rated products

A large discount does not solve a product-quality or customer-experience problem. Products with high discounts and poor ratings should be reviewed for product quality, specification accuracy, images, fulfilment and customer feedback before further promotion.

3. Use price, rating and reviews together

Price alone was only weakly associated with rating. Sellers should evaluate the full picture: price position, review volume, rating, customer comments and the product's value proposition.

A few lessons from the project

The biggest lesson was that dashboard building is not the same as analysis.

The analysis sheets answer the questions. The dashboard should make the answers easy to understand.

Final takeaway

The most important result was not a single product or a single discount percentage. It was the lack of a simple relationship between discount and reviews, combined with the presence of several products where high discounts coexisted with poor ratings or no engagement.

For sellers, that suggests a more targeted approach: understand customer response, monitor product quality and use pricing as one part of the strategy rather than treating discount depth as a universal solution.

Project resources

Project brief: https://www.dataeast.africa/projects/jumia-product-performance-dashboard

From One Variable to Many: A Beginner's Guide to Univariate, Bivariate, and Multivariate Analysis

Jason Ndalamia — Thu, 24 Sep 2026 14:16:21 +0000

Imagine you're given a dataset containing information about 10,000 customers: age, income, purchases, advertising exposure, satisfaction, and churn.

Where do you start?

You could jump straight into machine learning, but a better first step is to understand the data.

This is where three fundamental types of analysis come in:

Univariate, bivariate, and multivariable/multivariate analysis.

Think of them as three levels of questions:

Univariate: What does one variable look like?
Bivariate: How are two variables related?
Multivariable: What happens when several variables are considered together?

Let's break them down.

1. Univariate Analysis: Understand One Variable

Univariate analysis focuses on one variable at a time.

Suppose we want to understand customer age.

We might ask:

What is the average age?
What is the median?
How spread out are the ages?
Are there unusual values?
Is the distribution skewed?

Common measures include:

Mean, median, mode, range, standard deviation, quartiles, and percentiles.

We can also visualize the variable using a:

Histogram
Box plot
Bar chart for categorical data

Univariate analysis is often the first step because it can reveal outliers, missing values, unusual categories, or unexpected distributions before we move into more complex analysis.

Before asking how variables relate to each other, understand each variable on its own.

2. Bivariate Analysis: Look at Relationships

Now suppose we want to examine advertising exposure and purchases.

We have two variables, so this is bivariate analysis.

We might ask:

Do customers who see more advertisements tend to make more purchases?

For two numerical variables, we can use:

Scatter plots
Correlation
Simple linear regression

For example, a correlation of 0.78 suggests a strong positive linear relationship.

But there's an important lesson:

Correlation does not mean causation.

If ice-cream sales and swimming accidents both increase during the same period, that doesn't mean ice cream causes accidents. A third factor, such as hot weather, may influence both.

So whenever you find a relationship, ask:

What else could explain it?

Bivariate analysis can also use other methods depending on the types of variables involved. For example, categorical variables can be examined using cross-tabulations and chi-square tests.

3. Multivariable Analysis: Real-World Data Is More Complicated

In reality, outcomes rarely depend on just one factor.

Customer purchases might be influenced by:

Advertising
Income
Age
Satisfaction
Product price

Instead of asking:

"Is advertising related to purchases?"

we can ask:

"How are advertising, income, age, and satisfaction related to purchases when considered together?"

This is where multiple regression can be useful.

A simplified model might look like:

$$
Purchases = \beta_0 + \beta_1(Advertising) + \beta_2(Income) + \beta_3(Age) + \beta_4(Satisfaction)
$$

The important idea is not the formula. It's the question:

What happens when we consider several predictors at the same time?

This is much closer to how real-world data works.

Watch out for multicollinearity

When predictors are strongly related to each other, we can have multicollinearity.

For example, using both:

Age
Years since graduation

might introduce overlapping information.

This can make it harder for a regression model to estimate the individual contribution of each predictor.

Multivariable vs Multivariate

You may see these terms used interchangeably, but technically they are different.

Multivariable analysis usually involves:

Multiple predictors → one outcome

For example:

Salary ← Education + Experience + Location

Multivariate analysis involves multiple outcome variables being analyzed together.

For beginners, the key idea is simply:

As the number of variables increases, the questions and analytical techniques become more complex.

A Simple Way to Remember

Imagine you're analyzing customer data.

Univariate

Age

What does the age distribution look like?

Bivariate

Advertising + Purchases

Are they related?

Multivariable

Advertising + Income + Age + Satisfaction → Purchases

What happens when we consider several factors together?

That's the progression.

Why This Matters

It's tempting to jump straight into machine learning, but strong data analysis starts with understanding the data.

A simple workflow is:

Understand → Explore → Compare → Model → Interpret

The goal isn't to use the most complicated statistical method.

It's to use the right method for the question you're trying to answer.

And that is where good data analysis begins.

The Secret Engine of Data Science: Why You Can't Ignore Statistics

Jason Ndalamia — Sat, 19 Sep 2026 10:15:36 +0000

We often hear that data is the new oil. But just like crude oil, raw data has little value until it is processed and refined.

A large dataset may contain millions of records, but without the right analysis, it is simply a collection of numbers, patterns, and noise. Data science is about turning that raw data into useful information, insights, and decisions. Statistics is one of the main foundations that makes this possible.

Statistics helps us answer important questions:

What is happening in the data?
How much variation is there?
Are two things actually related?
Is a pattern meaningful, or could it have happened by chance?
Can what we observe in a sample tell us something about a larger population?
How confident should we be in our conclusions?

This is why statistics matters so much in data science. It helps us move from guessing to evidence.

Raw Data (The What)	Statistical Insight (The How/Why)
Millions of student scores	Identifying average performance and measuring variation
Customer ages and purchase dates	Finding patterns, trends, and unusual observations
Website activity logs	Determining whether a change is meaningful or simply random variation
Clinical trial results	Estimating treatment effects and measuring uncertainty

Statistics creates the bridge between raw numbers and meaningful conclusions.

And that bridge appears throughout the data science lifecycle, long before we train a machine learning model.

The Data Science Lifecycle: Where Statistics Shows Up

Statistics is not a single step that you check off during a project. It is something you use repeatedly throughout the data science process.

One common mistake among beginners is to jump straight into algorithms and model training. But before building a model, we need to understand the data, the problem, and the assumptions behind our analysis.

Here are some of the areas where statistics plays an important role.

1. Problem Definition

Before collecting or analysing data, we need to understand what success looks like.

For example, if a company wants to increase sales, we need to define what "increase" actually means and how we will measure it.

2. Exploratory Data Analysis (EDA)

Statistics helps us summarize the data, identify patterns, detect unusual observations, and understand how variables behave.

3. Data Preparation and Feature Engineering

Real-world datasets often contain missing values, inconsistent measurements, and categorical variables.

Statistical reasoning helps us decide how to handle these issues without introducing unnecessary bias.

4. Feature Selection

Not every variable in a dataset is useful.

Statistical techniques can help us identify relationships between variables, remove redundant information, and reduce unnecessary complexity.

5. Machine Learning and Model Evaluation

Statistics is also central to machine learning.

It helps us understand probability, estimate relationships, measure uncertainty, evaluate model performance, and determine whether a model is likely to generalize well to new data.

Exploratory Data Analysis: The Detective Work

Before building complex models, a data scientist needs to become a detective.

This is where Exploratory Data Analysis (EDA) comes in.

EDA is about looking at the data from different angles to understand what is actually there.

We might ask:

What does a typical observation look like?
Which values are unusual?
Are some variables strongly related?
Is the data evenly distributed?
Are there missing values?
Are there patterns that need further investigation?

Think of EDA like inspecting a house before buying it.

You do not move in immediately. First, you check the foundation, look for leaks, inspect the rooms, and make sure there are no major problems hidden from view.

EDA serves a similar purpose in data science.

We use descriptive statistics to summarize what we already have, while inferential statistics helps us draw conclusions beyond the data we directly observed.

Descriptive Statistics: Summarizing the Story

Imagine having 100,000 customer records.

Looking at every row individually is not practical.

Instead, we use statistics to summarize the dataset.

Two important groups of descriptive statistics are measures of central tendency and measures of dispersion.

Concept	Examples	What It Tells Us
Central Tendency	Mean, Median, Mode	Where the center or typical value of the data lies
Dispersion	Range, Variance, Standard Deviation	How spread out the data is

Mean, Median, and Mode

The mean is the arithmetic average.

The median is the middle value when the observations are arranged in order.

The mode is the most frequently occurring value.

These measures can tell very different stories.

For example, imagine five salaries:

40,000 — 45,000 — 50,000 — 55,000 — 500,000

The mean would be heavily influenced by the extremely high salary.

The median, however, remains much closer to what most people in the group earn.

This is why understanding the data matters. Choosing the wrong summary statistic can give us a misleading picture.

The 5-Number Summary and the Box Plot

Another useful statistical tool is the 5-number summary:

Minimum
First Quartile (Q1)
Median
Third Quartile (Q3)
Maximum

These values are commonly used to construct a box plot.

Box plots are particularly useful for understanding the spread of data and identifying potential outliers.

For example, suppose most student grades fall between 40 and 80, but one student has a score of 2 or 100.

That observation may deserve further investigation.

Importantly, an outlier is not automatically an error. It could be a genuine observation, an unusual event, or a data-quality problem.

The goal of statistics is not simply to remove unusual values. It is to help us notice them and investigate them properly.

Inferential Statistics: Going Beyond the Data

Descriptive statistics tells us what is happening in the data we have.

Inferential statistics helps us make conclusions about a larger population using a sample.

For example, imagine a university has 20,000 students but we want to understand their average study time.

Measuring every student may be expensive and time-consuming.

Instead, we could take a representative sample and use statistical methods to estimate what is happening across the larger population.

This introduces several important concepts.

Sampling

The quality of our conclusions depends heavily on the quality of our sample.

Poor sampling can introduce bias.

Random sampling, stratified sampling, and other sampling methods help us build samples that are more representative of the population we are studying.

Hypothesis Testing

Suppose a company launches a new marketing campaign and sales increase by 10%.

Did the campaign actually cause the increase?

Or could the increase have happened because of normal variation, seasonality, or some other factor?

Hypothesis testing gives us a framework for investigating questions like this.

The same idea applies to many fields.

In healthcare, we may ask whether a treatment produces a measurable improvement.

In education, we may ask whether a new teaching method improves test scores.

In business, we may ask whether a new product feature increases customer engagement.

P-values

The p-value is often misunderstood.

A p-value does not tell us the probability that a hypothesis is true or false.

Instead, it helps us assess how compatible the observed data is with a particular null hypothesis.

A commonly used significance level is 0.05. When the p-value is below that threshold, researchers may describe the result as statistically significant.

But statistical significance does not automatically mean that an effect is large, important, or useful in practice.

That distinction matters.

Feature Engineering and Feature Selection: Less Is Often More

Real-world data is rarely clean.

We may have missing values, inconsistent formats, categorical variables, duplicated records, and irrelevant features.

This is where feature engineering becomes important.

Feature engineering involves transforming raw variables into useful features that a machine learning model can understand.

For example, instead of using a customer's date of birth directly, we might create an age variable.

Instead of using separate purchase date and transaction date fields, we might calculate days since last purchase.

Statistics helps us understand whether these transformations make sense and how the resulting variables behave.

Feature Selection

Feature selection is about identifying the variables that provide useful information to the model.

We can use techniques such as:

Correlation analysis
Statistical tests
Feature importance
Mutual information
Regularization methods

The goal is not simply to have fewer features.

The goal is to build a model using features that contain useful information while reducing unnecessary complexity.

One principle is especially important:

Correlation does not prove causation.

If two variables move together, that does not necessarily mean that one causes the other.

For example, ice cream sales and cases of sunburn may increase at the same time.

That does not mean buying ice cream causes sunburn.

A third factor—hot weather—could influence both.

This is one of the reasons statistical thinking is so important in data science.

The Statistical Roots of Machine Learning

Machine learning can feel very different from traditional statistics, but the two fields are closely connected.

Many machine learning methods rely heavily on concepts from statistics, probability, optimization, and mathematical modelling.

Consider a few examples.

Linear Regression

Linear regression models the relationship between variables and can be used to predict continuous outcomes.

For example:

Study Hours → Exam Score

We can use a regression model to estimate how changes in study time are associated with changes in exam performance.

Logistic Regression

Logistic regression is commonly used for classification problems.

For example:

Customer Information → Will the customer churn?

The model estimates probabilities that can then be used to classify observations.

Naive Bayes

Naive Bayes is based directly on probability theory and Bayes' theorem.

It is commonly used for classification problems such as spam detection and text classification.

Clustering

Clustering groups observations based on their similarity.

The concept of distance plays an important role in determining which observations are considered similar.

So while machine learning has developed far beyond traditional statistical modelling, the statistical foundations are still very important.

Model Evaluation: You Cannot Judge a Model by Feelings

Building a machine learning model is only half the job.

We also need to determine whether the model is actually performing well.

This is where evaluation metrics become important.

For classification problems, some common metrics include:

Precision

Of the observations predicted as positive, how many were actually positive?

Recall

Of all the observations that were actually positive, how many did the model correctly identify?

F1 Score

The F1 score combines precision and recall into a single measure using their harmonic mean.

Each metric answers a different question.

For example, in a medical screening system, missing a true positive may be much more serious than incorrectly flagging a negative case.

This is why choosing a model metric should depend on the business or real-world problem, not simply on which number looks highest.

Bias and Variance: Finding the Balance

Another important statistical concept in machine learning is the bias-variance trade-off.

Underfitting

A model is too simple to capture the important patterns in the data.

This is associated with high bias.

Overfitting

A model becomes too closely adapted to the training data, including random noise and small details that do not generalize to new observations.

This is associated with high variance.

A model that performs extremely well on training data but poorly on unseen data may be overfitting.

Statistics helps us recognize these problems through methods such as validation, cross-validation, error analysis, and comparison of training and test performance.

The real objective is not to memorize the training data.

It is to learn patterns that generalize to new data.

Practical Case Study: Predicting Student Success

Let's make this practical.

Suppose an education company has the following data:

Student ID	Study Hours	Attendance (%)	Previous Score	Final Result
S001	2	60	55	Fail
S002	5	85	70	Pass
S003	1	40	45	Fail
S004	4	90	75	Pass
S005	3	65	60	Fail

Even with this very small dataset, statistics can help us start asking useful questions.

Failure Rate

Three out of five students failed.

That gives us a sample failure rate of:

3 ÷ 5 = 60%

Average Study Hours

The average study time is:

(2 + 5 + 1 + 4 + 3) ÷ 5 = 3 hours

Attendance

The two students who passed had attendance of 85% and 90%.

Their average attendance was:

87.5%

The three students who failed had attendance of 60%, 40%, and 65%.

Their average attendance was:

55%

At first glance, attendance appears to be associated with better outcomes in this small sample.

Study hours also appear to follow a similar pattern.

But there is an important lesson here.

Five students are not enough to make a strong general conclusion about all students.

We can identify a pattern, but we would need much more data to determine whether that pattern consistently exists across a larger population.

This is exactly where statistical thinking protects us from overinterpreting our results.

Why Statistics Matters in the Real World

The value of statistics becomes even clearer when we look at real-world applications.

In healthcare, statistics helps researchers analyse clinical trials, estimate treatment effects, and understand uncertainty.

In finance, it helps analysts measure risk, understand market behaviour, and model financial outcomes.

In marketing, it helps organisations test campaigns, understand customers, and measure whether changes are producing meaningful results.

In cybersecurity, statistical analysis can help identify unusual network activity and detect potential anomalies.

In data analytics, statistics helps transform dashboards and reports from collections of numbers into useful evidence for decision-making.

And in machine learning, statistical concepts help us build, evaluate, and improve predictive models.

In other words, statistics is not something that sits on the side of data science.

It is woven into the entire process.

Conclusion: Statistics Is the Foundation, Not an Optional Skill

Data science is often presented as a field dominated by Python, SQL, machine learning algorithms, cloud platforms, and AI.

Those tools are important.

But tools alone do not make someone a good data scientist.

You also need to understand what the numbers mean, how reliable they are, what assumptions you are making, and how confident you should be in your conclusions.

That is where statistics comes in.

Statistics helps us:

Understand data
Detect patterns and anomalies
Measure variability
Test assumptions
Quantify uncertainty
Evaluate models
Avoid misleading conclusions
Make evidence-based decisions

The more complex the data becomes, the more important this foundation becomes.

You do not need to become a theoretical statistician before becoming a data scientist.

But you should understand the statistical ideas behind the methods you use.

Because writing Python code to calculate an average is easy.

Knowing why that average matters, when it can be misleading, and what you can legitimately conclude from it is the real skill.

And that is why statistics remains one of the most important skills in data science.

Beyond the Best Fit: 5 Counter-Intuitive Truths About Statistical Regression and Regularization

Jason Ndalamia — Mon, 27 Jul 2026 06:49:02 +0000

Imagine you are an ice cream vendor on a scorching Saturday. The temperature hits 32°C (90°F), and your sales explode. It feels like a simple game: as the temperature climbs, your profits follow a predictable, upward path. You draw a straight line through your data points and feel like you’ve cracked the code of the universe.

But then a rainy Tuesday arrives, or the school term starts, and your "perfect" line misses by a mile.

In data science, we often treat regression as a simple exercise in connecting dots. In reality, it is a sophisticated dance between signal and noise. To move from "Zero to Hero" in this field, you must look past the surface-level "best fit" and embrace the counter-intuitive logic that separates amateurs from experts.

Here are 5 truths that will change how you view your models.

1. The "Godly Line" vs. The Snapshot

The first step toward mastery is a lesson in humility. In statistics, we distinguish between the Population Regression Equation (the universal truth) and the Sample Regression Line (our best guess).

The population equation contains the "true" parameters— $β_{0}$ and $β_{1}$ . These represent the actual, underlying relationship in the entire universe of your data:

Y = β_{0} + β_{1} X + ε

However, as humans, we only ever have a snapshot—a sample of 10 Saturdays or 500 patients. We use this data to calculate estimates: $β^_{0}$ ("beta-hat zero") and $β^_{1}$ ("beta-hat one").

"There is a golden kind of Godly line that we can never know."

Every model you build is not the absolute truth; it is a snapshot-based estimate of a hidden reality. We calculate the "hats" precisely because the true population parameters remain forever out of reach.

2. The Paradox of the "Linear" Label

One of the most frequent "lightbulb moments" is discovering what actually makes a regression "linear." You might assume that a linear regression must always be a straight line. Paradoxically, a model can be a curve—like a parabola—and still be technically linear.

Consider this quadratic equation:

Y_{i} = β_{0} + β_{1} X_{i} + β_{2} X_{i 2} + ε_{i}

Visually, this is a curve. Mathematically, it is still a linear regression. Why? Because linearity refers to the parameters, not the variables.

Linear in Variables: $X$ cannot be squared or cubed. (Our model fails this)
Linear in Parameters: The $β$ coefficients are not multiplied together, squared, or used as exponents. (Our model passes this)

As long as your $β$ coefficients behave, you can bend, twist, and curve your variables to fit complex real-world data while staying firmly within the territory of linear regression.

3. The R-Squared Trap: The Art of the "Escape"

We all want a high $R^{2}$ , the measure of how much variation our model explains. But $R^{2}$ has a devious flaw: it is a "yes-man." If you add a completely nonsensical variable—like the current phase of the moon—your $R^{2}$ will always increase or stay the same. This is why we use Adjusted $R^{2}$ as our signal detector; it penalizes unnecessary complexity and drops when you add a useless variable.

To understand why more variables aren't always better, we look at Degrees of Freedom and the logic of the "Escape":

The 2D Prison: You need at least two points to draw a line. But with only two points, the line is "trapped"—it must pass through both, leaving zero possibility for error and an $R^{2}$ of 1.0. This isn't a good model; it's a model with zero freedom.
The 3D Plane: If you add a second variable (like rainfall), you are fitting a plane of cardboard in a 3D room. In this space, three points "trap" the plane completely.

A true regression requires the opportunity to show error. This only happens when the number of data points ( $N$ ) exceeds the number of parameters ( $k$ ):

N > k

Only when $N > k$ can the line or plane "escape" the data points and cut between them, revealing where the real signal lies.

4. The Geometry of Constraints: Valleys and Fences

When a model is too complex, it overfits—memorizing the past while failing to predict the future. To fix this, we turn to Regularization (Ridge and Lasso). Think of this as the mathematics of self-restraint.

Imagine a Contour Plot as a top-down view of a valley. The deepest part of the valley represents the lowest Residual Sum of Squares (RSS). Without constraints, your model runs straight to the bottom, even if it means blowing up coefficients to unstable sizes. Regularization builds a "fence" around the center of your parameter map.

Ridge Regression ( $L_{2}$ Norm): Creates a circular constraint. It shrinks coefficients toward zero smoothly, but because the circle has no sharp corners on the axes, it rarely sets them to exact zero. Use this when many variables contribute small effects.
Lasso Regression ( $L_{1}$ Norm): Creates a diamond-shaped constraint. Because a diamond has sharp corners aligned directly with the axes, the optimal solution frequently hits those vertices. This forces coefficients to exactly zero, automatically performing feature selection by eliminating useless variables.

5. The Generalization Paradox: Accuracy is the Sacrifice

The ultimate goal of data science is generalizability—the ability to perform accurately on unseen data. This brings us to the Bias-Variance Trade-off, the fundamental sacrifice in statistical learning.

To make a model better at predicting the future, you must intentionally allow it to be less accurate on your current training data.

Perspective	Objective
Stated Goal	Achieve a model that hits the bullseye every single time on training data (Low Bias).
Statistical Reality	A model that is "too perfect" on past data has likely memorized noise (High Variance).

By introducing a small amount of intentional bias during training, we prevent the model from overfitting to past quirks—allowing it to adapt reliably when tested against real-world data.

Conclusion: The Future of the Fit

We are evolving from an era of memorizing data to one of generalizing truth. Whether we are using regression to optimize biofuels for climate challenges or sifting through 15,000 genes in cancer research, the stakes of our fit have never been higher.

The next time you build a model that hits every single data point with a perfect $R^{2}$ , don't celebrate just yet. Ask yourself:

Is this model showing me a universal truth, or is it just afraid to show me its error?

Trust the model that has the restraint to be slightly imperfect today, so that it can be genuinely useful tomorrow.

Getting Started with Python: A Practical Introduction for Beginners

Jason Ndalamia — Wed, 06 May 2026 10:16:08 +0000

Welcome to the world of Python! If you are just starting your programming journey, you have chosen the perfect language. This beginner-friendly guide will teach you the fundamentals of Python in a simple, practical, and engaging way.

1. Introduction

What is Python? Python is a powerful, flexible, and dynamically typed programming language created by Guido van Rossum and first released in 1991. It is widely considered one of the most loved and used programming languages in the world.

Why is it Popular? Imagine you are cooking: would you rather follow a long, complex recipe full of jargon, or a simple one written in plain English? Python is designed to be highly readable, looking almost like spoken English rather than complex machine code. It allows you to write fewer lines of code to achieve the same result as other languages, like Java.

Python is highly versatile and serves as a "multi-tool" for a wide range of real-world scenarios. It is used for web development (powering apps like Instagram and YouTube), data analysis, AI, machine learning, and system scripting (automating repetitive computer tasks). It has even been used by NASA for rocket logic!

2. Installation

Before writing code, you need to set up Python on your computer.

Step-by-Step Guide:

Download Python: For Windows users, you can safely download the latest version (like Python 3.12) directly from the Microsoft Store. (Note: You can also download it from the official website at (https://www.python.org/downloads/), which is standard industry practice, though not explicitly linked in the provided sources. Please independently verify this URL.
Install: Run the downloaded installer. It is critical during this step to ensure Python is added to your system's "PATH" environment variable, which allows your system to recognize Python commands in your terminal.

Verify Installation: Once installed, open your Command Prompt (CMD) or terminal. Type the following command and press Enter to verify your installation:
python --version
(Note for Mac and Linux users: You may need to use python3 --version.)

3. Your First Program: Hello World

In Python, writing your first program is incredibly simple and avoids confusing symbols or extra fluff. Let's write a program that displays a message on the screen.
print("Hello, World!")

What the code does: Think of the print() command as a megaphone. You are giving Python a clear, direct instruction: "Hey, shout this message on the screen!". Because Python is an interpreted language, it runs your code line by line and immediately outputs the result.

4. Comments in Python

Comments are notes left in the code for humans to read. Python ignores them when running the program, but they are crucial for explaining what your logic does.

Single-Line Comments: You can create a single-line comment by using the hash symbol #. As a best practice, try to keep your comment lines from exceeding 72 characters.

# This line is ignored by Python
print("Hello!")  # You can also place comments after code

Multi-Line Comments: For longer explanations, you can use triple quotes (""") to create a multi-line string, which Python treats as a multi-line comment when not assigned to a variable.

"""
This is a
multi-line comment.
It is great for long explanations!
"""

5. Variables

Imagine your brain is a giant notebook; when you learn something new, you store it under a label. In Python, variables act as labelled boxes or containers where you store data to open and use later.

Rules for Naming Variables:

Must start with a letter (a–z, A–Z) or an underscore _ (e.g., _my_age).
Cannot start with a number (e.g., 1user is invalid).
Can only contain alphanumeric characters and underscores.
Names are case-sensitive (name and NAME are completely different).
Tip: Use descriptive names in snake_case (e.g., user_name instead of just x).

Examples of Different Data Types: Because Python is dynamically typed, you don't need to specify the data type—Python figures it out automatically based on the value you assign.

Integer (int) - Whole numbers like counting apples
age = 25

Float (float) - Decimal numbers like measuring height
height = 5.9

String (str) - Text enclosed in single or double quotes
name = "Alice"

Boolean (bool) - True or False values, like a light switch (ON/OFF)
is_logged_in = True

6. Operators

Operators allow you to perform calculations or compare data in Python.
Arithmetic Operators: Used to perform standard mathematical calculations.

a = 10
b = 3
print(a + b)   # Addition: outputs 13
print(a - b)   # Subtraction: outputs 7
print(a * b)   # Multiplication: outputs 30
print(a / b)   # Division: outputs 3.333...

Comparison Operators: Used to compare two values. They act as "Yes/No" questions and always result in a Boolean (True or False).

x = 10
y = 9
print(x > y)   # Greater than: True
print(x == y)  # Equal to: False
print(x != y)  # Not equal to: True

Logical Operators: Used to check multiple conditions at once using and and or.

age = 20
has_ticket = True`
# 'and' means both conditions MUST be True`
if age >= 18 and has_ticket:
    print("Entry allowed.")
else:
    print("Entry denied.")

Conclusion

You've just taken your first steps into Python! We covered how Python's readable syntax makes programming feel like writing English, how to install and run your first "Hello, World!" program, and how to use foundational concepts like variables, data types, and operators.

Integrating SQL Databases with Power BI for Advanced Analytics: A Complete Guide

Jason Ndalamia — Sun, 03 May 2026 10:04:07 +0000

Power BI is a powerful business analytics service developed by Microsoft that empowers users to visualise data and share interactive dashboards across their organisation. While Power BI can handle data from various sources, its true potential is unleashed when connected to robust data sources like SQL databases.

SQL databases—such as PostgreSQL, MySQL, and SQL Server—are the industry standard for storing and managing structured analytical data. They offer ACID compliance for reliable transaction processing, making them the perfect backbone for managing critical business information.
In this guide, we will walk you through connecting Power BI to both local and cloud PostgreSQL databases, modelling your data, and leveraging SQL skills for better reporting.

1. Connecting Power BI to a Local PostgreSQL Database

Many data analysts build prototypes using a local PostgreSQL database before deploying them to a production environment. Here is how to establish that connection:

Step 1: Open Power BI Desktop and Select Get Data

Launch Power BI Desktop. On the Home ribbon, click the Get Data button to open the data import dialog.

In the Get Data window, expand Database on the left and select PostgreSQL database.

Step 2: Configure the PostgreSQL Connection

In the connection dialog, you will be prompted for your server and database details. For a local machine, enter localhost (or 127.0.0.1) in the Server field, and type the name of your database (e.g., postgres or sales_db) in the Database field.

You can choose your Data Connectivity mode here—Import is typically recommended to copy tables into Power BI for fast, offline queries. Click OK.

Step 3: Authenticate and Load Tables

When prompted, select Basic authentication and enter your PostgreSQL username and password.

Once authenticated, the Navigator window will display all available schemas and tables. You can click each table to preview its data. Select the tables you want to import (for example, customers, products, sales, and inventory).

Click Load to import them directly or Transform Data to make changes first. Power BI will load the data into its data model.

Step 4: Verify Loaded Tables

After loading, the selected tables appear in the Power BI interface. The Data/Report view now lists the imported tables in the Fields pane on the right.

2. Connecting Power BI to a Cloud Database (Aiven PostgreSQL)

When using a managed cloud database like Aiven PostgreSQL, the process is similar but adds a security step.

Step 1: Gather Aiven Connection Details

In your Aiven web console, open the Overview page. Copy the Host name, Port, Database name, User name, and Password. Also note the SSL mode (usually "require").

Step 2: Download and Install the SSL Certificate

Aiven enforces encrypted (TLS) connections. Find the CA certificate link on the Overview page and download the ca.pem file.

To install the certificate on Windows:

Press Win + R, type certmgr.msc, and press Enter.
Expand the Trusted Root Certification Authorities folder.
Right-click Certificates, select All Tasks > Import.

Browse to select the ca.pem file (change file type to "All Files").
Ensure it is placed in the Trusted Root Certification Authorities store and click Finish.

Step 3: Connect from Power BI Desktop

Back in Power BI Desktop, go to Get Data > PostgreSQL database. Enter the Server as host:port (e.g., pg-instance.aivencloud.com:12345) and the Database name.

Step 4: Load Cloud Tables

Enter the Aiven Username and Password. If the SSL certificate was installed correctly, the Navigator will appear. Select your tables and click Load.

3. Loading Tables and Data Modeling

Once connected, you must define how these tables interact through data modeling.
A standard approach is the star schema, where a central fact table (like sales) connects to surrounding dimension tables (like customers and products). These links are formed by joining Primary Keys and Foreign Keys, creating a one-to-many relationship.

Why are relationships important?
Proper relationships allow Power BI to aggregate and filter metrics correctly. Without them, visuals could return incorrect numbers because the software wouldn't know how to join the data points across different tables.

4. Why SQL Skills Matter for Power BI Analysts

While Power BI's drag-and-drop features are incredibly powerful, foundational SQL skills separate good analysts from great ones. SQL allows you to:
Retrieve Data: Pull only specific columns to reduce memory consumption.
Filter Datasets: Use a WHERE clause to pre-filter data at the source, speeding up loading times.
Perform Aggregations: Use SUM, COUNT, or GROUP BY to push heavy calculations to the database engine.
Prepare and Shape Data: Handle null values, cast data types, and join tables into a single view before importing.

Conclusion

Connecting Power BI to SQL databases unlocks the highest level of business intelligence. By combining Power BI's visual capabilities with SQL's structural precision, analysts can build trustworthy, lightning-fast dashboards that drive strategic decisions.

Beyond the Basics: 5 Game-Changing Secrets of SQL Joins and Window Functions

Jason Ndalamia — Mon, 02 Mar 2026 07:05:39 +0000

1. Introduction: The Data Relationships Hook

Think of a database as a digital filing cabinet. In this architecture, information is organized into drawers—or schemas—such as HR, Finance, or Sales. Within these drawers sit the documents, which represent our individual tables.

While this structure ensures clean organization, real-world business intelligence rarely lives in a single drawer. To answer strategic questions, you must bridge the gaps between disparate tables without creating a "data mess". Whether you are joining sales records with inventory levels or HR data with finance budgets, mastering these relationships is what separates a basic query-writer from a Senior Architect. This guide distills complex SQL operations into high-impact takeaways, focusing on maintaining relational integrity while scaling your analysis.

2. The Horizontal vs. Vertical Divide: Joins vs. Unions

The most fundamental distinction in data architecture is how you choose to expand your result set.

Joins combine tables horizontally. They add columns based on a shared key (like a Primary or Foreign Key), making the result set "wider".
Unions combine tables vertically. They stack rows from one dataset on top of another, making the result set "longer".

For a UNION to be architecturally sound, the query must meet three strict structural requirements: the columns must be in the same order, have the same count, and—most importantly—possess matching data types. As an architect, you must remember that the database engine is indifferent to your column aliases but ruthless regarding data types.

"The database doesn't care about naming but cares about data types in Unions... If a VARCHAR is matched with an INT, the database will throw a mismatch error." — Data with Baraa

The Mental Model: Distinct vs. All From a strategy perspective, think of UNION as a DISTINCT operation for stacked rows; it automatically removes duplicates. If you need the "data as is" without the overhead of deduplication, UNION ALL is your preferred tool.

Sample Query: Vertical Stacking

-- Combining customer and employee names into a single master list
SELECT first_name, last_name FROM customers
UNION
SELECT first_name, last_name FROM employees
LIMIT 100;

3. The "Workhorse" Joins: Why Left is Often Better than Right

In relational logic, the Inner Join is the default state. When you use the JOIN keyword, SQL is explicitly looking for matching values in the ON statement. If a record in the first table—for example, a customer who hasn't placed an order yet—has no match in the second table, that record "disappears" from the final result set.

For high-level reporting where data integrity is paramount, the Left Join is the industry workhorse. It preserves every record from the left (first) table and fills missing data from the right table with NULL values. This ensures you don't accidentally drop crucial business entities (like customers or products) simply because they lack transaction history.

Architectural Insight: The Right Join is functionally redundant. Any Right Join can be reframed as a Left Join by simply reordering the tables. In production environments, Left Joins are the standard because they align with a left-to-right reading flow, making queries significantly easier to audit and maintain.

Sample Query: Production-Ready Left Join

-- Retrieving all customers and any associated event data
SELECT
  c.customer_id,
  c.first_name,
  e.event_name
FROM customers AS c
LEFT JOIN events AS e
  ON c.customer_id = e.customer_id
WHERE c.customer_id IS NOT NULL
LIMIT 500;

4. The Cartesian Chaos: Understanding the Cross Join

A Cross Join represents the "Cartesian Product" of two tables. Unlike other joins, it ignores matching values entirely, attaching every row of the second table to every row of the first. If you join a 1,000-row table with a 500-row table, you will generate a massive result set of 500,000 rows.

In standard relational reporting, Cross Joins are often avoided because they lack the primary/foreign key bond that defines logical relationships. Joining a car table and an item table by a shared color attribute "wouldn't make any sense" for data integrity. However, from a strategic standpoint, Cross Joins are powerful for generating permutations—such as creating a master grid of every possible product-color combination for an inventory audit.

5. Window Functions: Grouping Without the "Collapse"

The true "game-changer" for intermediate learners is the OVER() clause. Traditional GROUP BY operations "collapse" your data, rolling multiple rows into a single aggregate level. While useful for summaries, this loses the individual row detail.

Window Functions allow you to perform calculations across a result set while keeping every unique row intact. This allows you to view an individual's salary right next to the department average, or calculate a Rolling Total.

Strategic Use Case: The Rolling Total In finance and healthcare, tracking cumulative sums is vital. By adding an ORDER BY clause inside the OVER() window, you transform a static sum into a running balance.

Sample Query: Rolling Totals and Averages

-- Calculating a cumulative salary sum and a static department average
SELECT
  first_name,
  gender,
  salary,
  SUM(salary) OVER(PARTITION BY gender ORDER BY employee_id) AS rolling_total,
  AVG(salary) OVER(PARTITION BY gender) AS avg_dept_salary
FROM employees;

6. The Ranking Trio: Row Number, Rank, and Dense Rank

Sequencing data is a core requirement for leaderboards and performance tracking. SQL offers three nuances for handling ties within a window:
•Row Number: Assigns a unique, sequential integer to every row (1, 2, 3, 4). Even in the event of a tie, the numbers will not repeat.
•Rank: Assigns the same number to ties but skips the next position based on the count of duplicates. If two rows tie for 1st, the next is 3rd (1, 1, 3). This is "positional" ranking.
•Dense Rank: Assigns the same number to ties but keeps the next number sequential (1, 1, 2). This is "numerical" ranking.
Architectural Preference: DENSE_RANK is typically preferred for professional reporting. It ensures there are no "gaps" in your leaderboard, maintaining a clean hierarchy regardless of how many entities share the same value.7.

Conclusion: Levelling Up Your Query Game

Moving from a beginner to an advanced analyst is less about learning syntax and more about understanding the logic of relationships. Every time you approach a new dataset, you must make a strategic choice: do you need to collapse your data into an aggregate summary with a Join or Group By, or do you need to partition it with a Window Function to maintain row-level detail?

The absolute grounding rule of SQL is that it is a language of relationships. By mastering the nuances of how data stacks vertically via Unions or expands horizontally via Joins, you ensure the relational integrity of your architecture.

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

Jason Ndalamia — Sun, 08 Feb 2026 12:07:03 +0000

In the realm of business intelligence, the distance between a raw spreadsheet and a strategic decision is bridged by the data analyst’s technical workflow. Using Power BI, analysts do not merely report numbers; they architect a system that transforms chaotic inputs into clear, actionable insights. This process follows a rigorous path: harmonising messy data, structuring it for performance, applying business logic through DAX, and delivering clarity via interactive dashboards.

1. Taming the Chaos: From Messy Data to Trusted Information

Real-world data is rarely ready for immediate analysis. It arrives full of inconsistencies that can break calculations and skew results. Before any visualisation occurs, analysts use Power Query to clean and transform this raw material into trusted information.
• Harmonising Data: A common issue is the presence of "pseudo-blanks"—text entries like "NA," "error," "blank," or "not provided" mixed into columns. Power BI reads this as valid text rather than missing values. Analysts must use the "Replace Values" function to harmonise these into a single standard category, such as "unknown," to ensure accurate categorisation without deleting potentially valuable raw data.
• Ensuring Precision: Small formatting errors can lead to duplication. For instance, "Kenya " (with a space) and "Kenya" are treated as different values. Analysts use the TRIM function to remove leading and trailing whitespace, ensuring that categories aggregate correctly.
• Data Typing: Attempting to sum a column will fail if the data type is set to text. Analysts must rigorously define columns—setting revenue to "Decimal Number" for calculation while keeping identifiers like phone numbers as "Text" to prevent accidental aggregation.

2. The Blueprint for Speed: The Star Schema

A major pitfall in data management is the "flat table"—a single, massive spreadsheet containing every detail. This structure leads to duplication, wasted memory, and maintenance nightmares.
To solve this, analysts employ the Star Schema, a modelling technique that separates data into two distinct types:
• Fact Tables: These contain transactional metrics (e.g., Sales, Quantity, Total Revenue) and sit at the centre of the model.
• Dimension Tables: These contain descriptive attributes (e.g., Customers, Products, Stores) and surround the fact table.
This structure allows for "write once, use many" efficiency. When a store relocates from one city to another, the analyst updates a single row in the Dimension table, rather than millions of rows in the Fact table. This model ensures that when stakeholders ask complex questions, the relationships between tables allow filters to flow correctly, providing accurate answers instantly.

3. The Engine of Analysis: DAX Measures and Logic

Once the data is structured, DAX (Data Analysis Expressions) is the language used to extract business logic. Analysts distinguish between Calculated Columns (row-by-row logic) and Measures (dynamic aggregations) to answer specific business questions.
• Automating Business Logic: Analysts use logical functions like IF and SWITCH to automate categorisation. For example, a nested IF statement or a SWITCH function can scan phone number prefixes (e.g., 254, 256) and automatically classify the country of origin as Kenya or Uganda.
• Time Intelligence: Business decisions rely heavily on historical context. Using time intelligence functions like DATEADD and SAMEPERIODLASTYEAR inside a CALCULATE function, analysts can generate metrics like "Revenue Last Month" or "Revenue Last Year”. This shifts the context of the data, allowing a manager to instantly see if performance is trending up or down compared to previous periods without manual recalculation.
• Handling Complexity: Advanced iterators like SUMX allow for calculations that require row-by-row evaluation before aggregating, such as multiplying yield by market price for every single transaction to get a precise total revenue.

4. Visualising the Story: From Grids to Insights

A dashboard is not just a collection of charts; it is a tool for decision-making. Analysts select specific visuals to answer specific questions, ensuring the report is intuitive for non-technical stakeholders.
• Trends and Comparisons: To show how revenue evolves over time, analysts use Line Charts or Area Charts, which emphasise volume and trends. For comparing categories, such as revenue by county, Column Charts (vertical) or Bar Charts (horizontal) are used.
• Correlations: To test hypotheses, such as "Does higher profit correlate with higher revenue?", analysts use Scatter Charts. If the bubbles trend upward, it indicates a positive correlation, validating the business strategy.
• Managing High-Volume Data: When dealing with many categories (e.g., revenue by county and then by crop type), standard pie charts become cluttered. Analysts use Tree Maps or Decomposition Trees to visualise hierarchies and drill down into the data to understand exactly why a number is high or low.

5. The Executive View: The Dashboard

The final output is the Dashboard—a one-page summary designed to answer the most important questions at a glance.
• Immediate Health Checks: Critical numbers (Total Profit, Total Yield) are placed at the top using KPI Cards or Multi-row Cards. This ensures that decision-makers see the most vital metrics immediately.
• Interactivity: Static reports limit discovery. Analysts add Slicers to allow users to filter the entire dashboard by specific segments, such as "County" or "Crop Type." This transforms a generic report into a tailored tool for specific regional managers.
• AI-Driven Insights: Tools like Q&A allow users to type questions in plain English (e.g., "Total yield by crop type") and receive an instant visual answer, bridging the gap between technical data models and ad-hoc business inquiries.

Conclusion

By mastering these steps—cleaning data in Power Query, modelling with Star Schemas, calculating with DAX, and visualising in Power BI—analysts transform raw, messy data into a coherent narrative that drives real-world business action.

The Blueprint of Intelligence: Mastering Data Modelling and Schemas in Power BI

Jason Ndalamia — Sun, 01 Feb 2026 09:00:53 +0000

In the realm of business analytics, creating a visually stunning dashboard is often the final step of a much deeper process. The true backbone of every successful Power BI solution is data modelling.

Data modelling is the process of identifying, organising, and defining the data a business collects and the relationships between them. It involves creating visual representations of data structures to ensure that reports are not only accurate but also performant and scalable. As data volumes grow, the difference between a sluggish, confusing report and a high-speed analytical tool often comes down to the quality of the underlying model. The Building Blocks: Fact and Dimension Tables

To understand how to build a model, one must first distinguish between the two types of tables that inhabit it: Fact tables and Dimension tables.

1. Fact Tables

A fact table is the "main table" in your model, typically containing events such as sales transactions, hospital visits, or machine readings.
• Characteristics: They contain quantitative attributes (numbers) meant to be aggregated, such as "Revenue," "Yield," or "Quantity Sold".
• Structure: These tables are usually long and narrow. They often contain duplicate values because an event (like a specific product sale) can occur multiple times. They utilise keys (like Product ID) to link out to other tables.

2. Dimension Tables

Dimension tables contain the descriptive attributes used to slice, group, and filter the data found in fact tables.
• Characteristics: These tables hold information such as "Customer Name," "Product Category," or "Geographic Region".
• Structure: Unlike fact tables, dimension tables should contain unique values for the entity they describe (no duplicates). They are generally wider but contain fewer rows than fact tables.

Schema Design: Star vs. Snowflake

The arrangement of these tables is known as the schema. While different designs exist, the Star Schema is universally recognised as the gold standard for Power BI.

The Star Schema

In a star schema, a central fact table is surrounded by multiple dimension tables, resembling a star.
• Why it is preferred: The Power BI engine is optimised to work best with this structure. It reduces the number of joins required to filter data, creating a cleaner, more organised model.
• Benefits: It ensures DAX measures calculate faster, reports refresh more quickly, and the solution remains scalable even as data volume increases into the millions of rows.

The Snowflake Schema

The snowflake schema is a variant of the star schema where dimension tables are further normalised. In this design, dimensions branch off into other dimensions. For example, a "Product" table might link to a separate "Product Category" table, which in turn links to "Product Subcategory".
• The Trade-off: While this can be useful when fact tables exist at different levels of granularity (e.g., sales by product vs. targets by region), it generally adds unnecessary complexity. Extra relationships force filters to propagate through longer chains, which can negatively impact performance.

One Big Table (OBT)

Beginners often attempt to flatten all data into a single table. While this may work for quick prototyping or ad-hoc analysis, it is considered a transitory state. It limits functionality—such as time intelligence and handling multiple data grains—and often leads to performance challenges due to large file sizes and repetitive data storage.

The Glue: Relationships

A data model is only functional if the tables effectively talk to one another. This is achieved through relationships, which are defined by cardinality and cross-filter direction.

Cardinality

Cardinality defines how rows in one table relate to rows in another.
• One-to-Many: This is the ideal relationship for linking a Dimension table (one unique ID) to a Fact table (many transactions).
• Many-to-Many: This relationship type is problematic and should be avoided whenever possible. It typically arises when connecting two fact tables directly or when dimensions are not unique. Misusing this can lead to "ambiguous" results, duplicated totals, and incorrect reporting.

Directionality

• Single Direction: Filters flow from the "one" side (Dimension) to the "many" side (Fact). This is the recommended setting for most scenarios.
• Bi-directional (Both): This allows filters to flow in both directions. While it can solve specific problems (like filtering a slicer based on available data), it is computationally expensive and can produce unpredictable results by introducing ambiguity into the model path.

Why Good Modelling is Critical

The structure of your data determines the performance, flexibility, and accuracy of your reports.

Performance: Poor modelling choices—such as relying on "One Big Table" or using complex snowflake chains—can slow down data refreshes and visual rendering. Conversely, a star schema minimises the work the engine must do, allowing reports to scale to very large datasets without lagging.
Accuracy: Bad relationships jeopardise data integrity. For instance, analysing monthly sales targets against daily sales data using a many-to-many relationship can cause targets to be duplicated across every day, leading to vastly inflated and incorrect totals. A proper model ensures aggregations (sums, averages) are calculated correctly across different contexts.
Usability: A well-designed star schema groups attributes logically (e.g., all customer details in one Customer table). This makes the "Fields" pane cleaner and easier for end-users to navigate compared to searching through a massive, flat table.

Conclusion

While it is tempting to drag and drop raw data directly into visualisations, investing time in data modelling is non-negotiable for professional analysis. By adhering to the star schema, ensuring one-to-many relationships, and clearly distinguishing between facts and dimensions, developers can build Power BI solutions that are robust, accurate, and lightning-fast.

Introduction to MS Excel for Data Analytics

Jason Ndalamia — Sun, 25 Jan 2026 16:08:49 +0000

When people hear Data Analytics, they often think of complex programming languages like Python or SQL. However, the functions found in Excel are generally the same ones found in Power BI, SQL, and Python—the primary difference is just the syntax used for execution.

This guide will introduce you to MS Excel as a powerful analytics tool, covering everything from basic data cleaning to interactive dashboards using Pivot Tables.

1. Organising Your Data: Sorting and Filtering

Before analysing data, you must ensure it is organised.

Data Sorting: Sorting involves arranging data in a specific order.
Text: Arranges data from A to Z or Z to A.
Numbers: Arranges data from smallest to largest or largest to smallest.
Dates: Arranges data from oldest to newest or newest to oldest.

⚠️ Important Tip: When sorting, always expand the selection when prompted. If you don't, Excel might reorder only the selected column, which will disorient the rest of your data and cause records to be mismatched.

Data Filtering allows you to temporarily display only the rows that meet specific criteria while hiding the rest. You can toggle filters on or off using the shortcut Control + Shift + L.

Text Filters: Use these to find cells that "contain" specific words or "begin with" a certain letter.
Number Filters: Use these to filter for "Top 10" items or values "Greater Than" a specific number.

Sort & Filter dropdown menu in the Home tab

2. Cleaning Data with Text Functions

Raw data is often messy. Excel provides specific functions to clean and standardise text.

TRIM: Removes extra leading or trailing spaces that are often invisible but cause errors.
PROPER: Capitalises the first letter of each word (great for fixing names).
UPPER / LOWER: Converts text entirely to uppercase or lowercase.
CONCAT: Combines two or more text strings into one cell. In older versions of Excel, you can use the & (ampersand) symbol to achieve this.

Messy names and a 'Cleaned' column using =PROPER(TRIM(cell))

3. Automating Decisions with Logical Functions

Logical functions help you categorise data automatically based on rules.

The IF Function The IF function performs a test: it returns one value if the test is true, and a different value if it is false.

Example: Imagine you want to categorise salaries. If the salary in cell E2 exceeds 80,000, it is "High"; otherwise, it is "Low".

Formula: =IF(E2 > 80000, "High", "Low").

Nested IFs. If you have more than two categories (e.g., Old, Middle-aged, Young), you can use a Nested IF, which places a second IF function inside the first one.

AND / OR Logic You can combine IF with AND (where both conditions must be met) or OR (where at least one condition must be met).

AND Example: Assign a bonus only if experience > 30 years AND projects > 10.

Formula: =IF(AND(O2 > 30, P2 > 10), "Assign Bonus", "Do not Assign Bonus").

4. Connecting Data with Lookup Functions

Data is often split across different tables. Lookup functions allow you to retrieve data from one table and pull it into another.

VLOOKUP (Vertical Lookup) VLOOKUP searches for a value in the first column of a range and returns a result from a column you specify.

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

lookup_value: The ID you are searching for.
table_array: The range containing your data.
col_index_num: The column number containing the answer (e.g., column 5 for Salary).
range_lookup: Use FALSE for an exact match, which is recommended for IDs.

A VLOOKUP formula connecting an Employee ID to their Salary

5. Mastering Date Functions

Excel stores dates as serial numbers, allowing for powerful calculations.

TODAY: Returns the current date.
NETWORKDAYS: Calculates the number of working days between two dates, automatically excluding weekends.
DATEDIF: A "hidden" function that calculates the difference between two dates in years ("y"), months ("m"), or days.

6. The Power of Pivot Tables

Pivot tables are the ultimate tool for summarising data. They allow you to aggregate thousands of rows into a clear summary table without writing complex formulas.

How to create one:

Click a single cell inside your data range (avoid selecting the whole sheet).

Go to Insert > Pivot Table.

Drag and Drop fields:

Rows: For categories (e.g., Department).

Values: For numbers to calculate (e.g., Sum of Salary, Count of Employees).

Interactive Slicers: To make your report interactive, insert a Slicer. This is a visual button menu that filters your Pivot Table instantly when clicked.

A Pivot Table with a Slicer for 'Department' next to it.

Summary

Excel is more than just a spreadsheet; it is a robust data analytics environment. By mastering text cleaning, logical functions, lookups, and Pivot Tables, you can transform raw data into meaningful insights.

A Beginner’s Guide to Git and GitHub: From Installation to Your First Push

Jason Ndalamia — Fri, 16 Jan 2026 12:52:05 +0000

Starting my journey in Data Science, Analysis, and AI at LUXDevHQ felt like learning a new language while trying to build a house. One of the most important tools I’ve discovered along the way is Version Control.

In this guide, I’ll walk you through:

Setting up Git Bash
Connecting Git to GitHub
Mastering essential push and pull commands

1. What Is Git and Why Does It Matter?

Git is a Version Control System (VCS). Think of it as a save-point system for your code.

Why is Git important?

⏪ Time Travel – If you break your code, you can roll back to a version that worked.
🤝 Collaboration – Multiple people can work on the same project without overwriting each other’s work.
🧪 Experimentation – You can create branches to try new features without affecting the main project.

2. Setting Up Your Environment

Step A: Install Git Bash

Go to Git and download Git for your OS (I used Windows).
Run the installer. > 💡 Pro tip: You can keep the default settings for most options.
After installation, search for Git Bash in your applications. It looks like a terminal window.

Step B: Configure Your Identity

To let GitHub know who is uploading code, configure your global Git settings:

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

3. Secure Your Connection: Setting Up SSH Keys

Using SSH is the professional standard. It’s more secure and saves you from typing your password every time you push code.

Step 1: Generate Your SSH Key

Open Git Bash and enter (replace with your GitHub email):

ssh-keygen -t ed25519 -C your_email@example.com

• File Location: Press Enter to use the default location.
• Passphrase: As a beginner, I left this empty for convenience.

Step 2: Add Key to the SSH Agent

eval "$(ssh-agent -s)"
ssh-add ~/.ssh/id_ed25519

Step 3: Add the Public Key to GitHub

Copy the key:

cat ~/.ssh/id_ed25519.pub

Go to GitHub: Settings → SSH and GPG keys → New SSH Key.
Give it a name (e.g., "My Learning Laptop") and paste your key into the "Key" box.

Step 4: Test the Connection

Run:

ssh -T git@github.com

Success Check! If you see "Hi [YourUsername]! You've successfully authenticated", you are ready!

4. Navigating and Creating Your Project

Learning to navigate via Git Bash makes you much faster than using a mouse! Use these commands to create your first repository:
• Check Location: pwd (Print Working Directory).
• Go to Desktop: cd Desktop
• Create Folder: mkdir my-first-repo
• Enter Folder: cd my-first-repo

5. Tracking Changes (The Core Workflow)

Before sending code to GitHub, Git needs to "track" it locally. Run these inside your project folder:

Initialize Git: git init (Starts tracking the folder).
Check Status: git status (See what files Git notices).
Add Files: git add . (Stages all changes to be saved).
Commit: git commit -m "My first commit" (Creates the "save point").

6. Pushing Code to GitHub

Pushing sends your local save points to the cloud.

Step A: Create the Repository on GitHub.com

Log into GitHub, click the + icon → New repository.
Name it (e.g., my-first-project) and keep it Public.
Important: Leave "Add a README" unchecked to avoid conflicts.
Click Create repository.

Step B: Connect and Push

On the GitHub setup page, click SSH and copy the URL. Then run these commands one by one:

git remote add origin git@github.com:your-username/repo-name.git
git push -u origin main

7. Pulling Code from GitHub

If you work on a different computer, use Pull to download the latest updates from the cloud:

git pull origin main

📚 Resources to Keep Learning

• Official Git Documentation
• GitHub Skills: Interactive Courses
• Visualizing Git Commands (Game)

Conclusion: Congratulations! You've just set up a professional dev workflow. Git can be tricky at first, but keep practicing and it will become second nature. If you ran into any issues, drop a comment below and let's help each other out!