MANGAYARKARASI COLLEGE OF ENGINEERING

(Approved by AICTE, New Delhi & Affiliated to Anna University, Chennai)

MANGAYARKARASI NAGAR, PARAVAI, MADURAI – 625 402
Website: [Link] E-Mail: : [Link]@[Link]

DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING (AI&ML)

ACADEMIC YEAR (2024-2025 ODD SEMESTER)

LABORATORY MANUAL

[Link] : CCW331

[Link] : BUSINESS ANALYTICS

Regulation : 2021

PreparedBy, ApprovedBy,

[Link].,AP/CSE HoD-CSE
 LIST OF EXPERIMENTS

[Link] EXPERIMENT

1 Explore the features of Ms-Excel

(i) Get the input from user and perform numerical operations (MAX, MIN, AVG,
2 SUM, SQRT, ROUND) ii) Perform data import/export operations for different file
formats.
Perform statistical operations - Mean, Median, Mode and Standard deviation,
3 Variance, Skewness, Kurtosis

4 Perform Z-test, T-test & ANOVA

5 Perform data pre-processing operations i) Handling Missing data ii) Normalization

6 Perform dimensionality reduction operation using PCA, KPCA & SVD
7 Apply and explore various plotting functions on the data set.

8 Perform bivariate and multivariate analysis on the dataset.

9 Explore the features of Power BI Desktop

10 Prepare & Load data

11 Develop the data model

12 Perform DAX calculations

13 Design a report

14 Create a dashboard and perform data analysis

15 Presentation of a case study ( Improving Sales Performance with Power BI )

 CCW331 - BUSINESS NALYTICS
COURSE OBJECTIVES:
To understand the Analytics Life Cycle.
To comprehend the process of acquiring Business Intelligence
To understand various types of analytics for Business Forecasting
To model the supply chain management for Analytics.
To apply analytics for different functions of a business

LIST OF EXPERIMENTS:
I Cycle – MS Excel
1. Explore the features of Ms-Excel.
2. (i) Get the input from user and perform numerical operations (MAX, MIN, AVG, SUM,
SQRT, ROUND)
ii) Perform data import/export operations for different file formats.
3. Perform statistical operations - Mean, Median, Mode and Standard deviation, Variance,
Skewness, Kurtosis
4. Perform Z-test, T-test & ANOVA
5. Perform data pre-processing operations i) Handling Missing data ii) Normalization
6. Perform dimensionality reduction operation using PCA, KPCA & SVD
7. Perform bivariate and multivariate analysis on the dataset.
8. Apply and explore various plotting functions on the data set.
II Cycle – Power BI Desktop
9. Explore the features of Power BI Desktop
10. Prepare & Load data
11. Develop the data model
12. Perform DAX calculations
13. Design a report
14. Create a dashboard and perform data analysis
15. Presentation of a case study
COURSE OUTCOMES:
CO1:Understand the techniques in different phases of a compiler.
CO2:Design a lexical analyser for a sample language and learn to use the LEX
tool. CO3:Apply different parsing algorithms to develop a parser and learn to use
YACC tool
CO4:Understand semantics rules (SDT), intermediate code generation and run-time
environment.
CO5:Implement code generation and apply code optimization techniques.
 Ex. No.: 1
Explore the features of Ms-Excel
Date:

Aim:
To familiarize users with basic Excel functions and operations, including data
entry, formatting, and simple calculations.
Materials:
 Computer with Microsoft Excel installed
 Sample data (provided or generated as needed)
Procedure:
Task 1: Data Entry and Basic Formatting
1. Open Microsoft Excel on your computer.
2. Create a new workbook.
3. In cell A1, enter the title "Student Data."

4. In cells A3 to A8, enter the following labels for columns: "ID," "Name," "Math
Score," "English Score," "Total Score."

5. Enter sample data in cells B3 to F8. Make sure to include a variety of numeric and
text values.

Task 2: Formulas and Calculations 6. In cell E3, create a formula to calculate the total
score for each student. Use the formula =C3+D3 and drag it down to apply to the entire
column.
7. In cell E9, use the formula =SUM(E3:E8) to find the total of all the total scores.

Task 3: Cell Formatting and Styling 8. Format the total score cells (E3 to E8) as bold
and italic.
9. Apply conditional formatting to the "Total Score" column to highlight scores above a
certain threshold (e.g., scores above 150).
Task 4: Chart Creation 10. Highlight the data range from A2 to E8.

