Forum Discussion
No AlwaysOn server
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?
Thanks for any guidance you can give me!
WhiteBusLady ,
"Change the published report Data Sources from local offline files/databases to the online"?
How do you change database to the online? When you combine onedrive excel and on-premises SQL data source in a single PBIX file, gateway is required to refresh the dataset in Power BI Service.
Regards,
Lydia
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>?