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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jmdh
Advocate IV
Advocate IV

onedrive refreshWITHOUT gateway

I have spent several days trying to understand how a report (using data in a Table format in Excel 2016 stored on OneDrive pro) created with PowerBI Desktop (stored on OneDrive Pro) and published on the PowerBI service is refreshed in the service.

 

I want to understand if any refresh works without using the Gateway as i understood it should.
1 when i modofy and change the Pbix

2 when i change the data in the Excel file 

If so what are the restrictions if any.

 

Many thanks before i get mad.

 

PS by the way the Gateway works fine but if you uninstall it you get into serious problems

 

14 REPLIES 14
giro17
Frequent Visitor

@jmdh @Seth_C_Bauer

 

Just read through your thread as I had exactly the same question.

However, I was asking myself:

If I connect to data sources on OneDrive and I need to use PBI Desktop to do advanced data modeling,

I need to save the pbix file on OneDrive as well to be able to refresh in PBI service without a gateway, right?

Otherwise if I connect to the data source on OneDrive, have my pbix file on my desktop and I make changes in the pbix file, the changes won't be reflected in the service if I don't have a gateway installed...?

Hi,

 

Basically (and i spent a lot of hours on this) there are two ways to manage Refresh when the source is Excel files.

1) Publish From the Desktop :
you need to access your Excel files (stored on OneDrive / sharePoint) in WEB mode ,
then Publish to PowerBI.Com,
then make sure in the Dataset which is automatically created to check the boxes on refresh etc

 

However each time you change your PBix, you need to Re Publish

 

2) Publish from POwerBI.Com 

Access your PBIx file from PowerBI.Com

Your Pbix will be automatically refreshed at regular intervals (1hour i think)

 

I have not done it for a while but i think you also need to check the bowes to also Refresh your dataset 

 

This is a summary, there is more to it. If you need more, let me know.

Jmdh

@jmdhThis page walks through the overall refresh areas and whether or not a gateway is required. The gateway is the link between data sources on premises to the cloud. If the source is already in the cloud, like your scenario you may not need the gateway. There are certain cases in which you would need the gateway when using an "online" source - for instance they just changed that requirement with connecting to web url's. It's  challenge to understand every single scenario, but you can set it up in such a way that it will automatically refresh without 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

Thank you. Yes i read it at least ten times (and the aother realted pages) and i have never been able to make refresh work (without a gateway) .

I am unclear wether my Excel files have to be in Table form so i also tried that because it automatically uses the data model i think, but it just doe not work. And so far, all my data is in EXcel, so i really don't want to go the Gateway way if not absolutely necessary.

 

By the way i think the sheer lenght of the documentation of Refresh etc hints at an unusual complexity for such a product:

I am working on a summary table for beginners

I think refresh is complex because data storage is complex. Power BI does a good job of pulling data from a wide range of locations, so it's not suprising to me that refreshing that data from all those (mixed) locations can be a bit tricky.

 

I have a simple rule of thumb for refeshing. Cloud based data (Like Azure Databases, Salesforce.com. Google Analytics etc.) refresh automatically. OneDrive and SharePoint Online are cloud based data repositories, so if I put my .PBIX or .XLSX files there they will get refreshed hourly. On premise data (Access Databases, in-house hosted SQL databases, in-house hosted Salesforce.com, or even just the files on my laptop) need a Gateway to refresh, and if it needs a Gateway, you need a Pro Licence.  If I mix any of these, (Like Google Analytics and a CSV file on my C: drive) in a data model, I'll need a Gateway for Power BI to gain access to my CSV file. 

Hope this helps.

 

Stuart

Many thanks.
This is my understanding too.
However I have not been able to make it work (ie Refresh a Pbix getting data from Excel files on OneDrive for business and published to the Service) despite days of effort.

 

One thing i do not understand:  my data source on the Pbix desktop is mentionning my files as "C: etc" . How would that data source once in the Service would know that it is a OneDrive file?

 

Also : do i by chance need to have an azure account for this to work?

 

At this stage i am stuck because i have still not been able to uninstall the gateway and re test from scratch.

 

@Burningsuit  Your rule of thumb info is helpful.  I am wondering about Cloud Based Data: if you do your modeling in Desktop - and so have imported data sets into desktop to work with and make your reports......

 

and then move it up to PBI Service for Dashboards

 

does one reset those data connections to be direct between PBI Service and Cloud Based Data - - - or must the linking always be a 2-step process of Cloud connected to Desktop and then moved up to BI Service? - - - or does one need to avoid using Desktop altogether??

 

 

www.CahabaData.com

@CahabaData, would that it were that simple. You'll need to use Desktop (or excel) to create a Data model. This may contain data from Cloud-based and on-premise files. That then is published to Power BI service. If you've only used Cloud based data, refresh is (relatively) easy and won't need a gateway. If you've used any on-premise data you'll need a gateway to refresh that data.

 

However if you connect to data through Get Data in power BI service, and choose a Service,  that will connect to the cloud-based service and set up the data model for you along with the refresh. So you don't need desktop here.

 

I guess that if you only ever want to access cloud based data as provided in Services, you can do without Power BI desktop, but if you want to access on-premise data, you'll need to use Desktop.

 

Stuart

When you say "If you've used any on-premise data you'll need a gateway to refresh that data"  do you include Excel files stored on OneDrive for business?

If so your information contradict what i understand from others and from what i read here:

Question: What if my Excel workbook just has rows of data I typed in?**

Answer: A gateway is not required. You only need to install and configure a gateway if your workbook uses Power Query or Power Pivot to query and load data to the data model from a supported on-premises data source

 

From <https://powerbi.microsoft.com/en-us/documentation/powerbi-personal-gateway/>

@jmdh A couple things:

You are correct, if the Excel file is stand alone in the One Drive it does not need a gateway, because you aren't refreshing any data in the file from an on premises source.

 

To your previous post:

When you reference the Excel file in the One Drive location you need to use the external facing link. Details here

 

 


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

Thank a lot: this is the first time that i can have a refresh working without a gateway as it should.

The link you sent was very useful since i had tried using a Onedrive link before as a source of data , but without success, when with your link it was a pleasure.

 

So i am left with : can automatic refreshes work when i use the same data file in PowerBI Desktop , but i access it with Get Data Excel insteadof Get Data Web  (that has never worked for me)

 

 

@jmdh I guess I don't understand the question. The link I provided was for the Desktop, it should work either way... 


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

One more related question: Do you feel BI Service can model the Cloud Based Data as robustly as Desktop?  Maybe that question is too open ended and relative - let's try it again:

 

Right now I don't think of the need to use Desktop as being due to the type data source but rather due to a need for more elaborate data modeling (Query Editory) regardless of the data source.  Or to say this another way - that Service doesn't offer as comprehensive modeling.  Is that much true?

 

So IF it a Cloud Based Data source and IF one must use Desktop to get the modeling right in order to produce the Visualizations - then it still is a little unclear to me once you move the PBI Report up to Service - - how does that refresh get automated.....

www.CahabaData.com

Like you i believe Desktop is needed only to do advanced modeling and to save time by accessing "local" files stored on Onedrive or elsewhere.

 

As to the refresh, it is true that it works well in all cases once a gateway is installed, but i would like to avaoid it since i am only using Excel files and i am still struggling to make it work.

jmdh

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors