Forum Discussion

Pallavi's avatar
Pallavi
Helper II
8 years ago

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...
Need Help
TomMartens's avatar
TomMartens
Super User
8 years ago

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

  1. Try to create a table that contains the necessary column for each table of your data model
  2. If you can't create a table, create a view in the database
  3. if 1 or 2 do not work try, to use SELECT ... FROM ... INNER JOIN ...
  4. Use Import instead of DirectQuery

Hope this helps somehow

 

Regards

Tom