11. Insert a bar chart to visualize the total scores of each student.
Task 5: Data Sorting and Filtering 12. Sort the data based on the "Total Score" column
in descending order.
13. Apply a filter to the data and display only the students with scores above 150.
Output:

Results:
After completing the tasks, your Excel workbook should have the following features:
1. A well-organized table with student data.
2. Calculated total scores for each student.
3. Formatted cells for total scores.
4. Conditional formatting applied to highlight scores above a certain threshold.
5. A bar chart representing the total scores of each student.
6. Data sorted in descending order based on total scores.
7. Data filtered to display only students with scores above 150.
 Ex. No.: 2

Date: Numerical Operations and Data Import/Export in Excel

AIM:
The aim of this experiment is to perform various numerical operations (MAX, MIN, AVG,
SUM, SQRT, and ROUND) on user-provided input data in an Excel spreadsheet. And
demonstrate the process of importing and exporting data in different file formats using Excel.

ALGORITHM:
Numerical Operations:
1. Read user input data from the Excel spreadsheet.
2. Calculate and display the maximum (MAX), minimum (MIN), average (AVG),
and sum (SUM) of the data.
3. Calculate and display the square root (SQRT) and rounded values of the data.
Data Import/Export:
1. Import data from external file formats (e.g., CSV, TXT) into Excel.
2. Export data from Excel to different file formats.

PROCEDURE:
Part 1: Numerical Operations
Open Microsoft Excel.
1. Create a new spreadsheet or open an existing one.
2. Label cell A1 as "Data" and the adjacent cell B1 as "Results".
3. Enter the input data in column A, starting from cell A2.
4. In cell B2, use the following formulas to perform numerical operations:
i. MAX: =MAX(A2:A) (Replace "A" with the last row containing data)
ii. MIN: =MIN(A2:A)
iii. AVG: =AVERAGE(A2:A)
iv. SUM: =SUM(A2:A)
v. SQRT: =SQRT(A2)
5. ROUND: =ROUND(A2, 2) (Rounded to 2 decimal places)
6. Copy the formulas from cell B2 to the rest of the cells in column B, corresponding
to each data point in column A.
Part 2: Data Import/Export
Import Data:
 1. Click on the "Data" tab in the Excel ribbon.
2. Select "Get Data" or "From Text/CSV" (depending on the format).
3. Browse and select the external file.
4. Follow the import wizard to specify delimiters, data types, and destination.
5. Click "Load" to import the data into Excel.
6. Export Data:
7. Select the range of data you want to export.
8. Click on the "File" tab in the Excel ribbon.
9. Choose "Save As."
10. Select the desired file format (e.g., CSV, TXT, XLSX).
11. Specify the destination folder and file name.
12. Click "Save" to export the data.
Dataset:

Input:
sum(j2:j1001)
j1004/1000
max(j2:j1001)
min(j2:j1001)
round(j1004/1000)
Sqrt(R1004)

Output:

Result:
Thus the program to perform the numerical operations link (MAX, MIN, AVG, SUM, SQRT,
ROUND) for the data given is successfully executed and output verified.
 Ex. No.: 3
Perform statistical operations – Mean, Median, Mode and
Date: Standard deviation, Variance, Skewness, Kurtosis

AIM:
The aim of this experiment is to perform various statistical operations (Mean, Median, Mode,
Standard Deviation, Variance, Skewness, Kurtosis) on user-provided data in an Excel spreadsheet.
This procedure also includes the process of calculating and interpreting these statistical measures.

ALGORITHM:
1. Statistical Operations
- Read user input data from the Excel spreadsheet.

- Calculate and display the mean, median, mode, standard deviation, variance, skewness, and kurtosis
of the data.
PROCEDURE:

1. Open Microsoft Excel.

2. Create a new spreadsheet or open an existing one.
3. Label cell A1 as "Data" and the adjacent cell B1 as "Results".
4. Enter the input data in column A, starting from cell A2.
5. In cell B2, use the following formulas to perform statistical operations:
a. **Mean:** `=AVERAGE(A2:A)` (Replace "A" with the last row containing data)
b. **Median:** `=MEDIAN(A2:A)`
c. **Mode:** `=[Link](A2:A)`
d. **Standard Deviation:** `=STDEV.S(A2:A)`
e. **Variance:** `=VAR.S(A2:A)`
f. **Skewness:** `=SKEW(A2:A)`
g. **Kurtosis:** `=KURT(A2:A)`
6. Copy the formulas from cell B2 to the rest of the cells in column B, corresponding to
each data point in column A.
Dataset:
Output:

