Configure BI Optimizer for Power BI

This article details how to configure the BI Optimizer App for Power BI in Metric Insights.

PREREQUISITES:

  1. Configure Lineage Collection from Power BI and Log Analytics.
    IMPORTANT:     Complete ALL steps of this article to set up Power BI Lineage and Log Analytics specifically for use with Metric Insights:
    1. Grant the Tenant.Read.All permission; (Why BI Optimizer Requires Tenant.Read.All Permission?)
    2. Configure Metric Insights.
  2. The following files requested from Metric Insights support:
  3. The DEFAULT_DATASET_TEXT_COLUMN_SIZE System Variable set to "4100";
  4. Synced Metadata under the Metadata tab for the Power BI Data Source that is to be optimized.
    IMPORTANT: Note the Data Source ID for use in Step 5.1;
  5. Existing Notification Schedule.
    NOTE: The frequency must be set to at least once per day, but it is recommended to configure it to run every two hours;
  6. Configured External Application for the Custom Script. The Application must have "Application Can Manage Users" enabled.

IMPORTANT: If BI Optimizer is already configured for another Data Source, complete Step 1 and Step 5.1, then run the Custom Script.

1. Create Power BI Datasets for Assets

  1. Access +New > Dataset > Create New
  2. Under Data Source, select the name of the Power BI Data Source to be optimized

NOTE: "USAGE, USER_PERMISSIONS" must be listed under the Scope: Types to collect Optional Parameter for the Power BI Data Source for the following objects to be collected.

Create the following Datasets:
It is recommended to use the same naming convention to make the instructions easier to follow.

  1. Power BI Usage Dataset from System/Usage report;
    NOTE:
    • Use param usage_date_to_collect = :measurement_time in the MDX / DAX query Editor
    • Enable Snapshot Dataset? (keep history) for this Dataset
    • Make sure the DEFAULT_DATASET_TEXT_COLUMN_SIZE System Variable is set to "4100".
  2. Power BI User Permissions Dataset from System/Users Permissions report. This Dataset maps Power BI Objects to users and their respective access rights.
  3. Power BI Groups to Users Dataset from System/Groups to Users report. This Dataset maps all users to their Power BI groups. It must include Group ID and User ID columns.

NOTE: If BI Optimizer is already running for another Data Source, proceed to Step 5.1.

For more details on how to create Power BI Datasets, refer to Create a Dataset from Microsoft Power BI Cloud.

2. Create Empty CSV Datasets for App Template Entities and "Why Similar Storage" Variable

Access +New > Dataset > CSV Dataset

  1. Provide a descriptive Name. It is recommended to use the same naming convention to make the instructions easier to follow.
    • Datasets to be created for App Template Entities:
      • Report Cleanup access storage – for the accessData Entity;
      • Report Cleanup excluded storage – for the excludedRollups Entity;
      • Report Cleanup group data storage – for the groupToUserData Entity;
      • Report Cleanup processor storage – for the processorData Entity;
      • Report Cleanup unused storage – for the unusedInfo Entity.
    • Datasets to be created for Why Similar Storage variable:
      • Report Cleanup Criteria – for the criteriaDatasetId property;
      • Report Cleanup Criteria Hash – for the reportHashDatasetId property;
      • Report Cleanup Multipliers – for the multipliersDatasetId property.
        • IMPORTANT: Note the Dataset IDs required in Step 5.1.
  2. Choose a Category for the Dataset.
  3. [Save]

3. Create CSV Dataset for Custom Script

Access +New > Dataset > CSV Dataset

  1. Create a CSV Dataset by uploading a CSV file with one column, reportName. This Dataset is only needed if you want to exclude specific Reports from the optimization. If you use it, make sure to include at least one Report Name.
    • As a Dataset Name, it is recommended to use Report Cleanup Excluded names to make the instructions easier to follow.
    • NOTE: Names are not case sensitive. For example, "sales report" will exclude both "sales report" and "Sales Report".
    • IMPORTANT: Note the Dataset ID for use in Step 6.2. If you don't need to exclude any Reports, the Parameter can be left blank.

For more details on how to create CSV Datasets, refer to Create a Dataset from CSV File.

4. Create App Template

Access Content > Apps > Templates tab > [+New Template]

  1. Provide a descriptive Name such as "Report Cleanup".
  2. Name used in URL must be set to "report-cleanup".
  3. Display without Metric Insights navigation bar must be checked.

4.1. Upload Assets

  1. From the Assets tab, upload the Assets requested from Metric Insights Support as part of the prerequisites.
  2. The uploaded Assets will appear as a list.

5. Create App Based on this Template

Access Content > Apps > [+New App]

  1. Provide a descriptive Name.
  2. Choose the Template created at Step 4.
  3. [Save]
    • IMPORTANT: Note the App ID to be used in Step 6.2. See below.

5.1. Add Variables

Access the Content tab

  1. [+Add Item], then fill in the following fields where needed:
Property Description Value
connectionId
The ID of the Power BI data source to be optimized
datasetId
Power BI Usage Dataset (see Step 1)
workbookLuidKey
Column name for the Workbook LUID key from the datasetID (above)
default: ReportId
rollupNameKey
Column name for the Rollup name key from the datasetID (above)
default: WorkSpaceName
reportNameKey
Column name for the Report name key from the datasetID (above)
default: ReportName
accessListDatasetId
Power BI User Permissions Dataset (see Step 1)
accessListKeysMapping
Mapping of the Access List Dataset keys to the connection report metadata Dataset keys. The mapping should be in JSON formatting. 
The keys in the object must be the keys (column names) from the accessListDatasetId Dataset. 
The value must be one of the following: username, groupName, rollupNameKey, reportNameKey, permission, or a special format object like  
{
 "name": "permission”,
 "value”:
 {
  "Allow": true,
  "Deny": false,
  "[DEFAULT]": false
 }
}
For help with this variable, contact Metric Insights support.
 Copy / Paste from the  accessListKeysMapping code block below.
groupToUserDatasetId
Power BI Groups to Users Dataset (see Step 1)
groupToUserKeysMapping
Mapping of the group to user Dataset keys to the connection report metadata Dataset keys. The mapping should be in JSON formatting.
The keys in the object must be the keys (column names) from the groupToUserDatasetId Dataset. 
The value must be one of the following: username, fullName, groupName.
See the example on the right for reference:
 
{
 "User Email": "username”,
 "User Name": "fullName”,
 "Group Name": "groupName"
}
lastViewedDatasetId
Not currently used for Power BI
lastViewedKeysMapping
Not currently used for Power BI
accessListKeysMapping 
{
  "Email Address": "username",
  "Display Name":
  [
    {
      "name": "groupName",
      "$value":
      {
        "type": "ternary",
        "condition":
        {
          "left":
          {
            "type": "field",
            "name": "Principal Type"
          },
          "operator": "==",
          "right":
          {
            "type": "value",
            "value": "Group"
          }
        },
        "ifTrue":
        {
          "type": "field",
          "name": "Display Name"
        },
        "ifFalse": ""
      }
    },
    {
      "name": "fullName",
      "$value":
      {
        "type": "ternary",
        "condition":
        {
          "left":
          {
            "type": "field",
            "name": "Principal Type"
          },
          "operator": "==",
          "right":
          {
            "type": "value",
            "value": "User"
          }
        },
        "ifTrue":
        {
          "type": "field",
          "name": "Display Name"
        },
        "ifFalse": ""
      }
    }
  ],
  "project": "rollupNameKey",
  "workbook": "reportNameKey",
  "Access Right":
  {
    "name": "permission",
    "value":
    {
      "[DEFAULT]": true
    }
  },
  "Report ID": "guid"
}
Click to copy

2. "Why Similar Storage" variable, fill in the IDs for the Datasets created in Step 2, strictly following  JSON formatting:

  • criteriaDatasetId – Report Cleanup Criteria Dataset ID;
  • reportHashDatasetId – Report Cleanup Criteria Hash Dataset ID;
  • multipliersDatasetId – Report Cleanup Multipliers Dataset ID.

3. [Save]

5.2. Add Entities

Access the Entities tab

  1. [+Add App Entity], then the following Entities and configure them accordingly.
Entity Name "Type" "Access Type" "App Dataset" Dataset
accessData Internal Public Y Report Cleanup access storage
assignee Internal Public    
clusters Internal Public    
excludedRollups Internal Public Y Report Cleanup excluded storage
groupToUserData Internal Public Y Report Cleanup group data storage
history Internal Public    
processorData Internal Public Y Report Cleanup processor storage
recommendations Internal Public    
unusedInfo Internal Public Y Report Cleanup unused storage

NOTE: If BI Optimizer is already running, test-run the Custom Script.

6. Create Custom Script

Access Admin > System > Custom Scripts > [+New Custom Script]

  1. Provide a descriptive Name such as Report Cleanup Processor.
  2. Choose an External Application (See prerequisites above).
  3. Specify an Authentication User (Admin or System Admin).
  4. [Save]

6.1. Add Parameters

  1. From the Info tab, [+Add Parameters], then add the following Parameters and configure them accordingly.
Name Type Required Default Available Values
pageId String Y    
storageEntityName String   processorData  
unusedForMonths Number   6 1,2,3,4,5,6
scriptTimeout Number   600000  
logLevel String   info debug,info,warn,error,
emergency,silent
reportColumnsMatchCaseSensitive String   true ,true
minColumnCountDuplicates Number   2  
worksheetNameMultiplier Number   0.1  
tableMultiplier Number   1  
tableColumnMultiplier Number   1  
reportColumnMultiplier Number   1  
tagMultiplier Number   1  
minOverlapInCluster Number   0.2  
filteredColumnTypes String Y string,date,DateTime  
clustersEntityName String Y clusters  
excludedRollupsEntityName String   excludedRollups  
thresholdReportsToBeUsed Number   0  
skipUnusedCheckForCreatedInDays Number   15  
unusedInfoEntityName String   unusedInfo  
removeUnusedFromClusters String     ,true
accessEntityName String   accessData  
groupToUserEntityName String   groupToUserData  
calculateAccess String     ,true
excludedReportNamesDatasetId Number      
excludedReportNamesObjectKey String   reportName  
minItemsCount Number   8  
excludeReportDashboards String     ,true

6.2. Add Parameter Set

  1. [+Add Parameters Set]
  2. Provide a descriptive Name like Default.
  3. Include the Report Cleanup Excluded names CSV Dataset ID if you are using one.  Otherwise this can be left blank.
  4. Provide the ID of the App configured at Step 5.
  5. Enable Is Default.
  6. Fill in the rest of the fields based on the list below and [Save].

Parameter Description:

  • pageId: The ID of the App created in Step 5 above.
  • storageEntityName: The name of the storage entity where the script will store its data. (See Step 5.2) The default is processorData.
  • unusedForMonths: The number of months without activity after which a report is marked as unused. The default is 6.
  • scriptTimeout: The timeout for the script in milliseconds. The default is 600000 (10 minutes).
  • logLevel: The log level for the script. Values can be debug, info, warn, error, emergency, or silent. The default is info.
  • logColors: Specify whether to log colors in the console. The default is blank.
  • reportColumnsMatchCaseSensitive: Specify whether the report columns are case-sensitive. The default is true.
  • minColumnCountDuplicates: The minimum number of columns to consider for duplicates. The default is 2.
  • worksheetNameMultiplier: The multiplier for the worksheet name. The default is 0.1.
  • tableMultiplier: The multiplier for the table name. The default is 1.
  • tableColumnMultiplier: The multiplier for the table column name. The default is 1.
  • reportColumnMultiplier: The multiplier for the report column name. The default is 1.
  • tagMultiplier: The multiplier for the tag name. The default is 1.
  • minOverlapInCluster: The threshold percentage of similarity between two reports in a cluster, specified as a decimal. A report cluster will be included in the match results when the similarity percentage between any two reports in the cluster exceeds this threshold. Acceptable values range from 0 to 1 (e.g. 0.2, 0.35, 0.5), where 0 represents 0% similarity and 1 represents 100%. The default is 0.2 (20%).
  • filteredColumnTypes: The column types to be filtered out as a comma-separated list of types with no spaces. The default is string,date,DateTime.
  • clustersEntityName: The name of the clusters entity. (See Step 5.2) The default is clusters.
  • excludedRollupsEntityName: The name of the excluded rollups entity. (See Step 5.2) The default is excludedRollups.
  • thresholdReportsToBeUsed: The minimum number of views to consider a report as used. The default is 0.
  • skipUnusedCheckForCreatedInDays: The number of days after the report was created to skip the unused check. The default is 15.
  • unusedInfoEntityName: The name of the unused info entity. (See Step 5.2) The default is unusedInfo.
  • removeUnusedFromClusters: Specify whether to remove unused reports from clusters. The default is blank.
  • accessEntityName: The name of the access entity. (See Step 5.2) The default is accessData.
  • groupToUserEntityName: The name of the group-to-user entity. (See Step 5.2) The default is groupToUserData.
  • calculateAccess: Specify whether to calculate access information. The default is blank.
  • excludedReportNamesDatasetId: The ID of Report Cleanup Excluded names Dataset. (See Step 3) If not used, this can be left blank.
  • excludedReportNamesObjectKey: If using excludedReportNamesDatasetId, the column name from the dataset that contains the excluded report names. The default is reportName.
  • minItemsCount: The minimum number of items required in the report for processing to begin. The default is 8.
  • excludeReportDashboards: Specify whether to exclude the Power BI dashboard from the similar search results. The default is blank.

7. Add Custom Script Code and Run Script

  1. Under the Editor tab, copy and paste the code from the BI Optimizer Custom Script file requested from our support team.
  2. Select the Parameter Set.
  3. [Run Script]
    • See the example output below.

8. Verify Result

Access Content > Apps > Report Cleanup Processor> [View]

9. Associate Notification Schedule with Custom Script

  1. Access Admin > Distribution > Notification Schedules
  2. Select the notification schedule determined in the Prerequisite steps.

NOTE: The Script must run at least once per day, but it is recommended to run it once every two hours. Configure the Notification Schedule accordingly.

  1. Under the Run Scripts tab, [+Add Script].
  2. Choose the Custom Scrip created in Step 7.
  3. Choose the Parameter Set.
  4. [Save]