Power BI Performance

[sanjoyleo]

Hello All,

This would be the first time we are creating PBI reports for customer. I have provided the report page where 17 Tiles are there. Among them ther are 4 Slicer.

I have 4 Tables which where used in this page   (note : total 11 tables and 6 report pages) .  Record count of such tables are like below :

T	TI	CL	CCP
15795908	17830235	55	10598542

We have used DirectQuery and we have On prem Gateway installed for data source. Data source is SQL Server 2016  (no SSAS/Cube) .

Below is the page refresh time

	PBI Desktop	PBI Service
Changed date slice 2 months	40 Sec Full Page	45 Sec
Changed date slicer to 4 months	40 Sec full page	45 Sec
Changed date slicer to 3 months + Issuer Type V	80 Sec Full page	90 Sec
Select a Client Code when date slicer is default to 3 months	25 Sec Full page	35 Sec

Here one user is saying 45 Sec is too high to see a change.

My question is based on volume , no of Tile/slice -- 45 sec is too high  ?

If yes, how can I know where exactly  it's taking time ? 

I tried to do SQL Profiler , but found it works with Native query only , so no help.

I tried to read files from ../../Microsoft\Power BI Desktop Store App\Traces location  , but could not figure out much.

So how can I say my design has some flaw where performance can be improved (knowing that I will not change number of tiles/slicer) from page. I already followed https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance as much as possible. I am using Pro license version.

Thanks

[GilbertQ]

Hi there,

I would suggest ensuring that your databases is optimized for queries from Power BI. This will ensure that it can return the dataset as quickly as possible.

The potential bottleneck is that the data is being sent queries from Power BI (Which are not the most efficient) but it is the underlying database that is taking some time to answer the queries.

Also another factor is due to having so many visuals it has to send a query for each visual.

[sanjoyleo]

Thanks GilbertQ for your responce  !

I have just a question - exactly how can I find the actual Query (all the multiple qry fired by each tile from 1 page) that is being sent to SQL ? If I get I might be able to do something on new Indexing looking those where condition  .

At   this time new View/CUBE will not  be a solution for us... only we can add new index on tables if required.  So need acxtual qry sent to SQL ( Full direct Query method) .

[quentin_vigne]

@sanjoyleo

If you want to check the query sent from PowerBI to your data base, you need to go to the SQL Server Profiler if you are using management studio from Microsoft or download this for Oracle : https://docs.oracle.com/cd/B10501_01/em.920/a96674/db_admin.htm#1013955

- Quentin

[sanjoyleo]

What I found is if we use only Native query then only we can use that Profiler only . We arre using Direct query with out Native query .

[sanjoyleo]

Hi v-xjiin-msft  ,

Can you please provide your thoughts?

[quentin_vigne]

Hi @sanjoyleo

I've experienced the same issues. When checkin on the SQL server profiler I saw that the query made by PowerBI were not optimized (and there is no way to change that apparently)

The best option I found for my self was to create views of my table only keeping needed columns and by filtering my view with the years of data needed (I don't need data from before 2013)

Then, i've imported the view to PowerBI and it importing them work A LOT better than DirectQuery

Quentin

[sanjoyleo]

Thanks for your responce !

I can't filter data at this time. Need all the data available in table. So looking for some expert comments if the page refresh time that I have at this time is normal or not in terms of my setting & Volumes ?