RESULT:
This experiment demonstrates the calculation and interpretation of various statistical measures
using Microsoft Excel. And the output is verified.
 Ex. No.: 4
Perform Z-test, T-test & ANOVA
Date:

AIM:
The aim of this experiment is to perform hypothesis testing using Z-Test, T-Test, and
Analysis of Variance (ANOVA) on user-provided data in an Excel spreadsheet. The
procedure demonstrates how to conduct these tests and interpret their results.

ALGORITHM:

1. Z-Test:
1. Read the user's input data and sample mean from the Excel spreadsheet.
2. Calculate the sample size and population standard deviation (if available).
3. Perform the Z-Test and determine whether to accept or reject the null hypothesis.

2. T-Test:
1. Read two sets of user input data from the Excel spreadsheet (for independent or
paired T-Test).
2. Perform the T-Test and interpret the results.

3. ANOVA:
4. Read multiple sets of user input data from the Excel spreadsheet.
5. Perform One-Way ANOVA and interpret the results.

PROCEDURE:
1. Open Microsoft Excel.
2. Create a new spreadsheet or open an existing one.
3. For the Z-Test:
a. Label cell A1 as "Data" and cell B1 as "Sample Mean."
b. Enter the input data in column A, starting from cell A2.
c. Enter the sample mean in cell B2.
d. Calculate sample size (n) and standard deviation (if needed).
e. Use the formula
`=(B2-AVERAGE(A2:A))/(STDEV.S(A2:A)/SQRT(COUNT(A2:A)))` in a
cell to calculate the Z-Test statistic.
 i. Interpret the Z-Test statistic using a significance level and Z-Table
or Z-Distribution.
4. For the T-Test:
1. Label cell D1 as "Data Set 1" and cell E1 as "Data Set 2."
2. Enter the data sets in columns D and E, starting from cells D2 and E2.
3. Use the formula `=[Link](D2:D, E2:E, 2, 2)` in a cell to perform an Independent T-
Test (replace parameters as needed).
4. Interpret the T-Test result based on the p-value and significance level.
5. For ANOVA:
1. Label cell G1 as "Group 1," H1 as "Group 2," and so on for additional groups.
2. Enter the data for each group in columns G, H, and so on.
3. Use the formula `=ANOVA(G2:G, H2:H, ...)` in a cell to perform One-Way ANOVA.
4. Interpret the ANOVA result based on the p-value and significance level.

Output:
RESULT:
This procedure showcases the utilization of Excel for hypothesis testing and statistical
analysis, specifically Z-Test, T-Test, and ANOVA, aiding in making informed decisions
based on data analysis.
 Ex. No.: 5
Perform Data Pre Processing Operations Like I) Handling Missing
Date: Data II) Normalization

Aim:
The aim of this lab exercise is to perform data preprocessing operations, specifically handling
missing data and normalization, using Microsoft Excel.

Requirements:

Microsoft Excel installed on your computer.

Dataset:
For this exercise, you can use any dataset you have or create a simple one in Excel to
demonstrate the concepts.

Procedure:

I) Handling Missing Data:

Step 1: Open the Dataset in Excel

Start by opening your dataset in Microsoft Excel.

Step 2: Identify Missing Data

Scan through the dataset to identify cells that contain missing or blank values. They are
often represented as empty cells or with placeholders like "NA" or "N/A."

Step 3: Choose a Handling Strategy

Decide on a strategy for handling missing data (e.g., removal, imputation). For this example,
let's assume we'll use imputation with the mean value for numerical data and the mode for
categorical data.

Step 4: Impute Missing Data

For numerical columns with missing data, calculate the mean of the column and fill in
missing values with this mean.
Example formula in Excel: =AVERAGE(range_of_numbers)
For categorical columns with missing data, fill in missing values with the mode (most
frequent value) of the column.
Example formula in Excel: =[Link](range_of_categories)

II) Normalization:
Step 1: Select the Numeric Data
Identify the columns that contain numeric data that you want to normalize.

Step 2: Min-Max Normalization

Perform Min-Max normalization to scale the numeric data between 0 and 1. This is
achieved by using the formula:
Mathematical
Normalized Value = (X - Xmin) / (Xmax - Xmin)
Where:
X is the original value.
Xmin is the minimum value in the column.
Xmax is the maximum value in the column.
Apply this formula to each cell in the numeric columns.

Step 3: Z-Score Normalization (Optional)

If needed, perform Z-score normalization to transform the data with a mean of 0 and
standard deviation of 1. Use the formula:
Mathematical
Z-Score = (X - Mean) / Standard Deviation
Where:
X is the original value.
Mean is the mean of the column.
Standard Deviation is the standard deviation of the column.
Output:

Results:
After completing the above procedures, save your Excel file with a new name to preserve
the original data. Your dataset should now be preprocessed with missing data handled and
numeric data normalized.
 Ex. No.: 6
Perform Dimensionality Reduction Operations Using PCA, KPCA,
Date: And SVD In Microsoft Excel

Aim:
The aim of this lab exercise is to perform dimensionality reduction operations using PCA,
KPCA, and SVD in Microsoft Excel to reduce the dimensionality of a dataset while preserving
essential information.

Requirements:
Microsoft Excel installed on your computer.

Dataset: For this exercise, you can use any dataset with high dimensionality or create a synthetic
dataset in Excel to demonstrate the concepts.
Procedure:
I) Principal Component Analysis (PCA):
Step 1: Open the Dataset in Excel
Start by opening your dataset in Microsoft Excel.
Step 2: Standardize the Data (Optional)
If your data is not already standardized, create new columns to standardize the data (subtract the
mean and divide by the standard deviation).
Step 3: Calculate the Covariance Matrix
Create a new sheet to calculate the covariance matrix of your standardized data.
Example formula in Excel:
=COVARIANCE.P(range_of_data1, range_of_data2)
Step 4: Calculate Eigenvalues and Eigenvectors
Calculate the eigenvalues and eigenvectors of the covariance matrix using Excel functions or
a statistical software.
Example: Use a software like R or Python for this step.
Step 5: Sort Eigenvalues and Select Principal Components
Sort the eigenvalues in descending order and select the top 'k' eigenvalues and their corresponding
eigenvectors (where 'k' is the desired reduced dimensionality).
Step 6: Create Transformation Matrix
Create a transformation matrix using the selected eigenvectors as columns.
Step 7: Project Data onto Reduced Dimension
Multiply your standardized data by the transformation matrix to obtain the reduced-dimensional data.
Example formula in Excel:
=MULT(range_of_standardized_data, transformation_matrix)
Step 8: Result
Display the reduced-dimensional data in a new sheet or as a separate column in your original dataset.
II) Kernel Principal Component Analysis (KPCA) and SVD:

Performing KPCA and SVD in Excel is significantly more complex and typically requires specialized
software or programming languages like Python or R. These methods involve kernel tricks and matrix
manipulations that are not readily available in Excel's standard functions. Therefore, it's
recommended to use Python or R for these techniques.

Web Link :
[Link]
[Link]

[Link]

Output:

Results:
After completing the PCA procedure in Excel, you should have a reduced-dimensional dataset
that retains essential information while reducing dimensionality.
 Ex. No.: 7
Perform Bivariate And Multivariate Analysis On A Dataset
Date: Using Microsoft Excel

Aim:
The aim of this lab exercise is to perform bivariate and multivariate analysis on a dataset
using Microsoft Excel to explore relationships between variables and gain insights into
data patterns.
Requirements:
Microsoft Excel installed on your computer.
Dataset: For this exercise, you can use any dataset of your choice. Ensure the dataset
contains multiple variables for analysis.
Procedure:
I) Bivariate Analysis:
Step 1: Open the Dataset in Excel
Start by opening your dataset in Microsoft Excel.
Step 2: Select Two Variables
Choose two variables from your dataset that you want to analyze for a bivariate relationship.
Step 3: Create Scatter Plot
Create a scatter plot to visualize the relationship between the two selected variables.
Select both variables, go to "Insert" > "Scatter" > "Scatter with Straight Lines."
Step 4: Analyze Scatter Plot
Interpret the scatter plot. Look for patterns, correlations, or trends between the two variables.
Calculate the correlation coefficient to quantify the relationship between the variables
(optional).
Example formula in Excel:
=CORREL(range_of_variable1, range_of_variable2)
Step 5: Result
Report your findings, including any observed relationships between the two variables.
II) Multivariate Analysis:
Step 1: Open the Dataset in Excel
Start by opening your dataset in Microsoft Excel.
Step 2: Select Multiple Variables
Choose multiple variables from your dataset that you want to analyze simultaneously.
Step 3: Create Pivot Table
Create a pivot table to summarize and analyze the selected variables.
Select your data, go to "Insert" > "PivotTable," and select the variables for rows,
columns, and values based on your analysis goals.
Step 4: Analyze Pivot Table
Use the pivot table to generate insights, calculate statistics (e.g., mean, median, mode),
and explore relationships among the selected variables.
Step 5: Create Charts
Create charts (e.g., bar charts, line charts) to visualize relationships or trends among the
selected variables.
Select the data, go to "Insert" > "Recommended Charts," and choose the appropriate
chart type based on your analysis.
Step 6: Result
Report your findings from the multivariate analysis, including any patterns, trends,
or correlations among the selected variables.

Output:
Results:
After completing the bivariate and multivariate analyses, document your findings and
insights in a report or presentation, summarizing the relationships and patterns observed in
the dataset.
 Ex. No.: 8
To Explore And Visualize A Dataset Using Various Plotting
Date: Functions In Microsoft Excel

Aim:
The aim of this lab exercise is to explore and visualize a dataset using various plotting functions
in Microsoft Excel to gain insights into the data's characteristics and patterns.
Requirements:
Microsoft Excel installed on your computer.

Dataset: For this exercise, you can use any dataset of your choice. Ensure the dataset contains
relevant variables for exploration and visualization.
Procedure:
I) Scatter Plots:
Step 1: Open the Dataset in Excel
Start by opening your dataset in Microsoft Excel.
Step 2: Select Variables

Choose two variables from your dataset that you want to visualize using a scatter
plot. Step 3: Create Scatter Plot

Create a scatter plot to visualize the relationship between the two selected variables.
Select both variables, go to "Insert" > "Scatter" > "Scatter with Straight Lines."
Step 4: Analyze Scatter Plot

Interpret the scatter plot. Look for patterns, correlations, or trends between the two variables.
Optionally, calculate the correlation coefficient to quantify the relationship between the
variables. Example formula in Excel:
=CORREL(range_of_variable1, range_of_variable2)
II) Line Charts:
Step: Create Line Chart

Create a line chart to visualize the trends in the selected variables over time or other
relevant dimensions.
III) Bar Charts:
Step: Create Bar Chart

Create a bar chart to visualize the distribution or comparison of data across

categories. Select the data, go to "Insert" > "Bar" > "Clustered Bar."
IV) Pie Charts:
Step: Create Pie Chart
Create a pie chart to visualize the distribution of data as proportions of a whole.
Select the data, go to "Insert" > "Pie" > "Pie."
V) Histograms:
Step: Create Histogram
Create a histogram to visualize the distribution of data values in the selected numeric variable.
Select the data, go to "Data" > "Data Analysis" > "Histogram."
VI) Scatter Plot Matrix (For Multivariate Data):
Step: Create Scatter Plot Matrix
Create a scatter plot matrix to visualize relationships between multiple numeric variables.
Select the data, go to "Insert" > "Scatter" > "Scatter Matrix."
Output:

Results:

After completing the above procedures, document your findings and insights from the various plots in
a report or presentation, highlighting any observed patterns, relationships, or anomalies in the dataset.
Visualizations can help in better understanding and communicating your data.
 Ex. No.: 9
Explore the features of Power BI Desktop
Date:

Aim:
The aim of this lab is to introduce users to the process of creating a basic bar chart in Power
BI Desktop. Participants will learn how to connect to a data source, load data, and create a
simple bar chart visualization.
Algorithm:
1. Connect to Data Source:
 Open Power BI Desktop.
 Click on "Get Data" to connect to a data source (e.g., Excel, CSV).
2. Load Data:
 Select the appropriate dataset.
 Transform and clean data if necessary using the Power Query Editor.
3. Create a Bar Chart:
 Navigate to the "Visualizations" pane.
 Select the "Clustered Bar Chart" from the available chart types.
 Drag and drop the desired fields into the Axis and Values areas.
Procedure:
1. Opening Power BI Desktop:
 Launch Power BI Desktop.
 Click on "Get Data" in the Home tab.
 Choose a data source (e.g., Excel) and follow the prompts to connect.
2. Loading Data:
 Select the appropriate sheet or table from the data source.
 Apply necessary transformations in the Power Query Editor (optional).
 Click "Close & Apply" to load the data into Power BI.
