Skip to main content

Invoke a stored procedure in SQL server for a given parameter in Power BI


Case- In some cases we need to filter the data that has been imported from a SQL server database where it is not advisable to create a new query all the time.

Prerequisite – You need to have a SQL server database where it should contain a two column table with "town-id" and ''town-name'' also you need to create a stored procedure to accept town-id as input parameter and return the town-name as out put. 

 We are going to run a native query in Power BI with changing parameter values so in order to avoid too many confirmations go to "Files" then "Option and Settings" then "Option" then "Security" then untick Require user approval for new database queries in Power BI.

Calling a stored procedure from SQL server when importing data to Power BI


As you can see above I have chosen “SQL Server” in the “get data” option from task bar in order to import data from SQL Server database. Then I have given the server name and the database that I want to connect and chosen to import the data instead of direct query.

Below that you can notice the “Advanced option”, I have entered a SQL statement which have to run before retrieval and will result in a query output. 

 "EXECUTE pTown 1"

 You can notice an “Execute” function in the above SQL statement and you all know stored procedures can be executed in the SQL Server and Power BI is allowing to run a stored procedure and import the query out put.

Once you click ok we can retrieve data from the SQL Server Database corresponding to the parameter value "1" 

When the stored procedure “pTown” is executed with the parameter value "1" the result is as shown below.


In the above image you can notice that the outcome of the stored procedure is displayed and ready for you to load but now it is time for you to edit that before loading.


How to create a new parameter in Power BI


Now it is time for you to create a new parameter. You can do this by selecting manage parameter from the task bar and there you need to select the new parameter option and you will get something similar to what you can see above.

Creating a new parameter named TownId in Power BI

As you can see above I have given details for the new parameter such as the name TownId and a little description stating "it should contain values in-between 1-203" also changed the type to text in order to avoid data conversion problems and  provided "1" as the current value. Now click OK to create this parameter.

Using the parameter created in the power BI in the query when calling the stored procedure

In SQL Server database there is a table for town and it has 203 rows with town id and town name, now we have created a parameter in Power BI which we can take in put from the user. Now the problem is how we are going to link the stored procedure with the newly created parameter in power BI instead of the value “1” which has been used for importing.

In order to solve this go to the advanced editor by right clicking the query and what you will get is something similar as shown below.


Check the above image, can you notice the stored procedure that we have called during the time of import in the very beginning is clearly shown in the advanced editor as a query? What we are going to do is a small change in that by replacing the value 1 to the parameter TownId that we have created in Power BI. If you don’t understand check the image below.



Can you notice the difference I have made by removing “1” and adding the parameter "TownId'' that I have created? 

Now click ok to apply the changes. You might wonder why nothing has happened but the truth is your parameter is influencing the stored procedure in the query, in order to check this give different values to the parameter and see the outcome of the query. 

 Some examples are given below.

When 15 is given as parameter value.



When 105 is given as parameter value.



Can you notice the value that we have given for the parameter TownId is their within the bracket also for each different parameter corresponding different towns are being displayed in the query.

What is happening – In a holistic view what we are doing is simply calling a stored procedure to import data from SQL Server database. The best part is the parameter for that stored procedure can be given from Power BI using a new parameter, in other words you are filtering what you want with style.











Labels:

Comments

  1. latesttechnologyblogsJuly 25, 2018 at 4:44 AM

    Thanks for sharing this Informative content. Well explained. Got to learn new things from your Blog onPower BI Online Training Bangalore

    ReplyDelete

Post a Comment

Popular posts from this blog

Choosing color scheme for Power BI reports

Choosing beautiful colors for an attention seeking Power BI report is always challenging. Sometimes we may have an important message but it might go unseen if proper color scheme is not used, so it is important to create reports which are informational and visually appealing. In this blog our main objective is to get an idea about selecting appropriate color scheme and tools that can be beneficial to do this. What is a color scheme? As you all know not all colors look great together and you can tell stories by just having only two colors, have a look at the following image, there in the middle you can see a circle with same color and same dimension, only thing that changes is the back ground, but depending on the back ground the way you see those circles are totally different. Some small circles appear softer, some appear sharper, and some are brighter also you might notice movements or depth effect too. It is important to choose colors wisely to serve the purp...
Post a Comment
Read more

Five tips to enhance a tableau model with usability features.

In the previous blogs I was more focused in Power BI since i work with it day to day, but this time we are going to dig into increasing the usability features of tableau. This blog revolves around the end-user simplicity, if a model is hard to comprehend then it will be a disaster, so we should deploy some means to stay simple. So without further ado lets head on to the first topic of the day. Configuring table and  column  properties. A model can consist of many tables but the important fact is they have to be unique and it is better if we can have a table description to elaborate on whats it about. This can be also considered as an internal documentation, when a project member is changed it is easy to pick up and continue. We use connections to load data for the tables and we have a connection name for it but once if we set the source it cannot be changed in the latter part, so be mindful because once you are dropping a connection you are losing all th...
5 comments
Read more

Ragged hierarchy in Power BI

Case- I have a data set which has to be visualized in a ragged hierarchy. What is ragged hierarchy A ragged hierarchy is a user defined hierarchy with an uneven number of levels.   You can see the above table which has 5 levels of hierarchy. In the first level we only have “World” as a category then moving forward to the next level we have two categories  namely “UK” and “USA” likewise the level of hierarchy is drilling down up to five levels with an uneven amount of categories. Creating a hierarchy in Power BI In the above image  you can notice that I have right clicked the “level 1” column which has to be the first level of our hierarchy, Once it is right clicked there you will notice “New hierarchy” as the second option then you will have to click that in order to create a new one. As you can notice in the above image once I have created a new hierarchy then a new column is created named “Level 1 Hierarchy” in that I have d...
3 comments
Read more