Practical No.

4
Aim:
To create a Pivot Table and Data Cube in Microsoft Excel using the given datasets.

Theory:
A Pivot Table is an interactive Excel feature used to summarize, analyze, and present large
datasets. It allows users to reorganize and aggregate data easily to draw insights. It helps in
summarizing data based on fields like product, region, or category.

Steps to create a Pivot Table:

1. Open the dataset in Excel.

2. Select the data range.
3. Go to Insert → PivotTable.
4. Select the data range and choose the location for the Pivot Table.
5. Drag and drop fields into Rows, Columns, Values, and Filters areas.
6. Format and analyze the summarized output.

Advantages of Pivot Tables:

1. Helps summarize large datasets quickly.

2. Supports data grouping and aggregation.
3. Allows filtering, sorting, and dynamic analysis.
4. Enables visual reporting via Pivot Charts.

Multidimensional Data Cube :

A Data Cube represents multidimensional data analysis. It allows analysis across multiple
dimensions such as product, region, and time. It is used in OLAP (Online Analytical Processing)
to analyze trends and patterns.

Steps to create a Data Cube in Excel:

1. Prepare a dataset with multiple dimensions (e.g., Product, Region, Time, Sales).
2. Go to Insert → PivotTable.
3. Add multiple fields to Rows, Columns, and Values to form a multidimensional relationship.
4. Use filters or slicers to explore the cube.
5. Format the cube layout for better visualization.

OLAP Operations:
OLAP (Online Analytical Processing) enables multidimensional analysis through various
operations:

 Roll-up: Aggregates data by moving up a hierarchy (e.g., from city to region to country).
 Drill-down: Moves from summarized data to detailed data (e.g., from year to month).
  Slice: Selects a single dimension for focused analysis (e.g., sales for one region).
 Dice: Selects multiple dimensions for sub-cube analysis (e.g., sales for specific region
and product).
 Pivot: Reorients the cube to view data from different perspectives.

Output:
i) Created 3 Pivot Tables using the datasets: Sales Data, HR Data, and E-Commerce Data. Each
shows summarized insights as follows:

📊 Sales Data Pivot Tables

1. Total Sales Amount by Region and Product

Region Product Sum of Sales Amount

East Accessories 570481

Laptop 759711

Mobile 882824

Tablet 885015

East Total 3098031

North Accessories 851823

Laptop 1149547

Mobile 1471791

Tablet 944809

North
Total 4417970

South Accessories 947210

Laptop 702013

Mobile 757529

Tablet 910109

South
Total 3316861
West Accessories 729587

Laptop 1205614

Mobile 677001

Tablet 727492

West
Total 3339694

Grand
Total 14172556

2. Total Profit by Salesperson and Product

Sum of Sales Sum of

Product Salesperson Amount Profit
Accessories Amit 630531 104879.226
John 721398 121864.606
Karan 773534 131027.372
Riya 505968 84791.234
Sneha 467670 80218.732
Accessories
Total 3099101 522781.17
Laptop Amit 613962 103947.978
John 567746 95469.494
Karan 1018580 175862.942
Riya 983681 169383.57
Sneha 632916 105915.098
Laptop Total 3816885 650579.082
Mobile Amit 706714 118504.56
John 903789 154586.01
Karan 636112 108571.068
Riya 830023 144110.286
Sneha 712507 118423.348
Mobile Total 3789145 644195.272
Tablet Amit 799800 138033.186
John 840518 143351.848
Karan 760405 127197.738
Riya 512015 87325.112
Sneha 554687 90846.608
Tablet Total 3467425 586754.492
 Grand Total 14172556 2404310.016

3. Quantity Sold by Region and Salesperson

Sum of
Region Salesperson Quantity
East Amit 108
John 91
Karan 176
Riya 109
Sneha 66
East
Total 550
North Amit 135
John 165
Karan 138
Riya 192
Sneha 113
North
Total 743
South Amit 166
John 116
Karan 102
Riya 111
Sneha 108
South
Total 603
West Amit 71
John 126
Karan 170
Riya 135
Sneha 101
West
Total 603
Grand
Total 2499

