AWS Redshift - Update Connection Strings via API

[shravans]

Hello,

Is there any way/article which can help me to update all connections in datasets for AWS Redshift datasource using Power BI API.

Thank you.

[kiran_mahadev]

Newbie to Power BI.. Need guidance... 

Here is what we are planning to achieve:

• We have created reports using AWS Redshift as the data source. And, we can publish the PBIX files to the workspace using the Create Import API(Ref: https://msdn.microsoft.com/en-US/library/mt243840.aspx)
• We are trying to update the connection string on all datasets by using the Datasource API Set All Connections(Ref: https://msdn.microsoft.com/en-US/library/mt748181.aspx)

Need guidance on two aspects:

1. On a PBIX file we created, we were able to query and find the connection string in the datasource, we see a mashed up connection string that reads something like... Provider=Microsoft.Mashup.OleDb.1;Data Source=$EmbeddedMashup(blah-blah-blah-blah)$;Extended Properties="blah-bhaj-blah-blah-blah-blahblah-bhaj-blah-blah-blah-blah-blah-bhaj-blah-blah-blah-blah-blah-bhaj-blah-blah-blah-blah=" will it be possible to programmatically create the exact same connection string if we knew the connection parameters like the Redshift Server Name, Database Name, User Credentials (Username and Password)? if yes, can you please provide a reference.. This will help us create connection strings for Set All Connections API
2. We have Redshift dev and qa environments, we created 2 reports one pointing to the dev another to the qa environment. We then tried calling the Set All Connections API to interchange the connection strings on the 2 reports. It appeared that the API requests went through successfully, received a HTTP 200. But, when we login to app.powerbi.com and select the report under the workspace, we still get a prompt to update the data source credentials. What are we missing here? Is this the intended behavior of the Set All Connections API? Also, it appears that the dataset continues to retain the original connection string, using which the report was built despite a successful call to Set All Connections API.

[v-haibl-msft]

@kiran_mahadev

If you update the AWS RedShift credential credential manually in Power BI Service, can you do it successfully?

As far as I know, there is a limitation here. It’s when the customer is going through a load balancer. What happens is that the name they're accessing is the load balancer name, and this doesn't match the name on the certificate which is the database server name. Then we are not able to update the credential properly.

Best Regards,
Herbert

[bedasa]

Yes, we could able to change credentials manually on power bi service. What we need is, a sample connection string for redshift that Microsoft.Mashups.Oledb.1 provider uses. For example, bellow connection string used for azure sql db :- {"connectionString":"data source=MyAzureDB.database.windows.net;initial catalog=Sample2;persist security info=True;encrypt=True;trustservercertificate=False"}

Power bi apiary

Is there something similar to the above for amazon redshift?

[kiran_mahadev]

Hi Herbert,

As @bedasa mentioned, we are not seeing any issue when updating the credential manually in Power BI Services. And, this is not an issue for us at this stage.

Let me try to rephrase the question -

1. Let's say, we developed a certain report by pointing to AWS Redshift on Development environment. We are trying to automate publishing the developed report to say QA or Staging or Production environments; which means the published report will have to point to AWS Redshift on QA or Staging or Production environments.
2. Today, when we upload a PBIX file, the Power BI Service prompts us to provide ONLY the credentials. It doesn't allow us to change the server information. And, what we want is the ability to change or point to a different server. Any help here is greatly appreciated.
3. This simply means, we'd have to create reports specific to each environment and this is not something we want.
4. We were under the impression that Set All Connections API would come-in handy for us. But, we don't know how to produce a connection string that will help point the report to a different AWS Redshift database.
5. So, we decided to take a crude approach, Create a report each against QA or Staging or Production AWS Redshift. Then query and retrieve the connection string for each reports' datasource. This gave us mashup connection strings' that look something like - Provider=Microsoft.Mashup.OleDb.1;Data Source=$EmbeddedMashup(blah-blah-blah-blah)$;Extended Properties="blah-bhaj-blah-blah-blah-blahblah-bhaj-blah-blah-blah-blah-blah-bhaj-blah-blah-blah-blah-blah-bhaj-blah-blah-blah-blah=", but if you observe the connection string, you'll see Extended Properties... this is all encrypted information, not sure what information the encrypted string holds. And, in case we create different looking reports, the Extended Properties come out completely different which simply means we cannot have a standard mashup connection string. And, this crude approach won't fly.

So, what information we seek is how to point a report developed against a certain AWS Redshift environment point to a different AWS Redshift?