3. Creating a Bar Chart:
 In the "Visualizations" pane, click on the "Clustered Bar Chart" icon.
 Drag and drop a categorical field into the "Axis" well.
 Drag and drop a numerical field into the "Values" well.
Output:

Result:
Upon completing this lab, participants will have gained the following skills:
 Connecting to a data source in Power BI Desktop.
 Loading and transforming data using the Power Query Editor.
 Creating a basic bar chart visualization.
 Understanding the relationship between data fields and chart elements.
Ex. No.: 10
Preparing And Loading Data Into Power BI Desktop
Date:

Aim:

The aim of this section is to guide users through the process of

preparing and loading data into Power BI Desktop from an Excel
spreadsheet.
Algorithm:
1. Open Power BI Desktop:
 Launch Power BI Desktop on your computer.
2. Connect to Data Source:
 In the Home tab, click on "Get Data."
 Choose the appropriate data source (e.g., Excel).
3. Select Data Source File:
 Browse and select the Excel file containing the data.
4. Navigate Power Query Editor:

 After selecting the data source, the Power Query

Editor will open.

 Review the available data and structure in the

Power Query Editor.
5. Transform Data (Optional):

 Use the Power Query Editor tools to transform and

clean the data (e.g., remove duplicates, filter
columns, rename columns).
6. Close & Apply:

 Once satisfied with the data transformations, click on

"Close & Apply" in the Home tab.
Procedure:
1. Opening Power BI Desktop:
 Launch Power BI Desktop from your computer.
 2. Connecting to Data:
 In the Home tab, click on "Get Data."
 Choose the data source type (e.g., Excel) and locate the
file.
3. Navigating Power Query Editor:
 Review the data in the Power Query Editor.

 Familiarize yourself with the available tools and options

for data transformation.
4. Transforming Data (Optional):
 Apply necessary transformations using Power Query
Editor.

 Common transformations include removing

unnecessary columns, renaming columns, and
filtering data.
5. Closing & Applying Changes:

 Click on "Close & Apply" in the Home tab to save the

changes and load the data into Power BI.
Output:
Result:

By following this guide, users will gain hands-on experience in:

 Opening Power BI Desktop and connecting to a data source.
 Navigating the Power Query Editor for data inspection and
transformation.
 Loading the prepared data into Power BI for further analysis and
visualization.
 Ex. No.: 11
Developing A Data Model In Power Bi Desktop
Date:

Aim:
The aim of this section is to guide users through the process of developing a data model in
Power BI Desktop using multiple tables and establishing relationships.
Algorithm:
1. Import Data Tables:
 In Power BI Desktop, load the necessary data tables from your data
source (e.g., Excel, SQL Server).
2. Review Data Structure:
 Examine the structure of each table, including column names and data
types.
3. Identify Common Columns:
 Identify common columns between different tables that can be used to
establish relationships.
4. Create Relationships:
 Navigate to the "Model" view in Power BI Desktop.

 Drag and drop common columns from one table to another to create
relationships.
5. Define Cardinality and Cross-Filtering:
 Specify the relationship cardinality (one-to-one, one-to-many, many-
to-many).

 Configure cross-filtering options for bidirectional relationships if

necessary.
6. Review Relationships:
 Verify and review the relationships in the "Model" view to
ensure correctness.
Procedure:
1. Importing Data Tables:
 Load the necessary data tables using the "Get Data" option.
 Click on "Close & Apply" to load the tables into Power BI.
 2. Reviewing Data Structure:
 Examine the structure of each table in the "Fields" pane on the right
side.
3. Identifying Common Columns:
 Identify columns with similar data in different tables that can be used
to establish relationships.
4. Creating Relationships:
 Switch to the "Model" view by clicking on the "Model" icon on the left
side.
 Drag and drop common columns from one table to another to create
relationships.
5. Defining Cardinality and Cross-Filtering:
 Click on the relationship line to specify the cardinality (one-to-one,
one-to-many).
 Configure cross-filtering options based on your data model
requirements.
6. Reviewing Relationships:
 Review the relationships in the "Model" view to ensure they are
correctly established.

Output:
Result:
By following this guide, users will gain practical experience in:
 Importing data tables into Power BI Desktop.
 Identifying and establishing relationships between tables.
 Defining cardinality and cross-filtering options for relationships.
 Ex. No.: 12
Data Analysis Expressions (DAX) in Power BI Desktop
Date:

