locked
Stored procedure modification breaks data refresh RRS feed

  • Question

  • Hello All,

    I have a PowerPivot model that is based off of a number of views and stored procedures on SQL Server. The procedures and views work fine, but I'm at a point where the business users have requested modifications that require changes (additions and deletions) of fields in these views and procedures. The views are easy to modify (I wrote select statements and selected field names rather than SELECT * ), but tedious since I have to write out all field names and do it for each table based on a view.

    The procedures are giving me more trouble, since the columns are not defined in the SQL calling the procedure. (Just procedure_name parameter1, ...). If I remove a column from the result set of the stored procedure, refresh fails for the table fed by that procedure. When adding columns to the result-set, the new columns are not available after a normal refresh. I have to open the table itself and view the table properties. Re-saving the SQL calling the procedure seems to be the trick for getting PowerPivot to recognize the new columns. These procedures are used in multiple workbooks. 

    The result of this is that I have to manually update each table in the model when I make a change like this. These views and procedures are used in multiple workbooks, making maintenance of this sort a chore.

    I am using a workaround found here (http://social.msdn.microsoft.com/Forums/sqlserver/en-US/356cb739-37bd-4b43-a202-5b40ae171919/powerpivot-excel-2013-crashes-after-column-rename-in-source-datatabase?forum=sqlkjpowerpivotforexcel) to delete columns from the stored procedure-fed tables. I manually delete the old column in PowerPivot before deleting it from the procedure.

    So I am able to complete my work, but in a very circuitous manner. Is there some better way to update these tables and add/remove columns from the model? 

    Monday, June 23, 2014 7:27 PM

Answers

  • I don't think you're going to find a better way. Versioning may help, by which I mean creating different views/procedures with versioned names (e.g. vwGetInfoV001) or some such thing then cloning the spreadsheets with the current version name available in a documentation tab. At least that way you can gracefully upgrade without simply breaking the old while waiting to get all spreadsheets upgraded to the new.
    Tuesday, June 24, 2014 1:47 AM