DEV Community: Gabriel Njoroge

Understanding SQL: DDL,DML,Filtering and DCL.

Gabriel Njoroge — Mon, 13 Apr 2026 06:30:31 +0000

SQL is an abbreviation for Standard Query Language. It is a programming language that is used to manage and work with data in databases.
Sql is a very important tool as it is used in all types of industries be it Banks, Hospitals, Schools, Mpesa. All these have databases that work behind the scenes
DBMS stands for a Database Management System which is a software used to create, manage and interact with database e.g Postgresql which i use in my class projects. We also have others like Mysql, IBM DB2,Mongo db e.t.c, There are two types of DBMS

Relational Database Management Systems
They store data in structured tables(rows&columns connected using
relationships(Dimension,fact tables).
-Non-Relational Database Management Systems
They store data in files like .json,.xml,.They are in flexible formats instead of fixed formats
Key Concepts in SQL
Data Query Language
It is used to retrieve data from database. Most common is select, where(filter),Order by(sort),Group by(group data).The main essence of DQL is that it does not change data but it gives information about data.
Data Manipulation Language
It is used to change data inside a database. It includes

Insert Statement for inputting data. e.g code snippet below shows how to inserts students data in the students table.

Update Statement It changes existing data into a database e.g The pic shows an update statement that enters correct marks for result_id=5

Delete Statement It is used to remove or drop data from database e.g you can be told that exam result with result_id 9 has been cancelled by the school. Write a DELETE statement to remove it from the exam_results table. This is how you shall implement the delete statement

Data Definition Language It is used to change the structure of a table. We have create statement to create table, alter is used to modify structure of database/Rename column/table, drop is used to delete database. A code snippet of how to delete a column is shown below. It drops the column city in the customers table.

Data Control language It is used to control access to database. It focuses mainly on Security and access control where we have the grant which gives permission and revoke which removes permission.

Key point to note is that Data Query Language is used mainly by Data Analysts. Data Manipulation Language and Data definition Language are used by Data Engineers while Data Administrators use
Data Control Language.

Practical Implementation of Power BI Report Embedding in Modern Website(Step-by-Step Guide)

Gabriel Njoroge — Mon, 06 Apr 2026 06:08:51 +0000

Last Week at LuxdevHQ we dealt with Power BI Concepts like Data Modelling, Power BI Queries, Joins, Charts, Dashboards, and reporting. These processes lead to a proper data analysis. Besides that, there is need to publish the reports online. The importance of publishing reports online cannot be underestimated
in the digital landscape we are in. It is advantageous and of immense value to orgazisations and business that want to thrive in the digital space.
The Importance of Publishing Power Bi Reports Online

It eases team work as people can view reports from anywhere
The reports can be accessed from any device
Data Access is real time
It enhances collaboration as multiple users can share insights.

Thus I saw the need to come up with a Step by Step guide to publishing Power BI Reports and Embedding it in a Website

Steps
Log in to your Power BI Account. Work on your Project/Assignment It should show your credentials on the top right corner of you screen as follows:

Open a New tab on your Preferred Browser(Google chrome) and type Power BI Service. Sign in with your Power BI Credentials and it will lead you to a Landing page as follows.

Click on Workspaces, Add a new workspace and fill in your name in the form below and click Apply

Go Back to Power BI on the Project you working on and Click
on Publish as highlighted by the pointer:

Go back to Power BI Service, Select on Workspaces and Click on your project/assignment. It should lead to a window as shown below

Click on File, Publish to Web. Then Once again Click on File, Embed Report, Website/Portal. It shall guide you to a form where you copy the part named "HTML to paste on a website" as shown below

Create a folder in your machine, Open folder in your preferred
text editor(VS Code),create file and give it a name e.g(Index.html)

Paste code(Iframe) on the file as shown below

Save your job, Go back to the folder that contains the file we
created(Index.html) and open.It will open in your Web browser and you will have successfully
Published your Power BI Report and Embedded it on a Website as shown:

Understanding Data Modelling in Power BI: Joins, Relationships and Schemas Explained.

Gabriel Njoroge — Sun, 29 Mar 2026 18:47:45 +0000

Data Modelling is the most important aspect of a Business Intelligence Implementation. Without a proper data model,
the business will not get a true picture of the data. Data Modelling is a process of organizing the data elements and defining how they relate with each other. A good data model adheres to the business rules and assists in quick decision making.
Combining Queries Using Joins
Power BI Supports combining queries using the merge queries option in Query Editor. It supports all the different types
of SQL Joins. Joins are used to display the data from multiple tables or when merging two tables based on a join condition. These are the various types of joins present in Power BI

Inner Join: Only the matching rows between the two tables are returned, else it returns zero rows.
Left Join:It returns all the rows from the first table and only the matching rows from the second table.
Right Join: Right join is opposite to the left join. It returns all the rows from the second table and only the matching rows from the first table.
Full Outer Join:It returns all the rows present in the first and the second table.
Left Anti:It returns all the rows from the first table which do not have a match in the second table.
Right Anti:It returns all the rows from the second table which do not have a match in the first table.

Power BI Relationships
Relationship in Power BI is an important activity while creating a data model. Relationship between two tables work by matching the data in the Key Columns. In a typical environment, two tables are created based on the Primary Key on one table and foreign Key on another table. Usually the columns/fields having the same name between the tables are assumed to be related, though this may be true/not.
There are three Kinds of relationships/cardinalities between the tables which are as follows

Many to one(*:1):This is the default relationship and means that many rows in one table relates to one row in another table.
One to One(1:1):In this relationship, one row in the first table is related to one row in the second table.
Many to Many:In this relationship, many rows in the first table relates to many rows in the second table

Key Concepts to Consider in Data Relationships

Cross Filter Direction

Single Direction-Filter flows one way
Bi-directional-Filter flows both ways

Active/Inactive Relationship

Active-used by default in visuals
Inactive-Must be activated by DAX
(USERELATIONSHIP())
The Main difference between Joins and Relationship is a join combines tables into one single table while a relationship
links tables but keeps them separate

DIMENSION AND FACT TABLES
Dimensions and Facts: Dimension and Fact tables are the
main ingredients of any Business Intelligence implementation.
They are used to form the Star or the Snowflake Schemas which
are designed as part of building a data warehouse or a data
mart.

Dimension table:It contains the qualitative/descriptive
attribute of the data. For example, Customer Dimension may contain information about the Customer such as Customer Name,
address, contact number and so on. The dimensions field contain
the textual/character type of data. It contains the Primary Key
with the respective foreign key in the fact table.
There are different types of Dimension Tables, some of the commonly used ones are as follows
Slowly Changing Dimensions(SCD):It is a dimension table where the row of data in the table varies with time. It is used
to track current and historical data.
Conformed Dimensions:A dimension table is said to be conformed if it has the same context and content when used with
the different fact tables
Role-Playing dimensions:A Single dimension table can be joined many times to a fact table. This can be done by creating
multiple copies of the dimension.

Fact Table:It contains the foreign keys of the dimensions table. It stores the quantitative attribute of the data.

Some of the other common fact tables are

Fact-less fact-table:This fact table contains only the foreign keys of the dimension tables and does not contain any
measure values.
Conformed fact tables:Similar to the conformed dimensions,
the conformed fact tables are used across multiple dimension
models.
The dimension table and fact table are the basis of the star schema.

Star Schema
Star Schemas are created in a data warehouse and data mart
environments. It consists of the fact and dimension tables. The
shape of the star schema is such that the fact table is in the
middle, surrounded by multiple dimension tables. The Schema
assumes the shape of a star and hence the name.
A star schema supports querying huge amount of data stored in
a typical data warehouse storage system. The queries run against
the star schema are faster due to the reduced number of joins to
query the data. Since the fact table contains the foreign keys of
the dimensions table, during the ETL Process, the dimension tables
are loaded before the fact tables.
Snowflake Schema
It is also used in the data warehouse and data marts. It is an extension of the star schema. In a star schema, each dimension is
stored in a single dimension table, whereas in a snowflake schema,
a dimension explodes or has a lookup table.

How Excel is Used in Real World Data Analysis

Gabriel Njoroge — Mon, 23 Mar 2026 10:35:04 +0000

Excel is a Spreadsheet Software by Microsoft that helps to turn raw data(numbers, text, date)into meaningful information/insights. It is used to organize data, do calculations, visualizations and create dashboards. Therefore What is Data Analysis? Data Analysis is the application of tools and techniques to organize, study, reach conclusions, and sometimes make predictions about a specific collection of information. Excel is one of the most powerful tools that is used to do Data Analysis. The point of data analysis is to understand information on some deeper, more meaningful level. By definition, raw data is a mere collection of facts that by themselves tell you little or nothing of any importance. To gain some understanding of the data, you must manipulate the data in some meaningful way. The purpose of manipulating data can be something as simple as finding the sum or average of a column of numbers or as complex as employing a full-scale regression analysis to determine the underlying trend of a range of values. Both are examples of data analysis, and Excel offers a number of tools — from the straightforward to the sophisticated — to meet even the most demanding needs.The data part of data analysis is a collection of numbers, dates, and text that represents the raw information you have to work with. In Excel, this data resides inside a worksheet, which makes the data available for you to apply Excel’s satisfyingly large array of data-analysis tools.

Various Features/Tools Used In Excel For Data Analysis

Data Cleaning-It helps to remove duplicates, handle blanks, fixing errors and ensuring correct data types. This is essential as it harmonizes data that will be used for analysis thus come up with correct insights.
Data Sorting and Filter-Sorting is arranging data in a certain order e.g Sort A to Z incase of text, Smallest to largest incase of Numbers, Newest to Oldest incase of Date. Filtering allows to display rows that meet a certain criteria thus it is needed for specific records.
Functions-Built in formula in Excel that is used to calculate results.e.g Number functions SUM(),AVERAGE(),COUNT(),MIN(),MAX()
Text functions used to manipulate string data e.g TRIM(),UPPER(),
LEN(),MID(),CONCATENATE(),LEFT(),RIGHT().
Date functions-Include Today(),Now(),dateif(),
Data Validation-It is vital as it restricts what users can enter in a cell thus ensuring consistency in data.

Use Cases of Excel in Real World Scenarios

A Sales Manager uses excel to Study the Sales history of a Product, determine the Overall trend, and produce a forecast of future sales.
A Scientist uses excel to Study experimental findings and determine the statistical significance of the results.
A family might use excel to find the maximum mortgage it can afford or how much put aside each month to finance retirement or the Kids education.
Marketers use excel to store and organize Customer Information,Market Segmentation,Campaign tracking and Analysis,Sales Revenue and Analysis,Competitor Analysis.

Tools Required for Data Analysis and Data Science

Gabriel Njoroge — Wed, 18 Mar 2026 13:35:12 +0000

Basic Data Analyst Toolkit

Excel-It is a spreadsheet tool widely used for Data Analysis. It helps analysts organize, clean, analyze and visualize data using tables, formulas and charts.
Power BI-It is a Business Intelligence and Data Visualization tool used to analyze data and create interactive dashboards and reports
Anaconda-It is a software platform used for Data Analysis, Data Science. It provides an easy way to install python, libraries and tools needed for Data Analysis e. g Jupyter Notebooks, Spyder.
DBeaver-It is a free and open-source database management tool used by data analysts, data administrators to work with databases easily.
Aiven-It is a cloud platform that provides fully managed open-source data services. It helps companies run databases and data tools without managing the servers themselves.
Postgresql/Mysql-These are widely used databases for storing structured data.
Github-It is an online platform to showcase your projects/portfolio
GitBash-It is a terminal(command-line interface)that lets you run Git Commands and Linux-style commands on windows. It is installed together with Git. 9.VS Code-It is a free-code editor developed by Microsoft. It is widely used by developers, data analysts, and programmers to write, edit and run code.