Reporting

Power BI - On Cloud

A Power BI cloud connector is a business analytics service that gives a single view of your most critical business data and supports report editing and collaboration for teams and organizations. Using OvalEdge, you can crawl the Reports, Report Columns, Dashboards, Tiles, Pages, Datasets and Dataflows existing in the Power BI Cloud and build the lineage for Report and Report Columns.

PowerBI_arch

Connector Capabilities

The following is the list of objects and data types supported by the Power BI-OnCloud connector

Functionality

Support Data Objects

Crawler 

  • Reports
  • Report Columns
  • Dashboards
  • Tiles
  • Pages
  • Datasets
  • Dataflows

Lineage

  • Report lineage
  • ReportColumn lineage
  • Lineage Sources

Prerequisites

Power BI supports two types of authentication.

  1. Service principal
  2. Service user

Service principal

This section lists the prerequisites for connecting the connector and the OvalEdge application.

  1. Configuration in Azure
  2. Configuration in Power BI
  3. Service Account Minimum Read Permissions 
  4. Configure environment variables (Optional)

Configuration in Azure

1. Creating an App

  1. Sign in to the Azure portal, and search for Azure AD in the Azure services text box. Under Manage, click on App registrations.
    manage-appreg
  2. Click on New Registration.
    newregistr
  3. Register an application - give a user-facing display name for the app, select the supported account types.
  4. Click on Certificates and secrets from the left side menu.
  5. Note the client id and tenant id from the above screenshot. Then create a Client id and Client secret.
  6. Click on Add.
  7. From the above screenshot, copy the client's secret key. This key is used while creating a connection.

2. Creating a new Security Group

Step 1: Create a new Security Group in Azure Active Directory. Read more about creating a basic group and adding members using Azure Active Directory. You can skip this step if you already have a security group you would like to use. Make sure to select Security as the Group type.

Step 2: Add the created service user and app which is created in step 1 to the current security group.

Configuration in Power BI Application

Log in with the Power BI administrator user and enable the below settings:

   1. Enable the Power BI service admin setting

  1. Log in to the Power BI admin portal. You need to be a Power BI admin to see the tenant settings page.
  2. Under Admin API settings, you'll see Allow service principals to use read-only Power BI admin APIs. Set the toggle to Enabled, select the Specific security groups radio button, and add the security group you created in Step 2 in the text field that appears.
  3. To enable these settings, go to Admin portal > Tenant settings > Developer settings.
  4. Select the entire organization option and enable the settings in the Admin Portal > Tenant Settings > Developer settings > Embed Content in apps. 
  5. In Developer settings > Allow service principals to use Power BI APIs, select specific security groups, and enable the settings.
  6. In Developer settings > Allow service principals to create and use profiles, select specific security groups, and enable the settings.
  7. In the Admin Portal > Tenant Settings > Admin API settings > Allow service principals to use read-only Power BI admin, select specific security groups, and enable the settings.
  8. In the Admin API settings > Enhance admin API responses with detailed metadata, select the entire organization option, and enable the settings.
  9. Select the entire organization option and enable the settings in the Admin API settings > Enhance admin API responses with DAX and mashup expressions.  
  10. To download the reports, 
    1. In the Admin Portal > Tenant Settings > Export and sharing settings > Download Reports, select The entire organization option then click Apply.
      All the users in the organization can download the reports. 
    2. Alternatively, if you select the Specific security groups option, enter the specific security group and click Apply. Then, only people in the entered group can download the reports.

2. Create a workspace in the Power BI instance or use the existing workspace

The following are prerequisites for connecting the PowerBI. 

To connect to Power BI from OvalEdge, creating a workspace in the Power BI instance is required. If you already created it, you can skip step 2.1 and move to step 2.2. 

2.1 Creating a Workspace

  1. Navigate to app.powerbi.com.
  2. The First step is to create a workspace (Premium / Non-premier ) in Power BI.
  3. Click on Create a workspace button.
  4. Enter the workspace name.
  5. Click on the Save button in the advanced tab, select the option for Specific users and groups, and then enter the users and groups.                     
  6. Once the workspace is successfully created, search the workspace name.