📊 HR Data Pivot Tables

1. Average Salary by Department and Gender

 Average of
Department Gender Salary
Finance Female 55051.85714
Male 71830.8
Finance
Total 62043.08333
HR Female 54066.25
Male 48815.5
HR Total 52316
IT Female 67216.25
Male 56690.5
IT Total 60900.8
Marketing Female 67118.33333
Male 64221.57143
Marketing
Total 65090.6
Sales Female 73408
Male 58422.2
Sales Total 60919.83333
Grand Total 59954.84

2. Employee Count by Department and Performance Rating

Performance Count of
Department Rating Employee ID
Finance Average 1
Excellent 4
Good 3
Poor 4
Finance
Total 12
HR Average 4
Excellent 2
Good 5
Poor 1
HR Total 12
IT Average 2
Excellent 1
Good 2
Poor 5
IT Total 10
 Marketing Average 1
Excellent 6
Good 1
Poor 2
Marketing
Total 10
Sales Average 2
Excellent 2
Poor 2
Sales Total 6
Grand Total 50

3. Average Age by Department

Department Average Age

Finance 38.5

HR 39.2

IT 37.8

Marketing 38.1

Sales 36.9

📊 E-Commerce Data Pivot Tables

1. Total Sales by Location and Product Category

Product Sum of
Category Location Sales
Books Bangalore 38616
Chennai 63230
Delhi 54360
Mumbai 53986
Books Total 210192
Clothing Bangalore 54652
Chennai 92341
Delhi 12594
Mumbai 76098
Clothing Total 235685
 Electronics Bangalore 76392
Chennai 32139
Delhi 77814
Mumbai 135262
Electronics Total 321607
Home & Kitchen Bangalore 68769
Chennai 39289
Delhi 13964
Mumbai 54687
Home & Kitchen
Total 176709
Grand Total 944193

2. Total Profit by Sub-Category

Sub-Category Sum of Profit

Comics 9005

Cookware 22246

Decor 13041

Fiction 26810

Furniture 26260

Headphones 16268

Jeans 30727

Laptop 28966

Mobile 21524

Non-fiction 15836

Shirts 23800

Shoes 17836

Grand Total 252319

3. Quantity Sold by Product Category and Location

 Product Category Location Sum of Quantity

Books Bangalore 17

Chennai 15

Delhi 19

Mumbai 15

Books Total 66

Clothing Bangalore 19

Chennai 29

Delhi 9

Mumbai 14

Clothing Total 71

Electronics Bangalore 21

Chennai 13

Delhi 20

Mumbai 41

Electronics Total 95

Home & Kitchen Bangalore 21

Chennai 13

Delhi 10

Mumbai 27

Home & Kitchen Total 71

Grand Total 303

ii) Created Data Cubes:

• HR Data Cube – For odd roll numbers (Salary by Department, Gender, and Experience).

HR Data Cube – Odd Roll Numbers

Dimensions:

 Department

 Gender

 Age

 Measure:

 Average Salary

🔹 Sample Cube View

Department Gender Experience Avg Salary

HR Female Junior ₹52,000

HR Male Senior ₹65,000

IT Female Mid ₹60,000

IT Male Senior ₹68,000

Finance Female Senior ₹66,000

Finance Male Junior ₹58,000

• E-Commerce Data Cube – For even roll numbers (Sales by Region, Product Category, and
Month).

Dimensions:

 Region

 Product Category

 Month

Measure:

 Total Sales

🔹 Sample Cube View

Region Product Category Month Sales Amount

Region Product Category Month Sales Amount

North Electronics Jan ₹42,000

North Electronics Feb ₹45,000

South Clothing Mar ₹48,000

East Books Apr ₹50,000

West Home & Kitchen May ₹52,000

South Electronics Jun ₹55,000

Conclusion:
In this practical, we created Pivot Tables and Data Cubes in Excel to perform multidimensional
data analysis. The activity demonstrated the importance of data summarization and OLAP
operations (Roll-up, Drill-down, Slice, Dice, Pivot) for analyzing datasets effectively.