Aim:
The aim of this section is to guide users through the process of creating custom
calculations using Data Analysis Expressions (DAX) in Power BI Desktop.
Algorithm:
1. Understand DAX Basics:
 Familiarize yourself with basic DAX functions such as SUM, COUNT,
AVERAGE, etc.
2. Create Calculated Columns:
 In the "Data" view, select the table for which you want to create a
calculated column.
 Go to the "Modeling" tab and click on "New Column."
 Write the DAX expression to define the calculated column.
3. Write DAX Measures:
 In the "Data" view, select the table for which you want to create a
measure.
 Go to the "Modeling" tab and click on "New Measure."
 Write the DAX expression to define the measure.
4. Use DAX Functions:
 Utilize various DAX functions based on your requirements (e.g.,
RELATED, CALCULATE, IF, SWITCH).
5. Test and Validate DAX Formulas:
 Enter sample data or use existing data to validate the output of your
DAX formulas.
 Use the "Data" view to observe the results of calculated columns
and measures.
Procedure:
1. Understanding DAX Basics:
 Review basic DAX functions and their usage in the context of your
data.
 2. Creating Calculated Columns:
 Navigate to the "Data" view.
 Select a table, go to the "Modeling" tab, and click on "New Column."
 Write a DAX expression to create the calculated column.
3. Writing DAX Measures:
 Navigate to the "Data" view.
 Select a table, go to the "Modeling" tab, and click on "New Measure."
 Write a DAX expression to create the measure.
4. Using DAX Functions:
 Use DAX functions within your calculated columns and measures as
needed.
 Explore the available functions and their documentation.
5. Testing and Validating DAX Formulas:
 Enter or use existing data to validate the output of your DAX formulas.

 Check the "Data" view for the results of calculated columns and
measures.
Output:
Result:
By following this guide, users will gain practical experience in:
 Understanding basic DAX functions and syntax.
 Creating calculated columns and measures for custom calculations.
 Using DAX functions to perform advanced calculations in Power BI.
 Ex. No.: 13
Designing A Comprehensive Report In Power BI Desktop
Date:

Aim:
The aim of this section is to guide users through the process of designing a
comprehensive report in Power BI Desktop.
Algorithm:
1. Define Report Objectives:
 Clearly define the objectives of your report. Identify the key insights
and metrics you want to communicate.
2. Select Visualizations:
 Based on your objectives, choose appropriate visualizations (e.g., bar
charts, line charts, tables, maps) from the "Visualizations" pane.
3. Arrange Visualizations:
 Drag and drop visualizations onto the report canvas.
 Arrange them in a logical and visually pleasing manner.
4. Format Visualizations:
 Customize the appearance of each visualization using the formatting
options.
 Adjust colors, fonts, and other style elements.
5. Add Titles and Text Boxes:
 Include titles and text boxes to provide context and explanations for
your visualizations.
 Use the "Text box" tool in the "Home" tab.
6. Apply Filters:
 Implement filters to allow users to interact with the report dynamically.
 Use slicers or the "Filters" pane to control data visibility.
Procedure:
1. Defining Report Objectives:
 Clearly articulate the goals of your report. What insights do you want
to convey?
 2. Selecting Visualizations:
 From the "Visualizations" pane, choose the appropriate charts
and visuals based on your report objectives.
3. Arranging Visualizations:
 Drag and drop visualizations onto the report canvas.
 Organize them in a logical order to tell a coherent data story.
4. Formatting Visualizations:
 Select each visualization and use the formatting options to customize
its appearance.
 Ensure consistency in colors and styles across visuals.
5. Adding Titles and Text Boxes:
 Insert titles for each visualization and use text boxes to add context or
explanations.
 Provide a clear narrative for the user.
6. Applying Filters:
 Implement filters to enable interactivity. Use slicers or the "Filters"
pane to control data visibility dynamically.

Output:
Result:
By following this guide, users will gain practical experience in:
 Defining report objectives and choosing appropriate visualizations.
 Arranging and formatting visualizations for a cohesive and professional look.
 Adding titles, text boxes, and filters to enhance the user experience.
 Ex. No.: 14
Creating A Dashboard And Performing Data Analysis In Power
Date: BI Desktop

Aim:

The aim of this section is to guide users through the process of creating a
dashboard and performing data analysis in Power BI Desktop.
Algorithm:
1. Design Reports:
 Follow the previous instructions to design individual reports using
appropriate visualizations and formatting.
2. Create New Dashboard:
 In Power BI Desktop, click on the "New Page" button to create a
