Installing Data Explorer Desktop Client

In this post I will go over the installation of the new “Data Explorer” client tool that is available to download as an Office plugin providing you the ability to launch the tool from within Excel.  Data Explorer is a new tool available in the SQL Azure Labs in the cloud that allows you to do some self-service ETL capabilities.  You can pull in your data from Excel, flat files, databases, the web, data feeds, etc. and then mash the data up as well.  Along with this Microsoft will also provide you additional data recommendations form the Azure Data Marketplace that might be of interest to integrate with your data and then you can publish the new set of data.  Once the new data is published you can consume it back into Excel using the Excel plugin and also make it available to others to consume as well.

So let’s take a look at the requirements for this new Excel plugin:

System requirements

Supported Operating Systems: Windows 7, Windows Vista

• .NET 3.5 SP1
• Office 2010 SP1 or Office 2007

You can download the tool (32-bit or 64-bit) from the Microsoft Download Center here – SQL Azure Labs Codename “Data Explorer” Client (Labs Release)

Here is a quick walkthrough of the install process

Once you have this installed you will see that this adds a new add-in to Excel called ‘Data Explorer Excel Add-In’:

And this will add a new section in the ‘Data’ tab of the Excel Ribbon called ‘Data Explorer’ with two buttons, one opens the Data Explorer tool to create workspaces and work with data, and the other one allows you to connect so that you can consume data into Excel.

The current version of the Data Explorer client is:

Version: 1.0.2755.2 (Release)

For more information check out the following sites:

• SQL Azure Labs: Microsoft Codename “Data Explorer”
• Data Explorer Team Blog
• Data Explorer MSDN Page

Check out some of the postings by fellow SQL Server MVPs Jamie Thomson and Chris Webb as well:

• Chris Webb BI Blog – Data Explorer
• Jamie Thomson SSIS Junkie – Data Explorer

New PowerPivot & Azure DataMarket Experience

I finally got around to installing the latest version of the PowerPivot application.  The new build number is 10.50.1747.0 (previous RTM build was 10.50.1600.1).  Here is my install experience while having the RTM x64 version already installed on my laptop (go from left to right and then work your way down):

Pretty neat, no need to uninstall and reinstall – thanks Microsoft!

Ok, so now that we have rebooted lets take a look at this new functionality that was added…Azure DataMarket data connection.  To the clouds we go!

New icon in the external data connection portion of the PowerPivot Ribbon – lets load up that cart with some data…	The option that is available in the ‘From Other Sources’ option	The new Import Wizard for getting data from the clouds
What you see when you click on the ‘View available Azure DataMarket datasets’ link in previous screenshot	Need to register for the service to get an Account Key setup – this is a FREE registration	Registration agreement screen
Going to subscribe to get access the to the crime data feed	Sweet, we are all set to go and the list of options is available for us for exploring the data	Account information provides Account Key needed in PowerPivot to connect.  This screen will be displayed if you click on the Find button in the wizard above
If you go into your account, datasets, you will get the URL for accessing it in the details section of the dataset in the browser. Place this in the wizard	Next screen, let’s try the preview now	Oh no, didn’t work, what now?
Never fear, we have Marco Russo to save us!  Just add an extra slash onto the end of the URL string	Now we can preview the data.  The Test Connection option would work either way, but need extra slash to get data.	Let the import begin.
Download complete.	New table in the PowerPivot Windows.	Time to analyze data for violent crimes, MN is looking okay (#34).

How cool is this?  Really neat that we can tap into some external data and get this incorporated into our existing internal data for analysis.  Lots of data is available out on the Azure DataMarket to take a look at including weather information which is always useful.  This analysis I did was extremely raw and has tons of gaps since I find it hard to believe MN had zero violent crimes in 2006 and you have to factor in population sizes into this analysis (that information was included, just not part of my Pivot or metrics).

So what are you waiting for, go ahead and download the latest version of PowerPivot and tap into some new data feeds –> Download PowerPivot.

Head to the clouds for your data – http://datamarket.azure.com/

Please support the connect bug to get this escalated – PowerPivot fails using URL published on Azure DataMarket website