Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Nolock

How to Protect a Dataset from Deletion

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. 

 

1.PNG

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! 

 

2x.png

 

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. 

 

 

4.PNG

 

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.

 

5.PNG

 

Aaaaaand wait for it:6.PNG

 

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. 

 

Comments