Reduce direct query mode loading time

[kapurastki]

Hi all,

I had created a report with a direct query mode. However, the data size is too large and it needs several minutes to retrieve the data. Is there any solution that I can retrieve part of the data first? For example, I have a field to store the date of the record, can I retrieve top 30 days data when I open the report. Then I can use a slicer to select the period to view the rest of the data to avoid performance issue. I already reduce the number of fields. Import mode is not applicable in my report. 

Thanks.

[Anonymous]

Hi @kapurastki ,

@Daniel29195 said it well.

You might also consider incremental refresh, which fits your needs well.

Incremental refresh extends scheduled refresh operations by providing automated partition creation and management capabilities for semantic model tables that are frequently loaded with new and updated data.

An incremental refresh strategy that partitions the table, refreshes only the latest imported partition, and can use another DirectQuery partition for live data, can significantly reduce the amount of data that must be refreshed.

For more information on incremental refresh, please see:

Incremental refresh for semantic models and real-time data in Power BI - Power BI | Microsoft Learn

Configure incremental refresh and real-time data for Power BI semantic models - Power BI | Microsoft...

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[kapurastki]

Hi @Anonymous ,

Is that only premium user can use the incremental refresh?

I am also trying the composite model to combine with the import and direct query data. Other than spreadsheet, can I import the data from the db?  Since I see the import button is grayed out when I try to import the data from db.

Thanks.

[Anonymous]

Hi @kapurastki ,

Incremental refresh is a feature exclusive to Power BI Premium users.

Composite models in Power BI allow combining data from various sources using import and DirectQuery modes.

For more information on using fit models in Power BI , please see:

Use composite models in Power BI Desktop - Power BI | Microsoft Learn

As for your import button being gray, is there any specific error message? Which data sources are you trying to use?

For related operations on connecting Power BI to the database, please refer to:

Tutorial: Connect to on-premises data in SQL Server - Power BI | Microsoft Learn

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[kapurastki]

Hi @Anonymous ,

Thanks for providing information about incremental refresh.

Query 1 is retrieve from oracle db by direct query mode. When I am trying to import another table from the db. The button turn into gray. Please refer to the following image. Is there any limitation of the import mode in a composite model?

Thanks

[Anonymous]

Hi @kapurastki ,

Oracle only supports direct query mode and does not support import mode.

This is not a limitation of import in composite mode.

For example, SQL Server Management Studio does not have this problem.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Daniel29195 · Accepted solution]

@kapurastki 

ways to reduce diretquery loading time : 

1. Data Source Optimization: Ensure that your data source is optimized for performance. This includes indexing tables, optimizing queries, and minimizing the number of joins and transformations.

2. Query Folding: Leverage query folding wherever possible. This means pushing data transformation steps back to the data source, allowing Power BI to generate more efficient queries.

3. Use Parameters: Utilize parameters to filter data at the source whenever possible. This can help reduce the amount of data being fetched from the source.

4. Aggregation: Consider pre-aggregating data at the source or using aggregations in Power BI to reduce the amount of data that needs to be queried. ( use aggs features in power bi ) 

5. Indexing: Ensure that your data source tables are properly indexed to improve query performance.

6. Parallel Queries: If your data source supports it, consider enabling parallel queries to allow Power BI to retrieve data more efficiently.

7. Data Model Optimization: Optimize your Power BI data model by reducing the number of tables and columns, and removing unnecessary relationships and calculations.

hope this helps /

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🙏