Improve DirectQuery performance

[troc24]

Hi all,

Our data model contains two tables that come from different data source. One of them is queried in Cosmos and the other in Azure SQL. Both are related by an ID, which is a string that can contain more than 20 characters. The data must be in real time, so we use Directquery. We find that as these tables grow, performance get worse. I know that using this type of identifier is not the most optimal way. There is any option to improve performance without having to generate a numerical ID?

Thanks in advance

[rajendraongole1 · Accepted solution]

Hi @troc24 - you can create Indexes on the ID columns in both Cosmos DB and Azure SQL. This can significantly speed up join operations and query execution.Optimize the queries in Cosmos DB and Azure SQL to reduce the amount of data being pulled into Power BI and also use filters and aggregations effectively at the source to minimize data transfer.Use the Manage Aggregations feature to map the aggregation table to the detailed table.

Only include necessary columns in your DirectQuery mode. Extra columns increase the amount of data transferred and processed

Reference:Index Creation :

Azure Cosmos DB indexing policies | Microsoft Learn

DirectQuery model guidance in Power BI Desktop - Power BI | Microsoft Learn

User-defined aggregations - Power BI | Microsoft Learn

Hope it helps