MAIL AUTOMATION USING RLS

Mail Automation Using RLS

RLS helps to filter and categorize the data as per the user/mail id, so using RLS we can share the report according to the team or groups. In this case, we can simply add slicers and pick the group name selectively then can view the report but if other team members are not allowed to see another team’s data and details means there we can use this RLS method, It provides the feature to restrict the data according to the Group or Team, So once applied the RLS and Mail automation people can easily view their team and team data alone. RLS is very helpful in data security.

What's RLS - Row Level Security?

RLS helps to filter and categorize the data as per the user/mail id, so using RLS we can share the report according to the team or groups. In this situation, adding slicers and picking the group name at will is all that is necessary. Then we may examine the report, but if other team members are not permitted to see the data and information of another team, we can use this RLS technique. It offers the feature to restrict the data according to the group or team, so once applied, users can simply view their team and team data alone. In terms of data security, RLS is quite useful.

What's RLS - Row Level Security?

Data access can be restricted for specific users by using RLS with Power BI. You can construct filters within roles, and these restrict data access at the row level. Members of a workspace in the Power BI service have access to the datasets in the workspace. This data access is not restricted by RLS. With Power BI Desktop, you may set up RLS for data models that have been loaded into Power BI. You can also configure RLS on datasets that use direct query, such as SQL Server.

RLS has two divisions.

• Dynamic 
• Static

Here, let me explain in detail how to apply Dynamic RLS: As a first step to executing RLS, we should create a separate table based on the primary column in which we want to apply the row-level filter along with the mail ID.

For instance: For employees, we can restrict the region-wise data.

Emp1 - East, Emp2 - West so we should create the table with the divisions and employees’ mail ID.

Emp1@gmail.com - East, Emp2@gmail.com - West 

Data modeling: Now we must create a relationship between both tables based on the division.

Manage Roles: In the PBI desktop data modeling option, we can see the Manage Roles option in which we can choose the table and the column (Region) which we created separately for RLS. Then we've got to write a USERPRINCIPAL DAX function to apply row-level filters based on region. Ex: Region -> Add filter [East-USERPRINCIPAL(Email-Column)]=>East- USERPRINCIPAL (Emp1@gmail.com), West - USERPRINCIPAL (Emp2@gmail.com).

So now we can check and view the employee-wise roles in Manage roles-> View as.

 Mail Automation

To activate mail automation in Power BI Pro, we can use the PBI service subscription option. There we can paste the employee mail IDs or a group which we created with all the users in Outlook and can schedule the timing. Normally, we can use this method to send the report via mail this usually sends a mail attachment with a snippet of the first page of the report.

Triggering Mail forward from Power BI subscription in Power automation. This method will be useful for sending the report to people outside of the organization or who do not have a Power BI subscription.

To those who do not have a PBI subscription: Those who do not have a PBI subscription can use Power automate, this will help us to share the data.

This concept is forwarding the same mail which we assigned in the PBI subscription to the subscribed people. To achieve this in power automation, we should choose Instant Cloud Flow.

So, the first flow is when a new mail arrives (V3):

The very first step is to create an instant flow and select action -V3(When a new mail arrives) and in To address we should pick the represented mail/primary/Admin mail id where the mail will be sent first in Power BI subscription, followed by in From address we need to mention the mail address of Power BI Microsoft id (no-reply-powerbi@microsoft.com)  

TO=> Receiver (admin@xyz.com) in PBI Subscribe

FROM=> Assignee (no-reply-powerbi@microsoft.com) in PBI Subscribe

The concept is that when a new message arrives to the PBI subscribers from Microsoft Power BI, In Power Automate

Step 1 - When a new mail arrives (V3) –

 

In the first step, select the action-V2(Forward an email), and here we can pick and list out the employee’s mail ID who does not have PBI subscription/Access to view the report, and in the message, we should select Message ID.

Step 2 - Forward an email (V2) - so when step 1 is progressing, the mail will be forwarded to the unsubscribed people with the screen snippet of the report but not with the access. In this step, we should paste the PBI unsubscribed listed people in the TO address.

 

Step 3 - SAVE the flow

 

 

Step 4 - So, when we are evaluating this flow, it asks us to send a mail from a PBI subscription, so the concept is that once the admin/Represented person receives the mail from Microsoft Power BI, in Power Automate the snip will forward to the unsubscribed employees, which we mentioned in the TO address of the V2 action.

 

Send Automation Mail With Share Point URL

Power Automate: Power automation helps so much automation progress. Especially we can schedule or can fix the manual trigger button, which helps to send particular PowerBI data or any kind of attachment or the URL which navigates to the particular report. Here I am going to explain two methods.

The first step is scheduling the mail to automate in Power Automate itself. Select Recurrence as a first action, which helps to schedule the mail automation timing.

 

