Using a SQL Server stored procedure as a source in Power BI

We might be most familiar with using a view as the source to our PBI datasets, a series of views creating a series of tables. This can be great but when handling more complex view queries with lookups, you might find yourself with a severely long refresh and even an error. This is where a stored procedure is required – this gives you more control layer by layer in the source query.

The approach is very similar when using a stored procedure – you choose a source server and database but instead of selecting the view, you use a custom TSQL statement. A view is ultimately a SELECT query that returns a result set, and a stored procedure can be set up the same way.

Create the SP

Firstly, create the stored procedure – it needs to end up being a SELECT query. The following query is simple, and could likely be a view but it demonstrates a non-parameterized stored procedure as the source:

An example of a SQL Server stored procedure

Set it as the source

This would be used as the PBI source as follows:

Setting a stored procedure as the source for a Power BI dataset in PBI Desktop

Now with parameters

A stored procedure is most beneficial when you parameterize it to enable incremental refresh. This lets you maximize refresh efficiency and time. The source is optimized by being filtered sooner and sooner in the query as well as refreshing smaller windows compared to the entire table. The following query showcases how you have a more granular control, layer by layer of a complex query to improve. So, parameterize your stored procedure as necessary:

An example of a parameterised stored procedure which would be a good source for an incremental refresh

 

Then set up the parameters within PBI Desktop as instructed for incremental refreshes. You can learn more here – 2 parameters, RangeStart and RangeEnd, both of type datetime:

 

Setting up the incremental refresh date time parametersRangeStart and RangeEnd parameters - for use in the incremental refresh

Then, replicate the source query when in PBI Desktop to just set the source as before. Finish setting up the source for any other tables then head over to the query editor (right-click the table, ‘Edit Query’). Select your table that relies on the stored procedure as the source, then go to the first step ‘Source’:

Use query editor to view the source query for a table

This is where we add in the parameters to enable the table to be an incremental refresh. Copy and paste the following into the source query editor after the SP but within the squared brackets.

‘” & DateTime.ToText(RangeStart , “yyyy-MM-dd hh:mm:ss”) & “‘,'” & DateTime.ToText(RangeEnd , “yyyy-MM-dd hh:mm:ss”) & “‘”

 

The result should look as follows:Final M code for the source of your Power BI table

 

Note: if you have any more parameters, just add in the comma between parameters and replicate the same approach of the correct data types.

 

Tags: , , ,