I have a scenario which I would expect to be pretty typical and would like to know how others have solved this. I am creating a Power BI solution for a client whose source database is on a 3rd party hosting service. I have no way to connect to this database from my machine, use VPN etc. to get a connection to it. To be able to connect to the data and create data model and reports I have created a copy of the database which I have installed locally (SQL Server). When I publish to the service I of course want the data connection to be to the production database and not to my local copy, so I have created paramters for the sql server instance and the database name that I then can alter in the service once the desktop model is published there. This works fine but it requires me to change these parameters every time I publish to the service. Is there any way to have this done automatically or is there another way of doing it? Could using data flows be an option?
What you need is a “location aware report” so you could switch automatically between datasets. Unfortunately I don’t think there is an option like that.
The only way to switch between datasets you already using it. One other way would be publish a new idea on the Microsoft site: https://ideas.powerbi.com/ideas/
One other idea, but not tested, if you want to change only the server name (assuming you use the same DB name) - is to create an entry in your local hosts file so your lab machine will have the real SQL server name. This way, as your report is Power BI Desktop the name will be resolved to your testing machine and after you publish it, you don’t have to change anything, because Power BI service will resolve the name to the real machine.
Changing the hosts file is an interesting approach but will only work if everything is the same e.g. the same instance and database name in the case of a SQL Server db. I am surprised that there is no better way to solve this issue. After all it must be very common to not have direct access to the database from your dev machine.
Indeed to use this approach you have to recreate the environment exactly as the original.
Probably Microsoft considered that should be enough that the person who can publish the report will have access either to the parameters in the Power BI service, either to the “real” data source before publish.
Another approach is that the 3rd party hosting service can make use of cloud services. Views to contains all required information should be created and connection parameters that will contain username, password and port number be used to connect via the cloud services. You can use the view to connect to your power bi and create your model.
With this approach you don’t need to have the production database on your local system and you don’t need to change parameter.
Hi @Frede , did the response provided by the contributors help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
Hi @Frede, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