2.2 Workspace Access

  1. Search results will display the recently created workspace name; next, click on three dots that will display options for the Workspace settings and  workspace access
  2. Click on the workspace access. 

  3. Click on Settings.
  4. An access pop-up window is displayed. Verify the permission details and member permission required on a particular workspace. 
  5. Add the security group that is created in Step 1: point 3  with contributor permission.

Service user

This section lists the prerequisites to establish a connection between the connector and the OvalEdge application. 

  1. Configuration in Azure
  2. Configuration in Office 365
  3. Configuration in Power BI
  4. Service Account Minimum Read Permissions 
  5. Configure environment variables (Optional)

Note: Create a Service User or use any existing user in OvalEdge

Configuration in Azure 

1. Creating an App

  1. Sign in to the Azure portal, and search for Azure AD in the Azure services text box. Under Manage, click on App registrations. 
  2. Click on New Registration.   
  3. Register an application - give a user-facing display name for the app, select the supported account types, and specify the redirect URI as https://app.powerbi.com
  4. Click on Register.
  5. Give API Permissions
  6. From the above screenshot, we can see the Directory Tenant ID, App Client ID.

2. Enabling API permission in Azure 

  1. Click on the API Permissions button to see an API permission window. Select API Permissions from the Manage list.
  2. Based on the below screenshot, enable the Request API Permissions—Microsoft APIs
  3. In the Request API Permissions > Application permissions > select the Tenant Read All permissions.              
    Note: Make sure that Tenant.Read.All must be available for Delegated and Application type with admin consent set to YES.
  4. Create Client Secrets -  Click on Certificates & Secrets—New Client secret, Add Client Secret.
  5.  Note the Secret ID Value.

3. Creating a new Security Group

Step 1: Create a new Security Group in Azure Active Directory. Read more about creating a basic group and adding members using Azure Active Directory. You can skip this step if you already have a security group you would like to use. Make sure to select Security as the Group type.

Step 2: Add the created service user and app which is created in step 1 to the current security group.

Configuration in Office 365

To enable Power BI Admin APIS for the created service user, you have two options: 

  1. Either enable the OvalEdge service user as a Power BI Administrator role or you can use an existing user with the Power BI Administrator role
    Active Users
    Next, you need to configure permissions in Power BI Admin Portal.

Configuration in Power BI Application

Login with the Power BI administrator user and enable the below settings:

1. Enable the Power BI service admin setting

  1. Log in to the Power BI admin portal with the service user credentials. You need to be a Power BI admin to see the tenant settings page.
  2. Under Admin API settings, you'll see Allow service principals to use read-only Power BI admin APIs. Set the toggle to Enabled, select the Specific security groups radio button and add the security group you created in Step 2 in the text field that appears.
  3. To enable these settings, go to Admin portal > Tenant settings > Developer settings.
  4. Select the entire organization option and enable the settings in the Admin Portal > Tenant Settings > Developer settings > Embed Content in apps.
    Developer Settings
  5. In Developer settings > Allow service principals to use Power BI APIs, select specific security groups, and enable the settings.
  6. In Developer settings > Allow service principals to create and use profiles, select specific security groups and enable the settings.
  7. In the Admin Portal > Tenant Settings > Admin API settings > Allow service principals to use read-only Power BI admin, select specific security groups, and enable the settings.
  8. In the Admin API settings > Enhance admin API responses with detailed metadata, select the entire organization option, and enable the settings.
  9. In the Admin API settings > Enhance admin APIs responses with DAX and mashup expressions, select the entire organization option and enable the settings.
  10. To download the reports,
    1. In the Admin Portal > Tenant Settings > Export and sharing settings > Download Reports, select The entire organization option then click Apply.
      All the users in the organization can download the reports.
    2. Alternatively, if you select the Specific security groups option, enter the specific security group then click Apply. Then only entered group people can download the reports.

2. Create a workspace in the Power BI instance 

The following are prerequisites for connecting the PowerBI database. 

To connect to Power BI from OvalEdge, creating a workspace in the Power BI instance is required. If you already created it, you can skip step 2.1 and move to step 2.2. 

2.1 Creating a workspace

  1. Navigate to app.powerbi.com.
  2. The First step is to create a workspace (Premium / Non-premier ) in Power BI.
  3. Click on Create a workspace button.
  4. Enter the workspace name.
  5. Click on the Save button in the advanced tab, select the option for Specific users and groups, and then enter the users and groups.                     
  6. Once the workspace is successfully created, search the workspace name.

