The Kalyan Wholesale Merchants Education Society’s

LAXMAN DEVRAM SONAWANE DEGREE COLLEGE

(Affiliated To University Of Mumbai)
&
LAXMAN DEVRAM SONAWANE JUNIOR COLLEGE
OF
ARTS, COMMERCE AND SCIENCE
KALYAN

CERTIFICATE

This is certify that Mr./Miss.

Class Roll No. Exam Seat No.
Has completed the prescribed practical in
during the Academic 202 - 202

Practical H.O.D. Internal External

In charge Examiner Examiner
 INDEX
26/11/25

[Link] Title Date Sign

Import the data warehouse data in Microsoft Excel and 19/11/25
1.A create the Pivot table and Pivot Chart
Import the cube in Microsoft Excel and create the Pivot 26/11/25
1.B table and Pivot Chart to perform data analysis

Apply the what – if Analysis for data visualization.

Design and generate necessary reports based on the
2.
data warehouse data. Use Excel.

02/12/25
Perform the data classification using classification algorithm
3. using R/Python.

08/12/25
Perform the data clustering using clustering algorithm using
4. R/Python.

Perform the Linear regression on the given data warehouse 16/12/25

5. data using R/Python.

19/12/25
Perform the logistic regression on the given data
6. warehouse data using R/Python.

16/12/25
Write a Python program to read data from a CSV file,
7.
perform simple data analysis, and generate basic
insights. (Use Pandas is a Python library).

8.A. Perform data visualization using Python on any sales data. 05/01/26

8.B. Perform data visualization using PowerBI on any sales data 12/01/26
9. Create the Data staging area for the selected database 20/12/25
using SQL
10. Create the cube with suitable dimension and fact tables 28/01/26
based on ROLAP, MOLAP and HOLAP model.
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Pratical -1(A)
AIM:- Import the data warehouse data in Microsoft Excel and create the Pivot table and
Pivot Chart.
STEPS:-

Step 1: Open Microsoft Excel

Step 2: Connect to Data Warehouse

1. Go to the Data tab on the Ribbon.

2. Click Get Data > From Database (Choose the appropriate option based on your database
type).

o From SQL Server Database (for Microsoft SQL Server)

o From Oracle Database (for Oracle)

o From MySQL Database (for MySQL)

o From Other Sources (for other databases)

3. Enter the Server Name and Database Name when prompted.

4. Click OK and then choose the required table or query.

Page | 1
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Step 3: Load Data into Excel

1. After selecting the table, you can either:

o Click Load to import the data directly into an Excel sheet.

o Click Transform Data to clean or filter data before importing (optional).

2. Wait for the data to load into an Excel worksheet.

Page | 2
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Step 4: Create a Pivot Table

1. Click anywhere in the imported data.

2. Go to the Insert tab and select PivotTable.

3. In the dialog box:

o Choose Select a table or range (if data is already in the worksheet).

o Choose Use an external data source (if connecting directly to the database).

4. Click OK to create the Pivot Table in a new or existing worksheet.

Page | 3
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Step 5: Customize the Pivot Table

1. Use the PivotTable Fields pane to drag and drop fields:

o Rows: Drag fields for grouping (e.g., Region, Product Category).

o Columns: Drag fields for column categories.

o Values: Drag numerical fields (e.g., Sales, Revenue) to perform calculations like Sum,
Average.

o Filters: Add fields to filter data dynamically.

Page | 4
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Step 6: Create a Pivot Chart

1. Click anywhere in the Pivot Table.

2. Go to the Insert tab and select PivotChart.

3. Choose the chart type (e.g., Column, Bar, Line, Pie).

4. Click OK, and the chart will appear in the worksheet.

5. Customize the chart using the Chart Tools tab.

Step 7: Save and Refresh Data

1. Save the Excel file for future use.

2. To refresh the Pivot Table and Chart when new data is added, go to Data > Refresh All.

--------------------------------------------------------------------------------------------------------------------------------------

Page | 5
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

PRATICAL-1(b)
AIM:- Import the cube in Microsoft Excel and create the Pivot table and Pivot Chart to
perform data analysis
STEPS:-

Step 1:-Open Microsoft Excel

Step2:- Step Go to Data tab → Get External Data → From Other Sources → From Analysis Services

Step 3:- Select Server name and Windows Authentication and click on Next

Page | 6
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Step 4:- Select OLAP(as per created before) click on Next

Step5:- Browse and select path name and click on Finish

Page | 7
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Step 6:- Select Pivot Table Report → OK

Step 7:- Drag and Drop Fields in rows column and values

Page | 8
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Step 8:-Go to Insert tab → pivot chart and select Pivot Chart from drop down

Step 9:- Select existing connection OLAP Sales DW and click on Open

Page | 9
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Step 10:- Click on Choose connection to select path

Step 11:- Click on OK

--------------------------------------------------------------------------------------------------------------------------------------

Page | 10
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

PRATICAL-2
AIM:- Apply the what – if Analysis for data visualization. Design and generate necessary
reports based on the data warehouse data. Use Excel.
STEPS:-

A book store and have 100 books in storage. You sell a certain % for the highest price of $50 and a
certain % for the lower price of $20.

If you sell 60% for the highest price, cell D10 calculates a total profit of 60 * 50 + 40 * 20 = 3800.

Create Different Scenarios But what if you sell 70% for the highest price? And what if you sell 80%
for the highest price? Or 90%, or even 100%? Each different percentage is a different scenario. You
can use the Scenario Manager to create these scenarios.

Note: To type different percentage into cell C4 to see the corresponding result of a scenario in cell
D10 we use what if analysis.

What-if analysis enables you to easily compare the results of different scenarios.

Page | 11
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Step 1: In Excel, On the Data tab, in the Data tools group, click What-If Analysis

Step 2: Click on What –if-Analysis and select scenario manager.

The Scenario Manager Dialog box appears.

Page | 12
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Step 3: Add a scenario by clicking on Add.

Step 4: Type a name (60percent), select cell F10 (% sold for the highest price) for the Changing cells
and click on OK.

Click on icon which is circled.

Select F10 cell.

Page | 13
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Click back on the icon again and then click OK

Step 5: Enter the corresponding value 0.6 and click on OK again.

Page | 14
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Step 6: To apply scenarios click on Show

Step 7: Next, add 4 other scenarios (70%, 80%, 90% and 100%) Finally, your Scenario Manager
should be consistent with the picture below:

--------------------------------------------------------------------------------------------------------------------------------------

Page | 15
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

PRATICAL-3
AIM:- Perform the data classification using classification algorithm using R/Python.
STEPS:-

Software required: R 3.5.1

Step 1:- Time series is a series of data points in which each data point is associated with a
timestamp. A simple example is the price of a stock in the stock market at different points of time
on a given day. Another example is the amount of rainfall in a region at different months of the
year. R language uses many functions to create, manipulate and plot the time series data. The data
for the time series is stored in an R object called time-series object. It is also a R data object like a
vector or data frame.

Step 2:-The time series object is created by using the ts () function.

Syntax
The basic syntax for ts() function in time series analysis is − [Link] <- ts(data,
start, end, frequency)

Following is the description of the parameters used –

• data is a vector or matrix containing the values used in the time series.
• start specifies the start time for the first observation in time series.
• end specifies the end time for the last observation in time series.
• end specifies the end time for the last observation in time series.
Except the parameter "data" all other parameters are optional

Consider the annual rainfall details at a place starting from January 2012. We create an R time
series object for a period of 12 months and plot it

Step 3:-Code

# Get the data points in form of a R vector.

rainfall <-

c(799,1174.8,865.1,1334.6,635.4,918.5,685.5,998.6,784.2,985,882.8,1071)

# Convert it to a time series object.

[Link] <- ts(rainfall,start = c(2012,1),frequency = 12)

# Print the timeseries data

print([Link])

# Give the chart file a name.

png(file = "[Link]")

Page | 16
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

# Plot a graph of the time series.

plot([Link])

# Save the file.

[Link]()

After this again plot to get chart

plot([Link])

Output:

Jan Feb Mar Apr May Jun Jul Aug Sep 2012 799.0 1174.8 865.1 1334.6 635.4 918.5 685.5 998.6
784.2 Oct Nov Dec 2012 985.0 882.8 1071.0

