Updating Stored procedure is not adding new columns

[AG_PBI]

Hi,

I created Power BI desktop reports as follows:

DFGEN2 (Get the source data) -> Warehouse (Table) -> Created Stored Procedure ->Data Pipeline to run the procedure daily.

While generating new dashboards, I realized I will need to retrieve additional columns. I updated the stored procedures which drops/creates intermediate tables and finally uses Union to join all intermediate tables. I added the additional columns in all intermediate tables which then reflects in final table being used across all dashboards. When I run the drop/create table queries manually, it generates the tables with newly added columns. But the daily refresh of the procedure (set via data pipeline) does not include the newly added columns. What needs to be done for the systematic refresh of procedure to refer to the updated query and include the additional columns too?

[AG_PBI]

Thanks VahidDM. I am using data pipeline -> Stored Procedure activity (https://learn.microsoft.com/en-us/fabric/data-factory/stored-procedure-activity) by connecting to the warehouese that has the stored procedure. I deleted the original Stored Procedure activity and set it up again. Now the new columns show up. But the refresh is not getting the data on daily run. As December started, I am expecting to see December numbers. But those are not getting updated with the daily refresh/run of the data pipeline. Again, when I run the stored procedure manaully, those numbers refresh correctly. It seems that it is keeping data in cache and does not actually refresh it on daily run. You have mentioned -

• Clear any cached results if your pipeline or database uses result-set caching.

How to check if it is setup to use result-set caching? How to ensure that it does not cache the result and refreshes it on the daily refresh that I have already scheduled?

[VahidDM]

Hi @AG_PBI 

When you update a stored procedure to include new columns, but your data pipeline doesn't reflect these changes during the scheduled refresh, it's likely because the pipeline is using cached metadata or hasn't been updated to recognize the new schema.

Here's what you need to do:

1. Refresh Metadata in Your Data Pipeline:

   • In your data pipeline tool (e.g., Azure Data Factory), locate the activity that calls the stored procedure.
   • Refresh or re-import the schema to ensure it recognizes the new columns.

2. Update Mappings in Data Flow (If Applicable):

   • If you're using a data flow with column mappings, update the mappings to include the new columns.
   • Ensure that the sink (destination) schema is updated to accommodate the additional columns.

3. Redeploy or Publish the Pipeline:

   • After making the changes, redeploy or publish your pipeline so that the updates take effect during the scheduled runs.

4. Verify Permissions and Caching:

   • Ensure the account running the pipeline has the necessary permissions to access the updated stored procedure.
   • Clear any cached results if your pipeline or database uses result-set caching.

5. Test the Pipeline Manually:

   • Run the pipeline manually to confirm that the new columns are being included.
   • Check the output to verify that the data reflects the updated schema.

By refreshing the metadata and updating your pipeline to recognize the changes in your stored procedure, the scheduled refresh should include the new columns moving forward.

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

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube