Tag: Power BI Query Parameters

Business Intelligence - BI, DAX, Good Practices, Power BI, Power BI Desktop RS, Power Query

Good Practices: Always Display the Reporting Environment

Good Practice: Always Display the Reporting Environment

When you work on real-world projects in power BI, you would probably have different environments Like DEV, UAT, Pre-Prod and Prod. It is important for you and your audience to know what the data is coming from. Am I looking at Dev or UAT data or I am actually looking at real data in Production environment. You may have asked or been asked with a question like “Where the data is coming from?”. It is important to know how trustworthy the data you’re analysing is. In this post I show you an easy way to show the environment your Power BI report is connected to.

How It Works

To display the environment name you use query parameters, then you reference that parameter, turn it to a table and add columns to show the environments accordingly. Easy right?

Read more about query parameters from a list output here.

Depending on your scenario the implementation might be slightly different, but the principals are the same. In this post I use a SQL server database. Therefore I need to Parameterise server name. in real world you may also need to parameterise the database name. Again, if your case is quite different, like if you get data from Excel, then the Excel path can be different for different environments. Let’s dig-in.

  • Open Query Editor
  • Click “Manage Parameters”
  • Click “New”
    • Enter “Name” and “description”
    • Tick “Required”
    • Select “Text” in “Type”
    • Select “List of values” in “Suggested Values” and type in server names for different environments
    • pick a “Default Value” and “Current Value”
    • Click OK
Creating new parameter in Power BI Query Editor

So far you created a new parameter that can be used to get data from a SQL Server data source.The next steps show you how to use that parameter to show the environments in your reports.

Continue reading “Good Practices: Always Display the Reporting Environment” →
Power BI, Power BI Desktop, SQL Server, SQL Server 2016, T-SQL

Power BI Desktop Query Parameters, Part2, Dynamic Data Masking and Query Parameters

Power BI Desktop and SQL Server Dynamic Data Masking

As I promised in my earlier post, in this article I show you how to leverage your Power BI Desktop model using Query Parameters on top of SQL Server 2016 Dynamic Data Masking (DDM). I also explain very briefly how to enable DDM on DimCustomer table from AdventureWorksDW2016CTP3 database. We will then create a Power BI Desktop model with Query Parameters on top of DimCustomer table. You will also learn how to create a Power BI Template so that you can use it in the future for deployment.

Note: If you want to learn about using a List output in Power BI Desktop Query Parameters have a look at the next post of these series “Power BI Desktop Query Parameters, Part 3, List Output“.

Use Cases

In the previous post I explained how to create dynamic data sources using Query Parameters. You also learnt how to use Query Parameters in Filter Rows. In this post you learn :

  1. Using Query Parameters on top of SQL Server Dynamic Data Masking (DDM)
  2. Query Parameters in Power BI Template

Requirements

Just like the Part1 of Power BI Query Parameters, you require to meet the following requirements to be able to follow this post:

  1. The latest version of Power BI Desktop (Version: 2.34.4372.322 64-bit (April 2016) or later)
  2. SQL Server 2016 (You can download SQL Server 2016 Developer Edition for free)
  3. AdventureWorksDW

Definitions

I’m not going to provide much details about DDM as you can find lots of information here. But, to make you a bit familiar with Dynamic Data Masking I explain it very briefly.

Dynamic Data Masking (DDM)

Dynamic Data Masking (DDM) is a new feature available in SQL Server 2016 and also Azure SQL Database. DDM is basically a way to prevent sensitive data to be exposed to non-privileged users. It is a data protection feature which hides sensitive data in the result set of a query. You can easily enable DDM on an existing table or enable it on a new table you’re creating. Suppose you have two groups of users in your retail database. Sales Persons and Sales Managers. You have a table of customers which in this post it is DimCustomer from AdventureWorksDW2016CTP3. This table contains sensitive data like customers’ email addresses, phone numbers and their residential adders. Based on your company policy, the members of Sales Persons group should NOT be able to see sensitive data, but, they should be able to all other data. On the other hand the members of Sales Managers group can see all customers’ data. To prevent Sales Persons to see sensitive data you can enable Dynamic Data Masking on the sensitive columns on DimCustomer table. In that case when a sales person queries the table he/she will see masked data. For instance he see uXXX@XXX.com rather than user@domain.com.

Create a table with DDM on some columns

It’s easy, just put “MASKED WITH (FUNCTION = ‘Mask_Function’)” in column definition. So it should look 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 existing table and enable DDM on desired columns

As you guessed you have to use “ALTER TABLE” then “ALTER COLUMN”. Your T-SQL should look 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 more information please refer to MSDN.

Power BI Template

A template is basically a Power BI file that represents an instance of a predefined Power BI Desktop which includes all definitions of the Data Model, Reports, Queries and parameters, but, not includes any data. Creating Power BI Templates is a great way to ease the deployment of existing models. Creating templates is very easy, you just click File –> Export –> Power BI Template. We will look at this more in details through this article.

Scenario

You are asked to implement a new level of security on customers’ data (DimCustomer on AdventureWorksDW2016CTP3 database) so that just privileged users can see the customers’ email, phone numbers and residential address. Privileged users are all members of “SalesManager” database role. You are also asked to prevent “SalesPerson” database role to see sensitive data. But, all members of both “SalesManager” and “SalesPerson” database roles can query DimCustomer table. The users should NOT have SQL Server logins.

Continue reading “Power BI Desktop Query Parameters, Part2, Dynamic Data Masking and Query Parameters” →