My company has monthly “Tech Talks” where one of the employees presents a topic on a new technology or a cool way to use an existing technology. This month I gave the tech talk on the Data Explorer add-in for Excel 2013 (currently in Preview). I have been playing with it for a couple of months and I am excited to see what it will look like when it hits RTM.
There are 3 major capabilities provided by Data Explorer:
- Obtain data from a multitude of sources including relational databases, flat files, OData feeds, web pages, Hadoop, and more
- Search for data (currently, you enter search criteria and Data Explorer searches Wikipedia for relevant results and lets you choose the one you like)
- Shape the data (combine data from disparate sources, split columns, remove duplicates, change data type, replace values, unpivot, etc.)
As a business intelligence professional, I’m excited about Data Explorer because:
- The data sources can be refreshed on demand and refreshing the data includes performing all data shaping tasks. (I don’t think you can access Data Explorer through the Excel Object model yet to automate the refresh, but I anticipate that will change in the future.)
- All data shaping steps are captured for easy evaluation, change, and duplication.
- The variety of data sources and data shaping capabilities are a major step forward in making Excel a powerful tool for BI developers
-
Data Explorer completes common but tedious/complicated ETL steps in minutes that would take me hours to code
I have already found several uses for Data Explorer in my job:
- Check Active Directory group membership to ensure I have security set up correctly for Analysis Services cubes or SharePoint permissions
- Quick and easy data acquisition for a proof of concept or initial data model before building the full DW and ETL process
- Flatten a parent/child relationship for use in a tabular SSAS cube
If you are unfamiliar with Data Explorer, here are some links to get you started.
5 Things You Need to Know About Microsoft Data Explorer (MSDN)
5 Cool Things You Can Do With Data Explorer
Kasper De Jonge’s NBA Spending Example (PowerPivot + Data Explorer)
Learn About Data Explorer Formulas
Data Explorer Specifications and Limitations
Data Explorer Language Specification (PDF)
Data Explorer Library Specification (PDF)
As a side note, I am wondering if the name Data Explorer will be changed before it goes to RTM as Google has Google Public Data Explorer, IBM has InfoSphere Data Explorer, and Informatica also has a Data Explorer.
Data Savvy 