How Power BI Desktop connect with SQL Server

[yangca12]

Hi , 

My understanding is that Power BI Desktop only access SQL Server to setup connection and fetch top 1000 data once opening the pbix file. 
However, when I was tracking in SQL Server Profiler, I saw it throwing same query to SQL Server again in the first maybe 20 minutes, having different SPIDs. 

'
select top 1000
[$Table].[Code] as [Code],
[$Table].[Description] as [Description],
[$Table].[Def_ Costing Method] as [Def_ Costing Method],
[$Table].[Apply For Extra Sales Discount] as [Apply For Extra Sales Discount]
from [dbo].[PBI_ITEM_CATEGORY] as [$Table] ' 

Can anyone help to answer my question and explain why it happened like that ? I am sure I  didn't refresh data. 

[yangca12]

is it possible that once Power BI desktop lost connection to SQL Server, it would setup a new connection automatically, although user does not click refresh data

[aj1973 · Accepted solution]

Power BI Desktop doesn't automatically refresh Reports. there is 2 ways that the desktop connects to SQL server

1- Import Mode needs, a manual refresh

2- Direct Query, The report refreshes when there is an action on a visual or slicer 

Power Bi desktop is for developpers to build reports. Power Bi service is for users to view and manage reports.

In Power Bi service, reports can be refreshed automatically via connectors or Power Bi Gateway.

[yangca12]

thanks for your reply. That's exactly what I thought but not what I saw in SQL Server Profiler unfortunately. that's why I posted this question 

[aj1973]

Please mark the thread as accepted for the rest of the community

[yangca12]

or maybe there are some settings in Power BI destop for user to change connection time or something like that ?? 

[aj1973]

Hi @yangca12 

"My understanding is that Power BI Desktop only access SQL Server to setup connection and fetch top 1000 data once opening the pbix file."   Says who?

[yangca12]

I saw this query in SQL Server Profiler, when I open one of the pbix file I was tracking 

'
select top 1000
[$Table].[Code] as [Code],
[$Table].[Description] as [Description],
[$Table].[Def_ Costing Method] as [Def_ Costing Method],
[$Table].[Apply For Extra Sales Discount] as [Apply For Extra Sales Discount]
from [dbo].[PBI_ITEM_CATEGORY] as [$Table] ' 

[aj1973]

and what are you trying to do?

[yangca12]

I was suspecting one of the tables which actually is a View that always took long time to fetch data, so I can see how long it took CPU to process it in SQL Server Profiler