Hands-on PowerPivot: To the Cloud
I previously wrote about an interesting demonstration that involved a 44-milliion-record data set imported into PowerPivot, Microsoft's coming "self-service business intelligence" add-in for Excel 2010 and SharePoint 2010.
Connecting to a cloud data service, importing a feed into Excel and culling BI info from the data is remarkably easy. You can try it for free with the Microsoft Office 2010 Beta (requires registration) and PowerPivot for Excel 2010.
Here's how.
Click on the PowerPivot tab in the main Excel menu, then click on the PowerPivot window, Launch button. It will take a few moments to load, displaying the message: "Preparing PowerPivot environment, please wait."
Now in the PowerPivot window, click on From Data Feeds under the Home menu item. You are presented with two options: From Reporting Services and From Other Feeds. Click on the latter. This brings up the Table Import Wizard (Fig. 1), where you input the data feed URL.
|
| Figure 1. Table Import Wizard. (Click image to view larger version.) |
You can find online data feeds with a Web search. I used the Open Government Data Initiative, specifically the Crime Incidents data set under the District of Columbia "Container"
(Fig. 2).
|
| Figure 2. Crime Incidents data is found in the District of Columbia container. (Click image to view larger version.) |
Interestingly, clicking on Advanced brings up a window with "Provider: Microsoft Data Feed Providers for Gemini" as the only option. This is one of several leftover references to the former code-name of the project, Gemini. The Advanced button brings up settings for Context, Initialization, Security and Source that adjust the connection string, along with a Test Connection button.
If you're not into Advanced settings, just paste in the source data set URL, click the Test Connection button, and a window comes up with "Test connection successful" (in a window titled "Gemini Client"; see Fig. 3).
|
| Figure 3. Testing the connection with the cloud data. (Click image to view larger version.) |
Click on Next. The Table Import Wizard shows a window where you can "Select Tables and Views" if you just want to download parts of the feed (Fig. 4). There is also a Preview & Filter button that lets you see the data as it will appear and deselect different columns to tailor what you want to download.
|
| Figure 4. Selecting Tables and Views you want to work with. (Click image to view larger version.) |
Click Finish. It takes a few seconds to import, then displays "Success"; under Status it shows you how many rows transferred (368 in this case).
Click close, and a PowerPivot window comes up with all the data in tabular format (Fig. 5).
|
| Figure 5. Time for some PowerPivot magic. (Click image to view larger version.) |
Click on PivotTable, and you are presented with various options for the number/type of tables to display, with or without charts, vertical or horizontal, and so on. I chose two vertical charts. You are asked whether you want to "Insert Pivot into New Worksheet or Existing Worksheet." Placeholders for charts come up, with a "Gemini Task Pane" (there's that code-name again) that lets you choose which fields to add to your charts, along with options for vertical and horizontal slicers, report filter, legend fields, axis fields and values.
I wanted to see which parts of D.C. are the most dangerous and when, so I chose Offense, Shift and Ward fields from the Crime Incidents data feed (Fig. 6).
|
| Figure 6. Selecting Offense, Shift and Ward fields in the Crime Incidents table. |
I then set up tables and charts to show crimes by Ward and by Shift, and with a simple formula constructed a pie chart displaying what percentage of crimes were committed during each shift. My resulting "BI" informs me that most crimes (38 percent) are committed during the Evening shift and most crimes occur in Ward 2 (Fig. 7).
|
| Figure 7. Crimes by Ward and Shift, with a handy pie chart . (Click image to view larger version.) |
There you go: practical, hands-on advice from the cloud, presented here free for your personal safety. Don't mess around in D.C.'s Ward 2 during the Evening police shift.
What danger areas are you avoiding these days? Comment below or e-mail me.
Posted by David Ramel on 02/01/2010