Home Business Intelligence Energy BI Desktop Parameters, Part2 Dynamic Knowledge Masking (DDM)

Energy BI Desktop Parameters, Part2 Dynamic Knowledge Masking (DDM)

0
Energy BI Desktop Parameters, Part2 Dynamic Knowledge Masking (DDM)

[ad_1]

Power BI Desktop and SQL Server Dynamic Data Masking

As I promised in my earlier put up, on this article I present you easy methods to leverage your Energy BI Desktop mannequin utilizing Question Parameters on high of SQL Server 2016 Dynamic Knowledge Masking (DDM). I additionally clarify very briefly easy methods to allow DDM on DimCustomer desk from AdventureWorksDW2016CTP3 database. We are going to then create a Energy BI Desktop mannequin with Question Parameters on high of DimCustomer desk. Additionally, you will discover ways to create a Energy BI Template so that you could use it sooner or later for deployment.

Notice: If you wish to find out about utilizing a Record output in Energy BI Desktop Question Parameters take a look on the subsequent put up of those collection “Energy BI Desktop Question Parameters, Half 3, Record Output“.

Within the earlier put up I defined easy methods to create dynamic knowledge sources utilizing Question Parameters. You additionally learnt easy methods to use Question Parameters in Filter Rows. On this put up you study :

  1. Utilizing Question Parameters on high of SQL Server Dynamic Knowledge Masking (DDM)
  2. Question Parameters in Energy BI Template

Identical to the Part1 of Energy BI Question Parameters, you require to fulfill the next necessities to have the ability to comply with this put up:

  1. The newest model of Energy BI Desktop (Model: 2.34.4372.322 64-bit (April 2016) or later)
  2. SQL Server 2016 (You may obtain SQL Server 2016 Developer Version free of charge)
  3. AdventureWorksDW

I’m not going to offer a lot particulars about DDM as you could find numerous data right here. However, to make you a bit acquainted with Dynamic Knowledge Masking I clarify it very briefly.

Dynamic Knowledge Masking (DDM)

Dynamic Knowledge Masking (DDM) is a brand new function out there in SQL Server 2016 and in addition Azure SQL Database. DDM is mainly a approach to forestall delicate knowledge to be uncovered to non-privileged customers. It’s a knowledge safety function which hides delicate knowledge within the outcome set of a question. You may simply allow DDM on an current desk or allow it on a brand new desk you’re creating. Suppose you will have two teams of customers in your retail database. Gross sales Individuals and Gross sales Managers. You’ve got a desk of consumers which on this put up it’s DimCustomer from AdventureWorksDW2016CTP3. This desk comprises delicate knowledge like clients’ e-mail addresses, cellphone numbers and their residential adders. Primarily based in your firm coverage, the members of Gross sales Individuals group ought to NOT be capable to see delicate knowledge, however, they need to be capable to all different knowledge. Then again the members of Gross sales Managers group can see all clients’ knowledge. To stop Gross sales Individuals to see delicate knowledge you may allow Dynamic Knowledge Masking on the delicate columns on DimCustomer desk. In that case when a gross sales particular person queries the desk he/she is going to see masked knowledge. As an illustration he see uXXX@XXX.com slightly than consumer@area.com.

Create a desk with DDM on some columns

It’s straightforward, simply put “MASKED WITH (FUNCTION = ‘Mask_Function’)” in column definition. So it ought to seem like this:

CREATE TABLE Table_Name   (ID int IDENTITY PRIMARY KEY,    Masked_Column1 varchar(100) MASKED WITH (FUNCTION = ‘Mask_Function’),    Masked_Column2 varchar(100) MASKED WITH (FUNCTION = ‘Mask_Function’),

 

)

GO

Alter an current desk and allow DDM on desired columns

As you guessed you must use “ALTER TABLE” then “ALTER COLUMN”. Your T-SQL ought to seem like:

ALTER TABLE Table_Name ALTER COLUMN Column_Name1 ADD MASKED WITH (FUNCTION = ‘Mask_Function’);

GO

ALTER TABLE Table_Name

ALTER COLUMN Column_Name2 ADD MASKED WITH (FUNCTION = ‘Mask_Function’);

GO

For extra data please confer with MSDN.

Energy BI Template

A template is mainly a Energy BI file that represents an occasion of a predefined Energy BI Desktop which incorporates all definitions of the Knowledge Mannequin, Stories, Queries and parameters, however, not contains any knowledge. Creating Energy BI Templates is an effective way to ease the deployment of current fashions. Creating templates may be very straightforward, you simply click on File –> Export –> Energy BI Template. We are going to have a look at this extra in particulars by means of this text.

You might be requested to implement a brand new degree of safety on clients’ knowledge (DimCustomer on AdventureWorksDW2016CTP3 database) in order that simply privileged customers can see the shoppers’ e-mail, cellphone numbers and residential deal with. Privileged customers are all members of “SalesManager” database position. You might be additionally requested to stop “SalesPerson” database position to see delicate knowledge. However, all members of each “SalesManager” and “SalesPerson” database roles can question DimCustomer desk. The customers ought to NOT have SQL Server logins.

  • In DimCustomer, “EmailAddress”, “Cellphone” and “AddressLine1” ought to be masked

  • SalesManager database position is privileged to see unmasked knowledge

  • SalesPerson database position is privileged to see masked knowledge solely

  • SQL Server database consumer “user1_nologin” is a member of “SalesManager”

  • SQL Server database consumer “user2_nologin” is a member of “SalesPerson”

On high of that, you must implement a report in Energy BI Desktop  for each gross sales managers and gross sales individuals. The report queries DimCustomer. You require to create a Energy BI Template in order that it covers the safety wants.

To have the ability to implement the above state of affairs you must comply with the steps under:

  • Create “SalesManager” and “SalesPerson” database roles in the event that they don’t exist

  • Create two new customers with out logins (user1_nologin and user2_nologin)

  • Add user1_nologin as a member of SalesManager database position

  • Add user2_nologin as a member of SalesPerson database position

  • Grant choose entry to each database roles

  • Masks “EmailAddress”, “Cellphone” and “AddressLine1” columns in DimCustomer

  • Grant SalesManager database position to see unmasked knowledge

  • Create Energy BI Desktop Report

  • Export the mannequin to Energy BI Template

Implementation

Let’s develop the above state of affairs in SQL Server after which Energy BI Desktop.

SQL Server Implementation

I’ll do the entire SQL Server improvement half utilizing T-SQL. However, you are able to do numerous the job utilizing SQL Server Administration Studio UI. I go away that half to you if you wish to do the job utilizing the UI.

  • Open SQL Server Administration Studio (SSMS)

  • Connect with your SQL Server 2016 occasion

  • Open a brand new question for AdventureworksDW2016CTP3

  • Copy and paste under code snipped to question editor then run it

USE [AdventureworksDW2016CTP3]

GO

 

— Create database roles if not exist

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE identify = N’SalesManager’ AND sort = ‘R’)

CREATE ROLE [SalesManager]

GO

 

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE identify = N’SalesPerson’ AND sort = ‘R’)

CREATE ROLE [SalesPerson]

GO

 

— Grant choose entry to each database roles

GRANT SELECT ON DimCustomer TO [SalesManager]

GO

 

GRANT SELECT ON DimCustomer TO [SalesPerson]

GO

 

— Create customers if not exist

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE identify = N’user1_nologin’)

CREATE USER [user1_nologin] WITHOUT LOGIN

GO

 

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE identify = N’user2_nologin’)

CREATE USER [user2_nologin] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]

GO

 

— Add user1_nologin to SalesManager

ALTER ROLE [SalesManager] ADD MEMBER [user1_nologin]

GO

 

— Add user2_nologin to SalesPerson

ALTER ROLE [SalesPerson] ADD MEMBER [user2_nologin]

GO

 

— Masks delicate columns

ALTER TABLE DimCustomer

ALTER COLUMN EmailAddress ADD MASKED WITH (FUNCTION = ’e-mail()’)

GO

 

ALTER TABLE DimCustomer

ALTER COLUMN Cellphone ADD MASKED WITH (FUNCTION = ‘partial(6,”XXXXXXX”,0)’);

Go

 

ALTER TABLE DimCustomer

ALTER COLUMN AddressLine1 ADD MASKED WITH (FUNCTION = ‘default()’);

Go

 

— Grant SalesManager to see unmasked knowledge

GRANT UNMASK TO SalesManager

GO

Energy BI Desktop Implementation
  • Open Energy BI Desktop

  • Get knowledge from SQL Server Database

  • Kind server identify and database identify

  • Click on “Superior choices”

  • Copy and paste the code snipped under in “SQL assertion” field then click on OK

EXECUTE AS USER = ‘user2_nologin’

SELECT * FROM DimCustomer

REVERT

Power BI Desktop Get Data from SQL Server

“DataSource.Error: Microsoft SQL: Can’t proceed the execution as a result of the session is within the kill state.
A extreme error occurred on the present command.  The outcomes, if any, ought to be discarded.”

DataSource.Error: Microsoft SQL: Cannot continue the execution because the session is in the kill state.

  • Click on “Apply Modifications”

Power BI Desktop Apply Changes

  • Now you must see “Query1” within the mannequin

Power BI Desktop Query

  • Click on “Edit Queries” from the ribbon
  • In case you scroll proper you’ll see masked knowledge for “EmailAddress”, “Cellphone” and “AddressLine1”

Power BI Desktop Query Editor

  • Rename the question to DimCustomer

You now have to create a parameter for the customers. This parameter can be referenced within the knowledge supply later

  • Click on “Handle Parameters” from the ribbon
  • Click on “New”
  • Enter a reputation and outline
  • Choose “Textual content” as Kind and “Record of values” as Allowed Values
  • Kind “user1_nologin” and “user2_nologin” within the values record
  • Choose “user2_nologin” in each default and present worth then click on OK

Power BI Desktop Query Parameters

