Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Pallavi
Helper II
Helper II

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

  1. 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

21 REPLIES 21
cchilton
Frequent Visitor

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 

cchilton_0-1704477035021.png

 

Anonymous
Not applicable

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?

Anonymous
Not applicable

@Pallavi 

Hello,

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

Thanks in advance

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.

I have tried this with Oracle stored procedure but it didn't work, can you please give me the steps how to achieve this with Oracle stored procedure.

Hello, I am using SQL SERVER, I dont know if you have something in Oracle. You need to search if you can exec a procedure using a select with Oracle.

Hi, Yes it works in SQL Server with inbuilt function OPENROWSET. But couldn't find the equivalent feature in Oracle, Can anyone impleneted this please provide the steps.

 

Thank you so much, I spent one day trying solve this.

Anonymous
Not applicable

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

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/#unlimit...

 

Hope that helps.

 

 

PegsPeterson
Regular Visitor

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-po...

 

Jithinraj2006
Frequent Visitor

I am facing the same issue, have you got any solution yet

v-jiascu-msft
Employee
Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
TomMartens
Super User
Super User

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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/...

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?

 

 

I have the same problem 

BR,
Noa.
Pallavi
Helper II
Helper II

-> 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

  1. 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.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.