The 2nd action is to select Get file content. Here we've to paste the share point URL and its name.

The 3rd action is V2 (Select an email). Fill the TO address and the Subject column should have the exact subject line in the Body of the mail we can compose the message and to apply the link we should click the URL symbol and there we can paste the exact link along with the name which should display a link to the mail receiver.

So when we save the flow and test it, the recipients will get the mail as shown below.

RLS with Power BI can be used to restrict data access for given users. Filters restrict data access at the row level, and you can define filters within roles. In the Power BI service, members of a workspace have access to datasets in the workspace. RLS does not restrict this data access.

You can configure RLS for data models imported into Power BI with Power BI Desktop. You can also configure RLS on datasets that are using Direct Query, such as SQL Server. You configure Row-Level-Security in the model for analysis services or Azure analysis services live connections, not in Power BI Desktop. The security options will not show up for live connection datasets.

RLS has two divisions 1) Dynamic 2) Static

Here let me explain in detail to apply Dynamic RLS: The first step to execute RLS, we should create a separate table based on the primary column in which we want to apply the row-level filter along with the mail Id.

For instance: For employees wise we can restrict the region wise data

Emp1 - East, Emp2 - West so we should create the table with the divisions and employees’ mail id

Emp1@gmail.com - East, Emp2@gmail.com - West 

Data modeling: Now we must create a relationship between both tables based on the division.

Manage Roles: In the PBI desktop Data modeling option we can see the Manage roles option in that we can choose the table and the column (Region) which we created separately for RLS then we've to write a USERPRINCIPAL DAX function to apply row-level filters based on region wise. Ex: Region -> Add filter [East-USERPRINCIPAL(Email-Column)]=>East- USERPRINCIPAL (Emp1@gmail.com), West - USERPRINCIPAL (Emp2@gmail.com).

 So now we can check and view the employee-wise roles in Manage roles-> View as.

Mail Automation

To activate mail automation in Power BI Pro we can use the PBI service subscribe option, there we can paste the employee mail IDs or a group which we created with all the users in outlook and can schedule the timing. Normally we can use this method to send the report via mail this usually sends a mail attachment with a snip of the first page of the report.

Triggering Mail forward from Power BI subscription in Power automation. This method will be useful to send the report snip to the people outside of the organization or who do not have the Power BI subscription.

To those who do not have a PBI subscription: To send mail like using the PBI subscription option, for the people who do not have a PBI subscription we can use the Power automate, this will help us to share the data.

This concept is forwarding the same mail which we assigned in the PBI subscription to the Subscribed people. To achieve this in power automation we should choose Instant cloud flow.

So, the first flow is When a new mail arrives (V3):

The very first step is 1)Crate an instant flow select action -V3(When a new mail arrives) and in To address we should pick the represented mail/primary/Admin mail id where the mail will be sent first in Power BI subscription, followed by in From address we need to mention the mail address of  Power BI Microsoft id (no-reply-powerbi@microsoft.com)  

TO=> Receiver (admin@xyz.com) in PBI Subscribe

FROM=> Assignee (no-reply-powerbi@microsoft.com) in PBI Subscribe

The concept is when a new mail arrives to the PBI subscribers from Microsoft Power BI, In Power Automate

Step 1 - When a new mail arrives (V3) –

In the second step select the action-V2(Forward an email) and here we can pick and list out the employee’s mail ID who does not have PBI subscription/Access to view the report and in the message, we should select Message ID

Step 2 - Forward an email (V2) - so when step 1 is progressing the mail will be forwarded to the unsubscribed people with the Screen snip of the report but not with the access. In this step, we should paste the PBI unsubscribed listed people in the TO address.

Step 3 - SAVE the flow

Step 4 - So, when we are evaluating this flow, it asks us to send a mail from a PBI subscription, so the concept is once the admin/Represented person receives the mail from Microsoft Power BI, in power automate automatically the snip will forward to the Unsubscribed employees which we mentioned in the TO address of V2 action.

Send automation mail with Share point URL

Power Automate: Power automation helps in so much automation progress. Especially we can schedule or can fix the manual trigger button, which helps to send particular Power bi data or any kind of attachment or the URL which navigates to the particular report. Here I am going to explain two methods

The first step is scheduling the mail to automate in power automate itself select Recurrence as a first action which helps to schedule the mail automation timing,

 2nd action is to select Get file content here we've to paste the Share point URL and Its name.

3rd action is V2 (Select an email) Fill the TO address and Subject column should have the exact subject line and in the Body of mail we can compose the message and to apply the link we should click the URL symbol and there we can paste the exact link along with the name which should display a link to the mail receiver.

So when we save the flow and test it, the recipients will get the mail like the below.