Can i call Stored Procedure with Direct Query?

[Pallavi]

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

[cchilton]

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 

[Anonymous]

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]

@Pallavi 

Hello,

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

Thanks in advance

[metox]

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.

[sravanpesari]

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.

[LucasTorra14]

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.

[sravanpesari]

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.

[LucasTorra14]

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

[Anonymous]

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]

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]

I get same error when I try to execute SP in direct query mode. 

Incorrect syntac near'EXECUTE.'

Is the a resolution yet?

[pratikthanki]

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]

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

[v-jiascu-msft]

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

[TomMartens]

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

[Anonymous]

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

[mehulvt]

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]

I have the same problem 

[Pallavi]

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

[Seth_C_Bauer]

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