As I promised in my earlier publish, on this article I present you methods 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 methods 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 so that you could use it sooner or later for deployment.
Word: If you wish to study utilizing a Checklist output in Energy BI Desktop Question Parameters take a look on the subsequent publish of those collection “Energy BI Desktop Question Parameters, Half 3, Checklist Output“.
Within the earlier publish I defined methods to create dynamic information sources utilizing Question Parameters. You additionally learnt methods to use Question Parameters in Filter Rows. On this publish you be taught :
- Utilizing Question Parameters on prime of SQL Server Dynamic Information Masking (DDM)
- 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 publish:
- The newest model of Energy BI Desktop (Model: 2.34.4372.322 64-bit (April 2016) or later)
- SQL Server 2016 (You’ll be able to obtain SQL Server 2016 Developer Version totally free)
- AdventureWorksDW
I’m not going to offer a lot particulars about DDM as you will discover a number of info 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 characteristic out there in SQL Server 2016 and in addition Azure SQL Database. DDM is mainly a solution to stop delicate information to be uncovered to non-privileged customers. It’s a information safety characteristic which hides delicate information within the end result set of a question. You’ll be able to simply allow DDM on an present desk or allow it on a brand new desk you’re creating. Suppose you’ve gotten two teams of customers in your retail database. Gross sales Individuals and Gross sales Managers. You’ve gotten a desk of consumers which on this publish it’s DimCustomer from AdventureWorksDW2016CTP3. This desk comprises delicate information like clients’ e 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 information, however, they need to be capable of all different information. However the members of Gross sales Managers group can see all clients’ information. To forestall Gross sales Individuals to see delicate information you’ll be able to 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 information. As an 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 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 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 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 info please seek advice from 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 Information Mannequin, Reviews, Queries and parameters, however, not contains any information. Creating Energy BI Templates is a good 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 means of this text.
You’re requested to implement a brand new stage of safety on clients’ information (DimCustomer on AdventureWorksDW2016CTP3 database) in order that simply privileged customers can see the shoppers’ e mail, telephone numbers and residential tackle. Privileged customers are all members of “SalesManager” database position. You’re additionally requested to forestall “SalesPerson” database position to see delicate information. 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 information
SalesPerson database position is privileged to see masked information 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 beneath:
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 information
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 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 beneath 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 = ’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 information
GRANT UNMASK TO SalesManager
GO
Energy BI Desktop Implementation
Open Energy BI Desktop
Get information from SQL Server Database
Kind server title and database title
Click on “Superior choices”
Copy and paste the code snipped beneath 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, ought to be discarded.”
- Click on “Apply Modifications”
- Now you need to see “Query1” within the mannequin
- Click on “Edit Queries” from the ribbon
- In case you scroll proper you’ll see masked information for “EmailAddress”, “Cellphone” and “AddressLine1”
- Rename the question to DimCustomer
You now have to create a parameter for the customers. This parameter can be referenced within the information supply later
- Click on “Handle Parameters” from the ribbon
- Click on “New”
- Enter a reputation and outline
- Choose “Textual content” as Kind and “Checklist of values” as Allowed Values
- Kind “user1_nologin” and “user2_nologin” within the values listing
- Choose “user2_nologin” in each default and present worth then click on OK
You should reference the DBUser parameter in DimCustomer information supply.
- Click on DimCustomer from Queries pane
- Click on “Superior Editor”
- Change “user2_nologin” with “”&DBUser&””
Word: Please be aware the place you set the citation marks.
- 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.
- Click on “Edit Parameters” from the ribbon
- Choose “user1_nologin” from the listing then click on OK
- Verify operating Native Database Question
Oops! You bought that nasty error message once more. In fact, you’ll be able to shut the message and click on “Apply Modifications”, however, it doesn’t look lifelike to get that error message every time we change the consumer.
What is absolutely incorrect with the question we wrote?
The reply is that there’s nothing incorrect 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 hook up with the database which on this pattern is a Home windows consumer. That is known as “Context Switching”. Principally Energy BI Desktop needs reset the standing of the present connection and reuse it for a unique consumer. Resetting the present session causes the issue.
By the best way, let’s shut the error message and click on “Apply Modifications” to guarantee that we are able to see unmasked information after switching the consumer.
As you see the method works nice, however, we want a treatment for this to eliminate 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 go the saved process names to the info supply relatively than the customers.
Word: You’ll be able to create only one saved process. In that case, it’s good to 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
- Change the prevailing 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
- Change the entire question with the next
“EXEC “&DBUser”
Word: Word 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 acknowledged earlier than, making a Energy BI Template is very 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 masses a brand new Untitled mannequin.
In case you change the parameter worth you’ll see you’ll now not requested to verify operating Native Database Question.
Final Phrase
You’ll be able to load the parameters’ information into the mannequin which is absolutely nice. I’m passing this to you for additional investigations.
Pattern template is Able to Obtain
You’ll be able to obtain the pattern template I created on prime of AdventureWorksDW2016 and Dynamic Information Masking right here. It comprises the earlier publish’s samples in addition to what you’ve discovered within the present article.
