Power Bi Data Modeling Expert

Dimension Table Structure:
- Unique key column (surrogate key preferred)
- Descriptive attributes for filtering/grouping
- Hierarchical attributes for drill-down scenarios
- Relatively small number of rows

Fact Table Structure:
- Foreign keys to dimension tables
- Numeric measures for aggregation
- Date/time columns for temporal analysis
- Large number of rows (typically growing over time)

Best Practices:
✅ Set proper cardinality based on actual data
✅ Use bi-directional filtering only when necessary
✅ Enable referential integrity for performance
✅ Hide foreign key columns from report view
❌ Avoid circular relationships
❌ Don't create unnecessary many-to-many relationships

When to Use Composite Models:
✅ Combine real-time and historical data
✅ Extend existing models with additional data
✅ Balance performance with data freshness
✅ Integrate multiple DirectQuery sources

Implementation Patterns:
- Use Dual storage mode for dimension tables
- Import aggregated data, DirectQuery detail
- Careful relationship design across storage modes
- Monitor cross-source group relationships

// Example: Hot and Cold Data Partitioning
"partitions": [
    {
        "name": "FactInternetSales-DQ-Partition",
        "mode": "directQuery",
        "dataView": "full",
        "source": {
            "type": "m",
            "expression": [
                "let",
                "    Source = Sql.Database(\"demo.database.windows.net\", \"AdventureWorksDW\"),",
                "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],",
                "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] < 20200101)",
                "in",
                "    #\"Filtered Rows\""
            ]
        },
        "dataCoverageDefinition": {
            "description": "DQ partition with all sales from 2017, 2018, and 2019.",
            "expression": "RELATED('DimDate'[CalendarYear]) IN {2017,2018,2019}"
        }
    },
    {
        "name": "FactInternetSales-Import-Partition",
        "mode": "import",
        "source": {
            "type": "m",
            "expression": [
                "let",
                "    Source = Sql.Database(\"demo.database.windows.net\", \"AdventureWorksDW\"),",
                "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],",
                "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] >= 20200101)",
                "in",
                "    #\"Filtered Rows\""
            ]
        }
    }
]

// Cross-source relationships in composite models
TotalSales = SUM(Sales[Sales])
RegionalSales = CALCULATE([TotalSales], USERELATIONSHIP(Region[RegionID], Sales[RegionID]))
RegionalSalesDirect = CALCULATE(SUM(Sales[Sales]), USERELATIONSHIP(Region[RegionID], Sales[RegionID]))

// Model relationship information query
// Remove EVALUATE when using this DAX function in a calculated table
EVALUATE INFO.VIEW.RELATIONSHIPS()

// Optimized incremental refresh with query folding
let
  Source = Sql.Database("dwdev02","AdventureWorksDW2017"),
  Data  = Source{[Schema="dbo",Item="FactInternetSales"]}[Data],
  #"Filtered Rows" = Table.SelectRows(Data, each [OrderDateKey] >= Int32.From(DateTime.ToText(RangeStart,[Format="yyyyMMdd"]))),
  #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [OrderDateKey] < Int32.From(DateTime.ToText(RangeEnd,[Format="yyyyMMdd"])))
in
  #"Filtered Rows1"

// Alternative: Native SQL approach (disables query folding)
let
  Query = "select * from dbo.FactInternetSales where OrderDateKey >= '"& Text.From(Int32.From( DateTime.ToText(RangeStart,"yyyyMMdd") )) &"' and OrderDateKey < '"& Text.From(Int32.From( DateTime.ToText(RangeEnd,"yyyyMMdd") )) &"' ",
  Source = Sql.Database("dwdev02","AdventureWorksDW2017"),
  Data = Value.NativeQuery(Source, Query, null, [EnableFolding=false])
in
  Data

When to Use Composite Models:
✅ Combine real-time and historical data
✅ Extend existing models with additional data
✅ Balance performance with data freshness
✅ Integrate multiple DirectQuery sources

Implementation Patterns:
- Use Dual storage mode for dimension tables
- Import aggregated data, DirectQuery detail
- Careful relationship design across storage modes
- Monitor cross-source group relationships

// Pre-aggregate at appropriate grain level
Monthly Sales Summary =
SUMMARIZECOLUMNS(
    'Date'[Year Month],
    'Product'[Category],
    'Geography'[Country],
    "Total Sales", SUM(Sales[Amount]),
    "Transaction Count", COUNTROWS(Sales)
)

Efficient Model Patterns:
✅ Star schema with clear fact/dimension separation
✅ Proper date table with continuous date range
✅ Optimized relationships with correct cardinality
✅ Minimal calculated columns
✅ Appropriate aggregation levels

Performance Anti-Patterns:
❌ Snowflake schemas (except when necessary)
❌ Many-to-many relationships without bridging
❌ Complex calculated columns in large tables
❌ Bidirectional relationships everywhere
❌ Missing or incorrect date tables

// Example RLS filter for regional access
Regional Filter =
'Geography'[Region] = LOOKUPVALUE(
    'User Region'[Region],
    'User Region'[Email],
    USERPRINCIPALNAME()
)

Type 1 SCD: Overwrite historical values
Type 2 SCD: Preserve historical versions with:
- Surrogate keys for unique identification
- Effective date ranges
- Current record flags
- History preservation strategy

Date Table Roles:
- Order Date (active relationship)
- Ship Date (inactive relationship)
- Delivery Date (inactive relationship)

Implementation:
- Single date table with multiple relationships
- Use USERELATIONSHIP in DAX measures
- Consider separate date tables for clarity

Bridge Table Pattern:
Customer <--> Customer Product Bridge <--> Product

Benefits:
- Clear relationship semantics
- Proper filtering behavior
- Maintained referential integrity
- Scalable design pattern