Deployment of Power BI reports into UAT and prod environment - Connection string change

[shamsuddeenvp]

Dear,

How can we change the data source of the reports dynamically based on the Dev/UAT/Prod connection string. We cannot change the data source setting and refresh the report being in the dev server and changing the connection string to prod, because of the connectivity will not be available form dev to prod.

I tried entering the server and connection details of the UAT opening datasource settings from power bi, however I cannot refresh the data since there is no connectivity from dev to UAT. When I publish to power bi service still it is mapping to dev server only.

How are we managing the deployment in to different environment? we cannot develop the dashboarding logging into prod server and cannot install anything in prod.

It should be doable from power bi service

Thanks for your help.

Br,

Shams

[cnschulz]

I believe using parameters for this purpose is next to useless. If I have to edit the pbix to change a parameter, I might as well just change the datasource. We have constructed dymanic server and database names using stored procedures. They analyse the originating machine name and return the server/database name for the requested environment. These are embadded in every dashboard as scalar variables. A dashboard can specify the environment it wants or by default it will choose based on the originating servername. This is all configured via SQL tables

[Greg_Deckler]

Typically this is done using paramters in Query Editor but I do not believe you can do this in the Service. You edit the parameter and deploy to each environment.

[shamsuddeenvp]

Thanks @Greg_Deckler for the reply.

The problem that we develop the reports in dev environment, which doesnt have any connectivity to prod environment. So even if you enter prod details, it has to refesh the power queries before saving and deploying to power bi service, and it will fail.

Also I think there is a limitation of using parameters that we cannot use schedule refresh.

Br,

Shams

[Greg_Deckler]

Let me make sure I understand your setup. Are you using Desktop at all or just the Service to develop your data model and reports?

Generally the way that I have seen this done is this:

1. Develop report in Desktop and publish to Service in Dev environment. There is a parameter that controls SQL Server name, for example.
2. Take a copy of the PBIX file and move to production environment
3. Change parameter
4. Save
5. Refresh data
6. Publish to Service (production)

[shamsuddeenvp]

Thanks. Yes, we are using power bi desktop to develop the reports. We have restrictions to install any development s/w in prod. If we can move the file in production I think we can change the connection and all in data source settings itself without parameters.
Here https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/

One of the comments it is mentioned that if u r using parameters, scheduled refresh is failing, I dint test

Br
Shams

[v-shex-msft]

Hi @shamsuddeenvp,

Current, power query custom functions still not available on power bi service, I think your issue may related to it.

Regards,

Xiaoxin Sheng

[shamsuddeenvp]

thanks @v-shex-msft

My issue is that I can install power bi desktop only on dev server, where I cannot access the prod database to change the connection string and also to load the data before publishing to power bi service.

My requirement is that, I should be able to view the reports on top of prod database and should be able to refresh the data.

PS: I have power BI gateway is running i a server where it has connectivity to prod database.

Br,

Shams

[v-shex-msft]

HI @shamsuddeenvp,

For your scenario, you can refer to below step if it suitable for your requirement. (prerequisite: all datasource has similar table structure and relationship)

1. Create report on current device.
2. Install the gateway and make sure it can connect to actual data source and sign in your power bi account.
3. Change datasource connection string to actual datasoruce before publish to power bi service. (after this operation, current report will not available)
4. Add actual data source to current gateway.
5. Use gateway which contains above datasource to manage the report refresh.

Regards,

Xiaoxin Sheng