How to switch between datasources using gateways?

[Anonymous]

Hi all,

What I want to do:

I want to be able to switch my reports data between gateways because I have one gateway with test data and another with actual production data and I want to be able to switch between them using the refresh schedule button in the dataset section.

What I did:

Install the gateway (G1 and G2) on both machine. Both DB (SQL Server) have the same name and structure. Only the table content is different. I added both gateways in the manage gateways menu. Each one in his own cluster and both have the "Connection successfull" message at the top.

Then, I created a report in Power BI desktop, using a dataset from G1 and published it.

What is happening:

If I go in the dataset section, the click on the schedule refresh button, in the gateway connection section, I see both gateways but is says that G2 is "Not configured correctly". Clicking on the arrow shows: SqlServer{"server": "G1.xxx.local", "database": "name_of_database"}

If I click on the "Add to gateway" link, it will go back to the gateways and prefil the fields but WHY is it filling the server URL from G1? What is the point of creating another gateway if both are using the same server?!?

[v-yuezhe-msft]

@Anonymous,

Please check that if the server name and database name of the SQL server data source are same in the PBIX file and in G2 gateway. 

You can post a screenshot about data sources of Power BI Desktop and G2 gateway here for us to analyze.  In Power BI Desktop, please go to File -> Options and settings ->Data source settings  to check all the data sources of the dataset,

Regards,
Lydia

[Anonymous]

Hi,

I think my understanding of gateways was wrong. I thought it was possible to switch between (different) datasets by selecting another gateway, but now I see that to switch between gateways, they must point to the same server/database. 

Is that correct?

Then, How can I solve my initial problem: using the same set of repports (pbix file) multiple times for different databases? For example, if I have a base file that I want to use for each of my clients (each one in its own workspace). I tried to duplicate/connect to the new DB/delete old table and rename new ones to match, but it gets very time consumming as the number of tables increase.

[v-yuezhe-msft]

@Anonymous,

Another option is to define database/table parameters in your PBIX file and create a Power BI template.

https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/

Regards,
Lydia

[Anonymous]

Thanks Lydia, I will look into this!

[Anonymous · Accepted solution]

Actually, I managed to get my desired result via the API by using https://api.powerbi.com/v1.0/myorg/groups/{{group_id}}/datasets/{{dataset_id}}//Default.UpdateDatasources and updating the server to point to the other one.