1. What the Exam Evaluates

The exam evaluates your ability to:

• – Understand data lakes, lakehouses, data warehouses, metadata, governance, and sustainability.
• – Model analytical systems (fact tables, dimensions, grain, hierarchies, star vs snowflake).
• – Perform OLAP reasoning (roll-up, drill-down, slice, dice, SQL OLAP feasibility).
• – Reason about edge–fog–cloud deployments, including economic, energy, CO₂, and social impact.
• – Understand project roles and estimate person-hour distribution in a DW project.

2. Core Knowledge to Review

A. Data Warehousing Fundamentals:

• – Fact vs dimension tables
• – Grain identification
• – Hierarchies and attributes
• – Additive, semi-additive, and non-additive facts

B. Granularities & Hierarchies:

• – Time: Day → Week → Month → Quarter → Year → Hour (optional)
• – Location: Site → City → Region → Country
• – User: User → Household → Segment → Country
• – Product/Page/Article: Item → Category → Domain

C. OLAP Operations:

• – Roll-up, Drill-down, Slice, Dice
• – GROUP BY, ROLLUP, CUBE

D. Edge–Fog–Cloud Concepts:

– What runs where, responsible data processing, energy/CO₂ reduction, privacy-preserving design.

E. Project Roles:

– PM, DE, DS, BI, DevOps; responsibilities and collaboration.

3. Practice Before the Exam

• – Identify fact tables, grains, and dimensions from textual descriptions.
• – Draw star and snowflake schemas with PK/FK.
• – Write OLAP reasoning and SQL pseudo-queries.
• – Compare cloud vs hybrid architectures.
• – Estimate project phases and person-days.

Practice Problem – Integrated Data Warehousing, OLAP, and Edge–Fog–Cloud Architecture

Scenario:
StreamView, a video news platform, wants to build a data warehouse for user analytics and real-time content adaptation.

Questions:

Q1. Fact Table Identification & Grain
a) Identify the fact table(s).
b) Define the grain.
c) List five measures and classify them (additive, semi-additive, non-additive).

Q2. Dimensional Modelling
Design four dimensions (granularity, hierarchy, snowflake relevance).

Q3. OLAP Reasoning
For each query:
1. Identify OLAP operations.
2. Write pseudo-SQL.
3. Explain feasibility.

Q4. Edge–Fog–Cloud Architecture
a) Cloud-centric architecture.
b) Hybrid architecture (edge, fog, cloud).
c) Two design decisions to reduce energy/CO₂ and social profiling.

Q5. Project Roles and Effort Estimation
a) Define phases and roles.
b) Estimate person-days.
c) Discuss trade-offs.

The 4 Pillars of Exam Success

 Understand DW Core Concepts
– Fact table, grain, dimensions, hierarchies, star vs snowflake.

Master OLAP Thinking
– Roll-up, drill-down, slice, dice, SQL ROLLUP/CUBE.

 Deployment Patterns
– Cloud-centric vs hybrid edge–fog–cloud.

 Modelling Checklist
– Grain → dimensions → hierarchies → PK/FK → measures.