Power BI Metadata Scanner

Metadata scanning is a feature that facilitates governance over your organisation's Power BI content by making it possible to quickly catalogue and report on all the metadata of your organisation's Power BI assets. This is very useful to get a complete picture of all your workspaces, datasets, dataflows, users and the lowest level details of the tables and their data types.

This scanning function uses a set of Admin REST APIs that are collectively known as the scanner APIs. With the scanner APIs, you can extract both general information such as artifact name, owner, sensitivity label, endorsement status, and last refresh, as well as more detailed metadata such as dataset table and column names, measures, DAX expressions, mashup queries etc.

This is a very useful tool for the administrators or Power BI power users who can see the status of the workspaces and their usage to take appropriate actions.

General Power BI users can also benefit from this as they can search the catalogue to find what data assets are available within the organisation and accordingly request access to the reports that are of interest to them.

Business Benefits

The metadata scanner dashboard allows business users to search the entire content of the Power BI artifacts of an organisation. A user may not be aware of reports and dashboards developed by other teams. With the metadata dashboard, they can easily find the content and request access accordingly.

They can even search for low-level details like column names, types, data sources and the owner of the content. With this information, a user can contact the owner of the report or data source for any further information.

The metadata scanner dashboard is a great tool for administrators allowing them to easily manage and govern the organisation's content.

Features:

Some of the featured metadata information you can get from the Metadata Scanner are:

1. Summary of all your workspace, datasets, reports, dataflows, data sources and users
   
   
   
2. Dataset page with details of tables and reports
   
   
   
3. Tables summary page with metadata of tables
   
   
   
4. Data sources page with information on the type of data sources like SharePoint, SQL or flat files.
   
   
   
5. Users page displaying users and the datasets they own.
   
   
   
6. Details page displaying underlying information about each artifact in the organisation.
   

Setting up and configuration

The scanning function requires an initial set-up and configuration and then it can be programmed to scan changes in workspaces that have been modified after a certain date.

Huge credit goes to Ferry Bouwman and Tommy Puglia who have created a viable solution that can be integrated into a report. The solution can be downloaded from GitHub. 

This solution requires you to set up some initial configurations. Below are the steps for correctly setting up the pre-requisites.

Step 1: Create an Azure AD app

The first step is to register an Azure AD app and assign your app to a subscription. Follow the instructions here.

A few things to note are:

1. If you are logged in to Office 365, you can log in directly to Azure Portal
2. Check if you have permission to register an app. If not, you will have to ask your Azure administrator to either give you permission or they can do it for you.
3. Generate a client secret for your application and save the details like the app name, app id, object id, secret and secret value in a safe place. 
4. Once you have registered an app, the next step is to assign it to a subscription. You may not be able to access subscriptions so ask your administrator for this step. Follow the instructions in the above link
5. Ensure to assign the “Contributor” role to your app in the subscription section.

Step 2: Create a Security Group in Azure Portal

If you already have a security group, then you can use that or create a new security group. You may have to ask your administrator to do this step if you don’t have permission. 

Follow the instructions here to create a security group.

Step 3: Create a Service Principal and assign your app as a member of this group

Follow the steps here to create a Service Principal and assign your app id (created in step 1) as a member of this Service Principal.

Note: You may have to ask your administrator to do this task.

Step 4: Enable Power BI service admin settings

For this step, you will need to log into Power BI Service as an Admin or ask your administrator to do this task.

Go to Tenant setting > Admin API Settings > Allow service principals to use read-only admin APIs

1. Enable the radio button.
2. Click apply to “Specific security groups”
3. Enter the name of the service principal that you created in step 3.
4. Enable other two options under Admin API Settings i.e., “Enhance admin APIs responses with detailed metadata” and “Enhance admin APIs responses with DAX and mashup expressions”

Step 5: Download the Power BI Metadata Scanner API & Template

Download the solution from GitHub. Download the Zip file. The zip file consists of the following components:

1. Power BI Automate – There are two more zip files under this folder
   1. PBIScanner2022_20220201162202.zip
   2. PBIScannerDailyRefresh_20220201162039.zip
2. Power BI – This folder contains the Power BI report template
3. README 

Step 6: Upload the flows to Power Automate

Log into Power Automate and go to “My flows”. Under “Cloud flows”, click import and browse to the PBIScanner2022_20220201162202.zip file and import the zip file as it is without unzipping it.

Do the same for the second flow PBIScannerDailyRefresh_20220201162039.zip

Step 7. Configure the flows

The last step is to configure the flows and run them to get a Power BI dashboard that you can publish to your Power BI Service.

Follow the instructions under “Installing & Using” section of this article.

Is your business ready to unleash the true power of your business intelligence?

Microsoft Power BI is a business intelligence tool designed to unleash your business potential and help make smarter decisions that propel the organisation forward.

If you’re looking for a better way to manage your data, contact us to discuss an introduction to Microsoft Power BI.