User-defined aggregations are a powerful - yet often overlooked - tool for optimizing query performance, especially when working with large DirectQuery semantic models. By configuring specialized aggregation tables within your data model, you enable Power BI to automatically serve commonly requested summaries from in-memory cache rather than querying the backend source each time. This not only delivers faster and more responsive reports but also reduces the workload on source systems, making your analytics solutions more scalable and efficient.

In this quickstart, you’ll learn how to set up User-defined aggregations and see first-hand how they can significantly boost the performance of your Power BI semantic models.

Before you begin, ensure you have the following:

• Databricks account, access to a Databricks workspace, Unity Catalog, and Databricks SQL Warehouse
• Power BI Desktop, latest version is highly recommended

1. Create a catalog and a schema in Databricks Unity Catalog.

   CREATE CATALOG IF NOT EXISTS powerbiquickstarts;
   USE CATALOG powerbiquickstarts;
   CREATE SCHEMA IF NOT EXISTS tpch;
   USE SCHEMA tpch;

2. Create test tables in the catalog by replicating tables from samples catalog.

   CREATE OR REPLACE TABLE nation AS SELECT * FROM samples.tpch.nation;
   CREATE OR REPLACE TABLE customer AS SELECT * FROM samples.tpch.customer;
   CREATE OR REPLACE TABLE orders AS SELECT * FROM samples.tpch.orders;
   CREATE OR REPLACE TABLE lineitem AS SELECT * FROM samples.tpch.lineitem;
   
   CREATE OR REPLACE TABLE lineitem_by_nation_agg AS
   SELECT
   `n_nationkey`
   , min(`l_shipdate`) as `EarliestShipdate`
   , sum(`l_discount`) as `SumofDiscount`
   , sum(`l_quantity`) as `SumOfQuantity`
   FROM `samples`.`tpch`.`lineitem` 
     INNER JOIN `samples`.`tpch`.`orders` on `l_orderkey` = `o_orderkey`
     INNER JOIN `samples`.`tpch`.`customer` on `o_custkey` = `c_custkey`
     INNER JOIN `samples`.`tpch`.`nation` on `c_nationkey` = `n_nationkey`
   GROUP BY ALL;
   
   CREATE OR REPLACE VIEW v_nation AS 
   SELECT *, now() as currenttime FROM nation;

3. Open Power BI Desktop → "Home" → "Get Data" → "More...".

4. Search for Databricks and select Azure Databricks (or Databricks when using Databricks on AWS or GCP).

5. Enter the following values:

   • Server Hostname: Enter the Server hostname value from Databricks SQL Warehouse connection details tab.
   • HTTP Path: Enter the HTTP path value from Databricks SQL Warehouse connection details tab.

6. Connect to powerbiquickstarts catalog, tpch schema.

7. Add tables to the semantic model as follows.

   • v_nation - DirectQuery storage mode. Dimension table containing nation name and details. Rename it to nation in the Power BI semantic model.

   • customer - DirectQuery storage mode. Dimension table containing customer information and connected to nation dimension table using nationkey.

   • orders - DirectQuery storage mode. Fact table containing orders information and connected to customer dimension using customerkey.

   • lineitem - DirectQuery storage mode. Fact table containing details like order shipment date, discount price etc.

   • nation_agg - DirectQuery storage mode. Replica of v_nation table and used for aggregate table report.

   • customer_agg - DirectQuery storage mode. Replica of customer table and used for aggregate table report.

   • orders_agg - DirectQuery storage mode. Replica of orders table and used for aggregate table report.

   • lineitem_agg - DirectQuery storage mode. Replica of lineitem table and used for aggregate table report.

   • lineitem_by_nation_agg - DirectQuery storage mode. Aggregated lineitem data.

8. Create table relationships as follows.

   • nation → customer → orders → lineitem
   • nation_agg → customer_agg → orders_agg → lineitem_agg
   • nation_agg → lineitem_by_nation_agg

The resulting data model should look like on the screenshot below.

Next, we will analyze the performance of a test report using pure DirectQuery mode and User-defined Aggregation.

1. Create a new report page. Add a Table visual. Add columns to the Table visual as follows.

   • n_nation from nation table
   • Sum of l_discount from lineitem table
   • Sum of l_quantity from lineitem table
   • Earliest l_shipdate from lineitem table

2. Open Optimize → Performance Analyzer → Start Recording → Refresh visuals.

3. Perfomance Analyzer tab will display the Table visual and a DAX query. Click on Copy Query. The DAX query should look similar to Sample DAX Query script. Below is the screenshot of DirectQuery report page.

   

4. To compare the performance between pure DirectQuery and User-defined Aggregations, it is important to get objective and precise query execution times.

5. Open DAX Studio, click Connect, choose local Power BI mode, click Connect, and click Server Timings.

6. Open the Sample_DAX_Query.dax query or paste DAX-query that was previously copied in Power BI Desktop. Click Run. As shown in screenshot below, the query takes 2.167 sec.

   

7. You can also find the SQL query execution time by looking at Databricks Query History. As shown below, the query took 2.053 sec and read ~38M rows.

   

   The SQL query looks as follows. Power BI built a SQL query joining nation dimension table with orders and lineitem fact tables.

   select ...
   from
   (
      select ...
      from
         (
         select ...
         from
            (
               select ...
               from
               (
                  select ...
                  from
                     `powerbiquickstarts`.`tpch`.`lineitem` as `OTBL`
                     inner join `powerbiquickstarts`.`tpch`.`orders` as `ITBL`
                        on (`OTBL`.`l_orderkey` = `ITBL`.`o_orderkey`)
               ) as `OTBL`
                  inner join `powerbiquickstarts`.`tpch`.`customer` as `ITBL`
                     on (`OTBL`.`o_custkey` = `ITBL`.`c_custkey`)
            ) as `OTBL`
               inner join `powerbiquickstarts`.`tpch`.`nation` as `ITBL`
               on (`OTBL`.`c_nationkey` = `ITBL`.`n_nationkey`)
         ) as `ITBL`
      group by
         `n_name`
   ) as `ITBL`
   where ...

1. Switch to Power BI Desktop → Model view.

2. Right Click lineitem_by_nation_agg → Manage aggregations.

3. Configure aggregation table as shown below on the screenshot.

   Aggregation column	Summarization	Detail table	Detail column
   n_nationkey	GroupBy	nation	n_nationkey
   EarliestShipdate	Min	lineitem_agg	l_shipdate
   SumofDiscount	Sum	lineitem_agg	l_discount
   SumOfQuantity	Sum	lineitem_agg	l_quantity

   

4. Create a new report page. Add a Table visual. Add columns to the Table visual as follows.

   • n_nation from nation_agg table
   • Sum of l_discount from lineitem_agg table
   • Sum of l_quantity from lineitem_agg table
   • Earliest l_shipdate from lineitem_agg table

5. Open Optimize → Performance Analyzer → Start Recording → Refresh visuals.

6. Perfomance Analyzer tab will display the Table visual and a DAX query. Click on Copy Query. The DAX query should look similar to Sample_DAX_Query_Using_Aggregations script. Below is the screenshot of User-defined Aggregation report page.

   

7. Open DAX Studio, click Connect, choose local Power BI mode, click Connect, and click Server Timings.

8. Open the Sample_DAX_Query_Using_Aggregations.dax query or paste DAX-query that was previously copied in Power BI Desktop. Click Run.

9. As shown in screenshot below the query takes 1.077 sec.

   

   Also, as shown in the screenshot, the first row under RewriteAttempted shows MatchFound, i.e., Power BI was able to find the aggregate table for this query. Hence, during the query execution as shown in the screenshot the values are fetched from lineitem_by_nation_agg instead of lineitem_agg fact table.

10. You can also find the query execution time by looking at Databricks Query History. As shown below the query took ~0.994 sec and read only 50 rows (instead of ~38M rows).

    

    The SQL query looks as follows. Power BI built a SQL query joining nation dimension table with aggregated table lineitem_by_nation_agg which is much smaller than orders and lineitem, hence the query is much more efficient.

    select ...
    from
    (
       select ...
       from
          (
          select ...
          from
             `powerbiquickstarts`.`tpch`.`lineitem_by_nation_agg` as `OTBL`
                inner join `powerbiquickstarts`.`tpch`.`nation` as `ITBL`
                on (`OTBL`.`n_nationkey` = `ITBL`.`n_nationkey`)
          ) as `ITBL`
       group by
          `n_name`
    ) as `ITBL`
    where ...

Thus, by utilizing User-defined aggregation we achieved much higher performance and decreased the amount of I/O operations.

User-defined aggregations in Power BI provide significant performance benefits, especially for large semantic models utilizing DirectQuery. By defining specialized aggregation tables, Power BI can automatically redirect queries for summary information to these pre-aggregated tables, allowing frequently needed results to be served from a much smaller and efficient in-memory cache instead of the full detail tables in the backend. This results in much faster and more responsive reporting, drastically reducing query times and the number of rows scanned during execution - for example, dropping from tens of millions of rows to just a few dozen in test scenarios. This approach not only speeds up report visuals but also decreases the workload on source systems like Databricks SQL, leading to increased scalability, reduced resource consumption, and better overall efficiency for enterprise analytics solutions. User-defined aggregations are powerful for enabling high performance and responsive analytics on vast datasets without compromising detail or accuracy.

A Power BI template User-defined Aggregations.pbit, as well as supporting scripts, are present in this folder to demonstrate the usage of User-defined Aggregations outlined above. To use the template, simply enter your Databricks SQL Warehouse's ServerHostname and HttpPath, along with the Catalog and Schema names that correspond to the environment set up in the instructions above.