KGiSL INSTITUTE OF TECHNOLOGY

(An Autonomous Institution)

Affiliated to Anna University, Approved by AICTE, Recognised by UGC, Accredited by NAAC &
NBA
365, KGiSL Campus, Thudiyalur Road, Saravanampatti, Coimbatore-641035

Department of Information Technology

CCW331 – BUSINESS ANALYTICS

NAME :

ROLL NO. :

REGISTER NO. :

YEAR & SEMESTER :

 MISSION OF THE INSTITUTION
MI-1: To practice Outcome Based Teaching Learning Methodology.

MI-2: To upskill Faculty Members’ Expertise in diverse domains.

MI-3: To build State-of-the-Art Infrastructure that provides Quality Education and fosters Research.

MI-4: To enrich Innovative Research Activities in collaboration with Industry and Institute.

MI-5: To ensure the Students’ Participation in Co-curricular and Extra-curricular Activities.

VISION OF THE DEPARTMENT

To produce Competent Graduates suitable for Industry and Organization in the
field of Information Technology by providing industry embedded learning with
social responsibility

MISSION OF THE DEPARTMENT

M1: To accomplish effective teaching learning process through innovative practices for empowering the
graduates to face societal challenges.
M2: To enhance the proficiency of faculty members across various domains of information technology
through skill development programs.
M3: To nurture IT professionals through the provision of essential infrastructure and facilities for effective
learning.
M4: To attain research excellence in the field of information technology by instilling the values of self-
directed learning and fostering creative thinking through collaborative partnerships with institutes and
industries.
M5: To foster holistic student growth by engaging them in co-curricular and extracurricular activities
centered around information technology.
 KGISL INSTITUTE OF TECHNOLOGY

DEPARTMENT OF INFORMATION TECHNOLOGY

NAME : ………………………………….

REGISTER NO. : ………………………………….

LABORATORY RECORD

Certified that this is bonafide record of work done by the above student in the CCW331 -
BUSINESS ANALYTICS during the academic year 2025-2026 / Semester V.

STAFF-IN-CHARGE HEAD OF THE DEPARTMENT

Submitted for the practical examination held on ……………………

INTERNAL EXAMINER EXTERNAL EXAMINER

 1

INDEX
PAGE
S. NO. DATE NAME OF THE EXPERIMENT MARKS SIGN.
NO.
1 Explore the features of Ms-Excel 2 - 10

Get The Input From User And Perform

Numerical Operations (MAX, MIN, AVG, SUM
2(i) 11 - 16
SQRT, ROUND)

Perform data import/export operations for

2(ii) different file formats 17 - 18

Perform statistical operations – Mean, Median,

Mode and Standard deviation, Variance,
3 19 - 21
Skewness, Kurtosis

4 Perform Z-test, T-test & ANOVA 22 - 24

5 Perform data pre-processing operations 25 - 37

Perform dimensionality reduction operation

6 using PCA, KPCA & SVD 38 - 40

Perform bivariate and multivariate analysis on

7
the dataset 41 - 46

Apply and explore various plotting functions on

8 the data set. 47 - 59

9 Explore the features of power bi desktop 60 - 61

10 Prepare & load data 62 - 65

11 Develop the data model 66 - 71

12 Perform dax calculations 72 - 75

13 Design a report 76 - 79

14 Create a dashboard and perform data analysis 80 - 81

15 Presentation of a case study 82 - 84

CCW331 - Business Analytics
2

Ex. No: 1
Explore the Features of Ms-Excel
Date :

AIM:
To explore the features of Ms-Excel

PROCEDURE:
a) Number Formatting
By default, all cells of Excel will have the General format applied.

However, type in a big number that exceeds the size of the cell, and Excel would give you back
something like 1.2E+12.

Under General format, Excel replaces a number too big to fit the cell with its scientific notation.

To turn it into a number, change the format to ‘Numbers’ and adjust the cell [Link] the formula
bar to note how the number remains the same under both formats i.e. 1200000000000.

b) Controlling decimals

In the example, there are two decimals to the number.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
3

But you want four decimal places to this number. How can this be has done?

1. Select the cell.

2. Go to Home > Number > More Number Formats
3. From the Format Cells dialog box, go to Number Format.
4. Adjust the decimal places to four.

5. Here are the results.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
4

A shortcut to adjust decimal places. Go to Home > Number > Add decimals button

With every click, Excel adds another decimal position to the number.

c) Sorting

Sorting lists is used to reorder your data. The most common type of sorting is alphabetical
ordering, which you can do in ascending or descending order.

Select a cell in the column you want to sort (In this example, we choose a cell in column A).

Click the Sort & Filter command in the Editing group on the Home tab.

Select Sort A to Z. Now the information in the Category column is organized in alphabetical
order.

To sort from smallest to largest:

1. Select a cell in the column you want to sort (a column with numbers).
2. Click the Sort & Filter command in the Editing group on the Home tab.
3. Select Sort Z to A . Now the information is organized from the smallest to
largest amount.

You can sort in reverse numerical order by choosing From Largest to Smallest in the list.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
5

To sort multiple levels:

1. Click the Sort & Filter command in the Editing group on the Home tab.
2. Select Custom Sort from the list to open the dialog box.

OR

1. Select the Data tab.

2. Locate the Sort and Filter group.
3. Click the Sort command to open the Custom Sort dialog box. From here, you can
sort by one item or multiple items.

4. Click the drop-down arrow in the Column Sort by field, then choose one of
the options—in this example, Category.

5. Choose what to sort on. In this example, we'll leave the default as Value.
6. Choose how to order the results. Leave it as A to Z so it is organized alphabetically.
7. Click Add Level to add another item to sort by.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
6

8. Select an option in the Column Then by field. In this example, we chose Unit Cost.
9. Choose what to sort on. In this example, we'll leave the default as Value.
10. Choose how to order the results. Leave it as smallest to largest.
11. Click OK.

The spreadsheet has been sorted. All of the categories are organized in alphabetical order, i.e. from
smallest to largest.

d) Filtering

Filtering, or temporarily hiding, data in a spreadsheet is simple. This allows you to focus on specific
spreadsheet entries.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
7

To filter data,

1. Click the Filter command on the Data tab. Drop-down arrows will appear
beside each column heading.

2. Click the drop-down arrow next to the heading you would like to filter. For
example, if you would like to only view data regarding Flavors, click the
drop-down arrow next to Category.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
8

3. Uncheck Select All.

4. Choose Flavor.
5. Click OK. All other data will be filtered, or hidden, and only the Flavor data is visible.
6. To clear one filter, Select one of the drop-down arrows next to a filtered column.
7. Choose Clear Filter From...

To remove all filters, click the Filter command.

