sid-powerbi-tools v0.12.0

SID Power BI Tools

This project provides a number of tools to help users of the Sussex Integrated Dataset work with Power BI as follows:

• Tools for setting up role based access control.
• More to come ...

Installation

You will need node.js to be installed, but this is already pre-installed on all trusted analysts VMs.

> npm install sid-powerbi-tools -g

To update to the latest version:

> npm update sid-powerbi-tools -g

Configuration

Once installed, you now need to configure database credentials and set up a configuraton file for the row level security feature.

Configure database credentials

> pbit configuration set

And enter your database host, database name, user and password. The password is stored in the Mac keychain, Windows credential vault or Linux secret service API. All other values are stored in the .pbit_config file placed in the user home folder.

Set up row level security configuration

The row level security commands need to understand the security that should be applied to each report. For example, the breast screening report will need different row level security to the hypertension report.

Firstly generate the configuration file using the follwoing command:

> pbit configuration create_report_config_template

This creates a file report_config.json in your current working folder. You should now edit this file for the report that you wish to generate. Further guidance is in the file itself.

Generate roles and table permissions to support row level security:

If you need to create dozens of roles with row level filters in Power BI, you may think that your only option is to enter them manually ... something that is time consuming and error prone. You'd be right, except for the generate tools that can allow you to automate the role and permission creation to some extent.

How does it work?

I know of no way to automate this with Power BI itself. This solution is slightly cumbersome but saves you time by generating XML that can be executed by DAX Studio. The utilities are also partially hard coded. Unfortunately the XML requires a value for the database ID that will be different for each Power BI report, and the permission filters will also vary from report to report. Rather than set up separate configuration files, I decided that the tools would be easier to use if the configuration is hard coded within it.

Step by step

1. Firstly you need to generate roles for GPs, PCNs and places with the following commands:

> pbit generate_create_roles -r breastscreening -t gp -o roles_for_gp.xml
> pbit generate_create_roles -r breastscreening -t pcn -o roles_for_pcn.xml
> pbit generate_create_roles -r breastscreening -t place -o roles_for_place.xml

These tools connect to the SID database and retrieve the latest reference information on Sussex PCNs and GPs. This is used to generate the XML for creating the roles.

2. Next you need to open the DAX Studio app and connect it to the Power BI report. Once connected, copy and paste the XML from each of the roles_for_gp.xml, roles_for_pcn.xml and roles_for_place.xml files into DAX Studio, then run the XML in turn to create the roles. You can now see the roles that have been created within Power BI by clicking on Modelling > Manage Roles. Note that you may need to delete all roles from the Power BI report first. See bulk row deletion below for details.

3. Before we can create the role permissions, we need to know the internal IDs of the roles that have just been created. You will need to copy the following command into DAX Studio:

select * from $SYSTEM.TMSCHEMA_ROLES

and run it. This will list all details of the roles in tabular form. You should then copy all the table data and paste it into a file in the current working folder called roles.tsv.

4. Generate the role permissions XML by running the following command:

pbit generate_role_permissions -r breastscreening -o role_permissions.xml

5. Finally, copy the XML in role_permissions.xml, paste it into DAX Studio, then run it. This should create all the necessary permissions.

Bulk role deletion:

If you have several hundred roles in your Power BI report, deleting them manually will be very tedious. The following will generate XML that can be pasted into DAX Studio to delete all the roles in one go.

1. Before we can delete the roles, we need to know the internal IDs of the roles currently within the Power BI report. You will need to copy the following command into DAX Studio:

select * from $SYSTEM.TMSCHEMA_ROLES

and run it. This will list all details of the roles in tabular form. You should then copy all the table data and paste it into a file in the current working folder called roles.tsv.

2. Next generate the XML that will actually delete the roles:

pbit generate_delete_roles roles.tsv -r breastscreening -o delete_roles.xml

Paste the contents of delete_roles.xml into DAX Studio to perform the deletion.

Adding support for more reports

Each report (eg. breastscreening) will need different row level access control rules to be applied, and the XML that is generated uses internal IDs unique to each report. The tool therefore needs unique configuration for each report.

Rather than creating separate configuration files outside of source code control, this tool uses internal configuration that needs to be updated to provide support for more reports. While it makes it slightly more difficult to add support for a new report, it does keep everything in one place and makes the tool easier to use once set up.

Add support by using the Set up for development guidance below. You will need to edit a single file 'config/powerbi_reports.ts' to add support for new reports.

Set up for development

> cd f:/Users/[your user name]/Developer/Workspaces
> git clone https://SussexOurCareConnected@dev.azure.com/SussexOurCareConnected/SID/_git/sid-powerbi-tools
> cd sid-powerbi-tools
> npm install

You can run and develop in two ways:

1. Use the appropriate run script.

> npm run start:windows:configuration
> npm run start:windows:create
> npm run start:windows:delete
> npm run start:windows:perm

You may need to alter the script arguments within the package.json file to get the desired behaviour. This method runs the tool using nodemon so all you have to do is change your code, then type 'rs' to restart it.

2. Build and running locally is a slighly less convenient way of running the tool. Once you have made code changes, build and run with the following commands:

> npm run build
> npm run local configuration show

To release the package to npm, increase the version number in package.json, then:

> npm run build
> npm publish