new blank page.
 Use this page as the dashboard canvas.
3. Add Visualizations to Dashboard:
 Drag and drop visualizations from existing reports onto the dashboard
canvas.
 Arrange them in a meaningful and visually appealing layout.
4. Implement Interactivity:
 Utilize slicers, filters, and other interactive elements to allow users to
explore the data dynamically.
 Link visuals to provide cross-filtering and drill-down capabilities.
5. Include Key Metrics:
 Add key metrics, KPIs, or cards to highlight important numbers on the
dashboard.
 Use the "Card" visualization or other appropriate visuals for this
purpose.
6. Apply Themes and Styles:
 Customize the overall theme and style of the dashboard using the
"View" tab.
 Ensure a consistent look and feel across all elements.
Procedure:
1. Designing Reports:

 Design individual reports with meaningful visualizations, titles, and

explanations.

2. Creating a New Dashboard:

 Click on the "New Page" button in Power BI Desktop to create a

new page, which will serve as your dashboard.

3. Adding Visualizations to Dashboard:

 Drag and drop visualizations from existing reports onto the dashboard
canvas.

 Adjust their size and placement for a well-organized layout.

4. Implementing Interactivity:

 Use slicers and filters to enable users to interact with the data on the
dashboard.

 Configure cross-filtering between visuals for a connected experience.

5. Including Key Metrics:

 Add key metrics or KPIs using cards or other relevant visuals.

 Ensure these metrics align with the overall goals of the analysis.

6. Applying Themes and Styles:

 Access the "View" tab to apply themes and styles to the entire
dashboard.

 Maintain a consistent theme for a polished appearance.

Output:

Result:
By following this guide, users will gain practical experience in:
 Designing individual reports with meaningful visualizations.
 Creating a dashboard to present an overview of key insights.

 Implementing interactivity for a dynamic user experience.

 Ex. No.: 15
Presentation of the Case Study
Date: ( Improving Sales Performance with Power BI )

Background: ABC Electronics is a global electronics retailer with operations in multiple

countries. The company has been experiencing a decline in sales performance over the
past year and wants to identify the underlying factors contributing to this decline. The
management believes that leveraging data analytics can provide valuable insights to
reverse this trend.
Objectives:
1. Identify key factors affecting sales performance.
2. Analyze regional and product-wise sales trends.
3. Discover patterns in customer behavior and preferences.
4. Provide actionable insights to improve sales performance.
Data Sources:
1. Sales Data: Contains information about sales transactions, including
date, product, quantity sold, and revenue.
2. Customer Data: Includes customer demographics, purchase history, and
customer feedback.
3. Inventory Data: Provides information about product availability and stock levels.
4. Marketing Data: Encompasses data related to marketing campaigns,
promotions, and advertising efforts.
Implementation:
1. Data Preparation:
 Combine and clean data from various sources for consistency.
 Handle missing or incomplete data points.
 Ensure data quality and integrity.
2. Data Modeling:
 Develop a star schema to organize and connect different data sets.
 Create relationships between tables, e.g., connect sales data to
customer data based on customer ID.
 Define calculated columns and measures to derive additional insights.
 3. Data Visualization:
 Build interactive dashboards with Power BI to present key
performance indicators (KPIs).
 Use charts, graphs, and tables to represent sales trends, customer
behavior, and inventory levels.
 Implement slicers and filters to enable dynamic exploration of data.
4. Analysis:
 Utilize Power BI's analytical capabilities to identify correlations
between sales performance and various factors.
 Explore regional variations, product performance, and customer segments.
 Perform sentiment analysis on customer feedback to understand
satisfaction levels.
5. Insights and Recommendations:
 Identify underperforming products or regions and suggest
targeted marketing strategies.
 Recommend inventory adjustments based on demand patterns.
 Propose customer engagement strategies based on preferences
and feedback.
6. Monitoring and Iteration:
 Establish a routine for monitoring dashboards and reports.
 Iterate on the analysis as new data becomes available.
 Incorporate feedback from stakeholders to improve the effectiveness of
the Power BI solution.

Results:
After implementing the Power BI solution, ABC Electronics gains valuable insights into
its sales performance. The company can make informed decisions to optimize
marketing strategies, manage inventory more effectively, and enhance customer
satisfaction. Over time, these improvements lead to a turnaround in sales performance,
demonstrating the power of data-driven decision-making with Power BI.
Sys2
Sys1