how to schedule refresh for the dataset using on-prem sql database with option 'Import'

[shanjaf]

Hi All,

how to schedule refresh for the dataset using on-prem sql database with option 'Import' instead of Direct query.

Can some one guide me how to schedule when we use 'Import' data.

[Seth_C_Bauer]

@shanjaf First, you will need to make sure you have a gateway set up. You can do either Personal or Enterprise, choose the fit that works best for your purpose.

Personal: https://powerbi.microsoft.com/en-us/documentation/powerbi-personal-gateway/

Enterprise: https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-enterprise/

And this will walk you through how to set up the refresh:

https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-desktop-file-local-drive/

Be aware, there is currently a 250mb limit on the size of the Power BI Desktop file. So any file that is larger than this will not be able to be pushed to the Service.

[THEG72]

Hey @Seth_C_Bauer @shanjaf 

Just checking if this advice in this post is still current.

I have an On Prem SQL data source and using the IMPORT option to gather data through a stored procedure as an SQL statement in the SQL Server Database dialogue box

Here is my statement

exec('[spReceivableSummaryReport] @ShipmentId = 294')

The data returns fine however i am having an issue with the refresh once i pubish to service.

I have an enterprise gateway installed and working and have added the SQL data source.

I setup the new connection in the Manage Gateway section of he service and it tests fine for the SQL source but when i go to the dataset to configure the refresh it states "you dont have any gateway installed or configured for the data sources in this dataset. Please install a new data gateway or configure the data source for an existing data gateway."

The Gateway connection Data Source credentials and Parameter are all greyed out ? 

Is there any common issues you have come accross that could shed some light on the issue? or is calling a stored procedure and trying to refresh in the service require a special setup?

I am running version 14.16.6670.1 April 2018 of the gateway software.

[Seth_C_Bauer]

@THEG72 It should work especially if you were the one that set up the Gateway. Did you remember to add the user that is publishing the report to the dataset you created on the gateway? Also, ensure that the connection Server/database name that you are using in the PBIX file matches exactly to what you set up as a datasource in the gateway.

[THEG72]

Hi @Seth_C_Bauer

Thanks for your advice it was spot on. One of my queries used local host instead of the server name like the other queries...given i didnt have localsource as a server name in the managed gateway section it greyed out the Refresh options.

Can you have localhost as a server name when setting up a gateway? Is it better practice to put the name in or not if localhost works?

[Seth_C_Bauer]

@THEG72 Avoid the "Localhost", it will just cause problems (like this use case). Even if it would work, it would require someone else to know what server it is if it did work.

[Phil_Seamark]

HI @Seth_C_Bauer

You do know this is the "Desktop" forum, don't you?

[Seth_C_Bauer]

@Phil_Seamark I was supposed to do a book review this weekend, I might forget and just focus on answering "Desktop" questions...

[Phil_Seamark]

Oh, I mean ..... Welcome @Seth_C_Bauer, come in and sit yourself down.  Is there anything I can get you?  😄