When data keep growing in Direct Query

[Anonymous]

Hi all, in my report, I am using direct query as storage mode.

However, when the dataset keep growing and the performance keep dropping down.

May I know is there any good way to maintain the performance?

I have tried the below:

1.) Hybrid Mode (direct query only today's data, but somehow its not improving)

2.) Indexed view in SQL (Tried to create a view contains last month data only, but there are too many limiation to create the view)

3.) Adding index and keys (doesn't help on performance)

[LP280388 · Accepted solution]

Hi,

There are many ways you can approach this issue. As you have not stated how many visuals you have in one page of the report, my first suggestion would be

1. to reduce the number of visuals in your report in one page.  

2. to use a blank page left intetionally so that queries doesnt run as soon as you open the report

3. change settings under  Options > Current file > Query Reduction >  add single apply button to filters and slicers

4. If you have filters/slicers, I recommend having those columns on import mode if they are slow changing dimensions.

5. With Direct query, when you publish to app.powerbi.com, there are certain limitations on data fetching etc... for example, when you apply a filter, the query firing to the DB and getting back data has around 3 mins of time limitation. if it cant complete the given task within this time, the report will not render

6. Remove unwanted columns and rows

7. Dont add steps in the powerquery if in case of direct query, make sure that the query folding is happening at each step, else the issue will continue

8. move all those power query steps to your view itself incase you have access to create your own view/table

9. If you are using a view, I recommend having a table for that in DB itself and pull from there instead of view

there are still lot many you can do to improve the performance but they completely depends on dataset, model design and report design etc...