Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
shanjaf
New Member

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
Seth_C_Bauer
Community Champion
Community Champion

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


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

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.

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


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

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?

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


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

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!

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


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

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


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

Proud to be a Datanaut!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.