Enterprise capabilities in Power BI

[ankitpatira]

Past releases in power bi have seen new enterprise related capabilities coming out which lets you manage power bi usage within your organisation. Few such feature that came out with Power BI desktop April update is query parameters and template files. In this blog post we will look at how to utilise those two features within power bi desktop to meet specific organisation needs.

Say for example you work for an organisation that has many divisions and each divisions has its own database. Within those divisions you have power bi users that uses their division specific database and develop reports. You as an administrator would like to have some capabilities to manage those reports such as apply version control, organisation specific branding, templates and layout. It would also make sense to have a central repository for each pbix file that users would develop for their division as currently if you lose pbix file and report is published, there is no way to recover that report from power bi service except create a new one. Using template files and query parameters you will be able to have a solution for above mentioned scenarios.

Query Parameters

Query parameters lets you create parameters that can be used as a data source within your reports. For this post I am going to assume you have SQL Server database as your data source.

In power bi desktop click Edit Queries. In Home ribbon, under Parameters tab click Manage Parameters dropdown and select New Parameter.

In parameters window fill appropriate details as shown below with Required checkbox ticked, Type as ‘Text’ and ‘Any value’ for Allowed Values. Provide sql server and instance name in format of servername\instancename in Current Value field and click OK.

Click again on Manage Parameters -> New Parameters and create new parameter as shown below.

Next click on New Source -> SQL Server Database -> for server dropdown choose Parameter as ConnDetails and for Database dropdown choose Parameter as DatabaseName and click OK. Provided power bi was able to make connection you will see navigator with database objects. Choose tables you require and click OK. Finally click Close & Apply at the top left hand of query editor.

At this stage you have parameterised data source in power bi. Build your visuals and save pbix file. Next you need to create template file that can be distributed to other users.

Template Files

Introduced in power bi desktop April update, template files (.pbit)  are similar to your pbix file except without data. So think of it as a template for your reports that you can distribute to your users and they just need to open it in power bi desktop, connect it to their data source and publish. As an administrator you will develop reports and layout which your users don’t need to worry about. This particular feature is very useful when you have a large organisation and lot of users using power bi to develop and publish reports. This way you will have control over not just layout of the reports but individual users won’t have to worry about maintaining pbix files as one template file can be maintained and distributed as required.

Open report you created above in power bi desktop.

Next click on File -> Export -> Power BI Template.

Provide template description -> OK -> save pbit file.

Once saved power bi template files with query parameters can be distributed to other users. Users when open template file will be prompted to enter parameter values based on parameter type such as dropdown or text field. In our example, user will be prompted to enter ConnDetails and DatabaseName paramters as both are text parameters.

Once parameter values are filled power bi will connect to data source and all visuals will appear.

[heathernicole]

Great article and good to know. This will be helpful for our company as we look to integrate Power BI more and more.

Thanks! 

[ankitpatira]

@heathernicole glad it was helpful.

[dejanle]

 Hi, I followed your procedure, however it does not work for me. I want to connect to SQL server with Microsoft Dynamics NAV database on my local PC (localhost). I created the two parameters you advise. I saved the template and sent it to my colleague who then imported the template. On both PCs server name is localhost. On my server Database name is ADACTA_NAV_90_DEMO and on my colleagues server Database name is ADACTA_NAV_90_BLD. He gets to enter Server and Database details, however when he wants to Load data, he gets first a pop up:

Encryption support

The data source you're trying to access doesn't support encryption. To access this data soure using and unencrypted connection, clik OK.

Then we he does that Loading starts however in tends up with 

localhost;ADACTA_NAV_90_DEMO: The user was not authorized error.

Use my current credentials is selected

We are both administrators on our respecitve PCs and use windows authentication for  SQL/NAV databases.

Any hints?

[quratzafar]

Good post! Tempaltes are useful but I really wish we could use a template to create a new report in a single .pbix file.

Let me explain. Assume that you have two people working on creating multiple reports on same dataset. 

a) Create .pbix file with the data model

b) Using this file both developers work concurrently 

c) Once both are done, Person B should be able to export his report as a template and then import it in Person A's 

file without having to manually replicate the work. 

Thus the ability to add a new page based on template would really help us out as working serially is a wastage of time