cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KoenVdB
Advocate I
Advocate I

Snowflake update datasource API

Hi,

we have created some PBI reports that are connected to snowflake Dev environment. The connection has been made during get data and using the "Snowflake connection"

KoenVdB_0-1667546394373.png

 

 

We have filled in the Server and warehouse.

During deployment we want to update the servername to connect the report to another snowflake environment ACC or PROD.

 

There is an API to update the datasource (https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/update-datasources-in-group

we use that for other connections to update like Sql, Odata,..

but for snowflake we always receive an error:

Invoke-RestMethod: {"error":{"code":"InvalidRequest","message":"Parameter UpdateDetails is missing or invalid"}}

 

we're sending this updatedetails with the API call, it looks like they are oke:

 

'updateDetails': [{
                    'datasourceSelector': {
                        'datasourceType': 'Extension',
                        'connectionDetails': {
                        'path': 'XXX.west-europe.azure.snowflakecomputing.com;POWERBI_WH',
                        'kind': 'Snowflake'
                        }
                    },
                    'connectionDetails': {
                        'path': 'YYY.west-europe.azure.snowflakecomputing.com;POWERBI_WH',
                        'kind': 'Snowflake'
                    }
                    }]

 

How can we update the connectiondetails for a Snowflake connection? because you can also not use parameters in the connection.

 

Kr,

Koen

 

1 ACCEPTED SOLUTION

That's weird but it look like you have an error in your code that make the request. if you want to make it easier for the text in the parameter try entering data in a new table with two columns. One of them is going to be the name of the environment and the second column the server name. If you need a third one for db name you can also create it. Then create a parameter that will filter the table with environment name.

Now for regular tables just take the first row in the entered data table to complete de server.

That way you can add new environments or change them dynamically in a more secure way.

I hope that make sense


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

View solution in original post

3 REPLIES 3
ibarrau
Super User
Super User

Hi. I usually recommend using parameters to change the data source. Just like the examples on youtube that they use to change it manually, but using the API
https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/update-parameters-in-group

The issue updating the sources directly is that every sources has different structure for the body. That can make the request quite difficult. 

If you build on your reports a hidden dummy table with prod and dev environment that will filter by a parameter you can even use the same code/script for every report. You can take it to the next level.

I hope that helps,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

You are not able to update the data source.

When you use parameters to configure the connection. You are not able to update the paramaters through API calls. The data source is snowflake.

Parameters:

KoenVdB_0-1667808857024.pngKoenVdB_1-1667808897944.png

 

KoenVdB_2-1667808929278.png

 

Even in the Power Bi portal, the parameters cannot be edited, the fields are disabled.

 

 

That's weird but it look like you have an error in your code that make the request. if you want to make it easier for the text in the parameter try entering data in a new table with two columns. One of them is going to be the name of the environment and the second column the server name. If you need a third one for db name you can also create it. Then create a parameter that will filter the table with environment name.

Now for regular tables just take the first row in the entered data table to complete de server.

That way you can add new environments or change them dynamically in a more secure way.

I hope that make sense


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Helpful resources

Announcements
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.