-> 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 outHow we can use Direct Query when loading data into Power BI DesktopI 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. SoConvert 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

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 procedureexecute schemaname.procedurenamethis 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 recommendationTry to create a table that contains the necessary column for each table of your data modelIf you can't create a table, create a view in the databaseif 1 or 2 do not work try, to use SELECT ... FROM ... INNER JOIN ...Use Import instead of DirectQueryHope this helps somehow RegardsTom

I get same error when I try to execute SP in direct query mode. Incorrect syntac near'EXECUTE.' Is the a resolution yet?

At present, its best to call a TVF instead - as such, if feasible - parse the SP into a function. If you're looking to pass parameters into your query (whether that is a function or SP) that is not possible at present. https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/20367082-passing-parameters-to-power-bi-through-stored-proc

Yes, solution exists. You can use stored procedure in SELECT from OPENROWSET. SELECT * FROM OPENROWSET('SQLNCLI','server=<SQLservername>;trusted_connection=yes;','EXEC <dbname>.<schema>.<storedprocedure>') Note: OPENROWSET construction is withnout spaces. This example is Windows authentication. Connection is under user security context. You need have RSWindowsNegotiate of AuthenticationTypes in configuration of ReportServer. Registred SPN of ReportServer portal, registred SPN of source SQL server (where is stored procedure) and set delegation in AD .. https://docs.microsoft.com/cs-cz/power-bi/report-server/configure-kerberos-powerbi-reportsStored procedure script can not use tempdb, because OPENROWSET don’t support it. OK, solution exsists, but not easy and it isn’t quick win. Insteed of you can use table variable and so.On source SQL server you mast enable Ad Hoc Distributed Queries. User Login must have read permissions of source database and execute permmission (execute your stored procedure).If you need use SQL login, you mast store OPENROWSET SELECT with password: SELECT * FROM OPENROWSET('SQLNCLI','server=<SQLservername>;Persist Security Info=True;UID=<SQLlogin>;PWD=<yourPassword>','EXEC <dbname>.<schema>.<storedprocedure>') It isn’t safety.BUT.. You can create encrypted view on source SQL server DB with OPENROWSET SELECT: CREATE VIEW PBIView_encrypted With ENCRYPTION AS SELECT * FROM OPENROWSET('SQLNCLI','server=<SQLservername>;Persist Security Info=True;UID=<SQLlogin>;PWD=<yourPassword>','EXEC <dbname>.<schema>.<storedprocedure>') Go And in your PBI report you can call SELECT from your encrypted view. Hope I help you.

Can i call Stored Procedure with Direct Query?

21 Replies

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
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
- mehulvt
  Advocate I
  8 years ago
  Hi,
  
  I get this error when I try to execute SP in direct query mode. SP has no parameters.
  
  Incorrect syntac near keyword EXECUTE. Incorrect syntax near ')'
  
  Does Power BI support SP call in direct query mode?
  - Noak
    Helper IV
    8 years ago
    I have the same problem
- Anonymous
  Not applicable
  3 years ago
  I dear, do you know how I can correctly call a SAP HANA database storage procedure? I have this problem:
  
  https://community.powerbi.com/t5/Desktop/Error-connecting-stored-procedure-on-the-SAP-HANA-Database/m-p/2895478#M995275
PegsPeterson
Regular Visitor
7 years ago
I get same error when I try to execute SP in direct query mode.

Incorrect syntac near'EXECUTE.'

Is the a resolution yet?
- pratikthanki
  Frequent Visitor
  7 years ago
  At present, its best to call a TVF instead - as such, if feasible - parse the SP into a function.
  
  If you're looking to pass parameters into your query (whether that is a function or SP) that is not possible at present.
  
  https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/20367082-passing-parameters-to-power-bi-through-stored-proc
Pallavi
Helper II
8 years ago
-> 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
- Anonymous
  Not applicable
  8 years ago
  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.
  - Pallavi
    Helper II
    8 years ago
    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
