Best Practice. Using sql stored procedure vs importing data from sql server?

[AKUMARKC]

Hello,

My organization just started utilizing the PowerBI and there are some developments going on. The vendor who is helping us is writing a lot of SQL queries and stored procedures, on the other hand, I was using the import option for data connectivity. It would be great if you give your opinion on what would be the best industry practice and how you are doing?

I am wondering if there would be any performance issue on using one or the other.

Thank you!

[Element115]

@Anonymous fascinating!  You have highlighted the need for me to go deeper into SQL Server and learn more advanced techniques.  Thumbs-up all around! 😁

[Anonymous]

Your consultant may have being attempting to satisfy a use case for near real-time data and so was using direct query where he performs all the transformations in database rather than PBImport. And, respectfully, @Element115 is only considering one approach for running a stored procedure in PBI which does force you into import.  There are other ways your consultant and I know about.  

[Element115]

Indeed!  First time I see this. @Anonymous Could you please explain what @Offset is and why is it needed?  

[Anonymous]

Sometime that won't work with particular queries, though.  In that case it is best to create a linked server, and query through the linked server using OPENROWSET technique.  (It is normal for a SQL serer to have created a linked server to itself.) This delightful new query, presumes you have created a linked server and it works cleanly. (Please remeber the community strips characters from the SQL sometime, so work it out if so)
😶

[Anonymous]

You'll need to use a procedure that uses a parameter for this to work as described.

[Element115]

@AKUMARKC 

If running native SQL statements and/or stored procedures: performance will depend on the DB config and its load.

If importing (assuming that the dataset size does not exceed the Power BI limit), once imported, your perf depends on the xVelocity engine (aka VertiPaq) and the resources of the platform on which you're running this (your desktop or the cloud for Power BI Service). But if within the size limits, once everything is imported, you will not have the time lag that comes from going over the network and the time it takes the DB to return the result set or sets everytime you hit the DB to load additional data.

Also, keep in mind 'import' is only 1 of the 3 modes available to get your data into Power BI. The other 2 are 'composite', and 'DirectQuery'. And stored procedures cannot be called in DirectQuery mode, and there are also restrictions as regards the type and complexity of SQL statements that can be used in DirectQuery mode.

[Anonymous]

A different approach to running stored procedures will support Direct Query. Consider creating a DQ with something like this as your SQL statement:
DECLARE @return_value INT
EXEC @return_value = pbi.Delightful_Prodcedure @Offset = -7
SELECT 'Return Value' = @return_value;

[Anonymous]