Experiment 1: Import Data from Different Sources
Aim
The objective of this experiment is to demonstrate the fundamental process of data
extraction by importing data from two common but different sources—a CSV file and an
Excel file—and loading it into a pandas DataFrame, a target system for in-memory analysis.
Theory
Business Intelligence (BI) begins with data. The first step in any BI or data analytics pipeline
is Data Ingestion or Extraction. This involves connecting to various data sources and loading
the data into a staging area or a data analysis tool.
Data Source: A location where data originates, such as a database, a flat file (like CSV,
TXT), a spreadsheet (Excel), a web API, or a data warehouse.
Target System: The destination where the data is loaded for processing. In data
science and analytics with Python, the pandas DataFrame is a common and powerful
target system for data manipulation and analysis.
ETL (Extract, Transform, Load): This experiment focuses on the 'E' (Extract) part of
the ETL process, which is the foundation for all subsequent data transformation,
modeling, and visualization tasks.
Code and Implementation
This Python script uses the pandas library to read data from a sales_data.csv file and a
product_data.xlsx file. For this experiment to run, you must first create these two files with
the content specified in the comments.
Code Output
Conclusion
This experiment demonstrates the critical first step of any BI project. In a real-world
scenario, this process would be scaled to pull data from numerous sources like SQL
databases, cloud storage, and APIs. By centralizing data into a consistent format (like a
DataFrame), businesses can create a single source of truth. This unified view is essential for
performing comprehensive analysis, building accurate reports, and generating reliable
insights to drive decision-making.
Experiment 2: Perform the ETL Process in Power BI
Aim
The goal is to perform a basic ETL (Extract, Transform, Load) process using the Power Query
Editor in Power BI. We will start with raw sales data, clean and reshape it, and then load it
into the Power BI data model for analysis.
Theory
ETL is a data integration process that is the backbone of business intelligence and data
warehousing. It involves three stages:
Extract: This is the process of retrieving data from one or more sources. In Power BI,
this is done using the "Get Data" feature.
Transform: This is the most critical stage, where raw data is cleaned and prepared.
Common transformations include data cleaning (handling nulls, duplicates), data
structuring (splitting columns, changing types), and data enriching (creating new
calculated columns).
Load: This is the final step where the transformed, high-quality data is loaded into
the target system. In Power BI, this means loading it into the internal data model,
making it available for creating reports and dashboards.
A robust ETL process ensures that the data used for analysis is accurate, consistent, and
reliable.
Implementation Steps
This practical provides step-by-step instructions for Power BI Desktop.
Prerequisite: Create a CSV file named raw_sales.csv with the following content:
Step 1: Extract (Get Data)
1. Open Power BI Desktop. On the Home ribbon, click Get Data and select Text/CSV.
2. Navigate to and select your raw_sales.csv file.
3. A preview window will appear. Click on Transform Data to open the Power Query
Editor.
Step 2: Transform (In Power Query Editor)
1. Remove Duplicates: Select the entire table, go to the Home ribbon, and click
Remove Rows -> Remove Duplicates.
2. Handle Missing Values: Right-click the UnitPrice column header, select Replace
Values, and replace (null) with 50.00.
3. Change Data Types: Change the type of the OrderDate column to Date. Ensure
UnitPrice and TotalSales are a Fixed decimal number.
4. Add a New Column: Go to the Add Column ribbon and click Custom Column.
o Create a Cost column with the formula [UnitPrice] * [Quantity] * 0.7.
o Create a Profit column with the formula [TotalSales] - [Cost].
o Change the data type of the new columns to Fixed decimal number.
Step 3: Load
1. From the Home ribbon in Power Query Editor, click Close & Apply.
Result
After clicking "Close & Apply", the transformed data is loaded into Power BI. The final table
in the Data View is clean, enriched, and ready for analysis, without duplicates or missing
values.
Data After Transformation:
Conclusion
This experiment successfully demonstrated the end-to-end ETL process within a powerful BI
tool. By transforming raw, messy data into a clean, enriched, and structured format, we have
built a reliable foundation for analysis. This process is fundamental in BI because it ensures
that all reports, dashboards, and insights are derived from high-quality data, leading to more
accurate and trustworthy business decisions.
Experiment 3: Create a Dashboard for Sales Data
Aim
To design and create a basic, interactive sales dashboard in Power BI using the cleaned data
from the previous experiment. The dashboard will visualize key performance indicators
(KPIs) to provide actionable insights at a glance.
Theory
A BI Dashboard is a data visualization tool that displays the current status of metrics and
KPIs for an organization. Its primary purpose is to provide a comprehensive, at-a-glance view
of business performance. Key principles of effective dashboard design include Clarity (easy-
to-understand visuals), Relevance (showing the most important information), and
Interactivity (allowing users to filter and drill down into data). Dashboards transform raw
data into insights, enabling managers to identify trends, spot anomalies, and make informed,
data-driven decisions.
Implementation Steps
These steps assume you have completed Experiment 2 and have the cleaned sales data
loaded in your Power BI Desktop file.
Step 1: Create KPI Cards
1. In the Report View, select the Card visual from the Visualizations pane.
2. Drag TotalSales into the "Fields" area.
3. Repeat this process to create two more cards for total Profit and a Count of OrderID.
Step 2: Create a Sales by Customer Chart
1. Select the Clustered bar chart visual.
2. Drag CustomerName to the Y-axis and TotalSales to the X-axis.
Step 3: Create a Profit Over Time Chart
1. Select the Line chart visual.
2. Drag OrderDate to the X-axis and Profit to the Y-axis.
Step 4: Add an Interactive Slicer
1. Select the Slicer visual.
2. Drag the Product field into the slicer's "Field" area.
Step 5: Arrange the Dashboard
1. Resize and arrange the visuals and the slicer on the canvas to create a clean and
logical layout.
2. Add a title to the dashboard using a Text Box from the Insert ribbon.
3.
Result
The result is a simple but effective interactive dashboard containing:
KPI Cards: At the top, showing total sales, total profit, and the number of orders.
Bar Chart: Visually comparing the sales performance of each customer.
Line Chart: Showing the trend of profitability over the order dates.
Slicer: Allowing a user to dynamically filter the entire report by a specific product.
When a user interacts with the slicer, all other visuals update instantly, providing a dynamic
and exploratory analytical experience.
Conclusion
This experiment demonstrates the ultimate goal of many BI processes: turning processed
data into actionable insights through visualization. This dashboard empowers a business
user, such as a sales manager, to monitor key metrics, identify top-performing customers,
analyze trends, and explore data interactively. This direct access to insights accelerates
decision-making and helps in formulating effective business strategies.
Experiment 4: Using a NoSQL Database (MongoDB)
Aim
The objective is to create documents in a NoSQL database (MongoDB) to store user
information. This involves inserting multiple user profiles and then reading all the stored
data to display it.
Theory
NoSQL databases (meaning "Not Only SQL") provide a mechanism for storage and retrieval
of data that is modeled in means other than the tabular relations used in relational (SQL)
databases.
MongoDB: A popular open-source NoSQL database that uses a document-oriented
data model. A record in MongoDB is a document, which is a data structure similar to
JSON objects.
Collection: Documents are stored in collections, which are analogous to tables in a
relational database but do not enforce a rigid schema. This flexibility makes NoSQL
databases like MongoDB ideal for handling unstructured or semi-structured data.
Code and Implementation
This Python script uses the pymongo library to connect to a local MongoDB server, insert five
user documents, and then retrieve and print them.
Prerequisite: You must have MongoDB installed and running on your local machine and
install the library: pip install pymongo.
Code Output
Conclusion
This experiment shows how to interact with a NoSQL data source. In modern BI
architectures, NoSQL databases are often used as data lakes for large volumes of diverse,
semi-structured data from sources like web applications and social media. BI systems then
extract this data to be transformed and analyzed. Understanding how to store and retrieve
data from NoSQL systems is a crucial skill for handling the variety and volume of data in
today's data landscape.
Experiment 5: Train a K-Nearest Neighbor (KNN) Classifier
Aim
The objective is to train a k-Nearest Neighbors (KNN) classification model to predict the
species of an iris flower based on its sepal and petal measurements. We will use Fisher's
famous iris dataset and set k (the number of neighbors) to 5.
Theory
Classification is a type of supervised machine learning where the goal is to predict a
categorical label (e.g., "Spam" or "Not Spam"). The k-Nearest Neighbors (KNN) algorithm
classifies a new data point based on the majority class of its 'k' closest data points in the
training set. "Closeness" is typically measured using a distance metric like Euclidean
distance. The choice of k is important; a small k can be sensitive to noise, while a large k can
be computationally expensive.
Code and Implementation
This Python script uses the scikit-learn library to load the iris dataset, split it, train a KNN
classifier, and evaluate its performance.
Code Output
Conclusion
This experiment demonstrates the use of a machine learning classifier, a core component of
predictive analytics in BI. Businesses use classification models for numerous BI tasks like
Customer Segmentation, Churn Prediction, Fraud Detection, and Sentiment Analysis. By
building such models, organizations can move from historical reporting to forward-looking,
predictive insights, allowing them to take proactive, data-driven actions.
Experiment 6: Prediction using Linear Regression
Aim
The objective is to create a simple linear regression model to predict a continuous outcome.
Specifically, the model will predict Sales based on Advertising Spend. This demonstrates a
foundational predictive analytics technique used in Business Intelligence (BI) for tasks like
forecasting.
Theory
In Business Intelligence, regression is a predictive analytics method used to forecast a
continuous numerical value. Linear Regression is a fundamental algorithm for this task. It
works by modeling the relationship between a dependent variable (what we want to
predict) and an independent variable (the predictor) by fitting a straight line to the data. The
equation for simple linear regression is:
y=mx+c
y: The dependent variable (e.g., Sales).
x: The independent variable (e.g., Advertising Spend).
m: The coefficient, representing the change in y for a one-unit change in x.
c: The intercept, representing the value of y when x is zero.
The algorithm's goal is to find the best m and c values that minimize the error between the
predicted line and the actual data points.
Code and Implementation
Result and Interpretation
The results show the linear regression model is an excellent fit for the data.
Interpretation of Coefficients:
o The intercept suggests a baseline of sales even with zero advertising.
o The coefficient (e.g., 2.19 from the code's output) is the key insight: for every
additional $1000 spent on advertising, the model predicts an increase of
approximately 2.19 units in sales. This provides a clear, quantifiable return on
investment.
Interpretation of Evaluation Metrics:
o Mean Absolute Error (MAE): Shows the average error in the model's
predictions in sales units.
o R-squared (e.g., 0.999 from the code's output): This very high value indicates
that almost all of the variability in sales can be explained by the advertising
spend, meaning the model is a very good fit.
Conclusion
This linear regression model provides powerful business intelligence. A marketing manager
can use this model for several key business functions:
Forecast Sales: Predict sales for a given advertising budget.
Optimize Budgeting: Justify marketing expenses by demonstrating a direct, positive
relationship between ad spend and sales.
Set Goals: Determine the advertising budget required to achieve a specific sales
target.
This experiment successfully shows how a simple predictive model can transform raw data
into actionable, strategic insights for better business planning.