This is an extension of 02/09/2019 post "No Gateway"
I'm trying to provide dynamic PBI reports for a small group of people without an intranet.
There is one developer (myself) who manages SQLServer data and creates PBI datasets and reports from both SQLS and Excel spreadsheets maintained in a shared OneDrive account. There are three people who contribute new and/or updated Excel worksheets, and use the PBI reports.
The four of us work for different companies, are geographically separated, and use a mix of Windows and Mac laptops, Android and iOS phones, etc. None of us have local networks. None of us have a local always-on machine. We use Office365 and OneDriveForBusiness to work together.
Our shared data creation/maintenance is working. PBI reports have been a hit. I develop the reports in PBI Desktop while working mostly offline. I don't usually need live data for design. I can work off the local copies of the OneDrive files, and a local backup of the online SQLS database. I keep my pbix files in a OneDrive folder, and eventually publish them to PBI Service. My scheduled refreshes work through a personal PBI gateway when my laptop is on and awake and connected to the internet. Which isn't very often.
That is a real problem. We read that it is super easy to base dynamic reports on OneDrive files because OneDrive and PBI Service are both online (and both controlled by Microsoft, as is Office365). But even though all data and the .pbix files are online, the data still seems to need to go through a PBI gateway on my laptop.
I have now set up OneDrive and a standard PBI gateway on the [virtual hosted cloud always-on] online server. But my .pbix files are created on my laptop using laptop copies of the OneDrive data. Do I now have to put PBI Desktop on the server, manually open every OneDrive pbix file and change the report data sources to addresses matching the server perspective/file structure? Do I have to do this every single time I want to publish an enhancement or correction to each report file?
This is getting way too complex. I have 1) four people with laptops; 2) a shared OneDrive account for all data and pbix files; and 3) a cloud web server with SQLServer data and a standard PBI gateway. What is the simple way to connect published reports with updated data under this scenario? The key: without going through a laptop for ongoing data refreshes
If I was developing in PBI desktop on the cloud server all would be fine. But I'm not. I'm developing off-line. No choice, several months of the year I'm in the mountains where I must drive out to get internet. And I wouldn't develop on a Production server anyway.
Do you combine Azure SQL database and OneDrive files in a single Power BI report or do you combine on-premises SQL Server database and OneDrive files in a single report? For the former case, gateway is not required, but you would need to use web connector in Power BI Desktop instead of Excel connector to connect to OneDrive excel files, in this case, you can change data source in Advanced editor of Power BI Desktop.
For the second case, gateway is required, and you would need to install gateway on a machine which is always on line and connecting to Internet to make refresh works smoothly. Also ensure that you are able to access your data sources from gateway machine.
Community Support Team _ Lydia Zhang If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Your 2nd paragraph applies. And I am now spending money for an always-on online server. My subject should now say "no LOCAL Always-On server". Now that I have "rented" a hosted cloud server, how do I use it?
Below is an outline of what I have done. All is mostly working except the final step. I need the details for Step #6.
Gather data, build model(s), set up online (shared) data collection process based on Excel and SQLServer. (Maintain Excel files in shared OneDrive folders, SQL Server on web server.)
Using local copies of sample data and PowerBI Desktop, work offline to develop datasets and reports. (Store .pbix file in shared/sync'd OneDrive folder that remains accessible offline.)
Publish reports to PowerBI Service
Add Standard PowerBI Gateway to web server. Schedule twice-daily refreshes of SQLServer data.
Schedule Excel/OneDrive data refreshes (and figure out why hourly refreshes are not working - will address that separately)
Change the published report Data Sources from local offline files/databases to the online, dynamically updating Data Sources
I need a simple method for promoting reports from Desktop testing to PBIService testing to PBIService production. In SQLServer SSIS I would set parameters for the Data Source Connection Strings and use SSIS Environments and Events to trigger the proper settings for each phase. I do NOT want to have to open up an already-tested report and manually change connectors. Too error-prone. There needs to be a consistent hands-off method. I'm sure I'm not the only one insisting on this, I just can't find the right guidance on how to approach it. Variables? Parameters? Triggered by what? Or a different kind of universal Connector rather than Excel and SQL Server?
This has evolved.... The current situation is that I DO have a gateway. I have placed a standard gateway on a hosted cloud server. However, that is not where initial development happens.
I do all my development on a laptop that is only online occasionally. I installed a personal gateway on the laptop for test purposes, but obviously it cannot be used for production because is it not usually online.
So here is the problem: I develop offline. When I am satisfied with my results I post the completed .pbix file on OneDrive, where the most current shared data files live. At that time I have to manually change all the Data Source connections from connection strings that are appropriate from the perspective of my laptop, to connection strings that are appropriate for the perspective of the online server with the standard gateway.
Changing the connections manually is both risky and time-consuming. How can I utilize some sort of parameter, variable, or other method to consistently change the connections every time I post a new or updated .pbix file from my offline test system to the online production system?
(Notice there is no network. My laptop is not connected to the online server, except occasionally through Remote Desktop Program. The report consumers are likewise using independent laptops. We all work for different companies. Office 365 is supposed to function as a faux "network" for us.)
Comment: I'm still wondering what I'm missing. It can't be this hard, lots of people develop in places other than their production server. I'm used to SQL Server SSIS, which is often overly complex. I do now have a gateway. But it is not really on-premise, rather it is in-the-cloud. Is that a major problem? Isn't that supposed to be a good thing, not a bad thing <grin>?