dataCoverageDefinition: A New Optimisation For Hybrid Tables In Power BI

DirectQuery, Performance Tuning, Power BI 1 Minute

Hybrid tables – tables which contain both Import mode and DirectQuery mode partitions to hold data from different time periods – have been around for a while. They are useful in cases where your historic data doesn’t change but your most recent data changes very frequently and you need to reflect those changes in your reports; you can also have “reverse hybrid tables” where the latest data is in Import mode but your historic data (which may not be queried often but still needs to be available) is in DirectQuery mode. Up to now they had a problem though: even when you were querying data that was in the Import mode partition, Power BI still sent a SQL query to the DirectQuery partition and that could hurt performance. That problem is now solved with the new dataCoverageDefinition property on the DirectQuery partition.

You can find full documentation here:
https://learn.microsoft.com/en-us/analysis-services/tom/table-partitions?view=asallproducts-allversions

What dataCoverageDefinition does is tell Power BI what data is present in the DirectQuery partition so it knows whether to generate a SQL query to get data from your DirectQuery source or not. It takes the form of a simple DAX expression that returns True for rows that are stored in the DirectQuery partition, for example:

RELATED('DimDate'[CalendarYear]) IN {2017,2018,2019}

If you’re working with large fact tables in DirectQuery mode and don’t have a religious objection to some of that data being stored in Import mode, you should check out hybrid tables because they can really improve report performance!

Published by Chris Webb

My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.

Published

3 thoughts on “dataCoverageDefinition: A New Optimisation For Hybrid Tables In Power BI”

  1. Pingback: dataConvergeDefinition and DirectQuery Partitions – Curated SQL

  2. I tried this on an existing hybrid table but it seems like it is still sending SQL queries when I only query the import partitions.

    All partitions are imported besides this one and here i configured the dataCoverageDefinition

    {
    “name”: “2024Q10311-onward”,
    “mode”: “directQuery”,
    “source”: {
    “type”: “policyRange”,
    “start”: “2024-03-11T00:00:00”,
    “end”: “2030-01-01T00:00:00”,
    “granularity”: “day”
    },
    “dataCoverageDefinition”: {
    “description”: “DQ partition only for TODAY”,
    “expression”: “‘LIVE'[column]=TODAY()”
    }
    }

    any idea what I am doing wrong?

    Reply

  3. If you run the query

    select * from $SYSTEM.TMSCHEMA_DATA_COVERAGE_DEFINITIONS

    is anything helpful returned in the State and ErrorMessage columns?

    Reply

Leave a Reply