Data Warehousing and Online Analytical Processing

• OLTP Task: A customer buys a laptop. The database updates the stock count from 10 to 9 and records the payment. (High frequency, row-level access)

• OLAP Task: A manager asks, "What were the sales trends for laptops in South India over the last 3 summers compared to the rainy season?" (Low frequency, massive data aggregation)

1. Step 1 (OLTP): You withdraw money from an ATM. This is a single, fast transaction that must be consistent (ACID).

2. Step 2 (Data Flow): At the end of the day, your transaction data is moved to a central Warehouse.

3. Step 3 (OLAP): The bank analyzes the "Withdrawal Patterns" of all customers in your city to decide if they need to install more ATMs in that area.

Sources:

• Store A database: Sales in USD, date format MM/DD/YYYY

• Store B database: Sales in INR, date format DD-MM-YYYY

• Online portal: JSON logs with Unix timestamps

Transform steps:

• Convert all currencies to INR using exchange rates

• Standardize all dates to YYYY-MM-DD format

• Parse Unix timestamps to readable date-time

• Remove duplicate transaction IDs

• Fill missing product categories using product lookup table

Load: Nightly batch job inserts transformed records into the central warehouse.

Tier 1 — Data Sources and Warehouse Server:

• Sources: Core banking system (Oracle DB), ATM logs (CSV), Mobile app (JSON API), Credit card system (MySQL)

• ETL: Nightly batch — extract transactions, standardize currency, clean nulls, load into central warehouse

• Warehouse: Stores 10 years of transaction history (Star Schema with Time, Branch, Product, Customer dimensions)

Tier 2 — OLAP Server:

• MOLAP cube pre-aggregated for: monthly deposits by branch, loan defaults by region, product-wise revenue

• Supports roll-up (branch $\rightarrow$ zone $\rightarrow$ region), drill-down, slice (product = home loan)

Tier 3 — Client Tools:

• Executives: Power BI dashboard showing real-time KPIs

• Risk analysts: Python-based data mining for default prediction

• Compliance: Crystal Reports for audit reports (fixed format)

• Branch managers: Ad-hoc OLAP queries for local performance analysis

Dimensions and their domains:

• Total cells $= 4 \times 3 \times 3 = 36$

• Measure: Sales (lakhs) — one value per cell

• If only 20 of 36 combinations have actual sales data $\rightarrow$ cube is sparse (44% empty)

Q: A 3-dimensional data cube has Time(12), Location($x$), and Product(50) as dimensions. If the total number of cells is 36{,}000, what is the domain size of the Location dimension?

\[ \text{Total cells} = 12 \times x \times 50 = 36{,}000 \] \[ 600x = 36{,}000 \implies x = 60 \] Answer: The Location dimension has $\mathbf{60}$ distinct values (e.g., 60 cities).

Q: A 4-dimensional cube has equal domain size $d$ for all dimensions. If the total number of cells is 10{,}000, what is $d$? \[ d^4 = 10{,}000 \implies d = \sqrt[4]{10{,}000} = 10 \] Answer: Each dimension has $\mathbf{10}$ distinct values.

Each cuboid corresponds to a SQL GROUP BY query on a different subset of dimensions:

Q: A data cube has 5 dimensions: Time, Location, Product, Customer, Channel.
How many cuboids does the full data cube contain?
\[ \text{Number of cuboids} = 2^5 = 32 \]

• 1 apex cuboid (0-D)

• $\binom{5}{1} = 5$ one-dimensional cuboids

• $\binom{5}{2} = 10$ two-dimensional cuboids

• $\binom{5}{3} = 10$ three-dimensional cuboids

• $\binom{5}{4} = 5$ four-dimensional cuboids

• $\binom{5}{5} = 1$ base cuboid (5-D)

• Total: $1+5+10+10+5+1 = 32$    cuboids

Given a Time dimension with the hierarchy: Day(365) $\to$ Month(12) $\to$ Quarter(4) $\to$ Year(1), and a Location dimension: City(50) $\to$ State(10) $\to$ Country(3) $\to$ ALL, combined with a Product dimension with a flat domain of 200 items, calculate the number of cells for each combination of hierarchy levels:

A cube has 6 dimensions. How many 3-D cuboids exist? What is the total cuboid count? \[ \binom{6}{3} = \frac{6!}{3!\,3!} = 20 \quad \text{three-dimensional cuboids} \] \[ \text{Total cuboids} = 2^6 = 64 \]

A cube has $n$ dimensions. The number of 2-D cuboids equals 21. Find $n$. \[ \binom{n}{2} = \frac{n(n-1)}{2} = 21 \implies n(n-1) = 42 \implies n = 7 \]

