PowerBI

This page will walkthrough the setup of PowerBI in K using the direct connect method.

Integration details

Known Power BI limitations (limited by Microsoft)

• Power BI API is currently rate limited. This may impact extracts where you have 200+ workspaces or groups. Some Power BI reports may fail to extract correctly and may their details may not be profiled.

• Power BI datasets using large dataset storage format cannot be profiled.

• Power BI reports created in web browser (not uploaded from desktop) cannot be profiled for detailed lineage. Dataset lineage will be available.

• Power BI dashboards in Apps are not able to be linked to their Power BI dashboard.

Known KADA Power BI integration limitations (to be resolved in upcoming releases)

• Power BI currently only supports lineage to Snowflake, SQL Server and Oracle.

• Power BI reports that use inbuilt fields cannot be profiled for detailed lineage.

• Power BI reports created in web browser missing dataset lineage.

• Power BI access does not consider direct grants to users. Access is only detected for users via AD groups.

• Power BI data flows are not currently included in the metadata extracted.

Step 1) Enabling PowerBI Admin APIs to be accessible to an AD Group

This step is performed by the Power BI Admin

• Open Power BI portal and navigate to Admin Portal in the top right hand navigation

• Go to Tenant Settings and scroll to Admin API settings

• Set Allow Service Principals to use read-only Power BI admin APIs to Enabled

  • Select Specific Security Groups

    • Add an AD group that is appropriate for managing this access. This is typically the Admin group for Power BI. If no appropriate group exists, contact your Azure AD admin to create a new security group.

    • Record this AD group for Step 2

• [Required for Power BI detailed lineage] Set Enhance admin API responses with detailed metadata to Enabled

  

  • Add the same AD group as the previous API Settings option

If you have previously published PowerBI datasets and the Enhance admin APIs response with detailed metadata was not enabled, you will need to republish all PowerBI datasets in workspaces that you want K to profile.

The ability to collect detailed lineage information only applies to datasets that are published after this feature is turned on.

• Set Enhance admin APIs responses with DAX and mashup expressions to Enabled

  • Add the same AD group as the previous API Settings option

• [Required for Power BI detailed lineage] Set Allow Service Principals to use Power BI APIs to Enabled

  • Select Specific Security Groups

    • Add the same AD group as the previous step

Make sure you send the AD Group name to the Azure AD Admin so that they can complete the next step

Step 2) Registering PowerBI App in Azure AD

This step is performed by the Azure AD Admin

• Log in to your company’s Azure Portal and open the Azure Active Directory page

• Select App Registration in the side panel and click New registration

• Complete the registration form

  • Name: Enter a name for the integration e.g. KADA Power BI API Integration

  • Supported account types: Select Accounts in this organisation directory only

  • Redirect URL: Add Web / https://www.kada.ai

• Click Register to complete the registration

• Click on the newly created KADA Power BI API Integration App

• Save the Application (client) ID and Directory (tenant) ID for use in a later step

• Click on Endpoints and save the URL for OpenID Connect metadata document for use in a later step

• Select Certificates & secrets in the side panel and click New client secret

• Complete the new secret form and save the Secret Value for use in a later step

• Return to the Azure Portal and select Group

• Find the AD Group that was created in step 1 and click Members

• Select Add Members and add KADA Power BI API Integration to the group as a Service Principal

Make sure you send the following information to the K Admin so that they can complete the next process.

• Application (client) ID

• Directory (tenant) ID

• Secret Value

Step 3) Add Service Principal to PowerBI Workspace(s)

This step is performed by the Power BI Admin

[Required for Detailed Lineage] Your K instance will now have access to the majority of your Power BI assets. To profile and catalog items such as Pages within a Report, or if your datasource connections use parameters, please add the KADA Power BI API Integration (created in Step 2) as a Member to each Power BI workspace you want it to profile.

Step 4) Add PowerBI as a New Source

This step is performed by the K Admin

• Select Platform Settings in the side bar

• In the pop-out side panel, under Integrations click on Sources

• Click Add Source and select Power_bi

  

• Select Direct Connect and add your Power BI details and click Next

• Fill in the Source Settings and click Save & Next

  • Name: Give the Power BI source a name in K

  • Host: Must be unique. You can use http://app.powerbi.com if you only have 1 instance of Power BI.

  • Timeout: Default is 10, sometimes it may take longer for the API to respond, so we recommend increasing it to 20

  • Update the Host / Database mapping - see Host / Database Mapping. This step can be completed after the initial load via the guided workflow.

  • Select Enable Workspace Filtering if you wish to load only select Workspaces

• Add Connection Details and click Save & Next

  • Tenant ID: Add the Directory (tenant) ID copied from step 2

  • Client ID : Add the Application (client) ID copied from Step 2

  • Client Secret: Add the Secret ID copied from Step 2

• Test your connection and click Next

• If you selected Enabled Workspace Filtering select the Workspaces you want to load. If you have a lot of workspaces this may take a bit of time to load.

• Click Finish Setup

Step 4) Schedule PowerBI source load

• Select Platform Settings in the side bar

• In the pop-out side panel, under Integrations click on Sources

• Locate your new PowerBI Source and click on the Schedule Settings (clock) icon to set the schedule

Note that scheduling a source can take up to 15 minutes to propagate the change.

Step 5) Manually run an ad hoc load to test Power BI

• Next to your new Source, click on the Run manual load icon

  

• Confirm how your want the source to be loaded

  

• After the source load is triggered, a pop up bar will appear taking you to the Monitor tab in the Batch Manager page. This is the usual page you visit to view the progress of source loads

  

A manual source load will also require a manual run of

• DAILY

• GATHER_METRICS_AND_STATS

To load all metrics and indexes with the manually loaded metadata. These can be found in the Batch Manager page

Troubleshooting failed loads

• If the job failed at the extraction step

  • Check the error. Contact KADA Support if required.

  • Rerun the source job

• If the job failed at the load step, the landing folder failed directory will contain the file with issues.

  • Find the bad record and fix the file

  • Rerun the source job

• If you noticed that some reports do not have lineage