📈 Power BI & Business Intelligence Masterclass
1️⃣ Power BI Architecture & Core Workflow
Definition: Microsoft Power BI ek enterprise-grade Business Intelligence (BI) aur Data Visualization tool hai jo raw data sources (Excel, SQL, Cloud) ko connect karke interactive visual dashboards aur actionable business insights me transform karta hai.
| Stage | Tool Used | Key Responsibility |
|---|---|---|
| 1. Extract & Transform (ETL) | Power Query | Data clean karna, missing values handle karna, columns merge/split karna. |
| 2. Data Modeling | Relationship View | Tables ke beech relationships (1-to-many) aur Star Schema establish karna. |
| 3. Analytical Calculation | DAX Engine | Dynamic KPIs, aggregations, aur growth percentages calculate karna. |
| 4. Presentation & Sharing | Report View & Power BI Service | Interactive charts design karna aur stakeholders ke sath cloud par share karna. |
2️⃣ Power Query & ETL Transformations
Power Query background me M Language code generate karta hai. Common operations:
- Promote Headers: Pehli row ko column titles banana.
- Unpivot Columns: Wide tables (har mahine ka alag column) ko tall analytical format me convert karna.
- Merge vs Append Queries:
Merge:SQL JOIN ki tarah horizontally columns jodna.Append:SQL UNION ALL ki tarah vertically nayi rows append karna.
ETL Rule: Sabhi data types (Integer, Text, Date) ko Power Query level par strictly set karein, warna DAX calculation errors aur performance slowdown ho sakta hai.
3️⃣ Data Modeling: Star Schema vs Snowflake Schema
Enterprise reports ke high performance ke liye relational modeling foundation hoti hai:
| Concept | Fact Table | Dimension Table |
|---|---|---|
| Nature | Numeric transactions & events (Sales, Orders, Revenue) | Descriptive lookup context (Customer, Product, Date) |
| Size | Millions of rows (Deep & Narrow) | Hundreds/Thousands of rows (Wide attributes) |
| Relationship | Many side (*) in 1:* relation |
One side (1) with primary key |
Best Practice: Power BI ke VertiPaq engine ke liye hamesha Star Schema use karein. Yeh query execution ko fast aur DAX logic ko clean rakhta hai.
4️⃣ DAX Essentials: Measures vs Calculated Columns
DAX (Data Analysis Expressions): Power BI ki formula language jo Excel functions se milti-julti hai lekin dimensional model par chalti hai.
| Feature | Calculated Column | DAX Measure |
|---|---|---|
| RAM Consumption | High (Memory me physically store hota hai) | Zero (Query time par on-the-fly evaluate hota hai) |
| Context | Row Context (Har row par calculate hota hai) | Filter Context (Current visual filters par dynamically chalta hai) |
| Best Use Case | Slicers ya Matrix columns me group karne ke liye | Numeric KPIs (Total Sales, Profit Margin, Growth %) |
// Simple Aggregation Measure
Total Sales = SUM(FactSales[SalesAmount])
// Iterative Measure (Row-by-row math)
Total Revenue = SUMX(FactSales, FactSales[Quantity] * FactSales[UnitPrice])5️⃣ Advanced DAX: CALCULATE & Time Intelligence
// 1. CALCULATE: Overrides existing Filter Context
Sales for Electronics =
CALCULATE(
[Total Sales],
DimProduct[Category] = "Electronics"
)
// 2. Year-to-Date (YTD) Calculation
YTD Sales =
TOTALYTD(
[Total Sales],
DimDate[DateKey]
)
// 3. Year-over-Year (YoY) Growth %
YoY Sales Growth =
VAR CurrentSales = [Total Sales]
VAR PreviousSales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DimDate[DateKey]))
RETURN
DIVIDE(CurrentSales - PreviousSales, PreviousSales, 0)
Live DAX Measure & Dynamic Filter Context Simulator
[Power BI VertiPaq Engine]: Current Context: All Regions | [Total Sales] = ₹1,25,00,000 | Active Slicers: None
6️⃣ Visualizations & Dashboard Design
Business problems ke hisab se sahi visual select karein:
| Visual Type | Best Business Application |
|---|---|
| Card / Multi-Row Card | Top-level standalone KPIs (Total Revenue, Total Customers). |
| Line Chart | Trend analysis over time (Monthly Sales growth, Churn rate). |
| Decomposition Tree | Root cause analysis (Revenue breakdown by Category → Region → Sales rep). |
| Matrix Table | Hierarchical financial reporting (P&L Statements with conditional formatting). |
7️⃣ Row-Level Security (RLS) & Cloud Deployment
Security rules define karna taaki har manager sirf apne region ka data dekh sake:
// Modeling Tab → Manage Roles → North_Region_Role
// Filter Expression on DimRegion:
[RegionName] = USERPRINCIPALNAME() // Dynamically filters by logged-in user's email- Power BI Desktop: Free authoring tool jahan models aur reports design hoti hain (
.pbixfile). - Power BI Service (Cloud): Web portal jahan dashboards publish hote hain, scheduled refreshes configure hoti hain aur app workspaces manage hote hain.
