Using Rest API to change a Report/Dataset to use a different On-Prem Enterprise Gateway

Question

I have a developed a PowerBI report in PowerBi desktop, which is sourcing it's data from an on-prem Analysis Server.

  In the PowerBi service I have created a workspace, I have then installed a on-prem ( enterprise ) gateway, with a datasource which points to the same Analysis server,then I publish my report from powerbi desktop which it then publishes into the workspace and automatically it is wired up to the gateway and all works good and well.

I want to take this pbix file and upload to a the production workspace which has a different gateway and a datasource which points to our production analysis server.

So I'm doing the following:

Uploading the Pbix to the production workspace using the powerbi powershell commandlet: New-PowerBIReport   This goes fine of course it's not wired up to anything which is expected.

I then trying to switch the gateway on the dataset for the report using this rest API call  

https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/bindtogatewayingroup  

Specifying the GatewayID I want it to bind to and the Datasource Id of the production analysis server.  

Which then comes back with a error saying: DMTS_CanNotFindMatchingDatasourceInGatewayError  

Which kind of makes sense because the report will be looking at the dev analysis server and that won't be configured on the prod gateway.

 

So I thought I wonder if I could change that on the report  using the API, and I can by using:   https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/updatedatasourcesingroup  

So using this I'm able to change the database name and server from development to Production, and verify the service has picked up this change.  

Then I thought I could then call the bindtogatewayingroup and specify my production gateway and production datasource but it still fails with the same message "DMTS_CanNotFindMatchingDatasourceInGatewayError"

After hours of research I'm coming to the conclusion that it's not possible to change a report from one gateway to another, unless the exact same data source exists on both gateways, which seems a bit pointless.

Is this a limitation of the API or am I doing something wrong???

Answer 1

You can use connection-level parameters to define the datasource (server name and database name) and after publishing the report, to use the REST API to update their values.

Define two text parameters in your report, let's name them ServerName and DatabaseName. Set their values to point to the desired datasource. At this point when you are adding a datasource to your report, you can specify the datasource using these parameters:

If you need to amend an existing report, replace server and database names in report's M code with parameter's names. The code could look like this:

let
    Source = AnalysisServices.Database(ServerName, DatabaseName, [TypedMeasureColumns=true, Implementation="2.0"]),
    Model1 = Source{[Id="Model"]}[Data],
    Model2 = Model1{[Id="Model"]}[Data],
    #"Added Items" = Cube.Transform(Model2,
        {
            {Cube.AddAndExpandDimensionColumn, "[OrdersData]", {"[OrdersData].[OrderDate].[OrderDate]", "[OrdersData].[OrderYear].[OrderYear]", "[OrdersData].[Quantity].[Quantity]"}, {"OrdersData.OrderDate", "OrdersData.OrderYear"}}
        })
in
    #"Added Items"

Note that the first two parameters of AnalysisServices.Database are not literals, but the parameters defined above.

When you publish this report to Power BI Online, it will look for the gateway depending on the current parameter values (e.g. the one to your development server). But you can use Update Parameters In Group REST API to redirect this report to another datasource (e.g. your production server). If your gateway is properly set, you shouldn't do anything else and it will be used automatically. Probably you will want to refresh your model after that. With PowerShell you can do something like this:

Invoke-PowerBIRestMethod -Url 'groups/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/datasets/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/UpdateParameters' -Method Post -Body '{
  "updateDetails": [
    {
      "name": "ServerName",
      "newValue": "MyProductionServerName"
    },
    {
      "name": "DatabaseName",
      "newValue": "MyDatabaseName"
    }
  ]
}'


Invoke-PowerBIRestMethod -Url 'groups/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/datasets/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/refreshes' -Method Post

Hope this helps!

Answer 2

Nowadays there is an API method for this type of task, allowing to alter demands for live connections to Analysis services made via data gateway: https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/updatedatasourcesingroup#updatedatasourceconnectionrequest

call it with a body like this:

{
  "updateDetails": [
    {
      "datasourceSelector": {
        "datasourceType": "AnalysisServices",
        "connectionDetails": {
          "server": "server name mentioned in your pbix file",
          "database": "original database name used in pbix"
        }
      },
      "connectionDetails": {
        "server": "a new server name that is used on the gateway",
        "database": "potentially, another database name"
      }
    }
  ]
}