Power BI - Core Principles of Data Modeling in Power BI

 Core Principles of Data Modeling in Power BI

• Star Schema: Central fact tables surrounded by dimension tables.
• Single Date Dimension: Shared across all dashboards for consistent time intelligence.
• Conformed Dimensions: Common dimensions (Date, Employee, Customer, Supplier, Product) reused across domains.
• Granularity: Fact tables should store data at the lowest transactional level (e.g., daily sales, ticket logs).
• Relationships: One-to-many relationships from dimensions to facts.

---

 Domain-Specific Data Models

1. Finance Dashboard

• Fact Tables:
  • FactRevenue (Revenue, COGS, Profit)
  • FactExpenses (Operational, HR, Marketing costs)
  • FactBudget (Planned vs. Actuals)
• Dimensions:
  • DimDate, DimDepartment, DimAccount, DimRegion

---

2. HR Dashboard

• Fact Tables:
  • FactEmployee (Headcount, Attrition, Tenure)
  • FactAttendance (Working days, Absences)
  • FactTraining (Hours, Certifications)
• Dimensions:
  • DimDate, DimEmployee, DimDepartment, DimJobRole

---

3. Sales Dashboard

• Fact Tables:
  • FactSales (Revenue, Units Sold, Discounts)
  • FactOpportunities (Pipeline, Win/Loss)
• Dimensions:
  • DimDate, DimCustomer, DimProduct, DimSalesperson, DimRegion

---

4. Operations Dashboard

• Fact Tables:
  • FactProduction (Units Produced, Cycle Time)
  • FactResource (Hours Used vs. Available)
  • FactDowntime (Maintenance logs)
• Dimensions:
  • DimDate, DimPlant, DimMachine, DimDepartment

---

5. Marketing Dashboard

• Fact Tables:
  • FactCampaign (Spend, Revenue, ROI)
  • FactLeads (Generated, Converted)
  • FactEngagement (Clicks, Impressions, CTR)
• Dimensions:
  • DimDate, DimChannel, DimCampaign, DimCustomerSegment

---

6. Customer Service Dashboard

• Fact Tables:
  • FactTickets (Volume, Response Time, Resolution Time)
  • FactCSAT (Survey Scores)
• Dimensions:
  • DimDate, DimCustomer, DimAgent, DimIssueCategory

---

7. IT & Technology Dashboard

• Fact Tables:
  • FactSystemLogs (Downtime, Uptime %)
  • FactIncidents (Severity, Resolution Time)
  • FactProjects (Delivery Status, Timeliness)
• Dimensions:
  • DimDate, DimSystem, DimProject, DimTeam

---

8. Supply Chain & Procurement Dashboard

• Fact Tables:
  • FactInventory (Stock, Turnover)
  • FactProcurement (Spend, Variance)
  • FactLogistics (Freight Cost, Delivery Accuracy)
• Dimensions:
  • DimDate, DimSupplier, DimProduct, DimRegion

---

9. Executive Leadership Dashboard

• Fact Tables:
  • Consolidated KPIs from Finance, HR, Sales, Operations, Customer Service, IT, Supply Chain.
• Dimensions:
  • DimDate, DimDepartment, DimRegion, DimKPI

---

Takeaway

By designing separate fact tables per domain but sharing common dimensions (Date, Employee, Customer, Supplier, Product, Region), you create a unified enterprise data model.