2.2 Workspace Access

  1. Search results will display the recently created workspace name; next, click on three dots that will display options for the Workspace settings and  workspace access
  2. Click on the workspace access. 

  3. Click on Settings.
  4. An access pop-up window is displayed. Verify the permission details and member permission required on a particular workspace. 
  5. Add the security group that is created in Step 1: point 3  with contributor permission.

Service Account with Minimum Permissions

The following are the minimum privileges required for a service account user to crawl and profile a connector.

Operation 

Access Permission

Connection Validation

Read

Configure environment variables (Optional)

This section describes the settings or instructions that you should be aware of prior to establishing a connection. If your environments have been configured, skip this step.

Configure Environment Names

The Environment Names allow you to select the environment configured for the specific connector from the dropdown list in the Add Connector pop-up window.
You might want to consider crawling the same schema in both stage and production environments for consistency. The typical environments for crawling are PROD, STG, or Temporary, and may also include QA or other environments. Additionally, crawling a temporary environment can be useful for schema comparisons, which can later be deleted, especially during application upgrade assistance.

Steps to Configure the Environment 

  1. Navigate to Administration > System Settings.
  2. Select the Connector tab.
  3. Find the Key name “connector.environment”.
  4. Enter the desired environment values (PROD, STG) in the value column. 
  5. Click ✔ to save.

Establish a Connection 

