How to use stored procedure with parameters in Power BI

[Anonymous]

Hi,

After going through many articles on this platform, I am posting this quetion as still I am struggling to solve our problem.

We want to embed Power BI report or dashboard in our website dashboard.

Let me try explaining thescenario.

User specific data is shown on the existing dashboard report once the user is logged into the portal.

Example: User 1 sees something like:

Total products added by you: 100;

Total productes sold by you: 60;

Country: India;

Sub Country: North India

User can change country and sub-country fom dropdown.

User 2 can see different values based on his user id the country value he has selected.

Example:

Total products added by you: 140;

Total productes sold by you: 56;

Country: USA;

Sub Country: New York

Is it possible in Power BI to pass MS SQL stored procedure with parameters whose values will change based on the person who is logged-in?

Some thing like this:

For user 1, following query output will be source data for Power BI:

EXEC GetsalesMilestone @CountryID = 1269, @SubCountryID = 1245, @UserID = 12

For user 2, following query output will be source data for Power BI:

EXEC GetsalesMilestone @CountryID = 1289, @SubCountryID = 3453, @UserID = 34

So that the different users see different report data as all the three parameter values will change with the user.

Also is it possible to change these parameter values through Power BI API so that we can passed parameter values based on who is logged in?

[Greg_Deckler]

Might be possible to do something like that but what it seems like you really want is Row Level Security (RLS). Are you trying to do this in a DirectQuery or Import environment?

[Anonymous]

Hi @Greg_Deckler

We tried RLS but it can work only for the data which is already loaded in Power BI desktop dataset (for creation of Roles).

It's something similar to how we apply filters on columns in MS Excel.

To implement this we will need to import entire data in Power BI dataset (which runs into millions of rows-practically not possible) and then apply filters on them with RLS.

Also we won't be able to use our existing stored procedures which has complex data fetching logic written.

We are looking to reaplace our existing user specific reports displayed in our web application with Power BI where each user should see Power BI report based on the user specific parameters passed to stored proc (explained my in first port).

Please let us know if there is something in Power BI with which we can achive functionlality as explained in our first post.

[Anonymous]

Hi Greg,
I am trying to do this with import environment.
But I am open to use any of the two approaches if it satisfies our requirement.

[Greg_Deckler]

Hey @Anonymous, I'm a bit confused as you say you want to do this with import but also say that it is practically not possible to import everything. I don't know your exact setup but here is an alternative way to potentially do what you are asking.

https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-row-level-security-onprem-ssas-tabular

[Anonymous]

Hi @Greg_Deckler I said it's practically impossible to import entire data because, assume I have total 1000 records (just for example-actual count runs into millions) in my database.

Lets say, our stored procedure call for user one returns 5 records (a smaller record count) based on user specific parameters as mentioned in my first post.

I pass this stored procedure with hard coded parameters in query editor of Power BI.

The result set will form the data set in Power BI on which I can build a report.

This report once embedded in our web app, will be visible to all our logged in users (but actually this report was user specific !! ).

Now to avoid a user seeing report meant for other user, may be I can apply row level security.

To implement row level security I will need to import all 1000 records in Power BI, create roles with specific filter conditions, and in Power BI service, I will need to assign specific users to these roles so that specific users see the specific report data - which is what we want.

Now why this seems to be impractical?

Reason: There are millions of records which will need to be imported for implementing RLS.

If I assume country as the filter critera for implementing row level security, I will need to create one role for each country - which will be ahuge task.

After this I will need to add all users of a country to that country specific role.

Also each time a new user is registered in our web application, I will need to add that user to that country specific role.

These are the reaosns why I think it's going to be tedious.

I am going through the link provided by you. Will update you if it helps.

Thanks !

[Greg_Deckler]

Right, also check out this blog post by RADACAD:

http://radacad.com/dynamic-row-level-security-with-power-bi-made-simple