Stored Proc in Power BI

Power BI
1

Hello,
Our current SSRS reports all use stored procedures (SPs) as data sources from On Prem sql server.
As part of the move of data from on prem to azure, we are migrating SSRS reports to Power BI reports/dashboard…
Question:
When we move the SPs to azure, should we use these SPs inside the new Power BI reports as datasources via DirectQuery or should we use Just the sql server tables and Import them into the power bi model and create DAX measures to reproduce the result of the SPs?
Note that some of the SPs are complex with hundredth of lines of sql due to business needs.
Thank you

2

Hi @arkiboys

Marco quote “it depends”, if you want to see real time data then you should opt for direct query being said that you need to see how it is affecting your report and visuals. Below video might give you more idea

https://youtu.be/tDIsCVB7mnI

3

Hi!

Good news! You can copy SP’s in Power BI (but as far as I know only through Import).
When opening Power BI add the SQL server source as to how you would normally connect to a SQL server. After adding server and database, you can paste the query from your stored procedure under “Advanced options”.
Click the checkbox navigate in full hierarchy. Click OK and you should see your results.

4

Thank you all

5

Hi @arkiboys.

I agreee with @MK3010 … the standard consultant’s answer … “it depends”. Some of the factors you may want to consider include:

  • the desirability of a “single source of truth”; if your business logic is currently centralized in your stored procedures, then you probably don’t want to have copies of it in floating around in many Power BI reports; if you create a single dataset in Power BI using the SP and re-use that dataset as the dataset (or main dataset after the recent release of composite models, haven’t explored it too much…) for individual PBI reports, then centralized business logic can be maintained.
  • Power BI works best and can utilize query folding with flat tables or views, so materializing your SP as a physical table or view (if possible) and then using Import mode may be a possibility worth considering.
  • As for Direct Query, your decision will likely involve determining whether you need/want up-to-the-minute data, or if the dataset refresh frequency of, say Power BI Pro (8x per day max) or Power BI Premium (32x per day max) is sufficient. DQ Performance is usually slower than Import mode, and this connection mode can lower the performance of you reports beyond the tolerance level of your users, so testing is key.

Ultimately your specific case and testing should provide more information on the best direction to take.

Greg

1 Like