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.

Connect to the Data: Before crawling and building a data catalog, you must first connect to your data. OvalEdge requires users to configure a separate connection for each data source type. The users must enter the source credentials and database information for each type of connectivity. Once a data connection is made, a simple click of the Crawl button starts the crawling process.

Prerequisite

Before you start the configuration setup of connecting PowerBI with OvalEdge, create a Service user for OvalEdge, or you can use the available user and then follow the below steps. 

Step1: Configuration in Office 365

     To enable PowerBI administration, 

1. The OvalEdge service user must be the Power BI Administrator.
   
2. After enabling Power BI Administrator permissions, you need to configure permissions in azure.

Step2: 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, and App Client ID.

2. Enabling API permission in Azure

1. Click on the View 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.    
   
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

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.

4. Enable Service Principal Authentication for Read-only Admin APIs

The service principal is an authentication method that can be used to let an Azure Active Directory (Azure AD) application access Power BI service content and APIs. A service principal object is created when you create an Azure AD app. The service principal object, known simply as the service principal, allows Azure AD to authenticate your app. Once authenticated, the app can access Azure AD tenant resources.

Add your App-Id as a member of the security group you created. To do so:

1. Navigate to Azure portal > Azure Active Directory > Groups, and choose the security group you created in Step 2.
2. Select Add Members.  Ensure the app you use doesn't have any Power BI admin roles in the Azure portal. To check the assigned roles:

1. Sign in to the Azure portal as a Global Administrator, an Application Administrator, or a Cloud Application Administrator.
2. Select Azure Active Directory, then Enterprise applications.
3. Select the application you want to grant access to Power BI.
4. Select Permissions.
   
   

Step3: Configuration in Power BI Application 

1. Enable the PowerBI 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.
   
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 APIs 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.
   

2. Create a workspace in the PowerBI instance 

The following are prerequisites for connecting the PowerBI database.

To connect to PowerBI from OvalEdge, creating a workspace in the PowerBI instance is required. If you already created it, you can skip step 1 and move to step 2. 

Creating a workspace

1. Navigate to app.powerbi.com.
2. The First step is to create a workspace (Premium / Non-premier )  in PowerBI.
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.

Workspace Access

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. 

1. Click on the workspace access.
   
2. Click on Settings
   
3. An access pop-up window is displayed. Verify the permission details and member permission required on a particular workspace. 
   

Connect to PowerBI with OvalEdge

You must know the following technical specifications and User permission. 

Technical Specifications

Connectors Capabilities	Supported Data Objects
Crawler	Reports
	Report Columns
	Dashboards
	Tiles
	Pages
	Datasets
	Dataflows
Lineage	Report lineage
	ReportColumn lineage
	Lineage Sources

User Permission

To use the PowerBI Cloud connector, the details specified in the following section should be available.

Operation	Roles	Access Permission
Connection Validation	Admin/service account	Read

Connection Details

Complete the following steps to connect to the PowerBI using the OvalEdge application.

1. Log in to the OvalEdge application
2. Navigate to Administration > Connector module. 
3. Click on the + icon, and the Manage Connection with Search Connector pop-up window is displayed. Select the connection type as Power BI. The Manage Connection with PowerBI specific details pop-up window is displayed.

The following are the field attributes required for the connection.

Configuration	Description
Connection Type	By default, the selected connection type is displayed as PowerBI. If required, the connection type can be changed, and depending on the connector selected, fields are displayed accordingly.
Authentication	On-premises or Cloud
License Type*	Select the license type as Auto Lineage. License Type:  In a license type, the permissions are specified based on the customer's requirements. The user has the option to select the license type as Standard or Auto Lineage.  The connector license is categorized into (i) Standard: The standard connectors may not have Auto Lineage functionality. It will not build the lineage for the selected database. (ii) Auto Lineage: Additionally, auto lineage connectors have Auto Lineage functionality. It will build the lineage for the selected database. See, License Types for more information.
Connection Name*	The connection name specified in the Connection Name textbox will be a reference to the PowerBI database connection in the OvalEdge application. Enter the name of the connection Example: Powerbi_accountsdb
Host Name*	Database instance URL (on-premises/cloud-based)
Client ID*	Identifier ID generated after the app is 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*	User name for Power BI service account
Password*	Password for Power BI service account
Files Path	To enter the server files path
Connection String*	https://app.powerni.com (powerbicloud)
Default Governance Roles	From the dropdown list, select Stewards, Custodian, and Owner.
Premium Report(Y/N)	Select the option for Premium report. When the option is Yes, the user can crawl the report's dataset and view the report when the premium option is No.
No of the archive objects	By default, the number of archive objects is set to disable mode. Click on the Archive toggle button and enter the number of objects you wish to archive. No. of archive objects:  It is the count of the number of last modifications made in the metadata data of a Remote/source.  For example, if you update the count as 4 in the ‘No. of archive object’ field, and then the connection is crawled. It will provide the last 4 changes that occurred in the remote/source of the connector. You can observe these changes in the ‘version’ column of the ‘Metadata Changes’ module.
Select Bridge	The Bridge ID will be shown in the Bridge dropdown menu when bridges are configured and added, or it will be displayed as "NO BRIDGE".  Bridge: To collect data from both on-premises and cloud sources, data administrators need to configure Cloud Bridge by specifying a data source connection and unique parameters.

4. Once after entering the connection details in the required fields, 
Click on the Save button or Save & Configure to establish and configure the connection settings. When you click the Save & Configure button, the Connection Settings pop-up window is displayed, where you can configure the connection settings for the selected Connector.
  5. click on the Validate button the entered connection details are validated. 

Error Validation Details

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

Error Messages	Description
AADSTS7000215: Invalid client secret provided. Ensure the secret sent in the request is the client's secret value, not the client's secret ID	The client's Secret ID is invalid.
AADSTS50126: Error validating credentials due to invalid username or password.	Invalid Username or Password.

Connection Settings

Crawler

The crawler setting will help get the data source's information based on the crawler setting provided. Crawler setting has various settings options, including Crawler options, Crawler Rules, and Query Log Setting. 

Crawler Settings	Descriptions
Crawler options	Tables, Views, and Columns: This crawling will discover the tables, views, and Columns from the PoweBI database and bring them into OvalEdge. This is the default option for crawling. Procedures, Functions & Views Source Code: The crawling will discover the procedures, functions, and views source code within the schemas selected and bring them into OvalEdge.  Relationship: This crawl job will look at the schemas selected, determine relationships between the tables, and bring this information into OvalEdge.
Crawler Rules	Include Regex: Enter the specific schema, table, views, and column names that start with, end with, or have middle characters that are included for crawling.
	Exclude Regex: Enter the specific schema, table, views, and column names that start with, end with, or have middle characters that are excluded for crawling.

Lineage

1. Building a lineage with the help of PowerBI PBXFiles and Power BI report metadata which is coming from Admin API. 
2. Sometimes PBX is not downloaded because of the large file size. In such a case, we need PBIT or PBX, which need to be downloaded manually and placed in your path, which you gave at the time of creating the connection. 
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.