Analyze data in Azure Data Lake Store using familiar-and-powerful Excel 2016

  • Article

We are excited to announce that as part of the June 2017 updates of Excel 2016, Azure Data Lake Store is now supported as a source of data.

Sophisticated and powerful tools like Excel and Power BI are preferred by many Enterprise data analysts to access and analyze data. As enterprises are building cloud-based data lakes using fully-managed services such as Azure Data Lake Store (ADLS), data analysts want the ability to analyze data in data lakes using tools they are most familiar with.

Earlier, we announced Power BI support for ADLS which is used by many PowerBI users every day to analyze data in ADLS. Encouraged by this, we are now extending this capability to Excel 2016. Data analysts who prefer Excel due to the convenience and richness provided by Power Query, can now use it to analyze data stored in ADLS. This support in Excel is available as part of your Office 365 subscription. As an Office 365 subscriber, you can get the latest updates using the steps provided here. You will need Version 1706 (Build 8229.2073) or higher to get the new support.

 

 

Until now, if you had to analyze data stored in ADLS with Excel, you would have to copy it into a relational data store like Azure SQL Data Warehouse or download the data onto a machine, and then use Excel to analyze that data. This was rather cumbersome involving additional cost and time. With this new support, you can now access files stored in ADLS with Excel in-place, without having to copy them to other stores or locations. You can quickly get advanced insights into raw or prepared data. Models and queries you have created using Excel that ran against local data, can be run seamlessly against data stored in ADLS.

Security capabilities of ADLS allow administrators to control access to the data stored in ADLS in a discretionary manner. With this you can limit the access that Excel users have for the data in ADLS. In this manner, data in the ADLS-based data lake continues to be the single source of truth with no redundant copies and can be analyzed by analytics tools of your own choice .

You can view the video below and see how simple it to access data in ADLS using Excel. After you have accessed ADLS data, you can use these Power Query tutorials to get insights into your data - Power-Query-101 and Shape-data-Power-Query. Give it a try today!

 

 

[video width="1080" height="720" mp4="https://msdnshared.blob.core.windows.net/media/2017/07/Excel2016-ADLS-Support.mp4"][/video]