Copy Data from Power BI through Azure Data Factory / Synapse Pipeline – Part 1

Problem Statement :

Is it possible to copy data from Power BI via Azure Data Factory (ADF) / Synapse as there is no out of the box connector for Power BI.

Prerequisites :

1. Power BI Report / Dataset
2. Service Principal
   • How to Create a Service Principal

Solution :

Note : Service Principal authentication can be leveraged in case of Cross tenant scenarios wherein the ADF / Synapse Pipelines are in tenant other than the Power BI tenant.

1. Allow Service Principal to access Power BI APIs
   • Go to the Power BI Service at https://app.powerbi.com and open the Admin Portal.
   • Go to the ‘Tenant Settings’ section, and scroll to the setting for ‘Allow service principals to use Power BI APIs’. Enable the setting and Either allow the entire organization to use the Rest APIs (not recommended) or specify an Azure Active Directory Group and make sure your Service Principal is a member of that group

2. Enable the tenant setting Dataset Execute Queries REST API, found under Integration settings.

3. Next step is to give your Service Principal Dataset.Read.All rights on the dataset from which you want to extract the data.

4. We would be leveraging the Power BI REST API : Datasets – Execute Queries for data extraction purpose.

For which below are the values required :

a) datasetId

Login to the required workspace and click on the Dataset

5. One can leverage Web activity to extract data via Power BI REST API as below :

GitHub Code

Get AAD Token Config :

URL : @concat(‘https://login.microsoftonline.com/’,activity(‘Get TenantId from AKV’).output.value,’/oauth2/token’)

Body : @concat(‘grant_type=client_credentials&resource=https://analysis.windows.net/powerbi/api&client_id=’,activity(‘Get ClientId from AKV’).output.value,’&client_secret=’,encodeUriComponent(activity(‘Get Secret from AKV’).output.value))

Extract Data Config :

URL : @concat(‘https://api.powerbi.com/v1.0/myorg/datasets/’,pipeline().parameters.PBIDatasetId,’/executeQueries’)

Header : @concat(string(activity(‘Get AAD Token’).output.token_type),’ ‘,string(activity(‘Get AAD Token’).output.access_token))

Output :

Query : EVALUATE VALUES(Date)

The output is a Nested JSON and in case if we need to flatten it to copy data into a flat file / SQL table, we would need to leverage Dataflow which I have described in my next blog : Copy Data from Power BI through Azure Data Factory / Synapse Pipeline – Part 2

To Overcome the API limitation of max 100000 rows or 15MB Data per query &/or max 4MB Data in Web Activity limitation, please follow the below blog :

Overcoming Data Size / Row Limitations of Power BI REST API for Automated Data Extraction from Dataset