Update multiple reports

[DeBIe]

Hello all,

I would like to know if I can improve my way of working with updating multiple reports.

The situation now is: I have 10 reports with exactly the same visuals. The only difference is the customer(datasets). I had to deploy a fix and you guessed it, it has to be applied to all reports. Instead of changing the data source one by one to the specific customer and re-publishing it, I would like to know if it is possible to automatically push this fix and apply to all 10 reports. The fix I had to do was done in the query editor.

Thank you for your time. Hopefully, someone can help me.

[DeBIe]

@Greg_Deckler Hi,

I do not yet pull in the data from all customers at once. What I do is change the data source to the desired cliënt, wait for it to load in all the data. After that, I will publish it to the workspace of that specific cliënt. I have like 15 cliënts so with every update I have to do this 15 times. The way I think this will be possible to solve with RLS is:

Pulling in all 15 tables from the clients, add a column with cliënt name, then merge all. And then add RLS based on the Client name column. I wonder if this is going to work after merging the data and reaching +5.000.000 records. Probably I should be able to select the RLS filter in the query editor before loading the data into the model...

[amitchandak]

@DeBIe , refer if this can help

https://community.powerbi.com/t5/Desktop/parameterize-connection/m-p/205900#M90712

[Greg_Deckler · Accepted solution]

@DeBIe - Maybe something via the Power BI REST API but the best thing to do in your scenario is to use Row Level Security (RLS) and that way you only have a single report to deal with.

[DeBIe]

@Greg_Deckler 

I understand that I can enable RLS and set up access rights for multiple users for the same dataset. But is this also possible with multiple customer environments (multiple SQL-databases)? 

[Greg_Deckler]

@DeBIe - Are you bringing all of those multiple SQL databases into a single Power BI dataset? Is this a Direct Query situation?

[DeBIe]

@Greg_Deckler 

I've tried to draw this in paint lol. Hopefully, this makes it clear what I'm looking for?

[Greg_Deckler]

@DeBIe - Right, what I am talking about is creating a single Power BI dataset that includes ALL of the SQL databases for ALL of the customers. You then apply RLS to that so that each customer only sees their own data.

[DeBIe]

@Greg_Deckler 

Thanks for your reply. Is there a website/article with an explanation of how to do this? 

[Greg_Deckler]

@DeBIe - If you are talking about RLS this should do it: https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls

or this:

https://www.sqlshack.com/introduction-to-row-level-security-in-power-bi/

[DeBIe]

@Greg_Deckler 

Hi, thank you for your reply. I understand now how RLS works, and I've started trying to apply it to the dashboard. But I encounter a problem. That problem is that I have to select the columns for which I want to apply RLS. I need to be able to filter based on the SQL-Server of the desired cliënt.

So imagine I have 15 cliënts. All have their own data warehouse on their SQL-Server environment. I use one of the cliënts data to develop the dashboard. Once developed, I would like to apply this to all existing dashboards. I don't see how that is possible with RLS, because I see RLS as filtering for one cliënt, but for several people within the same organization. For example, one RLS applied to the financial guy, one for the sales guy, etc. All from the same organizations. In this case, I have a lot of different cliënts. I didn't find the solution in the links you gave me, but it gives a good explanation of what RLS does.

[Greg_Deckler]

@DeBIe So the way I see this going down if I read everything correctly. When you bring in data from multiple clients, each is its own query. So bring each data in and add a column "Customer" with the customer name if it is not already present. Then, configure 15 RLS rules, one for each customer. The RLS rules will all be similar:

[Customer] = "ABC Customer"

1 role per customer with an RLS rule like above.