Question

You maintain a Fabric warehouse with a large 800-million-row Sales fact table. A daily executive Power BI report only needs total revenue and order count summarized by product category and calendar month. Analysts report the report's DirectQuery visuals are slow because each refresh scans the full fact table. You want to reduce query time while keeping the underlying detail available for ad-hoc analysis. What is the most appropriate transformation approach?

Accepted Answer

Create a pre-aggregated summary table populated by a scheduled stored procedure that groups Sales by category and month, and point the executive report at it

Answer

Add a clustered columnstore index hint to the DirectQuery visuals so the report reads fewer rows

Answer

Create a non-materialized SQL view that groups Sales by category and month and connect the report to the view

Answer

Enable result set caching only and leave the report querying the detail fact table directly