Power BI for people who deliver the data

Power BI reads a star schema best: narrow, long fact tables, wide dimensions, one-to-many relationships from the dimension to the fact table. That is what the gold layer should deliver (see Dimensional modeling).

Import, DirectQuery and the middle ground

• Import: the data is copied into the model and compressed in memory. Very fast queries; the data is as old as the last *refresh*.
• DirectQuery: each visual becomes a query against the source (a Databricks SQL warehouse, for example). Always-current data; speed depends on the source, and every click costs compute there.
• Composite: dimensions in Import and the large fact table in DirectQuery, or imported aggregations on top of a DirectQuery fact table.

Measure, not calculated column

A calculated column is computed at refresh and stored row by row (it takes up memory). A measure is computed on the fly, in the filter context of the visual. Totals and rates are measures.

Revenue = SUM ( fact_items[revenue] )

Delivered Revenue =
CALCULATE ( [Revenue], orders[status] = "delivered" )

Average Ticket = DIVIDE ( [Revenue], DISTINCTCOUNT ( fact_items[order_id] ) )

CALCULATE changes the filter context of the measure; DIVIDE handles division by zero without an error.

What engineering can do for the report

• Deliver the gold layer already at the right grain and with the names the business uses, instead of the report redoing the modeling in Power Query.
• A continuous date dimension (no gaps), marked as a date table.
• Reduce cardinality: a high-cardinality ID that nobody filters on doesn't need to go into the model.
• On Databricks, connect through the native connector to a SQL warehouse, with Unity Catalog controlling who sees what.