🧠 LEVEL 1 — POWER BI FOUNDATIONS (DETAILED MASTER CARDS)
🟥 CORE ARCHITECTURE
🃏 Card 1 — Purpose of Power BI
Q: What fundamental problem does Power BI solve in the data ecosystem, and how does it differ from traditional reporting tools?
A:
Power BI solves the problem of self-service business intelligence:
Traditional reporting:
Static reports
IT-controlled
Slow updates
Limited interactivity
Power BI enables:
Interactive exploration
Real-time slicing/filtering
Integration of multiple sources
Democratized analytics
It shifts organizations from report consumption → data exploration.
Visual cue: Static PDF report vs interactive dashboard
🃏 Card 2 — End-to-End Workflow
Q: What is the full lifecycle of data inside Power BI from source to user interaction?
A:
Data acquisition (connect to sources)
Transformation (Power Query)
Loading into model (VertiPaq engine)
Modeling (relationships, measures)
Visualization (reports)
Publishing (Service)
Consumption (dashboards/apps/mobile)
Key insight:
Power BI is not just visualization — it is a complete BI pipeline.
🃏 Card 3 — Why Power BI Desktop Is Central
Q: Why is Power BI Desktop considered the core authoring environment rather than the Service?
A:
Because Desktop contains:
Full Power Query engine
Data modeling tools
DAX authoring
Report creation
The Service is primarily for:
Distribution
Collaboration
Governance
Think:
Desktop = Development
Service = Deployment
🟥 DATASET / REPORT / DASHBOARD — DEEP
🃏 Card 4 — Dataset (Semantic Model)
Q: What is a dataset in Power BI from a semantic modeling perspective?
A:
A dataset is a semantic layer that translates raw data into business meaning.
It contains:
Tables
Relationships
Measures
Hierarchies
Metadata
It defines how users interpret data.
Important insight:
Reports do not analyze raw data — they query the semantic model.
🃏 Card 5 — Report Mechanics
Q: How does a report interact with the dataset at query time?
Q: How does a report interact with the dataset at query time?
A:
Each visual generates a query against the dataset using filter context.
User interactions:
Slicers
Cross-filtering
Drill-down
modify the query dynamically.
🃏 Card 6 — Dashboard Limitations
Q: Why are dashboards only available in the Service and not Desktop?
A:
Because dashboards are:
Aggregations of visuals from multiple reports
Designed for monitoring
Not for detailed analysis
They rely on cloud-based sharing features.
🟥 STORAGE MODES — INTERNAL LOGIC
🃏 Card 7 — Import Mode Internals
Q: Why is Import mode typically faster than DirectQuery?
A:
Because data is stored in-memory using the VertiPaq columnar engine.
Benefits:
Compressed storage
Vectorized scans
No network latency
🃏 Card 8 — DirectQuery Trade-offs
Q: What are the architectural trade-offs of DirectQuery?
A:
Advantages:
Real-time data
No duplication
Disadvantages:
Slower performance
Limited DAX functions
Dependency on source database
🃏 Card 9 — Composite Models
Q: What problem do composite models solve?
A:
They allow combining:
Import tables
DirectQuery tables
in one model.
Useful when:
Some data needs real-time access
Other data benefits from in-memory speed
🟥 POWER QUERY — CONCEPTUAL FOUNDATION
🃏 Card 10 — Role Separation
Q: Why should data cleaning be done in Power Query instead of DAX?
A:
Power Query operates before data enters the model, making transformations:
Reusable
Efficient
Applied once
DAX transformations happen at query time → expensive.
🃏 Card 11 — Immutable Pipeline Concept
Q: Why are Power Query steps considered an immutable transformation pipeline?
A:
Each step produces a new table without altering previous steps.
Benefits:
Traceability
Reproducibility
Debugging
🟥 DATA MODEL — WHY IT EXISTS
🃏 Card 12 — Need for Modeling Layer
Q: Why doesn’t Power BI simply visualize tables directly without modeling?
A:
Because raw tables:
Lack relationships
Lack business logic
Cause ambiguous aggregations
Modeling creates a coherent analytical structure.
🃏 Card 13 — Semantic vs Physical Model
Q: What is the difference between the semantic model and the physical data source?
A:
Physical:
Raw tables in databases/files
Semantic:
Business interpretation layer:
Relationships
Measures
Hierarchies
🟥 VISUAL INTERACTION MECHANICS
🃏 Card 14 — Cross-Filtering Logic
Q: How does selecting one visual affect others on a report page?
A:
It modifies filter context applied to the dataset.
Other visuals recompute results based on new context.
🃏 Card 15 — Drill-Down Concept
Q: What analytical problem does drill-down solve?
A:
Navigating between aggregation levels:
Year → Quarter → Month → Day
Supports hierarchical analysis.
🧠 LEVEL 2 — DATA MODELING MASTERY (ELITE CARDS)
🟥 WHY DATA MODELING MATTERS
🃏 Card 1 — Modeling vs Visualization
Q: Why is data modeling considered more important than visualization in Power BI?
A:
Because the model determines:
Correctness of aggregations
Query performance
Filter behavior
DAX complexity
Poor model → impossible calculations
Good model → simple measures
Expert insight:
Visualization errors often originate in model design.
🃏 Card 2 — Analytical Engine Dependency
Q: Why does the VertiPaq engine rely heavily on model structure?
A:
VertiPaq scans columns independently.
Efficient models:
Narrow fact tables
Low-cardinality dimensions
Clear relationships
Bad models:
Wide tables
High cardinality
Ambiguous joins
🟥 STAR SCHEMA — DEEP UNDERSTANDING
🃏 Card 3 — Star Schema Purpose
Q: What fundamental analytical problem does the star schema solve?
A:
Separating:
Measurements (facts)
Context (dimensions)
This enables:
Flexible slicing
Efficient aggregation
Clear semantics
🃏 Card 4 — Fact Table Properties
Q: What characteristics define a proper fact table?
A:
Contains:
Numeric measures
Foreign keys to dimensions
High row count
Granular events
Should NOT contain:
Descriptive attributes
Repeated text
🃏 Card 5 — Grain Concept (Critical)
Q: What is the grain of a fact table and why must it be defined explicitly?
A:
Grain = level of detail of each row.
Example:
One row per:
Transaction
Order line
Daily summary
Undefined grain causes:
Double counting
Inconsistent aggregations
🃏 Card 6 — Dimension Table Role
Q: Why are dimension tables essential for slicing data?
A:
They provide descriptive attributes used for filtering:
Customer demographics
Product categories
Geographic hierarchy
Dimensions define analysis perspectives.
🟥 RELATIONSHIPS — INTERNAL LOGIC
🃏 Card 7 — One-to-Many Relationship
Q: Why should relationships typically flow from dimension (one) to fact (many)?
A:
Because:
Dimensions filter facts
Not the reverse
This mirrors real-world logic:
Category → Products → Sales
🃏 Card 8 — Filter Propagation
Q: How does filter propagation work across relationships?
A:
Filters applied to a dimension:
→ propagate to fact table
→ affect aggregations
Direction matters.
🃏 Card 9 — Bidirectional Relationships Risk
Q: Why can bidirectional filtering create ambiguity?
A:
It allows filters to travel multiple paths, causing:
Circular dependencies
Incorrect totals
Performance issues
Use sparingly.
