PowerBI reports from your Datamart
PowerBI started as a simple reporting tool distributed by Microsoft through it’s popular Office 365 service only a few years back. I remember the early days, when functionality was basic without much distribution capability available.
However, with the latest additions like PowerBI Dataflows, Data Gateways, and dozens of additional connectors, PowerBI has become a serious contender to more conventional reporting tools like Tableau or OBIEE.
So how can create PowerBI reports from your Datamart without giving everybody in your MI/BI team access to GDPR sensitive data?
PowerBI Dataflows using Entities
Using PowerBI Dataflows, you can link to specific databases,tables, or columns from your Datamart based on specific reporting needs. Through PowerBI Dataflows, with a little bit of planning, you can enable PowerBI reports from your Datamart to anyone in your organisation, without them having explicit access to your Datamart. These are the two most popular use cases for PowerBI data sources:
- Your data sits on a Datamart backed by SQLServer
- Your data sits in the Cloud or with someprovider for which you would use a connector
PowerBI Data Gateway with PowerBI Cloud
A PowerBI Data Gateway is a tool that you download from the PowerBI Cloud and install on a VM with access to your Datamart. Once installed and configured, the PowerBI Data Gateway will enable your users to fetch data from your Datamart using the PowerBI Cloud and setup a scheduled refresh rate.
If your data sits somewhere in a Datamart, your IS department needs to provide the following:
- VM with access to the SQL Datamart
- *microsoft.com whitelisted in IE on your VM (this is to provide access to the PowerBI cloud from your VM)
- ADM account with access to the VM (this also needs to be added to the Local Admin Group and of course access to the Databases you want to connect to)
- Service ADM O365 Account with access to the PowerBI Admin Center and the ability to create workspaces
- Data Gateway installed/configured on the VM (downloaded from the Service O365 account and installed on the VM)
Once all the above have been provided by IS, you can get down to building your infrastructure as follows:
- Login to the above VM using the ADM Account provided by your IS department
- In the scenario where you’ve got multiple servers as data sources and you require a more intricate access model, then you would look at configuring multiple gateways with various admins assigned to each.
- If you’re only looking at a single Datamart where all your MI/BI department has access to all resources, then 1 gateway with multiple admins/users should suffice.
- Once you’ve configured your Gateway/s, you can then look at configuring the PowerBI Cloud service
- Suggestion is you use the O365 Service ADM account to manage Workspace/Dataflow/Gateway distribution. Use this account to login to app.powerbi.com
- In the PowerBI Cloud, go to Settings, Manage Gateways and start assigning data sources to each gateway. In here you can also assign more admins to each gateway and users to your data sources.
- Now you can start creating Workspaces by expanding the Workspaces dropdown, select “Create App Workspace”. Your workspace users can either be Admin or Member. So if you’ve setup your Workspace Privacy where Members can only view PowerBI Content, then your MI/BI team members would need to be set as Admin so they can edit reports.
- The last step would be to start creating Dataflows within your Workspaces
A few things to remember when architecting and designing your reporting structure based on a Pro License:
- Each Workspace has a maximum of 10GB storage available or up to 200 datasets
- There are 2 types of Workspaces you can create:
- A workspace that is tied to an underlying Office 365 group
- A workspace that is not tied to an Office 365 group and allows you to also assign permissions to Azure/O365 groups. This option is more granular and allows for central user administration from the AD rather then locally from the PowerBI Cloud.
- If you are connecting to a Datamart with large Databases, you would need to consider multiple Workspaces
- Documentation is key. Make sure you document and create diagrams of what your Workspaces mean, what do they connect to, what Dataflows they contain, access levels.
Why would I need to use PowerBI Dataflows?
Think about GDPR. In general, the usual suspects having access to your entire Datamart are MI/BI/BA people. And usually, in large organisations there are a lot of them. The more people you have with direct access to customer personal data, the more chances somebody could potentially add this data to some report and distribute with the wrong people, hence exposing this to the public. This could result in huge fines for your company, and irreparable damage to your company’s reputation.
By using PowerBI Dataflows, you can target only the necessary fields for a specific report, rather then giving a bunch of people access to the whole Datamart or to entire tables/databases. In theory, a PowerBI Dataflow is a data container with a PowerQuery feed, so essentially you can alter the data that your user has access to. For example, if your MI team needs to produce a report that shows how many valid email/mobile you have in certain regions or customer groups, instead of them having access to the raw data, you can make these figures available to them through a PowerBI Dataflow. In this way, your reporting team has the data they need, and they also have the flexibility to customise the report based on their customer requirements.