If you would like to have Azure Data Lake Storage generation 2 (AKA StorageV2) implemented within your power BI Dataflows, I’ve created a step-by-step guide on how to do so. Follow along with these steps and you should be on your way to implementing ADLSgen2 with Power BI Dataflows in no time!
Create the Storage Account
Before you create an account, you first create a resource group that acts as a logical container to storage accounts or any other Azure resources you create.
Note: You must create new storage accounts as type StorageV2 (general-purpose V2) to take advantage of Data Lake Storage Gen2 features.
When naming your storage account, keep these rules in mind:
- Storage account names must be between 3 and 24 characters in length and may contain numbers and lowercase letters only.
- Your storage account name must be unique within Azure. No two storage accounts can have the same name.
Sign in to the Azure portal.
Create a resource group
To create a resource group in the Azure portal, follow these steps:
- In the Azure portal, expand the menu on the left side to open the menu of services, and choose Resource Groups.
- Click the Add button to add a new resource group.
- Enter a name for the new resource group: “pbidataflows”
- Select the subscription in which to create the new resource group: “PWTraining”
- Choose the location for the resource group: (US) North Central US
- Click the Review & Create button, then the Create button.
Create a general-purpose v2 storage account
- In the Azure portal, expand the menu on the left side to open the menu of services, and choose All services. Then, scroll down to Storage, and choose Storage accounts. On the Storage Accounts window that appears, choose Add.
- Select your Subscription and the Resource group you created earlier.
- Enter a name for your storage account: dataflowspbi
- Set Location to North Central US
- Leave these fields set to their defaults: Performance, Account kind, Replication, Access tier.
- Choose the subscription in which you want to create the storage account.
- Select Next : Advanced >
- Leave the values under SECURITY and VIRTUAL NETWORKS fields set to their defaults.
- In the Data Lake Storage Gen2 section set Hierarchical namespace to Enabled.
- Click Review + Create to create the storage account.
THERE ARE TWO OPTIONS TO CREATE BLOB/FILE SYSTEM: HERE ARE BOTH WAYS. ALSO NOTE THAT BOTH OF THESE METHODS REQUIRE DOING SOMETHING EXTRA TO MAKE IT WORK. IN STEP 1 YOU CAN CREATE A FILE SYSTEM BUT ALSO NEED TO ADD A READER ROLE IN THE SAME LOCATION. STEP 2 YOU CAN CREATE A FILE SYSTEM BUT ALSO NEED TO ADD PERMISSIONS AT THE OBJECT ID LEVEL. I AM GOING TO LEAVE IT UP TO YOU WHICH WAY YOU WANT TO CREATE THE FILE SYSTEM, BUT I RECOMMEND ONLY CREATING THE FILE SYSTEM FROM STEP 1 AND GOING SEQUENTIALLY.
You must create a file system named powerbi before your storage account can be added to Power BI. There are many ways to create such a file system, including using Azure Databricks, HDInsight, AZCopy, or Azure Storage Explorer.
1) Create a File System for Power BI & Grant the Power BI service a reader role
- In the Azure portal, navigate to your storage account and display the Overview for the account.
- Under Data Lake Storage, select File Systems
- Hit +File system button to create a new File System: powerbi
- Select Access control (IAM) to display access control settings for the container. Select the Role assignments tab to see the list of role assignments.
- Click the Add role assignment button to add a new role.
- In the Add role assignment window, select the Reader role to assign to the Power BI service. Then use search to locate Power BI Service. The following image shows the Reader role assigned to the Power BI service.
Allow at least 30 minutes for permission for propagate to Power BI from the portal. Any time you change permissions in the portal, allow 30 minutes for those permissions to be reflected in Power BI.
2) Create a file system for Power BI & Grant Power BI Permissions to the file system
This step requires that you install Azure Storage Explorer version 1.6.2 or higher. To install Azure Storage Explorer for Windows, Macintosh, or Linux, see Azure Storage Explorer.
- Once you’ve successfully installed Azure Storage Explorer, on first launch, the Microsoft Azure Storage Explorer – Connect window is shown. While Storage Explorer provides several ways to connect to storage accounts, only one way is currently supported for the required setup.
- In the left pane, locate and expand the storage account you created above.
- Right-click Blob Containers, and – from the context menu – select Create Blob Container.
- A text box will appear below the Blob Containers folder. Enter the name powerbi (you already did this in the first create a file system step)
- Press Enter when done to create the blob container
In the next section, you grant the Power BI family of services full access to the file system you created.
Grant Power BI permissions to the file system
To find your tenant applications, follow these steps:
- In the Azure portal, select Azure Active Directory from the left navigation panel.
- In the Azure Active Directory blade, select Enterprise applications.
- From the Application Type drop-down menu, select All Applications and then select Apply. A sample of your tenant applications appears.
- In the search bar, type Power and a collection of Object IDs for Power BI and Power Query applications appear. You’ll need all three values in the subsequent steps.
- Select and copy both Object IDs for Power BI service and Power Query online from the results of your search. Be ready to paste those values in subsequent steps.
- Next, use Azure Storage Explorer to navigate to the powerbi file system you created in the previous section. Follow the instructions in Managing access section of Set file and directory level permissions using Azure Storage explorer article.
- For each of the two Power BI Object IDs collected in step 5, Assign Read, Write, Execute Access and Default ACLs to your powerbi file system.
- For the Power Query Online Object ID collected in step 4, Assign Write, Execute Access and Default ACLs to your powerbi file system.
- In addition, for the Other, Assign Execute Access and Default ACLs as well.
Connect your Azure Data Lake Storage Gen2 to Power BI
Once you’ve set up your Azure Data Lake Storage Gen2 account in the Azure portal, you connect it to Power BI in the Power BI admin portal. You also manage Power BI dataflow storage in the Dataflow storage settings section of the Power BI admin portal. For guidance on launching and basic use, see How to get to the admin portal for detailed information.
You connect your Azure Data Lake Storage Gen2 account with the following steps:
1. Navigate to the Dataflow settings tab of the Power BI admin portal
2. Power BI admin portal
3. Select the Connect your Azure Data Lake Storage Gen2 button. The following window appears.
4. Azure Data Lake Storage Gen2
5. Provide the Subscription ID of the Storage Account.
6. Provide the Resource Group name in which the storage account was created.
7. Provide the Storage Account name.
8. Select Connect.
And you’re done! Now you should have the integration you’re looking for!