Use the Power BI REST APIs to get refresh history for all datasets across all workspaces, store the results in JSON and CSV files, and copy these files to Azure Blob Storage. Automate this entire process using an Azure DevOps pipeline that runs on a schedule.
- Create a service principal in Azure Active Directory
- Create a security group in Azure Active Directory
- Grant the security group access to Power BI
- Create an Azure DevOps organization
- Create an Azure DevOps project
- Create an Azure DevOps service connection
- Create an Azure Storage account
- Create an Azure Storage container
- Give the Azure DevOps service connection access to the storage account
- Create an Azure DevOps pipeline variable group
- Create an Azure DevOps git repository
- Create an Azure DevOps pipeline
This service principal will be used by the Get-RefreshHistory.ps1
script to authenticate with the Power BI REST APIs.
- In the Azure portal, navigate to
Azure Active Directory > App registrations > New registration
- Enter a name for the app registration
- Select 'Accounts in this organizational directory only' as the supported account types
- Leave the redirect URI empty
- Click 'Register'
- Copy the 'Application (client) ID' and save it for later as the
AppId
- Click
Certificates & secrets > New client secret
- Enter a description for the client secret
- Select an expiration date
- Click 'Add'
- Copy the client secret value and save it for later as the
AppSecret
- Click 'API permissions'
- Remove the default 'Microsoft Graph' permission(s)
This security group will be used to grant the service principal access to all workspaces in the Power BI tenant.
- In the Azure portal, navigate to
Azure Active Directory > Groups > New group
- Enter a name for the group
- Select 'Security' as the group type
- Set the membership type to 'Assigned'
- Set 'Azure AD roles can be assigned to the group' to 'Yes'
- Click 'No members selected'
- Select the service principal that was created in the previous step
- Click 'Select'
- Click 'Create'
This will allow the service principal to get refresh history for all datasets across all workspaces.
- Navigate to the Power BI Admin Portal
- Click 'Tenant settings'
- Scroll to the 'Developer settings' section
- Set 'Allow service principals to use Power BI APIs' to 'Enabled'
- Set 'Specific security groups' to the security group that was created in the previous step
- Click 'Apply'
- Scroll to the 'Admin API settings' section
- Set 'Allow service principals to use read-only admin APIs' to 'Enabled'
- Set 'Specific security groups' to the security group that was created in the previous step
- Click 'Apply'
- Click on 'Workspaces'
- For each workspace, click on the 'Access' tab, and add the security group created in the previous step as an admin
- Navigate to the Microsoft 365 admin center
- Click on 'Roles > Role assignments > Fabric Administrator > Assigned > Add groups'
- Select the security group created in the previous step
- Click 'Add'
This organization will contain the project that stores the Git code repository and pipeline.
Note: you can skip this step if you do not plan to use Azure DevOps pipelines to automate running the scripts, or if you already have an existing Azure DevOps organization that you want to use.
- In the Azure DevOps portal, navigate to
Organizations > New organization
- Enter a name for the organization
- Enter the captcha
- Click 'Continue'
This project will contain the Git code repository and pipeline that runs on a schedule to get refresh history for all datasets across all workspaces, store the results in JSON and CSV files, and copy these files to Azure Blob Storage.
Note: you can skip this step if you do not plan to use Azure DevOps pipelines to automate running the scripts, or if you already have an existing Azure DevOps project that you want to use.
- In the Azure DevOps portal, navigate to
Projects > New project
- Enter a name for the project
- Select 'Private' as the visibility
- Select 'Git' as the version control
- Select 'Agile' as the work item process
- Click 'Create'
This service connection will be used by the pipeline to authenticate with Azure Resource Manager and copy the JSON and CSV files to Azure Blob Storage. The service connection will be created using a service principal that is automatically generated by Azure DevOps.
Note: you can skip this step if you do not plan to use Azure DevOps pipelines to automate running the scripts, or if you already have an existing Azure DevOps service connection that you want to use.
- In the Azure DevOps portal, navigate to
Project settings > Service connections > New service connection > Azure Resource Manager
- Select 'Service principal (automatic)'
- Select the Azure subscription containing the storage account
- Select the resource group containing the storage account
- Enter a name for the service connection and save it for later as the
ServiceConnection
- Click 'Save'
This storage account will be used to store the JSON and CSV files that contain the refresh history for all datasets across all workspaces.
Note: you can skip this step if you do not plan to use Azure DevOps pipelines to automate running the scripts, or if you already have an existing Azure Storage account that you want to use.
- In the Azure portal, navigate to
Storage accounts > Add
- Enter a name for the storage account and save it for later as the
StorageAccount
- Select the Azure subscription containing the storage account
- Select the resource group containing the storage account
- Select the location of the storage account
- Select 'StorageV2 (general purpose v2)' as the account kind
- Select 'Hot' as the performance
- Select 'LRS' as the replication
- Click 'Review + create'
- Click 'Create'
This storage container will be used to store the JSON and CSV files that contain the refresh history for all datasets across all workspaces.
Note: you can skip this step if you do not plan to use Azure DevOps pipelines to automate running the scripts, or if you already have an existing Azure Storage container that you want to use.
- In the Azure portal, navigate to
Storage accounts > Select storage account > Containers > + Container
- Enter a name for the container and save it for later as the
StorageContainer
- Select 'Blob' as the public access level
- Click 'OK'
This will allow the pipeline to copy the JSON and CSV files to Azure Blob Storage.
Note: you can skip this step if you do not plan to use Azure DevOps pipelines to automate running the scripts.
- In the Azure portal, navigate to
Storage accounts > Select storage account > Access control (IAM) > Add > Add role assignment
- Select the role 'Storage Blob Data Owner'
- Select the service principal generated when you created the service connection (it will be in the format
<AzDevOpsOrgName>-<AzDevOpsProjectName>-<GUID>
) - Click 'Next'
- Click 'Review + create'
This variable group will contain the variables that are used by the pipeline.
Note: you can skip this step if you do not plan to use Azure DevOps pipelines to automate running the scripts.
- In the Azure DevOps portal, navigate to
Pipelines > Library > Variable groups > + Variable group
- Enter the name
pbi-rest-api-variables
for the variable group - Add the following variables and their values to the variable group:
TenantId
- The Azure Active Directory tenant IDAppId
- The application ID of the service principalAppSecret
- The client secret of the service principal (click the lock icon to encrypt the value)ServiceConnection
- The name of the Azure Resource Manager service connectionStorageAccount
- The name of the Azure Storage accountStorageContainer
- The name of the Azure Storage container
This repository will contain the PowerShell script Get-RefreshHistory.ps1
and YAML pipeline definition pbi-rest-api.yml
files.
Note: you can skip this step if you do not plan to use Azure DevOps pipelines to automate running the scripts, or if you already have an existing Azure DevOps git repository that you want to use.
- In the Azure DevOps portal, navigate to
Repos > Files > + New Repository
- Enter a name for the repository
- Click 'Create'
- From the context menu, click 'Upload files'
- Click 'Browse'
- Select the
Get-RefreshHistory.ps1
andpbi-rest-api.yml
files - Click 'Commit'
This pipeline will run on a schedule to get refresh history.
Note: you can skip this step if you do not plan to use Azure DevOps pipelines to automate running the scripts.
- In the Azure DevOps portal, navigate to
Pipelines > Pipelines > New pipeline
- Select 'Azure Repos Git'
- Select the Azure DevOps git repository containing the
Get-RefreshHistory.ps1
andpbi-rest-api.yml
files - Select 'Existing Azure Pipelines YAML file'
- Select the
pbi-rest-api.yml
file - Click 'Continue'
- Click 'Save'