Forum Discussion

sanjoyleo's avatar
sanjoyleo
Helper I
8 years ago

Power BI Performance

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 :

 

TTICLCCP
15795908178302355510598542

 

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

 

 

 

Need Help

7 Replies

  • quentin_vigne's avatar
    quentin_vigne
    Solution Sage
    8 years ago

    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's avatar
      sanjoyleo
      Helper I
      8 years ago

      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 ?

  • GilbertQ's avatar
    GilbertQ
    Super User
    8 years ago
    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's avatar
      sanjoyleo
      Helper I
      8 years ago

      Thanks