Power BI Data Model

You are a Power BI data architect. The user wants to design a star schema data model with proper relationships, hierarchies, and optimization for analytical queries.

What to check first

• Open Power BI Desktop and verify you're in Model view (not Report view)
• Check your source data tables — identify which are fact tables (transactional, numeric measures) and which are dimension tables (descriptive attributes)
• Run a quick cardinality check: fact tables should have many rows; dimension tables should have fewer, unique rows per key

Steps

1. Create or import your fact table first — this contains transactions, sales, or events with foreign keys pointing to dimensions and numeric columns for aggregation (SUM, COUNT, AVERAGE)
2. Import all dimension tables separately — these contain the attributes you'll slice and dice by (Date, Product, Customer, Geography, etc.)
3. In Model view, drag the dimension table's primary key onto the fact table's foreign key to create a relationship; set it as "Many-to-One (*:1)" with the asterisk always on the fact table
4. Set the cross-filter direction to "Single" (from dimension to fact) unless you have specific bidirectional requirements — verify under "Manage relationships"
5. Create date hierarchies by right-clicking the Date dimension column, selecting "New hierarchy," then nesting Year → Quarter → Month → Day as separate hierarchy levels
6. Mark the Date table as a date table: right-click the date dimension table, select "Mark as date table," specify the date column — this enables native time intelligence functions
7. Hide foreign key columns from the report layer by unchecking them in the field list (right-click in Model view > Hide in report view)
8. Set data types correctly: numeric measures as Decimal/Whole Number, dimension keys as Text or Integer, dates as Date type
9. Add data categories to geographic columns: select a column like City, go to Properties > Data Category > City to enable map visuals

Code

// Create a calculated measure in your fact table for a common metric
Sales Total = SUMX(
    FactSales,
    FactSales[Quantity] * FactSales[UnitPrice]
)

// Create a year-over-year comparison measure
Sales YoY Growth = 
VAR CurrentYear = SELECTEDVALUE(DimDate[Year])
VAR PriorYear = CurrentYear - 1
VAR CurrentSales = [Sales Total]
VAR PriorSales = 
    CALCULATE(
        [Sales Total],
        FILTER(ALL(DimDate), DimDate[Year] = PriorYear)
    )
RETURN
    DIVIDE(CurrentSales - PriorSales, PriorSales, 0)

// Create a dimension attribute hierarchy for analysis
// In Power BI UI: DimProduct table > New Hierarchy > Category > Subcategory > Product Name

// Example role-playing dimension for multiple date scenarios
// Right-click DimDate, Duplicate as new table: DimDate_ShipDate
// Update the relationship to

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change