v-jiascu-msft
Microsoft Employee
8 years ago
Hi Pallavi,

The performance depends on many factors.
1. The capability of the Server. (Most of the operations are sent to the Server.)
2. The quality of the network.
3. The size of the dataset.
4. The complexity of the measures.
So using stored procedure might not help. I believe the Direct Query is running in the best way. Usually many people use Direct Query to avoid transfer the whole big dataset.
You can give it a try.

Best Regards!
Dale
Jithinraj2006
Frequent Visitor
8 years ago
I am facing the same issue, have you got any solution yet
Anonymous
Not applicable
7 years ago
I had a DirectQuery connection set up that was running a SELECT query - our organisation disabled SELECTs and so we needed to change all of our datasources to Stored Procedures.

When I changed the query from a SELECT to an EXEC <stored proc> - it gave me this error:

"This step results in a query that is not supported in DirectQuery mode." - with an option to Switch All Tables to Import Mode
- pratikthanki
  Frequent Visitor
  7 years ago
  You will need to switch over to import - the SQL editor in Power BI looks to wrap statements in a "SELECT * FROM".
  
  If you can't SELECT fromt the database and need data real time then I suggest (like you're doing) use of stored proc's + Refresh Now API (https://powerbi.microsoft.com/en-us/blog/power-bi-developer-community-february-march-update/#unlimited-refresh)
  
  Hope that helps.
Anonymous
Not applicable
4 years ago
Pallavi

Hello,

Were you able to solve this issue? If yes, then can you please let us know how?

Thanks in advance
- metox
  Frequent Visitor
  4 years ago
  Yes, solution exists. You can use stored procedure in SELECT from OPENROWSET.
  
  SELECT * FROM OPENROWSET('SQLNCLI','server=<SQLservername>;trusted_connection=yes;','EXEC <dbname>.<schema>.<storedprocedure>')
  
  Note: OPENROWSET construction is withnout spaces. This example is Windows authentication. Connection is under user security context. You need have RSWindowsNegotiate of AuthenticationTypes in configuration of ReportServer. Registred SPN of ReportServer portal, registred SPN of source SQL server (where is stored procedure) and set delegation in AD .. https://docs.microsoft.com/cs-cz/power-bi/report-server/configure-kerberos-powerbi-reports
  Stored procedure script can not use tempdb, because OPENROWSET don’t support it. OK, solution exsists, but not easy and it isn’t quick win. Insteed of you can use table variable and so.
  On source SQL server you mast enable Ad Hoc Distributed Queries. User Login must have read permissions of source database and execute permmission (execute your stored procedure).
  If you need use SQL login, you mast store OPENROWSET SELECT with password:
  
  SELECT * FROM OPENROWSET('SQLNCLI','server=<SQLservername>;Persist Security Info=True;UID=<SQLlogin>;PWD=<yourPassword>','EXEC <dbname>.<schema>.<storedprocedure>')
  
  It isn’t safety.
  BUT.. You can create encrypted view on source SQL server DB with OPENROWSET SELECT:
  
  CREATE VIEW PBIView_encrypted With ENCRYPTION AS SELECT * FROM OPENROWSET('SQLNCLI','server=<SQLservername>;Persist Security Info=True;UID=<SQLlogin>;PWD=<yourPassword>','EXEC <dbname>.<schema>.<storedprocedure>') Go
  
  And in your PBI report you can call SELECT from your encrypted view. Hope I help you.
  - Anonymous
    Not applicable
    3 years ago
    Thank you so much, I spent one day trying solve this.
Anonymous
Not applicable
3 years ago
Hi ,
did you find out the solution for your question.
shall we use parameterized stored procedure. And if i use which mode i need to use import or direct query?
cchilton
Helper I
2 years ago
If you can't use OPENROWSET an alternative is to code the stored procedure directly into the query window. Use parameters in Power BI where you would use parameters in the stored proecuedures. Bind parameters to appropriate columns to change values dynamically in report. https://www.youtube.com/watch?v=3wvnjHPGNQQ