Refresh Schedule Help!

[krisbainbridge]

Hi

I have a model which we've built in SQL, we've trimmed the data as far as humanly possible, i want the reporting to be real time (or within 5 minutes), however due to the size of data (which is trimmed) the report times out when using direct query.  If we use import and publish to powerbi.com the report works well, however we are limited to 8 refreshes a day which is unacceptable.

Can anyone offer a suggestion?

Thanks

[bergjm2]

I believe Power BI Premium has unlimited refreshes - https://docs.microsoft.com/en-us/power-bi/service-premium, so that would be an option, but more cost than pro.

You could also look at your queries being sent to the database with a DBA and see if there are any long running queries, then work with a DBA to tune them to make your Direct Query queries run faster.

When you say you have trimmed the data as much as possible in SQL, are you trimming it based on time (last 30 days, last week, etc.) in the SQL model as well, or just adding new data to the existing data?

[krisbainbridge]

I looked at premium but the costs are way too high.

I only need data relating to a specifc part (which is linked to other tables and therefore need the data which is associated to the part).  I looked at parameters, which i got working, however you needed to manually go into query to enter the part number.  This is variable and could be any part number the user needs to select, I couldnt tie this to a slicer/filter so that it only queries associated to the parameter.

By trimming, I mean as many calculations done by SQL as possible, as well as removing any unneeded fields etc

[bergjm2]

What was the issue with tieing the part number to a slicer?

[krisbainbridge]

The part number needs to be the parameter, but theres no user friendly way of calling up the parameter (to enter the part number) as it has to be in the code for the query

[bergjm2]

I would sit with a DBA and look at the queries hitting the database from Power BI and make sure there is not any database tuning that can be done to prevent time outs - indexes, remove cartesian joins, different table structures, etc.

If the part numbers are grouped in any way and the groups are fairly stagnant, and certain users only need to see certain groups, maybe you could have a table with the part numbers and a group id, then you might get some more speed with row level security based on the user - https://docs.microsoft.com/en-us/power-bi/service-admin-rls.  You could set the row level security to the part number groups that apply to each user or user group.

Other than that, I don't know what you could do.