You must reference the DBUser parameter in DimCustomer knowledge supply.

  • Click on DimCustomer from Queries pane
  • Click on “Superior Editor”
  • Exchange “user2_nologin” with “”&DBUser&””

Power BI Desktop Parameterise Data Source

Notice: Please be aware the place you place the citation marks.

Power BI Desktop Parameterise Data Source

  • Click on “Shut & Apply” from the ribbon

It appears we’re executed. Now it’s time to change the customers to see what occurs. To make it simpler lets put a Desk on the report web page containing “FirstName”, “LastName”, “EmailAddress”, “Cellphone” and “AddressLine1” columns.

Dynamic Data Masking (DDM) in Power BI

  • Click on “Edit Parameters” from the ribbon
  • Choose “user1_nologin” from the record then click on OK

Power BI Desktop Select Parameters

  • Affirm operating Native Database Question

Power BI Desktop Native Database Query

Oops! You bought that nasty error message once more. After all, you may shut the message and click on “Apply Modifications”, however, it doesn’t look lifelike to get that error message at any time when we swap the consumer.

What is actually improper with the question we wrote?

The reply is that there’s nothing improper with the question certainly. The rationale of getting the error message is the primary line of the question. We’re executing the question as a consumer, however, we already used one other credential to connect with the database which on this pattern is a Home windows consumer. That is referred to as “Context Switching”. Principally Energy BI Desktop desires reset the standing of the present connection and reuse it for a special consumer. Resetting the present session causes the issue.

By the way in which, let’s shut the error message and click on “Apply Modifications” to guarantee that we are able to see unmasked knowledge after switching the consumer.

Power BI Desktop Apply Changes

Dynamic Data Masking (DDM) in Power BI Desktop

As you see the method works superb, however, we’d like a treatment for this to do away with that nasty error message.

The answer is to encapsulate the queries in saved procedures in SQL Server aspect. In that case Energy BI Desktop is not going to reset the connection. After creating saved procedures for every consumer we have to create a brand new parameter in Energy BI Desktop to cross the saved process names to the info supply slightly than the customers.

Notice: You may create only one saved process. In that case, you could outline a parameter for SQL Database consumer then assemble the saved process writing dynamic SQL. However, to maintain this so simple as doable I created two separated saved procedures for every consumer.

CREATE PROCEDURE [dbo].[DimCustomerMasked]

AS

EXECUTE AS USER = ‘user2_nologin’

SELECT * FROM DimCustomer

REVERT

GO

CREATE PROCEDURE [dbo].[DimCustomerUnMasked]

AS

EXECUTE AS USER = ‘user1_nologin’

SELECT * FROM DimCustomer

REVERT

GO

  • In Energy BI Desktop click on “Edit Queries”
  • In Question Editor click on “Handle Parameters” from the ribbon
  • Exchange the prevailing values with the saved process names
  • Choose “DimCustomerMasked” for each default and present values then click on OK

Power BI Desktop Edit Query Parameters

  • Choose DimCustomer from Queries pane then click on “Superior Editor” from the ribbon
  • Exchange the entire question with the next

“EXEC “&DBUser”

Power BI Desktop Parameterising Data Source

Power BI Desktop Parameterising Data Source

Notice: Notice the citation marks.

Power BI Desktop Parameterising Data Source

  • Click on “Edit Permission” then click on Run

Power BI Desktop Native Database Query

  • Click on “Shut & Apply” from the ribbon

Dynamic Data Maskin (DDM) in Power BI Desktop

  • It seems significantly better now
  • Click on “Edit Parameters” from the ribbon and swap the saved process to “DimCustomerUnmasked”

Power BI Desktop Enter Query Parameters

Power BI Desktop Native Database Query

Dynamic Data Maskin (DDM) in Power BI Desktop

Hmm, that appears good. Smile

Energy BI Template

As acknowledged earlier than, making a Energy BI Template is very easy. Simply save the present mannequin then File –> Export –> Energy BI Template.

Power BI Desktop Export Template

Write some description and click on OK.

Power BI Desktop Export Template

Save the template.

Power BI Desktop Export Template pbit

Shut Energy BI Desktop. Now double click on on the template file to open it. The very first thing that occurs after opening the template file is that it askes to enter parameters. As you may observed the Energy BI Desktop masses a brand new Untitled mannequin.

Power BI Desktop Import Template

Dynamic Data Maskin (DDM) in Power BI Desktop

In case you swap the parameter worth you’ll see you’ll now not requested to verify operating Native Database Question.

Dynamic Data Maskin (DDM) in Power BI Desktop

Final Phrase

You may load the parameters’ knowledge into the mannequin which is actually nice. I’m passing this to you for additional investigations.

Pattern template is Able to Obtain

You may obtain the pattern template I created on high of AdventureWorksDW2016 and Dynamic Knowledge Masking right here. It comprises the earlier put up’s samples in addition to what you’ve discovered within the present article.

[ad_2]

LEAVE A REPLY

Please enter your comment!
Please enter your name here