One of many coolest options added to the April 2016 launch of Energy BI Desktop is “Question Parameters”. With Question Parameters we are able to now create parameters in Energy BI Desktop and use them in varied instances. For example, we are able to now outline a question referencing a parameter to retrieve totally different datasets. Or we are able to reference parameters through Filter Rows. Typically talking we are able to reference parameters through:
Information Supply
Filter Rows
Preserve Rows
Take away Rows
Substitute Rows
As well as, parameters will be loaded to the Information Mannequin in order that we are able to reference them from measures, calculated columns, calculated tables and report parts.
In “Energy BI Desktop Question Parameters” sequence of articles I present you the best way to use Question Parameters in several eventualities.
On this article I’ll present you some use instances of Question Parameters primarily based on some eventualities as beneath:
Parameterising a Information Supply
Utilizing Question Parameters in Filter Rows
You’ll study extra about Question Parameters within the subsequent articles “Energy BI Desktop Question Parameters, Half 2, SQL Server Dynamic Information Masking Use Case” and “Energy BI Question Parameters, Half 3, Checklist Output“
You’ll require to satisfy the next necessities to have the ability to comply with this publish:
- The most recent model of Energy BI Desktop (Model: 2.34.4372.322 64-bit (April 2016) or later)
Notice: As Dynamic Information Masking (DDM) is a brand new function of SQL Server 2016 and it’s not accessible within the earlier variations of SQL Server you want to set up the most recent model of SQL Server 2016. So you will want SQL Server 2016 and Journey Works CTP3 solely if you wish to use Question Parameters on prime of Dynamic Information Masking (DDM).
Parameterising a Information Supply may very well be utilized in many alternative use instances. From connecting to totally different knowledge sources outlined in Question Parameters to load totally different combos of columns. To make it extra clear I break down the state of affairs to some extra particular use instances.
Use Case 1: Parameterising Information Supply to Hook up with Totally different Servers and Totally different Databases
Suppose you’ve totally different clients utilizing the identical database schema. However, the databases hosted in several cases of SQL Server and in addition the database names are totally different. With Question Parameters we are able to simply swap between totally different knowledge sources then publish the stories to every clients’ Energy BI Service.
1-Click on “New”
2-Sort a reputation for the parameter
3-You can even write an outline
4-Choose Sort as Textual content
5-From “Allowed Values” choose “Checklist of Values”. This opens an inventory that you could sort in several values for the parameter. When you don’t need to enter ant predefined values for the parameter choose “Any worth” for “Allowed Values”
6-Fill the record with some legitimate values. In our case it could be occasion names
7, 8, 9-Choose a “Default Worth” and “Present Worth” then click on OK
- The info supply refreshes to load knowledge from the brand new server/database
Use Case 2: Loading Dynamic Columns from the Information Supply
Bear in mind the earlier use case. We had totally different clients having totally different databases on totally different servers. Our clients even have totally different reporting wants. For example, they should see their clients’ names in several shapes. The purchasers knowledge saved in DimCustomer. We must always cowl the combos beneath for “Buyer Title” column:
1- Buyer Title = LastName from DimCustomer
2- Buyer Title = FirstName + LastName from DimCustomer
3- Buyer Title = LastName + FirstName from DimCustomer
4- Buyer Title = FirstName + MiddleName + LastName from DimCustomer
To help this we are able to create a parameter containing all combos above for Buyer Title.
Delete DimCustomer from the mannequin we created for the earlier use case
Create a brand new parameter and add all wanted combos within the record of values. You are able to do this by proper clicking on the Queries pane then click on “New Parameter” or by clicking on “Handle Parameters” from the ribbon
Notice: You must put T-SQL syntax to create totally different combos within the values record because the values shall be used as a column within the knowledge supply question.
Now we have to import DimCustomer into the mannequin once more. (Bear in mind, we eliminated DimCustomer in pervious steps)
Click on “New Supply” from the ribbon on Question Editor window
Click on “SQL Server Database” then “Join”
Choose “Server” and “Database” parameters
Click on “Superior choices”
Put the next SQL assertion
SELECT customerkey,
geographykey,
customeralternatekey,
title,
–firstname,
–middlename,
lastname,
namestyle,
birthdate,
maritalstatus,
suffix,
gender,
emailaddress,
yearlyincome,
totalchildren,
numberchildrenathome,
englisheducation,
spanisheducation,
frencheducation,
englishoccupation,
spanishoccupation,
frenchoccupation,
houseownerflag,
numbercarsowned,
addressline1,
addressline2,
telephone,
datefirstpurchase,
commutedistance
FROM DIMCUSTOMER
Notice: I took out “firstname” and “middlename” from the question. The “lastname” column shall be changed with the “CustomerName” parameter within the subsequent steps.
