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...
Hey,
you can use the following SQL statement that you can enter accessing a SQL Server database in DirectQuery mode calling for the resultset returned by a T-SQL stored procedure
execute schemaname.procedurename
this SQL statement has to inserted where you normally provide your custom SQL statement.
I doubt that using a stored procedure will provide faster access to your SQL Server data.
I guess you are aware that directQuery does not load the data, instead a query is generated by Power BI, representing the interaction betwenn the user and the data.
In directquery mode there is something called "Query Folding", this will by no means happen if you use a stored procedure.
My recommendation
- Try to create a table that contains the necessary column for each table of your data model
- If you can't create a table, create a view in the database
- if 1 or 2 do not work try, to use SELECT ... FROM ... INNER JOIN ...
- Use Import instead of DirectQuery
Hope this helps somehow
Regards
Tom