Filtering may look a little like grouping, but the difference is that now you can filter on another
field if you want to. For example, let’s say you want to see only the vanilla-related flavors. Just
click the drop-down arrow next to Item, then select Text Filters. From the menu, choose Contains
because you want to find any entry that has the word vanilla in it. A dialog box appears. Type
vanilla, then click OK. Now we can see that the data has been filtered again and that only the
vanilla-related flavors appear.

e) Separating Text within a Cell

When data is combined within a cell, such as a first and last name, Excel is able to separate this data
into two cells. To separate data within a cell:

1. Insert a blank column to the right of the column containing the merged data.
2. Highlight the column of full names.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
9

3. Select the Data tab.

4. Select Text to Columns.

The Convert Text to Columns Wizard dialogue box will appear.

5. Choose the appropriate data type. To separate a column based on punctuation characters, select
Delimited. To separate a column based on spaces between each field, select Fixed Width.

For this example we will select Delimited.

6. Select Next.
7. Choose your delimiters for the text separation.

For this example, select Space.

8. Select Next.
9. Select the data format for each column. For this example select General.

10. Select Finish.

Data will be displayed as separate columns.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
10

f) Removing Duplicate values

To highlight cells that contain either duplicate or unique values:

1. Highlight the data range.

2. Select Conditional Formatting.

3. Hover over Highlight Cells Rules.

4. Select Duplicate Values to open the Duplicate Values dialogue box.

5. Select either Duplicate or Unique from the drop down menu.

6. Select the type of cell formatting you wish to use.
7. Select Ok. The cells which contain either duplicate or unique values will now appear with
the cell formatting which you selected.

RESULT:
Thus the procedure to explore the features of Ms-Excel has done successfully.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
11

[Link] : 2(i) GET THE INPUT FROM USER AND PERFORM

NUMERICAL OPERATIONS (MAX, MIN, AVG, SUM, SQRT, ROUND)
Date:

AIM :
To get the Input from user and perform Numerical operations (MAX, MIN, AVG, SUM, SQRT,
ROUND) in Ms-excel

PROCEDURE:
Let’s take a look at a simple table showing the number of kilometres driven each month.

AVERAGE :

To calculate the AVERAGE monthly distance:

1. Select cell B8.

2. Click the drop-down arrow on the AUTOSUM button.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
12

3. Choose AVERAGE.
4. Press ENTER to confirm the cell range.

Max:

To find the LONGEST distance:

1. Select cell B9.

2. Click the drop-down arrow on the AUTOSUM button.
3. Choose MAX.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
13

At this point, the range that Excel has identified is too large so using the mouse, select cells B2:B7.

4. Press ENTER to confirm the cell range.

MAX can be used whenever you need to find the figure that is the LONGEST, TALLEST,
LARGEST, MOST etc.

MIN:

To find the SHORTEST distance:

1. Select cell B10.

2. Click the drop-down arrow on the AUTOSUM button.
3. Choose MIN.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
14

At this point, the range that Excel has identified is too large so using the mouse, select cells B2:B7.

4. Press ENTER to confirm the cell range.

MIN can be used whenever you need to find the figure that is the SHORTEST, LOWEST,
SMALLEST, LEAST etc.
SUM:
the SUM function in Excel is used to sum a range of cells.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
15

SQRT:

There are two ways to apply SQRT to the number.

Using Formula:

Using Function:

ROUND:

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
16

The ROUND function is used to round a number to a specified number of digits. Here's the syntax of
the ROUND function:

ROUND(number, num_digits)

number: This is the number you want to round.

num_digits: This argument specifies the number of digits to which you want to round the number.

Eg: =ROUND(3.45678, 2) . This formula will return 3.46 because it rounds 3.45678 to two decimal
places.
You can also use negative values for num_digits to round off to the left of the decimal point. For
example:=ROUND(12345.6789, -2)

This formula rounds 12345.6789 to the nearest hundred, giving you 12300.

RESULT:
Thus the procedure to get the input from user and perform numerical operations (MAX, MIN,
AVG, SUM, SQRT, ROUND) of Ms-Excel has done successfully.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
17

Ex. No : 2(ii) PERFORM DATA IMPORT/EXPORT OPERATIONS FOR

DIFFERENT FILE FORMATS
Date :

AIM:

To perform data import/export operations for different file formats in Ms-Excel

PROCEDURE:

In Microsoft Excel, the "Get & Transform Data" feature allows you to connect, import, and
transform data from various sources. This feature is available in Excel 2016, Excel 2019, and Microsoft
365. Here's how you can use "Get & Transform Data" to import data from different sources:

Importing Data Using "Get & Transform Data":

1. Open Excel:

Launch Excel and open a new or existing workbook.

2. Go to the "Data" Tab:
Click on the "Data" tab in the Excel ribbon.

3. Select "Get Data":

Click on the "Get Data" button. You'll see various data import options, such as "From Workbook,"
"From Table/Range," "From CSV," "From Database," and more.
4. Choose a Data Source:

- From Workbook: Import data from another Excel workbook.

- From Table/Range: Import data from an Excel table or a specific range of cells in the
current workbook.
- From CSV/Text: Import data from a CSV or text file.

- From Database: Import data from a database source like SQL Server, Access, or online sources
like Azure SQL Database.
- From Online Services: Import data from online services like SharePoint, Salesforce, or Azure.

5. Connect to the Data Source:

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
18

Select the appropriate data source and follow the prompts to connect to it. You might need to provide
login credentials or specify the file path, depending on the source.

6. Transform and Load Data:

After connecting to the data source, you can use the "Power Query Editor" to transform and clean
your data before loading it into Excel. You can perform operations like filtering, sorting, merging,
splitting, and more.

7. Load Data into Excel:

Once you've transformed the data as needed, click on the "Close & Load" button to import the data
into your Excel workbook. You can choose to load the data as a table, pivot table, or directly into
the worksheet.

Refreshing Data:
If your data source is dynamic and frequently updated, you can set up automatic data refresh in Excel.
To do this:
- Select the imported data in Excel.
- Right-click and choose "Refresh" to refresh the data manually.

- To enable automatic refresh, right-click, choose "Properties," go to the "Refresh" tab, and select
the refresh options.

Please note that the exact options and steps might vary based on your version of Excel. Always
refer to the specific version's documentation or help resources for detailed instructions tailored to
your Excel version.

RESULT:
Thus the procedure to perform data import/export operations for different file formats in Ms-Excel
has done successfully

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
19

[Link] : 3 PERFORM STATISTICAL OPERATIONS - MEAN, MEDIAN, MODE

AND STANDARD, DEVIATION, VARIANCE, SKEWNESS,
Date : KURTOSIS

AIM:
To perform statistical operations – Mean, Median, Mode and Standard deviation,
Variance, Skewness, Kurtosis

PROCEDURE:

1. Mean:
- Explanation: The mean is the average of a set of numbers. It is calculated by adding up all the
numbers in a dataset and then dividing the sum by the total count of numbers.
- Excel Function: `AVERAGE(range)`