--------------------------------------------------------------------------------------------------------------------------------------

Page | 17
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

PRATICAL-4
AIM:- Perform the data clustering using clustering algorithm using R/Python.
STEPS:-

k-means clustering using R

Step 1:-apply K means to iris and store result

newiris <- iris

newiris$Species <- NULL

(kc <- kmeans(newiris,3))

Step 2:-Compare the Species label with the clustering result

table(iris$Species,kc$cluster)

Step 3:-Plot the clusters and their centers

plot(newiris[c("[Link]","[Link]")],col=kc$cluster)

points(kc$centers[,c("[Link]","[Link]")],col=1:3,pch=8,cex=2)

[Link]()

Page | 18
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Step 4:-Plot the clusters and their centre

plot(newiris[c("[Link]","[Link]")],col=kc$cluster)

--------------------------------------------------------------------------------------------------------------------------------------

Page | 19
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

PRATICAL-5
AIM:- Perform the Linear regression on the given data warehouse data using R/Python.
STEPS:-

Input Data

Step 1:- Values of height

151, 174, 138, 186, 128, 136, 179, 163, 152, 131

Step 2:-Values of weight.

63, 81, 56, 91, 47, 57, 76, 72, 62, 48

lm() Function :

This function creates the relationship model between the predictor and the response variable.

Syntax
Step 3:-The basic syntax for lm() function in linear regression is –

lm(formula,data)

Following is the description of the parameters used :−

• formula is a symbol presenting the relation between x and y.

• data is the vector on which the formula will be applied.

Step 4:-Create Relationship Model & get the Coefficients

# Values of height

x <- c(151, 174, 138, 186, 128, 136, 179, 163, 152, 131)

# Values of width

y <- c(63, 81, 56, 91, 47, 57, 76, 72, 62, 48)

# Apply the lm() function.

relation <- lm(y~x)

print(relation)

OUTPUT:

Page | 20
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Step 4:- Get the Summary of the Relationship

# Values of height

x <- c(151, 174, 138, 186, 128, 136, 179, 163, 152, 131)

# Values of width

y <- c(63, 81, 56, 91, 47, 57, 76, 72, 62, 48)

# Apply the lm() function

relation <- lm(y~x)

print(summary(relation))

OUTPUT:

Step 5:- predict() Function

Syntax
predict(object, newdata)

Step 6:- Predict the weight of new persons

# The predictor vector.

x <- c(151, 174, 138, 186, 128, 136, 179, 163, 152, 131)

# The response vector.

y <- c(63, 81, 56, 91, 47, 57, 76, 72, 62, 48)

# Apply the lm() function.

relation <- lm(y~x)

Page | 21
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

# Find weight of a person with height 170.

a <- [Link](x = 170)

result <- predict(relation,a)

print(result)

OUTPUT:

Step 6:- Visualize the Regression Graphically

# Create the predictor and response variable

x <- c(151, 174, 138, 186, 128, 136, 179, 163, 152, 131)

y <- c(63, 81, 56, 91, 47, 57, 76, 72, 62, 48)

relation <- lm(y~x)

# Give the chart file a name.

png(file = "[Link]")

# Plot the chart.

plot(y,x,col = "blue",main = "Height & Weight Regression", abline(lm(x~y)),cex = 1.3,pch = 16,xlab =

"Weight in Kg",ylab = "Height in cm")

# Save the file.

[Link]()

Step 7:- Plot the chart.

plot(y,x,col = "blue",main = "Height & Weight Regression", abline(lm(x~y)),cex = 1.3,pch = 16,xlab =

"Weight in Kg",ylab = "Height in cm")

Page | 22
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

OUTPUT:

--------------------------------------------------------------------------------------------------------------------------------------

Page | 23
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

PRATICAL-6
AIM:- Perform the logistic regression on the given data warehouse data using R/Python.
STEPS:-

To perform this you need to download [Link] file from following link:
[Link] %20Logistic%20Regression/Data

Step 1:- provide path of file where it is saved on your machine

quality <- [Link]('C:/Users/Gauri/Downloads/[Link]')

> #analysing the quality dataset

> str(quality)

