cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nleuck
Post Patron
Post Patron

Database refresh breaking stored procedure

Hello,

 

I have a question concerning stored procedures being used in Power BI desktop and refreshing the SQL database that they are imported from. I imported a stored procedure to the Power BI desktop from our SQL server development database. Once I finished my reports they were then sent up to our workspace in the Power BI service. My manager wants to refresh that development database. If he refreshes the database would that cause my stored procedure in Power BI to break and cause errors on the visuals in the Power BI service?

 

Any advice or information on this would greatly appreciated!

 

Thanks for your time.

 

 

1 ACCEPTED SOLUTION
prateekraina
Memorable Member
Memorable Member

Hi @nleuck,

 

As per my  understanding, you have used SQL Stored Procedure(SP) to source data from your database into Power BI which is absolutely fine.

Now comes the question "What happens if you modify the Stored Procedure or refresh your SQL database"?

Untill and Unless your changes do not alter the schema which Power BI is consuming through your SP, it will not break your visuals or anything else.

 

So feel free to refresh your SQL database.

 

Cheers !!

View solution in original post

5 REPLIES 5
prateekraina
Memorable Member
Memorable Member

Hi @nleuck,

 

As per my  understanding, you have used SQL Stored Procedure(SP) to source data from your database into Power BI which is absolutely fine.

Now comes the question "What happens if you modify the Stored Procedure or refresh your SQL database"?

Untill and Unless your changes do not alter the schema which Power BI is consuming through your SP, it will not break your visuals or anything else.

 

So feel free to refresh your SQL database.

 

Cheers !!

View solution in original post

Hi Prateek,

I have a question. I am using a Stored Procedure inside Power BI Desktop using Import Mode. I created my visualization and everything works fine.

Now I added 2 more fields in my Stored Procedure and want to use them in Power BI. I tried Refreshing in the Query Editor but could not see the 2 new columns added to power BI.

Please can you advise?

Thanks

Sophia

Hi @scorreia,

 

This is weird. I tried on my machine and it works fine. Adding and Deleting columns in Stored Procedure are well detected by Power BI.

Anyway, try below steps. It might help:

 

1. Go to Query Editor, Open settings of the first step of the Power Query. Usually goes by name "Source" if you have not renamed.

2. Turn On "Enable SQL Server Failover Support" and click OK. If it is alreadt Turned ON then Turn it OFF. 

3. Now Refresh and Check. It should work.

 

Prateek Raina

Hi Prateek,

 

I have a dashboard with 10 reports in Power BI, the data is coming from SQL strored Procedures through import mode. 

The SQL Strored procedures will get updated with latest data when passing current date as input parameter to them, I want the same latest data in Power BI.The data in Power BI should always be the latest data like in the Stored Procedure could you please help on this.

I tried incremental refresh but it stores data from last refresh which I dont want.

I also tried calling function in Power BI but its always invoking a new table and my report is getting affected.

Please help

Hi Prateek,

This is getting refreshed automatically. My bad as I was updating the Stored Proc in a Test Database and not in the one where Power BI was pointing to.

 

Thanks.

 

Regards

Sophia

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.