To connect to Power BI using the OvalEdge application, complete the following steps:

  1. Log into the OvalEdge application.
  2. Navigate to Administration > Connectors.
  3. Click on the + icon, and the Add Connector with Search Connector pop-up window is displayed.
  4. Select the connection type as Power. The Add Connector with Power BI details pop-up window is displayed.

    Field Name

    Description

    Connector Type

    The selected connection type Power BI is displayed by default.

    The drop-down menu allows you to change the connector type if necessary. The fields associated with the selected connection type are displayed based on the selection.

    Server 

    Type*

    Select Power BI Cloud from the drop-down list. 

    Authentication*

    The Authentication drop-down list allows you to select either UserName and Password or Service Principal.

    Credential Manager*

    The Credential Manager helps manage user credentials, including usernames, passwords, tokens, API keys, certificates, and other sign-in information for various applications, websites, or networks.

    In OvalEdge, the Credential Manager allows connectors to read authentication information in real-time when establishing connections to data sources. This ensures that credentials are managed securely and up-to-date without hardcoding them into configurations.

    OvalEdge supports the following Credential Managers for managing connector credentials:

    • OE Credential Manager
    • AWS Secrets Manager
    • HashiCorp Vault
    • Azure Key Vault

    Note: If any user selects HashiCorp, AzureKeyVault, and AWS SecretsManager, Credential Manager Connector ID field displays. Enter the respective connection id. 

    License Add Ons

    In OvalEdge, each connector comes with a default Connector License, which enables users to crawl and profile data sources to gather metadata and statistical information. When setting up a connector, users can select and add specific features through License Add-Ons for enhanced functionality. 

    Available License Add-Ons:

    • Auto Lineage Add-On: Automatically constructs data lineage for a connector, allowing users to visualize and understand the data flow within and across systems.

    Authentication as Username and Password

    PBIX/PBIT Source*

    You choose either OneDrive or Local Drive as the source. 


    Note: However, it's important to note that if you opt for OneDrive, you need to provide a OneDrive connection and specify the name of the OneDrive folder.

    Connector Name*

    Enter a Connection name for Power BI Cloud.

    Users can specify a connection name to identify the Power BI Cloud connection in OvalEdge. 

    Example: Power BI-OnCloud_db

    Connector Environment

    The environment drop-down menu allows you to select the environment configured for the connector from the drop-down list. For example, PROD, or STG.

    The purpose of the environment field is to help you to understand that the new connector is established in an environment available at the Production, STG, and QA.

    Note: The steps to set up environment variables are explained in the prerequisite section.

    Client Id*

    Client Id generates after the app gets registered

    in Power BI.

    Client Secret*

    A secret is known only to the application authorization server.

    Tenant

    The default value (the organization that owns and manages a specific instance of Microsoft cloud services).

    Tenant Id*

    Enter Tenant ID

    Username*

    A Username required to connect to the Power BI Cloud server. Enter the Service Account Name established to access the Power BI Cloud environment.

    Premium reports(Y/N)

    Select the option for Premium report. When the option is Yes, the user can crawl the report's dataset and when the premium option is selected as NO user can only view the report.

    Okta Enabled(Y/N)

    If Okta is enabled for the given service user, enter ‘Y’; otherwise enter ‘N’.

    Read From NFS(Y/N)

    To retrieve reports directly from the folder without connecting to the Power BI service, enter 'Y'; otherwise, enter 'N'.

    Files Path*

    Enter the server files path. A user needs to provide a temp path to store the exported PBIX files.

    Crawl Hidden Pages(Y/N)

    To crawl the hidden pages, enter ‘Y’; otherwise, enter 'N'.

    Plugin Open In PowerBI Apps(Y/N)

    To open the reports using Apps in Power BI, enter ‘Y’. Else enter ‘N’.

    Note: If the report exist in apps, then only you can open the report through apps. Otherwise it will open through Workspaces.

    Password*

    Enter the required service account password to connect to the Power BI Cloud server.

    Authentication as Service Principal

    PBIX/PBIT Source*

    You choose either OneDrive or Local Drive as the source. 


    Note: However, it's important to note that if you opt for OneDrive, you need to provide a OneDrive connection and specify the name of the OneDrive folder.

    Connector Name*

    Enter a Connection name for Power BI Cloud.

    Users can specify a connection name to identify the Power BI Cloud connection in OvalEdge. 

    Example: Power BI-OnCloud_db

    Connector Environment

    The environment drop-down menu allows you to select the environment configured for the connector from the drop-down list. For example, PROD, or STG.

    The purpose of the environment field is to help you to understand that the new connector is established in an environment available at the Production, STG, and QA.

    Note: The steps to set up environment variables are explained in the prerequisite section.

    Client Id*

    Client Id generates after the app gets registered

    in Power BI.

    Client Secret*

    A secret is known only to the application authorization server.

    Tenant

    The default value (the organization that owns and manages a specific instance of Microsoft cloud services).

    Tenant ID* 

    Enter Tenant ID

    Premium reports(Y/N)

    Select the option for Premium report. When the option is Yes, the user can crawl the report's dataset and when the premium option is selected as NO user can only view the report.

    Okta Enabled(Y/N)

    If Okta is enabled for the given service user, enter ‘Y’; otherwise enter ‘N’.

    Read From NFS(Y/N)

    To retrieve reports directly from the folder without connecting to the Power BI service, enter 'Y'; otherwise, enter 'N'.

    Files Path*

    Enter the server files path. A user needs to provide a temp path to store the exported PBIX files.

    Crawl Hidden Pages(Y/N)

    To crawl the hidden pages, enter ‘Y’; otherwise, enter 'N'.

    Plugin Open In PowerBI Apps(Y/N)

    To open the reports using Apps in Power BI, enter ‘Y’. Else enter ‘N’.

    Note: If the report exist in apps, then only you can open the report through apps. Otherwise it will open through Workspaces.

    Default Governance Roles*

    Users can select a specific user or a  team from the governance roles (Steward, Custodian, Owner) that get assigned for managing the data asset. 


    Note: The dropdown list displays all the configurable roles (single user or a team) as per the configurations made in the OvalEdge Security > Governance Roles section. 

    Admin Roles*

    Select the required admin roles for this connector.

    • To add Integration Admin Roles, search for or select one or more roles from the Integration Admin options, and then click on the Apply button.
      The responsibility of the Integration Admin includes configuring crawling and profiling settings for the connector, as well as deleting connectors, schemas, or data objects.
    • To add Security and Governance Admin roles, search for or select one or more roles from the list, and then click on the Apply button.
      The security and Governance Admin is responsible for:
    • Configure role permissions for the connector and its associated data objects
    • Add admins to set permissions for roles on the connector and its associated data objects
    • Update governance roles
    • Create custom fields
    • Develop Service Request templates for the connector.
    • Create Approval workflows for the templates

    No of Archive Objects*

    The number of archive objects indicates the number of recent metadata modifications made to a dataset at a remote/source location. By default, the archive objects feature is deactivated. However, users may enable it by clicking the Archive toggle button and specifying the number of objects they wish to archive. 

    Select Bridge

    With the OvalEdge Bridge component, any cloud-hosted server can connect with any on-premise or public cloud data sources without modifying firewall rules. A bridge provides real-time control that makes managing data movement between any source and destination easy.


    When the bridge is configured and added, the Bridge ID will be displayed in the dropdown menu, or it will be displayed as "NO BRIDGE."

    For more information, refer to Bridge Overview