2. Median:
- Explanation: The median is the middle number in a sorted list of numbers. If the dataset has an
odd number of observations, the median is the middle value. If the dataset has an even number
of observations, the median is the average of the two middle numbers.

- Excel Function: `MEDIAN(range)`

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
20

3. Mode:
- Explanation: The mode is the number that appears most frequently in a dataset.

- Excel Function: `[Link](range)` for a single mode (most common), `[Link](range)`

for multiple modes.

4. Standard Deviation:
- Explanation: Standard deviation measures the dispersion or spread of a set of values. A low
standard deviation indicates that the values tend to be close to the mean, whereas a high standard
deviation indicates that the values are spread out over a wider range.

- Excel Function: `STDEV.P(range)` for the population standard deviation, `STDEV.S(range)` for
the sample standard deviation.

5. Variance:
- Explanation: Variance is the average of the squared differences from the Mean. It measures how
much each number in the dataset varies from the mean.
- Excel Function: `VAR.P(range)` for the population variance, `VAR.S(range)` for the sample variance.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
21

6. Skewness:
- Explanation: Skewness measures the asymmetry of the probability distribution of a real-valued
random variable about its mean. A negative skewness indicates that the left tail of the distribution is
longer or fatter than the right tail, while a positive skewness indicates the opposite.
- Excel Function: `SKEW(range)`

7. Kurtosis:
- Explanation: Kurtosis measures the tailedness of the probability distribution of a real-valued
random variable. It describes the shape of the distribution's tails. Higher kurtosis means more of the
variance is due to infrequent extreme deviations, as opposed to frequent modestly sized deviations.
- Excel Function: `KURT(range)`

RESULT:
Thus, Statistical operations – Mean, Median, Mode and Standard deviation, Variance,
Skewness, Kurtosis has done successfully.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
22

[Link] : 4
PERFORM Z-TEST, T-TEST ANOVA
Date :

AIM:
To perform Z-test, T-test & ANOVA

PROCEDURE:

Z-Test Syntax:
● [Link](array,x,[sigma])
● The [Link] function syntax has the following arguments:
● Array - The array or range of data against which to test x.
● x - The value to test.
● Sigma(Optional) The population (known) standard deviation. If omitted, the
sample standard deviation is used.
● Eg:

Formula Description (Result) Result

=2 * MIN([Link](A2:A11,4), Two-tailed probability-value of a z-test for the data set 0.181148
1 - [Link](A2:A11,4)) above, at the hypothesized population mean of 4
(0.181148)
=[Link](A2:A11,6) One-tailed probability-value of a z-test for the data set 0.863043
above, at the hypothesized population mean of 6
(0.863043)
=2 * MIN([Link](A2:A11,6), Two-tailed probability-value of a z-test for the data set 0.273913
1 - [Link](A2:A11,6)) above, at the hypothesized population mean of 6
(0.273913)
● Performing a T-test and ANOVA (Analysis of Variance) in Excel involves
comparing means of different groups to assess if there are statistically significant
differences between them. Here are the steps for both tests:

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
23

T-Test:
● 1. Enter Your Data:
● Let's assume you have two sets of exam scores for two different groups (Group A
and Group B):

● Explanation of the formula:

- `B2:B6`: Range for scores of Group A

- `B7:B11`: Range for scores of Group B

- `2`: Two-tailed distribution (for one-tailed, use `1`)
- `2`: Equal variance (for unequal variance, use `1`)

● The [Link] function returns a p-value. If the p-value is less than your chosen
significance level (commonly 0.05), you reject the null hypothesis, indicating
a significant difference between the means of the two groups.

ANOVA (Analysis of Variance):

● 1. Enter Your Data:
● Let's assume you have multiple sets of exam scores for different groups (Group A,
Group B, and Group C):

● Explanation of the formula:

● - `B2:B10`: Range for exam scores
● - `A2:A10`: Range for group labels

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
24

● - `1`: Indicates one-way ANOVA. For two-way ANOVA, use `2`.

● The ANOVA function assesses whether there are any statistically significant
differences between the means of the groups. If the p-value from ANOVA is less than
your chosen significance level (commonly 0.05), you can conclude that there is a
significant difference between at least two of the groups.

RESULT:
Thus, Z-test, T-test & ANOVA has been done successfully.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
25

Ex. No : 5
PERFORM DATA PRE-PROCESSING OPERATIONS
Date :

AIM:
To perform data pre-processing operations

i) Handling Missing data

ii) Normalization

PROCEDURE:
● Missing data, or Missing values, occur when no value is stored for the particular feature
in a Data. Missing data are a common occurrence and can have a significant effect on the
conclusions that can be drawn from the data.
1. Case Deletion: List wise deletion means that any individual in a data set is deleted from
an analysis if they're missing data on any variable in the analysis.
● The below code is used to delete all rows that have null [Link]: [Link]()
● Dropping columns that have missing values. Eg: [Link](axis =1)
● Filling all the missing values by 0. Eg: [Link](0)

2. Data Imputation:
● Imputation is the process of replacing missing data with substituted values.
When substituting for a data point we use Mean/Median/Mode Substitution.
● Eg: [Link]([Link]()) , [Link]([Link]()), [Link]([Link]())

HANDLING MISSING DATA:

In the beginning, select cell range D6:D8 as they are the first and last cells between the
missing values.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
26

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
27

Next, go to the Home tab and click on the Fill icon under the Editing group.

Here, select Series from the drop-down menu.

Following, the Series window will appear.

In this window, select Columns and Growth under the Series in and Type sections
respectively.
Along with it, mark the Trend box.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
28

After this, press OK.

That’s it, you will see the missing value in the blank cell.

Follow the similar process to get the final output like this:

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
29

