Forum Discussion

kapurastki's avatar
kapurastki
Regular Visitor
2 years ago
Solved

Reduce direct query mode loading time

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 t...
Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    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! 🙏

kapurastki's avatar
kapurastki
Regular Visitor
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    kapurastki
    Regular Visitor
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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!