How to Protect a Dataset from Deletion

[Nolock]

The new workspaces in Power BI Service contain 4 roles: Admin, Member, Contributor, and Viewer. Simplifying the table below, users or groups granted an Admin role have permissions to make any type of change. A Member publishes an app and grants roles to other users, and a Contributor is somebody who is responsible for the content – datasets, reports, and dashboards. The fourth role is a Viewer who can only view content. 

As you can see in the screenshot above, all roles, except the Viewer, are allowed to create, edit, and delete content in the workspace. Creating and editing is ok, but they can also delete a dataset and everything that is connected to it. All this with just 3 clicks! 

In our enterprise environment, we have created some datasets which are used by many Contributors. These Contributors create new reports and edit current ones. Both, dataset and report, are in the same workspace and are using the Power BI Service Live Connection. Therefore, every contributor could delete a dataset, unaware of what they are doing, which causes a deletion of all dashboards and reports connected to this dataset in the workspace. The risk is too high! 

Maybe you say: “We have a backup of all datasets and reports. If something happens, we will publish everything again.” Well, it is not so simple as it looks. A report which uses a live connection is connected via the dataset GUID. And if you publish a new dataset, even with the same name, it will get a new GUID. This means, you have to open every report and change the connected dataset. And then you have to create all dashboards from scratch. You would probably not be happy about having to do this. 

Fortunately, I have found a way to protect our datasets. The trick is similar to what you can do on the Microsoft SQL Server. You create a VIEW with SCHEMABINDING, which prohibits all changes to an underlying table that could affect the view definition. More on this topic can be found here. The trick in the Power BI Service is to create a separate workspace which contains a report for every dataset you want to protect. This new empty report is connected to the dataset via Power BI Service Live Connection: A shared dataset is created in the background. 

Because this report is in another workspace, nobody can delete the underlying dataset, as long as a shared dataset exists. Even admins cannot do that! How simple, right? 

Let's try our luck.

Aaaaaand wait for it:

In my opinion, it is a simple and elegant solution for protecting a dataset from unwanted deletion. If you know another way for doing so, please let me know down in the comments. 

And that’s all for today. 

[pade]

Last time I checked, the list of users having access to the report wasn’t propagated to the datasets in other workspaces so you needed to pre-populate read access to the dataset access to e.g. the whole of the company and then giving individual access to users in each report. 

Are you now saying that the access propagation are now working?

[Nolock]

Hi @pade,

nope, I don't. The intention is protecting a dataset from deletion.

I have datasets and reports in one workspace for one project. Let's call this dataset A. There are many contributors, who are creating new reports in this workspace A (build rights won't help in this case). I have created another workspace B, which is accessible only by admins. In the workspace B are reports using shared datasets from A. A shared dataset prohibits the possibility of deleting the underlying dataset in the workspace A.

[Siba]

As per the steps, I created a new workspace. Then New-->report-->pick a published dataset. Saved the new report (without any visuals). Then I tried deleting the old pbix from workspace. And it got deleted.

Am i missing something? Can you please share a link which has detailled steps in it.

Thanks,

Siba

[Nolock]

Hello @Siba,

You've done everything right. The problem is that PBI service has changed the behaviour as I see. I've tested the scenario and seen that the warning text has changed unfortunately.

It means it doesn't work any more 😞