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

As I promised in my earlier put up, on this article I present you learn how to leverage your Energy BI Desktop mannequin utilizing Question Parameters on prime of SQL Server 2016 Dynamic Information Masking (DDM). I additionally clarify very briefly learn how to allow DDM on DimCustomer desk from AdventureWorksDW2016CTP3 database. We’ll then create a Energy BI Desktop mannequin with Question Parameters on prime of DimCustomer desk. Additionally, you will learn to create a Energy BI Template to be able to use it sooner or later for deployment.

Observe: 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 sequence “Energy BI Desktop Question Parameters, Half 3, Record Output“.

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

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

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

1. The most recent model of Energy BI Desktop (Model: 2.34.4372.322 64-bit (April 2016) or later)
2. SQL Server 2016 (You possibly can obtain SQL Server 2016 Developer Version without spending a dime)
3. AdventureWorksDW

I’m not going to offer a lot particulars about DDM as you’ll find a number of data right here. However, to make you a bit accustomed to Dynamic Information Masking I clarify it very briefly.

Dynamic Information Masking (DDM)

Dynamic Information Masking (DDM) is a brand new function obtainable in SQL Server 2016 and likewise Azure SQL Database. DDM is principally a option to forestall delicate knowledge to be uncovered to non-privileged customers. It’s a knowledge safety function which hides delicate knowledge within the end result set of a question. You possibly can simply allow DDM on an present desk or allow it on a brand new desk you’re creating. Suppose you could have two teams of customers in your retail database. Gross sales Individuals and Gross sales Managers. You’ve a desk of consumers which on this put up it’s DimCustomer from AdventureWorksDW2016CTP3. This desk accommodates delicate knowledge like clients’ electronic mail addresses, telephone numbers and their residential adders. Based mostly in your firm coverage, the members of Gross sales Individuals group ought to NOT be capable of see delicate knowledge, however, they need to be capable of all different knowledge. Then again the members of Gross sales Managers group can see all clients’ knowledge. To forestall Gross sales Individuals to see delicate knowledge you may allow Dynamic Information Masking on the delicate columns on DimCustomer desk. In that case when a gross sales individual queries the desk he/she’s going to see masked knowledge. For example he see uXXX@XXX.com relatively than consumer@area.com.

Create a desk with DDM on some columns

It’s simple, simply put “MASKED WITH (FUNCTION = ‘Mask_Function’)” in column definition. So it ought to appear 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 present desk and allow DDM on desired columns

As you guessed it’s a must to use “ALTER TABLE” then “ALTER COLUMN”. Your T-SQL ought to appear 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 check with MSDN.

Energy BI Template

A template is principally a Energy BI file that represents an occasion of a predefined Energy BI Desktop which incorporates all definitions of the Information Mannequin, Studies, Queries and parameters, however, not consists of any knowledge. Creating Energy BI Templates is an effective way to ease the deployment of present fashions. Creating templates may be very simple, you simply click on File –> Export –> Energy BI Template. We’ll have a look at this extra in particulars by way of this text.

You’re 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’ electronic mail, telephone numbers and residential handle. Privileged customers are all members of “SalesManager” database function. You’re additionally requested to forestall “SalesPerson” database function 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” needs to be masked

• SalesManager database function is privileged to see unmasked knowledge

• SalesPerson database function 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 prime of that, it’s a must to 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 situation it’s a must to observe 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 function

• Add user2_nologin as a member of SalesPerson database function

• Grant choose entry to each database roles

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

• Grant SalesManager database function to see unmasked knowledge

• Create Energy BI Desktop Report

• Export the mannequin to Energy BI Template

Implementation

Let’s develop the above situation in SQL Server after which Energy BI Desktop.

SQL Server Implementation

I’ll do the entire SQL Server growth half utilizing T-SQL. However, you are able to do a number of the job utilizing SQL Server Administration Studio UI. I depart that half to you if you wish to do the job utilizing the UI.

• Open SQL Server Administration Studio (SSMS)

• Hook up 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 title = N’SalesManager’ AND sort = ‘R’)

CREATE ROLE [SalesManager]

GO

 

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE title = 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 title = N’user1_nologin’)

CREATE USER [user1_nologin] WITHOUT LOGIN

GO

 

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE title = 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 = ’electronic 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 title and database title

• 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

“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, needs to be discarded.”

• Click on “Apply Modifications”

• Now it is best to see “Query1” within the mannequin

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

• Rename the question to DimCustomer

You now must create a parameter for the customers. This parameter shall 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

You want to reference the DBUser parameter in DimCustomer knowledge supply.

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

Observe: Please notice the place you place the citation marks.

• Click on “Shut & Apply” from the ribbon

It appears we’re achieved. Now it’s time to modify 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.

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

• Verify operating Native Database Question

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 reasonable to get that error message every time we change the consumer.

What is de facto fallacious with the question we wrote?

The reply is that there’s nothing fallacious 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”. Mainly Energy BI Desktop needs reset the standing of the present connection and reuse it for a special consumer. Resetting the present session causes the issue.

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

As you see the method works tremendous, however, we want 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 won’t reset the connection. After creating saved procedures for every consumer we have to create a brand new parameter in Energy BI Desktop to go the saved process names to the info supply relatively than the customers.

Observe: You possibly can create only one saved process. In that case, that you must outline a parameter for SQL Database consumer then assemble the saved process writing dynamic SQL. However, to maintain this so simple as potential 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
• Substitute the present values with the saved process names
• Choose “DimCustomerMasked” for each default and present values then click on OK

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

“EXEC “&DBUser”

Observe: Observe the citation marks.

• Click on “Edit Permission” then click on Run

• Click on “Shut & Apply” from the ribbon

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

Hmm, that appears good.

Energy BI Template

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

Write some description and click on OK.

Save the template.

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 seen the Energy BI Desktop hundreds a brand new Untitled mannequin.

In case you change the parameter worth you’ll see you’ll not requested to substantiate operating Native Database Question.

Final Phrase

You possibly can load the parameters’ knowledge into the mannequin which is de facto nice. I’m passing this to you for additional investigations.

Pattern template is Able to Obtain

You possibly can obtain the pattern template I created on prime of AdventureWorksDW2016 and Dynamic Information Masking right here. It accommodates the earlier put up’s samples in addition to what you’ve discovered within the present article.

Associated