cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ta5656
Helper I
Helper I

How to change the Data source connection from 'SQL Server database' to 'Azure SQL Server database'

Hi,

 

I need a help regarding changing of data source connection from SQL Server database to Azure SQL Server database. Litte background, I have created a Power BI report using the 'SQL Server database connection' but I want to change it to the 'Azure SQL Server database', because I have same tables on Azure. So, is there a way we can do this on the PowerBI desktop?

 

I want to change to this 'Source' connectionI want to change to this 'Source' connection

Thanks!!

4 REPLIES 4
SNawalKishore1
Regular Visitor

Hello @ta5656 ,

 

 

I hope this will help you.

 

 

Step 1. Create new AzureDB.PBIX with Azure SQL DB Connection.

Step 2. Open SQLDB.PBIX with On-Prem SQL Server DB Connection, then go to Edit Query > Advance Editor and copy all Applied Steps "Except" Source (which is on-prem sql server).

Step 3. Go to AzureDB.PBIX, then go to Edit Query > Advance Editor paste all steps "Except" Source(which is New Azure SQL DB).

Step 4. Repeat Step 2 & Step 3 until all tables replaced with Applied steps.

 

Step 5. Then switch back to old PBIX, copy the all visuals from SQLDB.PBIX and Paste to AzureDB.PBIX as all the Tables & Column Names are identical.

 

Please try and let me know.

 

Cheers,

Nawal
+91-9866755720 | Power BI Consultant

https://www.linkedin.com/in/snawalkishore/

Nawal, I appreciate your continues help on this issue. As per you steps below, It's working partially that means I able to copy the visuals from one report to another report - which is really a good thing, but I have to create the 'Measures' once again on the Azure connection report. 

 

Thanks!!

SNawalKishore1
Regular Visitor

Hello @ta5656 ,

 

Very Simple.

Open Power BI Desktop > Open Edit query > Left side in Queries Pane> any table > Right side in Applied Steps > Double click on Source > Change Server name and database name and Click OK.

 

Source_SQL Server to Azure SQL.PNG

 

Cheers,

Nawal
+91-9866755720 | Power BI Consultant

https://www.linkedin.com/in/snawalkishore/

Thank you! Kishore, for your reply. I did that, but as per my testing, the way the connectors work seems like different because when you select 'SQL Server database' as your source at the first step and you deploy the report to service you can schedule the 'data set' through the gateway. But, if you select the 'Azure SQL Server' database at the first step by default it will automatically refresh every 1 hour on the Power BI service because I selected the 'Azure SQL Server' as my connector at the very first step. So, if you change the database name it will work but it won't bring the properties of the 'Azure SQL Server' like 'Cache' dataset refresh, I'm looking for changing the 'SQL Server database' to 'Azure SQL Server' at the very first step.

 

Thanks!!

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.