Forum Discussion
Can i call Stored Procedure with Direct Query?
-> Import vs Direct Query – So at the moment i am loading the queries into Power BI Desktop by using Import Query rather than Direct Query inturn it stores the data in the cloud, which isn’t security approved at the moment. So i want to figure out
- How we can use Direct Query when loading data into Power BI Desktop
I have a fear that using Direct Query will slow down our dashboards as when a user filters it will retrieve the information from the database through the gateway. So
- Convert the queries into stored procedures. I am hoping that by creating the stored procedures the data will load faster when a user filters a report in Power BI Service where the data source is from a stored procedure.
It would be great if one gives suggestions and explain me the better approach and please let me know whether stroed procedures gonna work with direct query, if so how i need to call from direct query.
Thanks
Pallavi you won't be able to pass parameters to stored procedures, and you can't execute them in direct query mode. You can however, create table valued functions which give you the ability to use table variables and perform other functions that are more complex in nature in Direct Query mode.
Could you please correct me whether i can go with the following approach for direct query mode
1) Creating a table with the same columns i am using in SQL Script
2) Coverting the SQL Script into Procedure and inserting data in a newly created table
3) schedule the procedure for refreshing data as many times based on the usage
4)use direct query mode and call this table instead of stored procedure.
Now the data will be loaded and i am able to create visualizations. Please correct me