How many cuboids does a 10-dimensional cube have? At which level is the number of cuboids maximised? \[ 2^{10} = 1024 \quad \text{total cuboids} \] The maximum occurs at $k = \lfloor n/2 \rfloor = 5$: \[ \binom{10}{5} = 252 \quad \text{(largest single-level count)} \]

A data cube has Time(T), Product(P), Store(S), Promotion(Pr), and Customer(C) as dimensions ($n=5$). An analyst pre-materialises all cuboids involving at least Product and at most 3 dimensions total. How many cuboids satisfy this condition?

• The only 1-D cuboid containing $P$ is $\{P\}=1$ .

• Cuboids of size exactly 2 containing $P$: choose 1 more from $\{T,S,Pr,C\}$ $= \binom{4}{1} = 4$

• Cuboids of size exactly 3 containing $P$: choose 2 more from $\{T,S,Pr,C\}$ $= \binom{4}{2} = 6$

• Total: $1 + 4 + 6 = \mathbf{11}$ cuboids

For an $n$-dimensional cube, prove that the number of cuboids containing a specific dimension $D_j$ is $2^{n-1}$.

• Fix $D_j$ as present. The remaining $n-1$ dimensions can each independently be included or excluded.

• This gives $2^{n-1}$ distinct combinations $\Rightarrow$ $2^{n-1}$ cuboids contain $D_j$.

• Verification ($n=3$): Each dimension appears in $2^2 = 4$ cuboids. For $T$: $(T)$, $(T,L)$, $(T,P)$, $(T,L,P)$ — indeed 4. ✓

A 4-dimensional cube has dimensions:
Time (12 months), Location (50 cities), Product (200 items), Customer (1000 segments)
Total possible cells: \[ 12 \times 50 \times 200 \times 1000 = 1{,}200{,}000{,}000 \quad \text{(1.2 billion)} \] Actual non-empty cells: 3,000,000 (3 million)
\[ \text{Density} = \frac{3{,}000{,}000}{1{,}200{,}000{,}000} \times 100\% = 0.25\% \] The cube is 99.75% sparse — ROLAP is preferred; MOLAP would waste enormous memory.

Sales cube with 36 cells (4 quarters $\times$ 3 locations $\times$ 3 products).
min_sup = 100 lakhs (store only cells where Sales $\geq$ 100).

Only cells meeting the threshold are materialized — the rest are pruned.

A 3-D cube has Time(4), Location(3), Product(3) with 22 non-empty cells. \[ \text{Total cells} = 4 \times 3 \times 3 = 36, \qquad \text{Density} = \frac{22}{36} \times 100\% \approx 61.1\% \] Moderately dense; either storage approach is viable.

A 4-D retail cube has Month(12), Store(500), SKU(10{,}000), Channel(5) with 2{,}000{,}000 actual sales records. \[ \text{Total cells} = 12 \times 500 \times 10{,}000 \times 5 = 300{,}000{,}000 \] \[ \text{Density} = \frac{2{,}000{,}000}{300{,}000{,}000} \times 100\% \approx 0.67\% \] Extremely sparse (99.33% empty) — ROLAP is strongly preferred.

A cube has density 2% and $5 \times 10^8$ total cells. How many cells are non-empty? \[ N = 0.02 \times 5 \times 10^8 = 10{,}000{,}000 \quad \text{(10 million non-empty cells)} \]

(MOLAP vs.\ ROLAP storage): For Problem 2 above, with $b = 8$ bytes/cell in MOLAP, and in ROLAP each record has 4 dimension keys (4 bytes each) + 1 measure (8 bytes): \[ \text{MOLAP} = 300{,}000{,}000 \times 8 = 2{,}400{,}000{,}000 \text{ bytes} \approx 2.4 \text{ GB} \] \[ \text{ROLAP} = 2{,}000{,}000 \times (4 \times 4 + 8) = 2{,}000{,}000 \times 24 = 48{,}000{,}000 \text{ bytes} \approx 48 \text{ MB} \] \[ \text{Space saving} = \frac{2400 - 48}{2400} \times 100\% = 98\% \]

(Find density from storage constraint): A 3-D cube has 1 billion total cells. MOLAP requires 8 GB (8 bytes/cell). At what minimum density does ROLAP require more storage than MOLAP? (ROLAP record size = 20 bytes: 3 $\times$ 4-byte keys + 8-byte measure.) \[ N \times 20 = 10^9 \times 8 \implies N = \frac{8 \times 10^9}{20} = 4 \times 10^8 \] \[ \text{Break-even density} = \frac{4 \times 10^8}{10^9} \times 100\% = 40\% \] At density $> 40\%$, MOLAP is more storage-efficient; below 40%, ROLAP wins.

Consider a 2-D cube with dimensions: Location $\in \{North, South\}$ and Customer $\in \{Retail, Wholesale\}$. The base cuboid (2-D) has the following COUNT values:

Checking closedness of (North) in the 1-D (Location) cuboid:
The cell $(North)$ has count = 15. Its refinements in the 2-D cuboid are: $(North, Retail) = 15$ and $(North, Wholesale) = 0$.
Since the refinement $(North, Retail)$ has the same count (15) as its parent $(North)$, the cell $(North)$ is not closed — adding the Customer dimension does not change the count.

Checking closedness of (Wholesale) in the 1-D (Customer) cuboid:
The cell $(Wholesale)$ has count = 25. Its refinements in the 2-D cuboid are: $(North, Wholesale) = 0$ and $(South, Wholesale) = 25$.
Since the refinement $(South, Wholesale)$ has the same count (25) as its parent $(Wholesale)$, the cell $(Wholesale)$ is not closed.

Case of a closed cell:
Checking $(South)$ in the 1-D (Location) cuboid: $(South)$ has count = 35. Its refinements are $(South, Retail) = 10$ and $(South, Wholesale) = 25$. Since neither refinement equals 35, the cell $(South)$ is closed.

Summary: Non-closed cells (like North and Wholesale in this table) are redundant. In a Closed Cube representation, we prune these and only store cells where every refinement results in a different count value, effectively compressing the data without losing information.

Consider a small 2-D cube: Time $\in \{Q1, Q2\}$, Product $\in \{Electronics, Clothing\}$. The base cuboid (2-D) has the following COUNT values:

Checking closedness of (Q1) in the 1-D (Time) cuboid:
$(Q1)$ has count = 10. Its refinements in the 2-D cuboid are: $(Q1, \text{Electronics}) = 5$ and $(Q1, \text{Clothing}) = 5$.
Since $5 \neq 10$, the cell $(Q1)$ is closed — adding the Product dimension changes the count.

Checking closedness of (Electronics) in the 1-D (Product) cuboid:
$(Electronics)$ has count = 13. Its refinements: $(Q1, Electronics) = 5$, $(Q2, Electronics) = 8$.
Since $5 \neq 13$ and $8 \neq 13$, $(Electronics)$ is closed.

Hypothetical non-closed case:
If all sales of Clothing occurred only in Q2 and there were no other data, then: $(Q2, Clothing) = $ all Clothing sales, and the (Clothing) cuboid cell would equal $(Q2, Clothing)$ — making (Clothing) not closed (redundant with its 2-D refinement).

Summary: In the example above, all cells happen to be closed. Non-closed cells arise when the same count value is shared by a cell and one of its direct 1-step refinements — signalling that the refinement adds no information.

Consider partitioned sales data split by region:
Region A: {10, 20, 30}    Region B: {40, 50, 60}
SUM (Distributive): \[ \text{SUM(A)} = 60, \quad \text{SUM(B)} = 150, \quad \text{SUM(All)} = 60 + 150 = 210 \quad \text{(computable from sub-aggregates)} \] AVG (Algebraic): \[ \text{AVG(A)} = 20, \quad \text{AVG(B)} = 50 \] \[ \text{AVG(All)} \neq \frac{20+50}{2} = 35 \quad \text{(wrong!)} \] \[ \text{AVG(All)} = \frac{\text{SUM(All)}}{\text{COUNT(All)}} = \frac{210}{6} = 35 \quad \text{(correct — needs both SUM and COUNT)} \] MEDIAN (Holistic): \[ \text{MEDIAN(A)} = 20, \quad \text{MEDIAN(B)} = 50 \] \[ \text{MEDIAN(All)} = \text{MEDIAN}(\{10,20,30,40,50,60\}) = \frac{30+40}{2} = 35 \] Cannot compute this from MEDIAN(A) and MEDIAN(B) alone — requires the full sorted list.

Three branch offices report quarterly sales:

Incorrect global AVG: \[ \text{AVG}_{\text{wrong}} = \frac{30 + 10 + 30}{3} = \frac{70}{3} \approx 23.33 \quad \text{\ding{55}} \]

Correct global AVG (algebraic combination): \[ \text{AVG}_{\text{correct}} = \frac{240 + 150 + 360}{8 + 15 + 12} = \frac{750}{35} \approx 21.43 \quad \checkmark \]

Conclusion: Simply averaging the branch averages ignores differing counts. The correct approach requires carrying both SUM and COUNT as sub-aggregates — confirming AVG is algebraic, not distributive.