Academic Year 2025-2026
FYMSc-CS Sem II (Batch 2025-27)
Business Intelligence
Assignment Journal
Course Code: MSC50190
Student Details
Name PRN
Parthiv Talaviya S 1272250250
Submitted to Dr. P.S. Metkewar
Dr. Vishwanath Karad’s MIT World Peace University, Pune 411038
Part 1 – OLAP Tool Structure
1.1 What is OLAP?
OLAP stands for Online Analytical Processing. It is a computing approach that enables users to
analyse large amounts of data stored in a data warehouse from multiple perspectives
simultaneously. OLAP was first defined by Edgar F. Codd in 1993 as a method for analysing
business data quickly and interactively, without affecting the performance of operational
systems.
The main idea behind OLAP is to allow users – managers, analysts, and executives – to ask
complex business questions and get answers almost instantly, without writing any SQL queries.
This is made possible by pre-computing and storing data in a special multi-dimensional format
called an OLAP Cube.
1.2 OLAP vs OLTP
Feature OLTP (Transactional) OLAP (Analytical)
Purpose Day-to-day operations Data analysis and reporting
Query Type Simple, frequent, small Complex, infrequent, large
Data Volume Current and recent data Historical data across years
Response Time Milliseconds Seconds to minutes
Users Clerks, cashiers, staff Managers, analysts, executives
Example Entering a new jewellery order Finding best product in Q3 2024
Database Design Normalised (3NF) Denormalised (Star/Snowflake Schema)
Table 1 – Comparison of OLTP and OLAP Systems
1.3 The OLAP Cube
The central data structure of any OLAP tool is the multidimensional cube (also called a
hypercube). Unlike a regular 2D table with rows and columns, an OLAP cube has multiple axes
– one for each dimension of analysis. Each point inside the cube (called a "cell") holds a numeric
measure value such as Revenue or Profit.
For the jewellery manufacturing business, the OLAP cube is built across three main dimensions:
• Time Dimension: Year → Quarter → Month → Day
• Product Dimension: Jewellery Type → Metal Type → Gemstone → Design Style
• Location Dimension: Zone → City → Store
The measures stored at each cell of the cube are: Revenue (Rs.), Profit (Rs.), Quantity Sold, and
COGS.
Figure 1 – OLAP Cube with Time, Product, and Location Dimensions
1.4 Structure of an OLAP Tool
An OLAP tool is made up of the following main layers, working together from the source data
up to the end-user interface:
(a) Data Sources Layer
Raw data is collected from different operational systems such as the jewellery ERP software,
Point-of-Sale (POS) billing machines, inventory management systems, and supplier databases.
These are the original sources of all transaction records.
(b) ETL Layer – Extract, Transform, Load
The ETL process takes raw data from the source systems, cleans and transforms it (for example,
standardising date formats, removing duplicate customer records, calculating derived columns
like Profit = Revenue – COGS), and then loads it into the data warehouse. ETL runs at scheduled
intervals, usually daily or weekly.
(c) Data Warehouse Layer
This layer stores the cleaned and structured data in a Star Schema or Snowflake Schema (as
designed in Assignment 1). For the jewellery business, this layer contains the FACT_SALES
table and dimension tables like DIM_DATE, DIM_PRODUCT, DIM_CUSTOMER,
DIM_STORE, and DIM_SUPPLIER.
(d) OLAP Server / Cube Layer
The OLAP server reads data from the warehouse and builds multidimensional cubes. Pre-
aggregated totals (like quarterly revenue or city-wise sales) are pre-calculated and stored for fast
retrieval. There are three common types of OLAP servers:
• MOLAP (Multidimensional OLAP) – Stores data in multidimensional arrays; fastest
performance. Example: Microsoft SSAS, IBM TM1.
• ROLAP (Relational OLAP) – Keeps data in relational tables and generates SQL queries
dynamically; more scalable for large datasets. Example: MicroStrategy.
• HOLAP (Hybrid OLAP) – Combination of both; summary data in multidimensional
format, detail data in relational tables.
(e) Client / Front-End Layer
This is the interface through which business users access and interact with the OLAP cube. It
includes BI dashboards, pivot tables in Microsoft Excel, and report designer tools. Users can
perform operations like Slice, Dice, Drill-Down, and Roll-Up from this layer without writing any
code or SQL.
Component Description Example (Jewellery Business)
Dimension Categorical axis of the cube providing Product, Time, Location
context
Hierarchy Levels within a dimension from general to Year → Quarter → Month → Day
specific
Measure / Fact Numeric value being analysed, stored in cells Revenue, Profit, Qty_Sold
Cell Intersection of all dimension values; holds Revenue for Gold Ring in Mumbai, Q1 2024
one measure
Slice Fix one dimension to get a 2D section of the All sales data for Year = 2024
cube
Dice Sub-cube by filtering two or more Gold + Ring + Q1 + Mumbai
dimensions
Roll-Up Aggregate from lower to higher level in Month revenue → Quarter total
hierarchy
Drill-Down Navigate from summary down to detail level Annual revenue → Monthly breakdown
Pivot Rotate the cube to view from a different Switch rows and columns in cross-tab
dimension
Table 2 – Key Terms and Components of OLAP Cube Structure
Part 2 – OLAP Operations with Examples
OLAP provides four standard analytical operations that allow users to view data from different
angles and at different levels of detail. All four operations are explained below with worked
examples from the jewellery sales dataset.
Figure 2 – All Four OLAP Operations on Jewellery Sales Data
2.1 Slice Operation
Definition: The Slice operation selects a single specific value for one dimension and extracts a
flat 2D section (slice) from the OLAP cube. All other dimensions remain free. By fixing one
dimension, we effectively remove it from the cube and get a simpler two-dimensional view.
Analogy: Think of the OLAP cube like a loaf of bread. Slicing means cutting one flat piece from
the loaf. You get a 2D cross-section because you have fixed one axis (e.g. fixing the year to
2024).
Example – Slice on Year = 2024
We fix the Time dimension to the year 2024. This extracts all sales data for 2024 across all
products and all cities:
Product Metal Mumbai Pune (Rs.L) Nagpur Total (Rs.L)
Type (Rs.L) (Rs.L)
Gold Ring Gold 9.60 5.60 3.10 18.30
Necklace Gold 8.50 9.20 — 17.70
Silver Necklace Silver 3.60 — — 3.60
Platinum Bangle Platinum — — 6.30 6.30
Gold Earring Gold — 2.10 — 2.10
Silver Earring Silver 1.90 — 1.90 3.80
TOTAL — 23.60 16.90 11.30 51.80
Table 3 – Slice: Year = 2024 | All Products x All Cities
Result: The slice gives us a 2D table showing revenue for every product-city combination in
2024 only. This is useful when a manager wants to compare product performance across all cities
for a specific year without being distracted by data from other years.
2.2 Dice Operation
Definition: The Dice operation applies conditions (filters) on two or more dimensions
simultaneously to extract a smaller sub-cube from the main OLAP cube. Unlike Slice which
fixes one dimension to a single value, Dice can select a range or multiple values across multiple
dimensions at the same time.
Analogy: If Slice is cutting one flat piece from a loaf of bread, Dice is cutting a smaller
rectangular block from inside the loaf. You are narrowing down the data across multiple axes at
once.
Example – Dice: Metal = Gold AND City IN (Mumbai, Pune) AND Quarter IN (Q1, Q2)
Product Quarter Mumbai (Rs.L) Pune (Rs.L)
Gold Ring Q1 4.20 2.80
Gold Ring Q2 — —
Diamond Necklace Q2 8.50 —
Gold Earring Q2 — 2.10
SUB-TOTAL — 12.70 4.90
Table 4 – Dice: Gold Products | Mumbai and Pune | Q1 and Q2 of 2024
Result: The Dice operation produces a sub-cube answering the question: "What was the revenue
from Gold jewellery sold in Mumbai and Pune during Q1 and Q2?" This is very useful in
regional sales review meetings where managers need focused insights rather than the full dataset.
2.3 Roll-Up Operation (also called Drill-Up)
Definition: Roll-Up aggregates data by moving up the dimension hierarchy – from a more
detailed level to a higher, more summarised level. For example, going from Month-level data to
Quarter-level totals, or from City-level to Zone-level. Aggregation functions like SUM or
AVERAGE are applied when combining data.
Analogy: Imagine you are looking at daily sales entries and you zoom out to see weekly totals,
then monthly totals, then quarterly totals. That process of zooming out is Roll-Up.
Example – Roll-Up: Month → Quarter → Year (Gold Ring Sales, Mumbai Store)
Level Time Period Revenue Qty Sold Operation Applied
(Rs.L)
Month January 2024 1.40 28 Base level – individual records
Month February 2024 1.50 30 Base level – individual records
Month March 2024 1.30 27 Base level – individual records
Quarter Q1 2024 4.20 85 SUM of January + February + March
Quarter Q3 2024 5.40 110 SUM of July + August + September
Year 2024 (Annual) 9.60 195 SUM of all four quarters
Table 5 – Roll-Up: Month to Quarter to Annual Revenue for Gold Ring in Mumbai
Result: Roll-Up aggregates the monthly data into quarterly and annual totals. A senior manager
reviewing yearly performance would use Roll-Up to see the bigger picture. In the jewellery
business this is especially useful for comparing festive seasons (Q3, Q4) against off-peak periods
(Q1, Q2) at the annual level.
2.4 Drill-Down Operation
Definition: Drill-Down is the exact opposite of Roll-Up. It navigates downward through the
dimension hierarchy – from a high-level summary to progressively more detailed, granular data.
Each step reveals the underlying detail behind a summarised figure.
Analogy: Drill-Down is like clicking on an annual sales total in a spreadsheet to reveal the
monthly breakdown behind that number. You are drilling into the summary to understand what it
is made of.
Example – Drill-Down: Year → Quarter → Month → Product → Store (All Sales, 2024)
Level Breakdown Revenue Qty Sold Detail Added at This Level
(Rs.L)
Year 2024 Total 51.80 719 Starting point – full year
summary
Quarter Q3 2024 16.80 240 Time dimension broken down by
quarter
Month September 2024 5.40 82 Quarter broken down by
individual month
Product Gold Ring – September 2024 2.10 42 Product category within that
month
Store Mumbai Store-A – September 1.30 25 Specific store within the product
Table 6 – Drill-Down: Annual to Quarterly to Monthly to Product to Store Level
Result: The Drill-Down operation lets an analyst begin with the full annual revenue of Rs.
51.80L and progressively dig deeper to discover that Mumbai Store-A alone contributed Rs.
1.30L from Gold Ring sales in September. This is extremely useful when management wants to
understand why a particular quarter outperformed or underperformed the target.
Part 3 – Dataset for OLAP Analysis
3.1 Dataset Description
The dataset below is used as the basis for all OLAP operations demonstrated in this assignment.
It represents a sample of the jewellery manufacturing and retail business sales data covering the
years 2023 and 2024, across multiple product types, metal types, store cities, and quarters.
In a real OLAP implementation, this data would be cleaned through an ETL pipeline, loaded into
a data warehouse (using the Star or Snowflake Schema from Assignment 1), and then pre-
aggregated into a multidimensional cube that can be accessed through a BI tool like Microsoft
Power BI, Tableau, or Apache Kylin.
Product Metal Jewellery Store Quarter Year Revenue Qty Profit
Type Type City (Rs.L) Sold (Rs.L)
Gold Gold Ring Mumba Q1 2024 4.20 85 1.10
Ring i
Gold Gold Ring Pune Q1 2024 2.80 56 0.72
Ring
Silver Silver Necklace Mumba Q1 2024 3.60 72 0.80
Necklace i
Diamond Gold Necklace Mumba Q2 2024 8.50 40 2.80
Necklace i
Platinum Platinum Bangle Nagpur Q2 2024 6.30 25 2.10
Bangle
Gold Gold Earring Pune Q2 2024 2.10 92 0.55
Earring
Gold Gold Ring Mumba Q3 2024 5.40 110 1.40
Ring i
Silver Silver Earring Nagpur Q3 2024 1.90 130 0.48
Earring
Diamond Gold Necklace Pune Q4 2024 9.20 45 3.10
Necklace
Gold Gold Bangle Mumba Q4 2024 4.80 60 1.25
Bangle i
Gold Gold Ring Nagpur Q1 2023 3.10 62 0.80
Ring
Silver Silver Necklace Pune Q2 2023 2.70 54 0.60
Necklace
Table 7 – OLAP Dataset: Jewellery Sales (2023 – 2024)
3.2 Dimension Hierarchies Used in OLAP Operations
Dimension Hierarchy Levels Example Values
Time Year → Quarter → Month → Day 2024 → Q3 → September → 15-Sep
Product Jewellery Type → Metal Type → Gemstone Ring → Gold → Diamond
Location Zone → City → Store → Counter West → Mumbai → Store-A → Gold Counter
Customer Loyalty Tier → Gender → Age Group Gold Tier → Female → 25-35
Table 8 – Dimension Hierarchies for the Jewellery OLAP Cube
3.3 Measures (Facts) in the Dataset
• Revenue (Rs.) – Total sales amount generated per transaction
• Profit (Rs.) – Net profit = Revenue minus Cost of Goods Sold (COGS)
• Qty_Sold – Number of jewellery pieces sold
• COGS (Rs.) – Raw material cost + manufacturing cost + overhead expenses
• Discount (%) – Percentage discount offered at the time of sale
Conclusion
OLAP tools are an essential part of any Business Intelligence system. They allow users to look at
business data from different perspectives interactively, without writing database queries. The
four main OLAP operations – Slice, Dice, Roll-Up, and Drill-Down – together give a complete
capability to analyse data at any level of detail and from any angle.
For the jewellery manufacturing business, these OLAP operations are especially valuable during
peak selling periods like Diwali, Dhanteras, and the wedding season. Management can quickly
identify which product categories are performing best, in which cities, during which months, and
at which stores. This information directly supports decisions about stock planning, marketing
budgets, and store expansion.
The combination of a well-designed data warehouse schema (as shown in Assignment 1) with a
properly built OLAP cube (as discussed in this assignment) forms the complete foundation of a
Business Intelligence system that supports data-driven decision making across the organisation.