Power BI and Read Only Access to Azure Data Lake Store

Power BI Desktop has a connector to read files in the Azure Data Lake Store. Azure Data Lake Store supports role based access security and granular permissions on files and folders. Business scenarios would require tight security permissions with respect to what files and folders, who have access along with appropriate permission. I will demonstrate one specific user account against a sub folder hierarchy with only read permissions. And to ensure that Power BI Desktop can get data with these security controls in place.

Azure Data Lake Store and Access Security

  1. Go to Azure Portal > Azure Data Lake Store > Data Explorer > Click on sub folder to grant access > Click on Access
    Power BI reports with Azure Data Lake Store-1
  2. Click Add to add a user or group
  3. Type in a name of an existing Azure Active Directory user. I had one previously created.
    Power BI reports with Azure Data Lake Store-2
  4. Click Select Permissions > Select Read and This folder and all children
    Power BI reports with Azure Data Lake Store-3
  5. Upon clicking Ok, you see the confirmed user permission setting
    Power BI reports with Azure Data Lake Store-4
  6. To connect to Azure Data Lake Store with Power BI Desktop record with URL. Go to Overview blade and copy the URL
    Power BI reports with Azure Data Lake Store-5

Power BI Desktop and Azure Data Lake Store

  1. From your computer, download and/or launch Power BI Desktop.
    https://powerbi.microsoft.com/en-us/desktop/
  2. Upon the launch screen, click on Get data
    Power BI reports with Azure Data Lake Store-6
  3. You will see a list of connectors. Filter by Azure and select Azure Data Lake Store
    Power BI reports with Azure Data Lake Store-7
  4. You will be prompted for the URL. Note this URL is specifically targeting the file system hierarchy which is the root. The read access permission was only set for a subfolder jobpostings, but let’s test out this URL.
    Power BI reports with Azure Data Lake Store-8
  5. Click Sign in
    Power BI reports with Azure Data Lake Store-9
  6. You will see a browser based login page. Enter the Azure AD account username and password. In my case, it was the John Smith user account.
  7. Upon successful authentication and clicking Connect, the user account is not authorized. This is expected.
    Power BI reports with Azure Data Lake Store-10
  8. Click Back and enter the URL including the subfolder
    Power BI reports with Azure Data Lake Store-11
  9. Click Sign in with the same credentials and click Connect
  10. Connection is successful due to granted read permissions.
    Power BI reports with Azure Data Lake Store-12
  11. I can load and create a query upon a .tsv file. This file is a result of U-SQL script.
  12. Here is a quick use of ESRI map visualization based on the query.
    Power BI reports with Azure Data Lake Store-13I have demonstrated the use case of allowing users with Azure AD Accounts to build reports with read only access to a specific sub folder in Azure Data Lake Store. This is to support overall security and governance practices.


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s