cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shanjaf Frequent Visitor
Frequent Visitor

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

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.

8 REPLIES 8
Super User
Super User

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

@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.


Near SE WI? Join our PUG Milwaukee Brew City PUG
THEG72 Member
Member

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

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.

Super User
Super User

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

@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.


Near SE WI? Join our PUG Milwaukee Brew City PUG
THEG72 Member
Member

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

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?

Super User
Super User

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

@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.


Near SE WI? Join our PUG Milwaukee Brew City PUG
Phil_Seamark Super Contributor
Super Contributor

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

HI @Seth_C_Bauer

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Super User
Super User

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

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


Near SE WI? Join our PUG Milwaukee Brew City PUG
Phil_Seamark Super Contributor
Super Contributor

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

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 133 members 1,802 guests
Please welcome our newest community members: