- Creating Azure Data Lake
- PowerShell and Options to upload data to Azure Data Lake Store
- Using Azure Data Lake Store .NET SDK to Upload Files
- Creating Azure Data Analytics
- Azure Data Lake Analytics: Database and Tables
- Azure Data Lake Analytics: Populating & Querying Tables
- Azure Data Lake Analytics: How To Extract JSON Files
- Azure Data Lake Analytics: U-SQL C# Programmability
- Azure Data Lake Analytics: Job Execution Time and Cost
- Azure Data Lake Analytics: Finding Duplicates With U-SQL Windows Functions
- 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
- Go to Azure Portal > Azure Data Lake Store > Data Explorer > Click on sub folder to grant access > Click on Access
- Click Add to add a user or group
- Type in a name of an existing Azure Active Directory user. I had one previously created.
- Click Select Permissions > Select Read and This folder and all children
- Upon clicking Ok, you see the confirmed user permission setting
- To connect to Azure Data Lake Store with Power BI Desktop record with URL. Go to Overview blade and copy the URL
Power BI Desktop and Azure Data Lake Store
- From your computer, download and/or launch Power BI Desktop.
- Upon the launch screen, click on Get data
- You will see a list of connectors. Filter by Azure and select Azure Data Lake Store
- 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.
- Click Sign in
- 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.
- Upon successful authentication and clicking Connect, the user account is not authorized. This is expected.
- Click Back and enter the URL including the subfolder
- Click Sign in with the same credentials and click Connect
- Connection is successful due to granted read permissions.
- I can load and create a query upon a .tsv file. This file is a result of U-SQL script.
- Here is a quick use of ESRI map visualization based on the query.
I 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.