04 marzo 2013

Microsoft Data Explorer Preview for Excel–Part 1

Some days ago, Microsoft released the first public preview of Microsoft Data Explorer for Excel (http://www.microsoft.com/en-us/download/details.aspx?id=36803) as strategy to enforce their Self-Service Business Intelligence strategy beyond PowerPivot and Power View.

This new and very interesting tool which installs an Excel Add-In for Excel 2010 SP1 and Excel 2013, is described by the Microsoft website as a tool that “...simplify access to public and corporate data. Easily discover, combine, and refine data for better analysis in Excel.“

To unveil what this new tool brings us, this is the first part of a series of post to discover some of the capabilities of Data Explorer. Let’s begin!

After you install Data Explorer, you will see a new ribbon in Excel:

image

For this first post, we will play with the Online Search feature, that allows us to search and discover data in public sources. For this preview, the search is limited to Wikipedia but it is expected to expand the number of public sources in the next releases:

image

The Online Seach pane opens:

image

Try a new search related to the highest income States which relative quickly shows 112 results distributed in pages:

image

If you mouse-over the search results, an automatic new Excel sheet named “preview” appears and shows a preview of the data exposed by each search result:

image

You can move your mouse to the next result and the same will happen for the new data source. Additional information is shown in the preview such as the description and the original URL for the data source:

image

If you clic over one of the data sources, you will navigate to the original webpage. In this case Wikipedia:

image

When you finally find your data source you need to clic on “USE” to get the data to your Excel spreadsheet:

image

Data will be downloaded to a new sheet:

image

The Query Settings pane appears with some new options and update information for the recent query:

image

You can personalize the default query by clicking Filter & Shape:

image

The “Edit Query” window opens showing the OData query for this data source:

image

If you clic over the row number you will get a preview of the whole row in form view:

image

Part of the “power” of Data Explorer is shown when you right clic selecting any column. You can change the data type, hide some rows, aggregate rows using a group by among other options. We will explore more of these options in future posts:

image

Other very interesting feature is that everything that you do with your query is “recorded” and shown in the Steps pane inside the Query Editor:

image

The first step is the selection of the “Source”:

image

If you clic over the Source step, the OData Feed query is shown:

image

I will perform some additional actions over my query:

image

Accordingly, the Steps are updated with each query “transformation”:

image

Now I removed the last two steps and filter the States begining with “A”:

image

The OData query and data are updated:

image

image

Now sort the Median Household Income descending:

image

Query and Steps are updated:

image

image

Going back to the Query Pane in Excel, you can notice some options about the data load in the third section: “Enable download” allow to enable or disable the use of the query. “Load to worksheet” indicates Data Explorer if in addition to create the data source query, Excel should download the data to a new Excel sheet. The last option “Load to data model” indicates Data Explorer to download the data to the workbook Power Pivot data model.

In this case we will turn-off the “Load to Worksheet” and load the data directly to our PowerPivot model:

image

Data is removed from the sheet:

image

After it indicates data has been loaded to data model, we open the Manage model from the PowerPivot ribbon:

imageimage

Having the data in PowerPivot allows you to create very complex and powerful data models:

image

Just to show you how easy is to play with our new dataset, you can create new Pivot Tables or better, if you have Excel 2013,  create a new Power View report:

image

image

image

Wrapping up…

As you saw, the new Microsoft Data Explorer Preview for Excel makes honor to its name and description, putting in the hands of the end-user (Information Worker in the Microsoft lingo), a very simple to use and powerful way to discover and obtain new data from local and external sources to enrich our analysis, even from non relational sources such as Wikipedia pages, fast and easy as never before. In the next post we will play with more of one source of data and the merge capabilities.

Alan

1 comentario:

Sean dijo...

Hey Alan,

Great article, Data Explorer is turning out to be a really interesting tool. Our DaaS platform, www.quandl.com has a lot of potential to work with it, as we have over 4 million datasets that can be pulled in through DE, or other tools and packages. We're currently trying to figure out how it can integrate best, but for you or any readers looking to try it out, any of our datasets can be used with it right now. As well, I'd love to hear any feedback on the service.

Cheers,
Sean