Power BI Architecture

Power BI architecture defines how data flows from its source to the final report that users interact with. Understanding this flow is essential for designing efficient BI solutions.

End-to-End Data Flow

1. Data Sources → 2. Power Query (ETL) → 3. Data Model → 4. DAX Calculations → 5. Visualizations → 6. Publishing & Sharing

Detailed Breakdown

Step 1: Data Sources • Where data originates: Excel, databases, APIs, cloud services, etc. • Power BI connects to 100+ data sources

Step 2: Power Query Editor (ETL Layer) • Extract: Pull data from sources • Transform: Clean, filter, reshape, merge data • Load: Load transformed data into the data model • Uses the M language for transformations

Step 3: Data Model • Tables are stored in an in-memory columnar database (VertiPaq engine) • Define relationships between tables (one-to-many, many-to-many) • Optimized for fast aggregation and querying

Step 4: DAX (Data Analysis Expressions) • Formula language for creating calculated columns, measures, and tables • Similar to Excel formulas but much more powerful • Examples: SUM, CALCULATE, FILTER, RELATED

Step 5: Visualizations • Drag-and-drop charts, tables, maps, cards, slicers • Interactive filtering and cross-highlighting • Custom visuals from AppSource marketplace

Step 6: Publishing & Sharing • Publish to Power BI Service (cloud) • Create dashboards, set up alerts • Share via workspaces, apps, or embedded links

Key Engines in Power BI