'[Link]': 131 obs. of 14 variables:

$ MemberID : int 1 2 3 4 5 6 7 8 9 10 ...

$ InpatientDays : int 0 1 0 0 8 2 16 2 2 4 ...

$ ERVisits : int 0 1 0 1 2 0 1 0 1 2 ...

$ OfficeVisits : int 18 6 5 19 19 9 8 8 4 0 ...

$ Narcotics : int 1 1 3 0 3 2 1 0 3 2 ...

$ DaysSinceLastERVisit: num 731 411 731 158 449 ...

$ Pain : int 10 0 10 34 10 6 4 5 5 2 ...

$ TotalVisits : int 18 8 5 20 29 11 25 10 7 6 ...

$ ProviderCount : int 21 27 16 14 24 40 19 11 28 21 ...

$ MedicalClaims : int 93 19 27 59 51 53 40 28 20 17 ...

$ ClaimLines : int 222 115 148 242 204 156 261 87 98 66 ...

$ StartedOnCombination: logi FALSE FALSE FALSE FALSE FALSE FALSE ...

$ AcuteDrugGapSmall : int 0 1 5 0 0 4 0 0 0 0 ...

$ PoorCare : int 0 0 0 0 0 1 0 0 1 0 ...

> table(quality$PoorCare)

01

98 33

> 98/131

[1] 0.7480916

Page | 24
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

> [Link]("caTools")

Installing package into ‘C:/Users/Saurav/Documents/R/win-library/3.5’ (as ‘lib’ is unspecified)

--- Please select a CRAN mirror for use in this session ---

Step 2:- installing the dependency ‘bitops’ from

'[Link] Content type

'application/zip' length 38894 bytes (37 KB)

installing the dependency ‘caTools’ from

'[Link]

Step 3:- package ‘bitops’ successfully unpacked and MD5 sums checked

package ‘caTools’ successfully unpacked and MD5 sums checked

The downloaded binary packages are in

C:\Users\Saurav\AppData\Local\Temp\RtmpmUN9oK\downloaded_package s

Step 4:- > library(caTools) Warning message: package ‘caTools’ was built under R version 3.5.2

> [Link](88)

> split = [Link](quality$PoorCare, SplitRatio = 0.75)

>

> split

[1] TRUE TRUE TRUE TRUE FALSE TRUE FALSE TRUE FALSE FALSE TRUE FALSE TRUE TRUE TRUE TRUE
TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE FALSE TRUE TRUE

[28] TRUE TRUE FALSE FALSE FALSE FALSE TRUE TRUE TRUE FALSE TRUE TRUE TRUE FALSE FALSE
TRUE TRUE FALSE TRUE FALSE TRUE FALSE TRUE TRUE FALSE FALSE TRUE

[55] TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE FALSE TRUE TRUE TRUE TRUE
FALSE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE

[82] TRUE TRUE FALSE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE FALSE TRUE TRUE
TRUE TRUE TRUE TRUE TRUE TRUE TRUE FALSE TRUE TRUE TRUE FALSE

[109] TRUE FALSE FALSE TRUE TRUE FALSE TRUE TRUE TRUE FALSE TRUE TRUE FALSE TRUE TRUE
FALSE TRUE TRUE FALSE TRUE TRUE TRUE FALSE

> qualityTrain = subset(quality, split == TRUE)

> qualityTest = subset(quality, split == FALSE) > nrow(qualityTrain)

[1] 99

> nrow(qualityTest)

[1] 32

> QualityLog = glm(PoorCare ~ OfficeVisits + Narcotics,data=qualityTrain, family=binomial)

Page | 25
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

> summary(QualityLog)

Call:

glm(formula = PoorCare ~ OfficeVisits + Narcotics, family = binomial, data = qualityTrain)

Deviance Residuals:

Min 1Q Median 3Q Max

-2.06303 -0.63155 -0.50503 -0.09689 2.16686

Coefficients:

Estimate Std. Error z value Pr(>|z|)

(Intercept) -2.64613 0.52357 -5.054 4.33e-07 ***

OfficeVisits 0.08212 0.03055 2.688 0.00718 **

Narcotics 0.07630 0.03205 2.381 0.01728 *

-Signif. codes: 0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

(Dispersion parameter for binomial family taken to be 1)

Null deviance: 111.888 on 98 degrees of freedom

Residual deviance: 89.127 on 96 degrees of freedom

AIC: 95.127

Number of Fisher Scoring iterations: 4

> predictTrain = predict(QualityLog, type="response")

> summary(predictTrain)

Min. 1st Qu. Median Mean 3rd Qu. Max.

0.06623 0.11912 0.15967 0.25253 0.26765 0.98456

> tapply(predictTrain, qualityTrain$PoorCare, mean)

0 1

0.1894512 0.4392246

> table(qualityTrain$PoorCare, predictTrain > 0.5)

FALSE TRUE

0 70 4
1 15 10

> 10/25

[1] 0.4

> 70/74

Page | 26
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

[1] 0.9459459

> table(qualityTrain$PoorCare, predictTrain > 0.7)

FALSE TRUE

0 73 1

1 17 8

> 8/25

[1] 0.32

> 73/74

[1] 0.9864865

> table(qualityTrain$PoorCare, predictTrain> 0.2)

FALSE TRUE

0 54 20

1 9 16

> 16/25

[1] 0.64

> 54/74

[1] 0.7297297

> [Link]("ROCR")

Installing package into ‘C:/Users/Saurav/Documents/R/win-library/3.5’ (as ‘lib’ is unspecified) also

installing the dependencies ‘gtools’, ‘gdata’, ‘gplots’

trying URL

'[Link]

'[Link]

'[Link]

> library(ROCR)

Loading required package: gplots

Attaching package: ‘gplots’

The following object is masked from ‘package:stats’:

Step 5:- Warning messages:

1: package ‘ROCR’ was built under R version 3.5.2

2: package ‘gplots’ was built under R version 3.5.2

Page | 27
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

> ROCRpred = prediction(predictTrain, qualityTrain$PoorCare)

> ROCRperf = performance(ROCRpred, "tpr", "fpr")

> plot(ROCRperf)

> plot(ROCRperf, colorize=TRUE)

> plot(ROCRperf, colorize=TRUE, [Link]=seq(0,1,by=0.1), [Link]=c(-0.2,1.7))

>

Page | 28
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

--------------------------------------------------------------------------------------------------------------------------------------

Page | 29
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

PRATICAL-7
AIM:- Write a Python program to read data from a CSV file, perform simple data analysis, and
generate basic insights. (Use Pandas is a Python library).

STEPS:-

Step 1:- Install Required Library

Before running the program, you need to install Pandas.

Open a command prompt or terminal and type:

pip install pandas

Step 2:- Open MS EXCEL

Prepare a CSV File

Step 3:- Open PYTHON IDLE

Write the Python Code

import pandas as pd

file_path = "[Link]”

Page | 30
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

df = pd.read_csv(file_path)

print("\nz’‘ First 5 rows of the dataset:")

print([Link]()) # Show the first 5 rows

print("\n‘
z Dataset Summary:")
’

print([Link]())

print("\n‘
z Basic Statistics:")
’

print([Link]())

print("\n’
z Missing Values in Each Column:")
‘

print([Link]().sum())

print("\n‘
z Column Names:")
’

print([Link])

if 'Salary' in [Link]:

print(f"\n’z‘ Average Salary: {df['Salary'].mean():.2f}")

if 'Age' in [Link]:

print(f"\n‘
z Youngest Person's Age: {df['Age'].min()}")
’

print(f"‘’z Oldest Person's Age: {df['Age'].max()}")

if 'Gender' in [Link]:

print("\n‘
z Gender Distribution:")
’

print(df['Gender'].value_counts())

p r i nt ( " \ n⬛Data Analysis Completed Successfully!")

Page | 31
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Output:-

--------------------------------------------------------------------------------------------------------------------------------------

Page | 32
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

PRATICAL-8(a)
AIM:- Perform data visualization using Python on any sales data.

STEPS:-

Step 1:- Install Required Libraries

Before starting, install the necessary libraries.

Run the following command in your terminal or command prompt:

pip install pandas numpy matplotlib seaborn

Step 2:- Import Libraries

Now, open your Python script and import the required libraries:

import pandas as pd

import numpy as np

import [Link] as plt

import seaborn as sns

Step 3:- Create Sample Sales Data

import pandas as pd
import numpy as np
import [Link] as plt
import seaborn as sns

# Generate Sample Sales Data

dates = pd.date_range(start='2023-01-01', periods=100, freq='D')
categories = ['Electronics', 'Clothing', 'Groceries', 'Furniture']
data = {
'Date': [Link](dates, 200),
'Category': [Link](categories, 200),
'Sales_Amount': [Link](100, 1000, 200),
'Units_Sold': [Link](1, 20, 200)
}
df = [Link](data)

# Convert Date column to datetime format

df['Date'] = pd.to_datetime(df['Date'])

# Summary Statistics
print([Link]())

# Sales Trend Over Time

[Link](figsize=(12, 6))

Page | 33
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

[Link](data=[Link]('Date')['Sales_Amount'].sum().reset_index(), x='Date',
y='Sales_Amount')
[Link]('Sales Trend Over Time')
[Link]('Date')
[Link]('Total Sales')
[Link](rotation=45)
[Link]()

# Sales by Category
[Link](figsize=(10, 5))
[Link](data=[Link]('Category', as_index=False)['Sales_Amount'].sum(), x='Category',
y='Sales_Amount', palette='viridis')
[Link]('Total Sales by Category')
[Link]('Category')
[Link]('Sales Amount')
[Link]()

# Distribution of Sales Amounts

[Link](figsize=(10, 5))
[Link](df['Sales_Amount'], bins=20, kde=True, color='blue')
[Link]('Distribution of Sales Amounts')
[Link]('Sales Amount')
[Link]('Frequency')
[Link]()

# Scatter Plot: Sales Amount vs. Units Sold

[Link](figsize=(10, 5))
[Link](data=df, x='Units_Sold', y='Sales_Amount', hue='Category',
palette='coolwarm')
[Link]('Sales Amount vs. Units Sold')
[Link]('Units Sold')
[Link]('Sales Amount')
[Link]()

Page | 34
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Output:-

Page | 35
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

--------------------------------------------------------------------------------------------------------------------------------------

Page | 36
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

PRATICAL-8(b)
AIM:- Perform data visualization using PowerBI on any sales data
Steps:-

Step 1:- Download & Install Power BI

Step 2:- create a sample data

Step 3 :- Import the Sales Dataset

• Click "Home" > "Get Data".

• Choose your data source:

o Excel (XLSX/CSV)

o SQL Server

o Online Services (Google Sheets, SharePoint, etc.)

• Browse and select the dataset.

• Click "Load" to import.

Page | 37
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Step 4 :- Clean & Transform Data (Power Query)

Step 5 :- Create Data Visualizations

• Click on "Line Chart" in the "Visualizations" pane.

• Drag Order Date to the X-axis.
• Drag Sales Amount to the Y-axis.
• Customize the chart (format labels, add title, etc.).

Step 6:- Add Filters & Interactivity

Output:-

--------------------------------------------------------------------------------------------------------------------------------------

Page | 38
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

PRATICAL-9
AIM: Create the Data staging area for the selected database using SQL

STEPS:-

Step 1:- Create a Staging Database

First, create a staging database to store raw sales data.

CREATE DATABASE Sales_Staging;

USE Sales_Staging;

Step 2:- Create Staging Tables

Create tables that match the structure of raw sales data but include additional fields like load date
and batch ID.

CREATE TABLE Staging_Sales (

SalesID INT PRIMARY KEY,

OrderDate DATE,

ProductName VARCHAR(100),

Category VARCHAR(50),

Region VARCHAR(50),

SalesAmount DECIMAL(10,2),

Profit DECIMAL(10,2),

Quantity INT,

LoadDate DATETIME DEFAULT CURRENT_TIMESTAMP,

BatchID INT );

Page | 39
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Step 3:- Load Raw Data into the Staging Table

Simulating data load from a CSV file, API, or external source:

INSERT INTO Staging_Sales (SalesID, OrderDate, ProductName, Category, Region, SalesAmount,

Profit, Quantity, BatchID)

VALUES

(1, '2024-01-01', 'Laptop', 'Electronics', 'North', 1200.00, 200.00, 3, 101),

(2, '2024-01-02', 'Smartphone', 'Electronics', 'South', 800.00, 150.00, 2, 101),

(3, '2024-01-03', 'Tablet', 'Electronics', 'East', 600.00, 100.00, 5, 101);

Step 4:- Perform Data Cleansing & Transformation

• Remove Duplicates

DELETE FROM Staging_Sales

WHERE SalesID NOT IN (

SELECT MIN(SalesID) FROM Staging_Sales GROUP BY OrderDate, ProductName, Region

);

Page | 40
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

• Handle Null Values

UPDATE Staging_Sales

SET Profit = 0

WHERE Profit IS NULL;

Step 5:- Transfer Clean Data to the Final Sales Table

Move the cleaned data into the Data Warehouse (DWH).

INSERT INTO Final_Sales (

SalesID, OrderDate, ProductName, Category, Region, SalesAmount, Profit, Quantity

SELECT

SalesID, OrderDate, ProductName, Category, Region, SalesAmount, Profit, Quantity

FROM Staging_Sales;

Page | 41
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Step 6:- Archive or Delete Processed Data

Once data is loaded, either archive it or delete it from the staging area.

DELETE FROM Staging_Sales WHERE BatchID = 101;

--------------------------------------------------------------------------------------------------------------------------------------

Page | 42
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

PRATICAL-10
AIM: Create the cube with suitable dimension and fact tables based on ROLAP, MOLAP
and HOLAP model.
Steps:-
Step 1: Creating Data Warehouse

CREATED IN PRACTICAL 9

Step 2: Start SSDT environment and create New Data Source

Go to Sql Server Data Tools --> Right click and run as administrator

Click on File → New → Project

In Business Intelligence → Analysis Services Multidimensional and Data Mining models →

appropriate project name → click OK

Page | 43
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Right click on Data Sources in solution explorer → New Data Source

Data Source Wizard appears

Click on New

Page | 44
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Select Server Name → select Use SQL Server Authentication → Select or enter a database name
(Sales_Staging)

Click ok

Page | 45
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Select Inherit → Next

Click Finish

Page | 46
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Sales_Staging.ds gets created under Data Sources in Solution Explorer

Step 3: Creating New Data Source View

In Solution explorer right click on Data Source View → Select New Data Source View

Click Next

Page | 47
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Click Next

Select FactProductSales(dbo) from Available objects and put in Includes Objects by clicking on

Page | 48
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Click on Add Related Tables

Click Next

Click Finish

Page | 49
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Sales_Staging.dsv appears in Data Source Views in Solution Explorer.

Step 4: Creating new cube

Right click on Cubes → New Cube

Page | 50
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Select Use existing tables in Select Creation Method → Next

In Select Measure Group Tables → Select FactProductSales → Click Next

In Select Measures → check all measures → Next

Page | 51
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

In Select New Dimensions → Check all Dimensions → Next

Click on Finish

Page | 52
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Sales_Staging.cube is created

Step 5: Dimension Modification

In dimension tab → Double Click Dim [Link]

Drag and Drop Product Name from Table in Data Source View and Add in Attribute Pane at left side

Page | 53
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Step 6: Creating Attribute Hierarchy in Date Dimension

Double click On Dim Date dimension -> Drag and Drop Fields from Table shown in Data Source
View to Attributes-> Drag and Drop attributes from leftmost pane of attributes to middle pane of
Hierarchy.

Drag fields in sequence from Attributes to Hierarchy window (Year, Quarter Name, Month Name,
Week of the Month, Full Date UK)

Page | 54
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Step 7: Deploy Cube .

Right click on Project name → Properties

This window appaers

Page | 55
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Do following changes and click on Apply & ok

Right click on project name → Deploy

Page | 56
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Deployment successful

To process cube right click on Sales_DW.cube → Process

Page | 57
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Click run

Browse the cube for analysis in solution explorer

Page | 58
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

--------------------------------------------------------------------------------------------------------------------------------------

Page | 59
 TYIT BUSINESS INTELLIGENCE AND DATA ANALYTICS PRACTICAL

Page | 60