5. Click on the Validate button to validate the connection details. 

6. Click on the Save button to save the connection.  Alternatively, the user can also directly click on the Save & Configure button that displays the Connection Settings pop-up window to configure the settings for the selected Connector. The Save & Configure button is displayed only for the Connectors for which the settings configuration is required.

Note: * (asterisk) indicates the mandatory field required to establish a connection. Once the connection is validated and saved, it will be displayed on the Connectors home page. 


Note:  You can either save the connection details first, or you can validate the connection first and then save it. 

Connection Validation Errors

The following are the possible error messages encountered during the validation. 

Error Message(s)

Description

Failed to establish a connection; please check the credentials.

Username and Password validation.

Note: If you have any issues creating a connection, please contact your assigned OvalEdge Customer Success Management (CSM) team.

Connector Settings

Once the connection is validated successfully, various settings are provided to retrieve and display the information from the data source. 

Connection Settings 

Description

Crawler

Crawler settings are configured to connect to a data source and collect and catalog all the data elements in the form of metadata. Check out the crawler options to set the crawler's behavior in the  Crawler & Profiler Settings.

Lineage

The lineage settings allow you to configure multiple dialects (by Selecting Source Server Type for lineage) and connection priority lists to pick the tables to build lineage.

Access Instruction

Access Instruction allows the data owner to instruct others on using the objects in the application.

Business Glossary Settings

The Business Glossary setting provides flexibility and control over how they view and manage term association within the context of a business glossary at the connector level.

Others

The Send Metadata Changes Notifications option is used to set the change notification about the metadata changes of the data objects.

You can use the toggle button to set the Default Governance Roles (Steward, Owner Custodian, etc.) 

From the drop-down menu, you can select the role and team to receive the notification of metadata changes

Note:  For more information, refer to the Connector Settings.

The Crawling of Report Group

A Crawl/Profile button allows you to select one or more  Report Group Names for crawling. 

  1. Navigate to the Connectors page, and click Crawl/Profile. It allows you to select the Report Groups that need to be crawled.
  2. The crawl option is selected by default. 
  3. Click on the Run button that gathers all metadata from the connected source into the OvalEdge Data Catalog. After a successful crawl, all the information is displayed in the Data Catalog > Report / Report Column Tab.

Note: For more information on Scheduling, refer to Scheduling Connector 

Additional Information 

Lineage Settings

The below steps are followed to build the lineage.

  1. Building a lineage with the help of PowerBI PBXFiles and Power BI report metadata which is coming from Admin API. 
  2. If the PBX files are not generated at the time of crawling due to the size of the report, in such cases you need to run an advanced job to export the PBX files. 
  3. While generating the Power BI metadata from Admin API, there are some limitations.
    1. Datasets that have not been refreshed or republished will be returned in API responses but without their detailed low-level information and expressions. For example, you will see the dataset name and lineage in the response but not the dataset's table and column names.
    2. Datasets containing only DirectQuery tables will return low-level details only if they have been republished since enhanced metadata scanning has been enabled. DirectQuery datasets don't use the regular Power BI refresh flow that triggers caching. If a dataset also contains tables that use import mode, caching takes place upon dataset refresh as described above, and it is not necessary for the dataset to be republished for low-level details to be returned.
    3. Real-time datasets, datasets with object-level security, datasets with a live connection to AS-Azure and AS on-prem, and Excel full-fidelity datasets are not supported for detailed metadata. The response returns the reason for not getting detailed metadata about the dataset for unsupported datasets. It is found in a field named schemaRetrievalError, for example, schemaRetrievalError: Unsupported request for RealTime model.
    4. The API doesn't return sub-artifact metadata for datasets that are larger than 1GB in shared workspaces. For Premium workspaces, there is no size limitation.

 


Copyright © 2024, OvalEdge LLC, Peachtree Corners, GA, USA.