Forum Discussion
Calling Amazon Redshift stored procedure from power bi
Hello,
I have a stored procedure in Amazon Redshift which accepts a parameter and returns refcursor.
This refcursor has a set or rows based on parameter values.
I want to use and call this stored procedure from Power BI in direct query mode so that I can provide the input parameter values and get the data back.
But I am getting different syntax error (please see attched screenshot) when I am calling this stored procedure using below code.
begin;
Call myschemaname.myprocname('1-Jan-2024','1-Jan-2024','myRefCursor');
Fetch all from myRefCursor;
End;
This block runs fine in DBeaver or any other editor tool, but giving error when used same in Power Bi.
Kindly let me know, if I am doing this correct, does power bi support such call and that too in direct query mode, is syntax different in Power Bi while calling Redshift stored procedure.
Thanks!
2 Replies
Hi PBIAniket ,
For Amazon Redshift, Power BI typically uses Power Query to connect and retrieve data. While Power Query provides robust data connectivity and transformation capabilities, executing stored procedures directly, as you would in a database tool, is not its standard operation. This is particularly true when dealing with complex outputs like refcursors.
I think you can use Power Query Advanced Editor. Instead of using direct SQL commands, you might need to leverage the Power Query M language to connect to your Amazon Redshift database and execute the stored procedure.
Also I think you can review Power BI and Amazon Redshift Documentation. You can read this two documents: Power Query Amazon Redshift connector - Power Query | Microsoft Learn and Set up your Amazon Redshift connection - Microsoft Fabric | Microsoft Learn
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi https://community.fabric.microsoft.com/t5/user/viewprofilepage/user-id/495146 , Are you able to resolve this issue, pls let me know as I am also facing the same issue, thanks.
