Data source change in service

[Yoga]

Hi there,

I have developed a report and data model in Power BI desktop in Development machine using Direct Query option and trying to deploy in Test server for QA testing the PBIX file but connecting to Test database. Both environments are Oracle with same schema.

In the Test server, I have installed on-premises Gateway with Test Database credentials. When deployment team imported the PBIX file in a new app work space following error mesaage indicating the data source (development machine TNS name) can't be accessed.

"This data source can't be accessed by a data gateway: Server: xxxxxx" Database: Oracle

Trying following,

In Development machine, develop report and save PBIX file in source control. We have Test, Stage and Prod servers where we move the file as per process and use respective file to import to a Power BI service. I cannot go back to change the data source in power BI desktop to test and give it to deployment team.

In Test server, installed Gateway with Test Database connection. Import the PBIX file (which has Devepment Database connection info) in Power BI service. Thought of same pbix file now connect to test database where the reports will show Test data. But errored as above mentioned.

In Stage server, installed Gateway with Stage Database connection. Will import the PBIX file here for User Testing
In Production server, install Gateway with production Database connection. Will import the same PBIX file here so user can use this.

Quetion:
1. How can I point DB connection of the PBIX file to respective DB in its app work spaces? Means, In Test app workspace, reports should get generated from Test DB data. In Stage app workspace, it should ahow stage data and same for Production.

2. Is this the right way to stage my PBIX file (creating different app workspaces (Test, Stage and Production) and connect to respective database in that worspaces? Or please suggest professional standard to follow for deployment.

Thanks,
Yoga

[SSKLK]

Hi,

I'm working on Power BI very recently. I generated a report using power BI desktop.

Get Data -> Sql Server (DB in my local machine)

I published the report in Power BI server and used on premises gateaway to refresh with up-to-date data. I worked as expected.

on premises gateaway -> used Sql Server as DataSource

Now, i want to connect DB in another server to this report. DB structure is same. instead of local i want to connect production DB.

How to do it? have to install on premises gateaway in production server? or What are the things i need to do for this change?

[v-shex-msft]

Hi @Yoga,

AFAK. you can't direct modify your datasource in power bi service. 

>>1. How can I point DB connection of the PBIX file to respective DB in its app work spaces? Means, In Test app workspace, reports should get generated from Test DB data. In Stage app workspace, it should ahow stage data and same for Production.

For your situation, you can change connection string before publish, then choose the specific workspace to store the reports with match datasource.

Steps: (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 string 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.

>>2. Is this the right way to stage my PBIX file (creating different app workspaces (Test, Stage and Production) and connect to respective database in that worspaces? Or please suggest professional standard to follow for deployment.

Actually, after finish publish reports, you can't modify datasets of published reports.(you can only edit the authorization certificate)

Regards,

Xiaoxin Sheng

[Yoga]

Thanks For the reply Xiaoxin.

Once connection information is set (which is Development connection), I need to check in the file and hand over to Deployment team. So I am unable to change the connection to Test DB in the PBIX file and publish. Also this will go to Stage and Produntion environments, so I cannot modify my PBIX files for that.

Trying solution as below as of now,

1. Have the DNS name same in Dev, Test, Stage and Prod server.

2. PBIX file connects through DB through the Server name (TNS name), but in each workspace different Gateways data set connects to different DBs with same server name. So seems it works. I am still working on it.

Ex:

TNSNames.ora in Dev server

TNS_SALE

{

...

server=xxxx

}

Gateway installed in this server having server as TNS_SALE 

TNSNames.ora in Test server

TNS_SALE

{

...

server=xxxx

}

Gateway installed in this server having server as TNS_SALE 

Service:

I have Two app worspaces, Dev and Test. Each connects to its respective gateway. Since the PBIX file is having same Server name, it will connect to respective DB.

Please suggest will this work, or any other solution?

Thanks,

Yoga

[v-shex-msft]

HI @Yoga,

It is impossible to change it on power bi service. If you want to change the datasource, you need to modify the pbix file.

>>So I am unable to change the connection to Test DB in the PBIX file and publish.

You can try ask other users who has modify permission to download pbix file from power bi service, then update the datasource connection string, publish to replace the current file.

Regards,

XIaoxin Sheng

[Yoga · Accepted solution]

Thanks XIaoxin for reply.

We maintain one TNS Name in all Dev, Test, Stage and Production servers. Since I use the same TNS in PBIX file, We are able to move the files to all servers without any change as expected. So no need to change in power bi services running in different servers.

Thanks,

Yoga