NORMALIZATION:
It is the process of scaling data in such a way that all data points lie in a range of
0 to 1. Thus, this technique, makes it possible to bring all data points to a common scale. The
mathematical formula for normalization is given as:
NORMALIZATION FORMULA : X^{'} = \frac{X - X_{min}}{X_{max}-X_{min}}
where X is the data point, Xmax and Xmin are the maximum and minimum value in
the group of records respectively. The process of normalization is generally used when the
distribution of data does not follow the Gaussian distribution.

Let’s have a look at one example to see how can we perform normalization on a sample dataset.
Suppose, we have a record of the height of 10 students inside a class as shown below:

Height (in
cm)

152

155

168

175

153

162

173

166

158

156

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
30

Step 1: Calculate the minimum value in the distribution. It can be calculated

using the MIN() function. The minimum value comes out to be 152 which is stored in the B14
cell.

Step 2: Calculate the maximum value in the distribution. It can be calculated

using the MAX() function. The maximum value comes out to be 175 which is stored in the B15
cell.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
31

Step 3: Find the difference between the maximum and minimum values. Their
difference comes out to be 175 – 152 = 23 which is stored in the B16 cell.

Step 4: For the first data stored in the A2 cell, we will calculate the normalized
value as shown in the below video.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
32

Step 5: We can manually calculate all values one by one for each data record or
we can directly get values for all the other cells using the auto-fill feature of Excel. For this, go
to the right corner of the B2 cell until a (+) symbol appears, and then drag the cursor to the
bottom to auto-populate values inside all the cells.

Note: While calculating the first normalized value in the B2 cell, it should be
made sure that the reference address for the B14 and B16 cells should be locked using Fn + F4
button otherwise an error will be thrown.
If we have a close look at the results, we can notice all the values lies in the range 0 to 1.

Standardization (Or Z-score normalization)

Standardization is a process in which we want to scale our data in such a way that
the distribution of our data has its mean as 0 and standard deviation as 1. The mathematical
formula for standardization is given as:
STANDARDIZATION FORMULA : X^{'} = \frac{X - X_{mean}}{\sigma_{_{x}}}
where where X is the data point, Xmean is the mean of the distribution and σx is the
standard deviation of the distribution.

The process of standardization is generally used when we know the distribution of

data follows the gaussian distribution.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
33

Method 1: Calculating z-score normalization manually

Step 1: Calculate the mean/average of the distribution. It can be done using the AVERAGE()
function. The mean value comes out to be 161.8 and is stored in the B14 cell.

Step 2: Calculate the standard deviation of the distribution which can be done
using the STDEV() function. The standard deviation comes out to be 8.323994767 which is
stored in the B15 cell.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
34

Step 3: For the first data stored in the A2 cell, we will calculate the standardized value as
shown in the image given below.

Step 4: After manually calculating the first value, we can simply use the auto-fill
feature of Excel to populate the standardized values for all other records.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
35

Method 2: Calculating Z-score normalization using the STANDARDIZE() function

We can even use the built-in STANDARDIZE() function to find the standardized
value of an element. The syntax for STANDARDIZE() function is given as:

=STANDARDIZE(x,mean,std_dev)

Where x is the specific element/range of cells, mean is the

average/arithmetic mean of all the elements in the record, and std_dev is the standard deviation
of all the elements in the record
Step 1: Calculate the mean/average of the distribution. It can be done using the
AVERAGE() function. The mean value comes out to be 161.8 and is stored in the B14 cell.

Step 2: Calculate the standard deviation of the distribution which can be done
using the STDEV() function. The standard deviation comes out to be 8.323994767 which is
stored in the B15 cell.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
36

Step 3: For the first data stored in the A2 cell, we will calculate the standardized value
as shown in the below image.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
37

Step 4: After manually calculating the first value, we can simply use the auto-fill
feature of Excel to populate the standardized values for all other records.

RESULT:
Thus, data pre-processing operations have been done successfully.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
38

[Link] : 6 PERFORM DIMENSIONALITY REDUCTION OPERATION

USING PCA, KPCA & SVD
Date:

AIM:
To Perform dimensionality reduction operation using PCA, KPCA & SVD

PROCEDURE:

PCA:
● Consider the below dataset

● Click PCA icon shown below

● Select range in input data

● Then click option tab and check the principal components and deselect Input variable
output. Refer below figure.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
39

● Then switch to the Missing value tab. If the dataset contains any missing values, users need to
remove those observations so select that and click OK.

● Once the OK button is clicked the wizard displays three separate tables.
● First table shows variance for each principal component.

● Second table shows the weight for each input variables.

● Third table shows the corresponding values for principal components.

● Next let’s generate the first three principal components to represent all the input variables.
● Select PCA->Options and deselect Principal Component and increase No. of PC’s included to
3 and click OK.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
40

● Once the OK button is clicked the wizard displays three separate tables.
● First table shows percentage of variance for each input of variable that three PC’s are
capturing. principal component.

● Second table shows the weight for each PC input variables.

● Third table shows the fitted values for each inputs.

RESULT:

Thus, dimensionality reduction operation using PCA, KPCA & SVD has been performed
successfully

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
41

[Link]: 7 PERFORM BIVARIATE AND MULTIVARIATE ANALYSIS ON THE

DATASET
Date :

AIM:
To perform bivariate and multivariate analysis on the dataset.

PROCEDURE:

Bivariate Analysis:

The term bivariate analysis refers to the analysis of two variables. You can remember this because the
prefix “bi” means “two.”

The purpose of bivariate analysis is to understand the relationship between two variables

There are three common ways to perform bivariate analysis:

1. Scatterplots

2. Correlation Coefficients

3. Simple Linear Regression

The following example shows how to perform each of these types of bivariate analysis in Excel using the
following dataset that contains information about two variables: (1) Hours spent studying
and (2) Exam score received by 20 different students:

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
42

1. Scatterplots

To create a scatterplot of hours vs. score, we can highlight cells A2:B21, then click the Insert tab along
the top ribbon, then click Insert Scatter Chart within the Charts group:

We can also modify the y-axis limits to gain a better view of the data points.

To do so, double click the y-axis. In the Format Axis panel that appears on the right side of the screen,
click Axis Options and then change the Minimum and Maximum bounds to 60 and 100,
respectively.

The y-axis will automatically update:

The x-axis shows the hours studied and the y-axis shows the exam score received.

From the plot we can see that there is a positive relationship between the two variables. As hours studied
increases, exam scores tend to increase as well.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
43

2. Correlation Coefficients

A Pearson Correlation Coefficient is a way to quantify the linear relationship between two variables.

We can use the following formula in Excel to calculate the correlation coefficient between hours studied
and exam score:

=CORREL(A2:A21, B2:B21)

The correlation coefficient turns out to be 0.891.

This value is close to 1, which indicates a strong positive correlation between hours studied and exam
score received.

3. Simple Linear Regression

Simple linear regression is a statistical method we can use to quantify the relationship between two
variables.

To fit a simple linear regression model in Excel, click the Data tab along the top ribbon, then click
the Data Analysis option in the Analyze group. In the new panel that appears, click Regression and
then click OK.

Note: If you don’t see the Data Analysis option, you need to first load the Excel Analysis ToolPak.

In the panel that appears, enter the following information and then click OK:

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
44

Once you click OK, the results of the regression model will appear:

The fitted regression equation turns out to be:

Exam Score = 69.0734 + 3.8471*(hours studied)

This tells us that each additional hour studied is associated with an average increase of 3.8471 in exam
score.

We can also use the regression equation to estimate the score that a student will receive based on their
total hours studied.

For example, a student who studies for 3 hours is estimated to receive a score of 81.6147:

● Exam Score = 69.0734 + 3.8471*(hours studied)

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
45

● Exam Score = 69.0734 + 3.8471*(3)

● Exam Score = 81.6147

Multivariate Analysis:

steps of conducting multivariate regression in Excel:

Step 1: Organize Your Data

Before you can conduct multivariate regression analysis, you need to organize your data. Your data
should include a dependent variable and one or more independent variables. Each row of your data
should represent a unique observation or case.

Step 2: Open the Data Analysis Tool

Excel has a built-in data analysis tool that you can use to conduct multivariate regression analysis. To
access this tool, you first need to enable it. To do this, go to the “Tools” menu, select “Excel Add-
ins” and Check the “Analysis ToolPak” box and click “OK.”

Step 3: Select the “Regression” Option

With the data analysis tool enabled, select the “Data” tab, and then select “Data Analysis” from the
“Analysis” group. Select “Regression” from the list of analysis tools and click “OK.”

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
46

Step 4: Choose Your Input Range

In the Regression dialog box, select the dependent variable and the independent variables. Make sure to
choose your input range carefully, as this will determine the variables that are used in the analysis.

Step 5: Choose Your Output Range

Next, choose where you want the output of the analysis to be displayed. This can be in a new worksheet
or in a specific range on an existing worksheet.

Step 6: Check the “Residuals” Box

In the Regression dialog box, make sure to check the “Residuals” box. This will allow you to see how
well the model fits the data.

Step 7: Interpret the Results

Once you have conducted the analysis, you can interpret the results. The output will include a summary of
the model, including the coefficient values for each independent variable, the standard error, t-value,
and p-value. You can use these values to determine the significance of each independent variable.

RESULT:
Thus, bivariate and multivariate analysis on the dataset has been done successfully.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
47

[Link] : 8 APPLY AND EXPLORE VARIOUS PLOTTING FUNCTIONS

ON THE DATA
Date :

AIM:

To perform various plotting functions on the data set.

PROCEDURE:
Creating a graph in Excel is easy. Types of plotting:

● Bar Graph

● Pie Chart

● Scatter Plot

The dataset that we will be using comprises Coronavirus cases, country-wise. It has records of:

● WHO Region of every country.

● Confirmed cases

● Active cases

● Recovered cases

● Deaths

Let’s move on to understand how to create a bar graph in an easy and simple way.

1. Bar Graph

A bar graph helps you display data using rectangular bars, where the length of each bar is a numeric value
depending on the category it belongs to.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
48

Follow the steps listed below to know how to create a bar graph in Excel.

● Once data that is required is ready, a bar graph can now be created. As shown you locate the
INSERT TAB → Charts section → Bar Graph option and select the type of bar graph that best suits
your requirement.

● After selecting the appropriate bar chart, you can see a blank window that is open on the Excel sheet.
On right-clicking on this blank window, you should find an option to Select Data. Clicking on it will
open the Select Data Source window on your screen.

● Here, the chart data range can be added by simply dragging the mouse and selecting the
required data.

● Now, select the Legend entries (or Vertical axis). In the current example, we would select the Y-axis
values as confirmed cases, recovered cases, deaths, and active cases.

● On the other hand, as for the horizontal axis, select all the countries that we have filtered in
the current example from the WHO region, i.e., South-East Asia.

● After specifying the appropriate values, click on OK. Excel will now display a graph on
your worksheet. You can go ahead and format your graph based on your requirement.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
49

● To make your charts attractive and aesthetically appealing, you may change the color palette of the
graph, add text or display more information about it, etc.

● Double click on the chart window to locate various customization options in the toolbar, available
to you.

● You may also arrive here by selecting the option beside the chart.

● This will open a drop-down box consisting of various Chart Elements.

For this example, do select the Legend checkbox for displaying the confirmed cases, recovered cases,
active cases, and deaths on the graph. Further, the Chart Title box can be selected to add a title.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
50

This was all about creating a bar graph in Excel. Let’s move ahead and learn how to create a pie chart.

2. Pie Chart

A pie chart is a circular graph that represents data by dividing the circle into sectors, where each sector
illustrates a proportion to the whole.

Follow the steps mention below to learn to create a pie chart in Excel.

● From your dashboard sheet, select the range of data for which you want to create a pie chart. We
will create a pie chart based on the number of confirmed cases, deaths, recovered, and active cases
in India in this example.

● Select the data range. Then, click on the Insert Tab. You will find the PIE chart option available in
the charts group.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
51

● Select an appropriate pie chart from a range of pie charts available. Clicking on it will open the PIE
chart in a window. Right-click on the chart window and click on Select Data. The Select Data
Source window will be opened where you can choose what data you want to be displayed on your
pie chart.

● Here, the chart data range can be added by simply hovering your mouse to select the data required.

1. You can now select the legend entries (or vertical axis). In this current example, you will need
to select India to display the cases pertaining to a specific country.

2. For labels on the horizontal axis labels, you may select confirmed cases, deaths, recovered, and
active cases, and depict them on the chart.

3. After specifying the entries, click on OK. This will display the pie chart on your window.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
52

● You can click on the icons next to the chart to add your finishing touches to it. Clicking on the chart
elements will show you options where you can choose to display or hide data labels, chart tiles, and
legend. You can choose from various styles by clicking on the chart styles. This lets you style your
chart based on your requirement. You can also add multiple colors in your graph to make it look
more presentable.

● You can also format the data by clicking on the Format data labels. This will show you different
label options from which you can check and uncheck the different options available under it.

In the next section, you will learn how to create a Scatter Plot in Excel.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
53

3. Scatter Plot

A Scatter Plot consists of a horizontal axis, a vertical axis, and a series of dots where each dot represents
data values.

To demonstrate this, we use a Day-wise COVID dataset that has the columns:

● Date

● Confirmed Cases

● Deaths

● Recovered Cases

● Active Cases

Follow the steps below to understand how to create a Scatter Plot in Excel.

● Select the required data. In this example, we depict the relation between date and confirmed
cases using a Scatter Plot. So, we select only the columns containing the date and confirmed
Cases.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
54

● Go to Insert Tab → Charts Section → Scatter Plot Option.

● Click on the appropriate Scatter Plot option. This will plot the values. You can format the chart,
select a chart title, and add axis labels too. As you can notice, we have created a scatter plot on Date
vs. Confirmed Cases. From the figure, we can easily conclude how the confirmed cases of COVID
rise drastically every day.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
55

In the final section, let’s look at how to make a histogram.

Histogram

A Histogram is a frequency distribution graph that uses rectangles/bars to group data into ranges and
indicates the frequency of occurrence for each range.

To understand how histograms work, let’s look at an example.

The following Employee Salary dataset contains the columns:

● Employee ID

● Employee Name

● Job Title

● Total Pay

We will demonstrate how to create a histogram that depicts the number of employees under each salary
group.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
56

We will demonstrate how to create a histogram that depicts the number of employees under each salary
group.

Follow the steps mentioned below to create a simple histogram.

● Select the data from the sheet on which you want to make a histogram.

● Click on the Insert Tab, you will find the Insert Statistic Chart option in the Charts group.

● A drop down will appear from where you can select the desired histogram chart.

● The histogram chart gets displayed. To customize your histogram, click on the icons that appear
next to it.

● Clicking on the chart elements will show you options where you can choose to display or hide
axis titles, data labels, chart tiles, and Legend, etc.

● Clicking on the chart styles lets you style your chart to your requirement.
You can add multiple colors in your graph to make it look more
presentable.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
57

● To format your histogram plot, double click on the graph. You will see the Format Axis window
open on the right side.

● Click on Axis options → Horizontal Axis.

● You will find a list of different options to modify your histogram chart.

● By Category: You can select this option if you have repeated lists of categories and if you want
to know the sum or count based on those categories.

● Automatic: This is the default option. It automatically decides what bins to create in the
histogram. As you can notice, 5 bins are created in our chart.

● Bin Width: This option defines the range width.

In this example, we have specified the bin width as 40000.

● Number of Bins: In this option, you can enter the number of required bins. The chart will be
created with the specified number of bins.

● Overflow Bin: This can be used when you want all the values over a certain value to be
grouped together in the histogram.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
58

We have specified 300000 as our overflow value. Any value higher than 300000 will be grouped under
the last range.

● Underflow Bin: This can be used when you want all the values below a certain value to be
grouped together in the histogram.

We have specified 80000 as our overflow value. Any value lower than 80000 will be grouped under the
last range.

● Right-click on the chart and click on Add Data Labels to include the values on top of each range.

● After formatting the histogram accordingly, we arrived at the following graph.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
59

This histogram successfully depicts the total number of employees grouped by salary range.

This is all you need to know about creating a graph in Excel.

RESULT:
Thus, various plotting functions on the data set has done successfully.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
60

[Link] : 9
EXPLORE THE FEATURES OF POWER BI DESKTOP
Date :

AIM : To explore the features of Power Bi desktop.

PROCEDURE:
Power BI Desktop is a powerful data visualization and reporting tool from Microsoft that allows
you to create interactive reports and dashboards. Here are some of the key features and capabilities of
Power BI Desktop:
1. Data Connectivity:
Power BI Desktop supports a wide range of data sources, including databases (SQL Server, Oracle,
MySQL), cloud services (Azure, AWS), Excel files, CSV files, and more. You can connect to on-
premises and cloud-based data sources, making it versatile for various data scenarios.
2. Data Transformation:
The Power Query Editor within Power BI Desktop allows you to clean, reshape, and transform data
before loading it. You can perform operations such as filtering, merging, pivoting, and aggregating data.
3. Data Modelling:

Power BI enables you to create data models by defining relationships between tables. You can create
calculated columns and measures using the Data Analysis Expressions (DAX) language.
4. Visualizations:
Power BI offers a wide variety of visual elements (charts, tables, maps, etc.) to represent data.
Customization options include colour schemes, formatting, and interactive features.
5. Reports and Dashboards:
You can create interactive reports and dashboards by arranging visualizations on report pages. Power
BI allows for drill-through actions and bookmarks for navigation.
6. Data Refresh:
Power BI supports data refresh from various sources, including scheduled refreshes. Real-time
streaming data is also supported.
7. Publishing and Sharing:
You can publish your reports to the Power BI Service, which is a cloud-based platform. Share your
reports and dashboards with others, control access, and collaborate with team members.
8. Natural Language Query (Q&A):
Q&A enables users to ask questions about their data in plain language, and Power BI generates
visualizations based on the queries.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
61

9. Mobile Support:
Power BI provides mobile apps for iOS and Android, allowing users to access reports and dashboards
on their mobile devices.
10. Custom Visuals:
You can extend Power BI's capabilities by importing custom visuals created by the community or developing
your own.
11. Advanced Analytics:
Power BI supports integration with machine learning models and the R language for advanced
analytics.
12. Power BI Service Integration:
Seamlessly publish reports to the Power BI Service, where you can collaborate, share, and create dashboards
for wider audiences.
13. Power BI Paginated Reports:
Create paginated reports for highly formatted, print-ready documents.
14. Drill-Through and Cross-Filtering:
Explore data in more detail by setting up drill-through paths and cross-filter visuals for enhanced
interactivity.
15. AI Capabilities:
Power BI includes AI-powered features like automated insights and quick insights to discover trends
and outliers in your data.

RESULT:
Thus the procedure to explore the features of Power Bi Desktop has has done successfully.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
62

[Link] : 10
PREPARE & LOAD DATA
Date :

AIM : To prepare and load data in Power Bi Desktop

PROCEDURE:

Step 1: Install Power BI Desktop

If you haven't already, download and install Power BI Desktop from the official Microsoft website.

Step 2: Launch Power BI Desktop

Open Power BI Desktop on your computer.

Step 3: Get Data

Click on "Get Data" on the Home tab in Power BI Desktop.
A "Get Data" window will open. Here, you can select the type of data source you want to connect to.
In this example, we'll choose "Text/CSV".

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
63

Step 4: Connect to Your Data Source

Select "Excel" from the list of data sources and click "Connect". Navigate to the location of your Excel
file and select it. Click "Open" to connect to the data.

Step 5: Choose Data to Load

In the Navigator window, you will see a list of tables and sheets available in your Excel file. Select the
data you want to import into Power BI by checking the checkboxes next to the tables or sheets. You can
also preview the data by clicking on a table or sheet. Once you've made your selection, click "Load."

Step 6: Transform Data (Optional)

After loading the data, you may want to transform it. Click on "Edit" to open the Power Query Editor,
where you can perform various data cleaning and transformation tasks. In the Power Query Editor, you
can filter data, remove columns, merge tables, perform calculations, and more. Once you've finished
transforming the data, click "Close & Apply" to save your changes and load the transformed data into
Power BI.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
64

Step 7: Build Your Report

After loading and transforming your data, you can start building your report by creating visuals and
dashboards in Power BI Desktop.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
65

Step 8: Save Your Power BI Report

Save your Power BI report in the .pbix file format so that you can reopen it later for further analysis
and report building.

RESULT:
Thus the procedure to prepare & load data in Power Bi Desktop has has done successfully.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
66

[Link] : 11
DEVELOP THE DATA MODEL
Date :

AIM : To develop the data model in Power Bi Desktop.

PROCEDURE:
Developing a data model in Power BI Desktop is a crucial step for creating interactive reports and
dashboards. Here's a step-by-step guide on how to develop a data model in Power BI Desktop:

Step 1: Data Source and Loading

1. Open Power BI Desktop and click on "Get Data" to connect to your data source, such as an
Excel file, database, or cloud service.
2. Choose the specific data source and load the necessary data into Power BI.

Step 2: Data Transformation (Optional)

1. Click on "Edit Queries" to open the Power Query Editor. Here, you can clean and transform
your data as needed.
2. Perform tasks such as filtering, removing columns, merging tables, and creating calculated
columns.
3. Click "Close & Apply" to save your transformations and load the data into the Power BI model.

Step 3: Data Modelling

Data modeling in Power BI involves creating relationships between tables, defining calculated
columns and measures, and shaping data to facilitate insightful reporting. Let's explore data
modeling in Power BI using an example.
1. In the Power BI Desktop report view, you'll see two sections: "Fields" and "Visualizations."
2. In the "Fields" section, you'll see all the tables and columns you've imported. These are the
components of your data model.
3. Define relationships between tables by clicking on "Model" in the left sidebar. Use the drag-
and-drop interface to connect related tables.
4. Create calculated columns and measures using the Data Analysis Expressions (DAX) language.
You can do this by right-clicking on a table and selecting "New Column" or "New Measure".

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
67

Example: Sales and Product_data

Suppose you work for a retail company, and you want to create a Power BI report that analyzes
sales_data. You have two main data sources:
1. Sales_data: This table contains information about each sale, including the sale date, product
sold, quantity, and revenue.

2. Product_data: This table contains product details, including the product name, category, and
price.

Step 1: Data Import and Relationship Building

1. Import both data tables into Power BI Desktop. Your "Fields" pane will show the tables and
their columns.
2. In the "Model" view, establish relationships between the tables. For example, create a
relationship between the "Product_name" column in the "Sales_data" table and the
"Product_name" column in the "Product_data" table. This relationship connects each sale to a
specific product.

Step 2: Calculated Columns

Now, let's create a calculated column in the "Sales_data" table to calculate the total revenue for
each sale.
1. In the "Model" view, select the "Sales_data" table.
2. Click "New Column" and enter a DAX expression:

Total Revenue = Sales_data[Quantity] * RELATED(Product_data[Price])

This expression multiplies the quantity of products sold by the price of the related product, giving
you the total revenue for each sale.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
68

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
69

Step 3: Measures
Now, let's create a measure to calculate the total revenue for all sales.
1. In the "Model" view, right-click on the "Sales_data" table and choose "New Measure."

2. Enter a DAX expression:

Total Sales Revenue = SUM('Sales_data'[Total Revenue])

This measure sums the total revenue from the calculated column to give you the total sales
revenue.

Step 4: Report Building

Now that you've set up the data model, switch to the "Report" view to create visualizations. You
can drag and drop fields onto the canvas to build your report. For example:
● Drag the "Date" column from the "Sales_data" table to create a date hierarchy.
● Create a chart to visualize monthly sales over time.
● Use the "Total Sales Revenue" measure to display the overall sales revenue.
You can create various visuals, such as bar charts, tables, and slicers, to explore your data and gain
insights.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
70

Step 5: Interactivity and Insights

Use features like slicers, cross-filtering, and drill-through actions to make your report interactive.
For instance, you can allow users to filter data by product category or drill through sales details for
specific products.
With data modeling, you've connected related tables, defined calculations, and created meaningful
visuals, enabling you to generate insights and reports that provide valuable information for your
retail company's decision-makers.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
71

Step 4: Visualization and Report Building

1. Go back to the "Report" view by clicking on the "Report" tab in the left sidebar.
2. Drag and drop fields from the "Fields" section onto the canvas to create visualizations, such
as charts, tables, and maps.
3. Customize your visualizations by using the "Visualizations" pane on the right. You can adjust
formatting, titles, and colours.
4. Add more pages to create a multi-page report or dashboard, and use bookmarks and actions
for interactivity.

Step 5: Test and Refine

1. Interact with your report to ensure that it provides the desired insights and functionality.
2. Use the "Data" and "Model" views to review your data and relationships.
3. Fine-tune your DAX measures and calculations to meet your reporting requirements.

Step 6: Save and Publish

1. Save your Power BI Desktop file in .pbix format so you can reopen and edit it in the future.
2. To share your report with others, publish it to the Power BI Service or export it to a PDF or
other formats.

RESULT:
Thus the procedure to develop the data model in Power Bi Desktop has has done successfully.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
72

[Link] : 12
PERFORM DAX CALCULATIONS
Date :

AIM : To perform dax calculations in Power Bi Desktop.

PROCEDURE:
Performing Data Analysis Expressions (DAX) calculations in Power BI allows you to create custom
columns, measures, and calculated tables to derive insights from your data. Let's go through some
common DAX calculations with examples:

Example 1: Creating a Calculated Column

Suppose you have a "Sales_data" table with the columns "Quantity" and "Price." You want to add a
new column called "Total Revenue."
1. In Power BI Desktop, go to the "Model" view.
2. Select the "Sales_data" table.
3. Click on "New Column" and enter the following DAX expression:

Note: Please refer above screenshot.

This calculation multiplies the quantity by the price for each row, giving you the total
revenue.

Example 2: Creating a Measure

Now, let's create a measure for the total sales revenue across all rows.
1. In the "Model" view, right-click on the "Sales_data" table and select "New Measure."
2. Enter the following DAX expression:

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
73

1. This measure sums up the total revenue calculated in the previous example, giving you the
overall sales revenue.

Example 3: Aggregating Data Using CALCULATE

Suppose you want to calculate the percentage of total sales revenue contributed by each product category.
1. Create a new measure:

This measure uses CALCULATE to calculate the total sales revenue for each product category, and
DIVIDE calculates the percentage.

Example 4: Time Intelligence Functions

If your data includes a date column, you can leverage time intelligence functions. Let's say you want
to calculate the year-to-date (YTD) sales.
1. Create a YTD measure:

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
74

This measure uses TOTALYTD to calculate the cumulative total sales revenue from the beginning of
the year to the current date.

Example 5: Ranking
You might want to rank products based on their sales revenue.
1. Create a ranking measure:

This measure uses RANKX to rank products based on their total sales revenue in descending order.
These examples demonstrate the versatility of DAX in Power BI. You can perform calculations,
aggregations, and time-based analyses to derive meaningful insights from your data.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
75

RESULT : The procedure to perform the dax calculations in Power Bi Desktop has been done
successfully.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
76

[Link] : 13
DESIGN A REPORT
Date :

AIM:
To design a report in Power Bi Desktop

PROCEDURE:
Designing a report in Power BI involves creating visuals that provide insights into your data. Let's
design a simple sales report using the example of "Sales_data" with the calculated columns and
measures we discussed earlier.

1. Open Power BI Desktop:

● Open Power BI Desktop and load your "Sales_data" with the calculated columns and
measures.

2. Create Visualizations:
● Visual 1: Total Sales Revenue Over Time
● Drag the "Date" column to the axis of a line chart.
● Drag the "Total Sales Revenue" measure to the values of the line chart.
● Adjust the chart properties, such as title, axis labels, and legend.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
77

● Visual 2: Product Sales Contribution

● Create a table visualization.
● Drag the "Product Name" and "Total Revenue" columns to the table.
● Sort the table by "Total Revenue" in descending order.

● Visual 3: Percentage of Total Revenue by Category

● Create a pie chart.
● Drag the "Category" column to the legend of the pie chart.
● Drag the "Percentage of Total Revenue" measure to the values of the pie chart.
● Adjust the chart properties.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
78

● Visual 4: YTD Sales Comparison

● Create a line and clustered column chart.
● Drag the "Date" column to the axis.
● Drag the "Total Sales Revenue" and "YTD Sales" measures to the values.
● Adjust the chart properties.

● Visual 5: Product Ranking

● Create a table visualization.
● Drag the "Product Name" and "Product Ranking" columns to the table.
● Format the table to highlight the top-ranked products.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
79

3. Arrange Visualizations:
Arrange the visuals on the report canvas to create a cohesive and informative layout.

4. Customize and Format:

Customize the visuals by adjusting colors, formatting, and titles.
Add a slicer for date filtering or other relevant filters.

5. Save and Publish:

Save the Power BI Desktop file (.pbix).
Publish the report to the Power BI Service for sharing and collaboration.

6. Interactivity:
Test interactivity by clicking on visuals to see how data filters across the report.
This is a basic example, and you can expand the report by adding more visuals, incorporating additional
insights, and refining the design based on your specific requirements. Power BI provides a wide range
of visualizations and customization options to create dynamic and engaging reports.

RESULT:
Thus the procedure to design a report in Power Bi Desktop has has done successfully.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
80

[Link] : 14 CREATE A DASHBOARD AND PERFORM

DATA ANALYSIS
Date :

AIM : To create a dashboard and perform data analysis in Power Bi Desktop

PROCEDURE:
Creating a dashboard in Power BI involves combining multiple visuals into a single view for a
comprehensive overview of your data. Let's build a dashboard using the sales example with the
calculated columns and measures.

Dashboard Design Steps:

1. Open Power BI Desktop:
● Open Power BI Desktop and load your "Sales_data" with the calculated columns and
measures.

2. Create Visualizations:
● Visual 1: Total Sales Revenue Card
● Create a card visualization.
● Drag the "Total Sales Revenue" measure to the card.

● Visual 2: Sales Over Time

● Create a line chart.
● Drag the "Date" column to the axis.
● Drag the "Total Sales Revenue" measure to the values.

● Visual 3: Product Sales Contribution

● Create a clustered bar chart.
● Drag the "Product Name" to the axis.
● Drag the "Total Revenue" measure to the values.

● Visual 4: Percentage of Total Revenue by Category

● Create a donut chart.
● Drag the "Category" column to the legend.

● Drag the "Percentage of Total Revenue" measure to the values.

● Visual 5: YTD Sales Comparison

● Create a combo chart.
● Drag the "Date" column to the axis.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
81

● Drag the "Total Sales Revenue" and "YTD Sales" measures to the values.

● Visual 6: Product Ranking Table

● Create a table visualization.
● Drag the "Product Name" and "Product Ranking" columns to the table.

3. Arrange Visualizations on the Dashboard:

● Create a new dashboard by clicking on the "New Dashboard" button.
● Drag and drop the visualizations onto the dashboard canvas.
● Arrange and resize the visuals to create a clean layout.

4. Customize and Format:

● Customize the visuals on the dashboard by adjusting colors, formatting, and titles.
● Add a title to the dashboard.

5. Interactivity:
● Test interactivity by clicking on visuals to see how data filters across the dashboard.

6. Save and Publish:

● Save the Power BI Desktop file (.pbix).
● Publish the dashboard to the Power BI Service for sharing and collaboration.

7. Power BI Service:
● Access the Power BI Service and open your published report.
● Pin the dashboard to the Power BI Service dashboard for easy access.
This dashboard provides a consolidated view of key sales metrics, allowing stakeholders to quickly
analyze and monitor performance. You can add more visuals, KPIs, and insights based on your specific
requirements. Power BI dashboards are dynamic, and users can interact with the data to gain deeper
insights.

RESULT:
Thus the procedure to create a dashboard and perform data analysis in Power Bi Desktop has has done
successfully.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
82

[Link] : 15
PRESENTATION OF A CASE STUDY
Date :

AIM:
To presentation of a case study in Power Bi Desktop

PROCEDURE:

For this case study, let's consider a retail company, XYZ Retailers, analyzing their sales_data to make
informed business decisions.

Case Study: XYZ Retailers Sales Analysis

Objective: XYZ Retailers wants to gain insights into their sales performance to identify trends, top-
selling products, and potential areas for improvement.

Data Source: The company has a dataset named "[Link]," which includes information about
sales transactions, products, and customer demographics.

Power BI Analysis:
1. Data Loading:
● Import the "[Link]" file into Power BI Desktop.
● Explore the data to understand the available tables and columns.

2. Data Model:
● Create relationships between relevant tables (e.g., Sales, Products, Customers).
● Define calculated columns and measures to enhance data analysis (e.g., Total Revenue,
Average Transaction Value).

3. Dashboard Design:
● Visual 1: Total Sales Revenue Over Time
● Line chart displaying total sales revenue over time to identify sales trends.

● Visual 2: Top Selling Products

● Bar chart showcasing the top-selling products by revenue.

● Visual 3: Customer Demographics

● Pie chart presenting the distribution of sales by customer demographics.

● Visual 4: Monthly Sales Comparison

● Stacked column chart comparing monthly sales performance.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
83

4. Insights and Analysis:

● Insight 1: Seasonal Trends
● Identify seasonal trends in sales and adjust inventory accordingly.

● Insight 2: Product Performance

● Recognize top-selling products to focus marketing efforts or manage inventory.

● Insight 3: Customer Segmentation

● Analyze sales across different customer segments to tailor marketing strategies.

5. Recommendations:
● Recommendation 1: Marketing Focus
● Allocate marketing budget based on top-selling products and customer segments.

Recommendation 2: Inventory Management

Optimize inventory levels by considering seasonal trends and product performance.

Recommendation 3: Customer Engagement

Develop targeted promotions for specific customer segments.

6. Action Plan:
Action 1: Marketing Campaign
Launch a marketing campaign focusing on top-selling products.

Action 2: Inventory Adjustment

Adjust inventory levels based on seasonal trends and product performance.

Action 3: Customer Engagement Program

Implement a customer engagement program targeting specific segments.

7. Dashboard Sharing:
● Save the Power BI report.
● Publish the report to the Power BI Service.

● Share the interactive dashboard with relevant stakeholders.

8. Monitoring and Iteration:

● Continuously monitor the dashboard for changes in sales patterns.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT

CCW331 - Business Analytics
84

● Iterate on the analysis based on new data and business goals.

This case study presentation in Power BI provides a structured approach, combining visuals, insights,
recommendations, and action plans to guide decision-making for XYZ Retailers. The interactive nature
of Power BI allows stakeholders to explore the data further and gain a deeper understanding of the
business dynamics.

RESULT:
Thus the procedure to presentation of a case study in Power Bi Desktop has has done successfully.

NAME:SHRI HARIHARA SUTHAN M REG NO : 711723205047 DEPT : [Link] IT