Last Saturday I not only attended but presented at SharePoint Saturday Birmingham. First I have to give Terry Webster a shout out, he did a fantastic job organizing and running the event. I learned a lot, although I admit I felt a bit like a fish out of water, being a SQL Server guy in the SharePoint pond.
Thanks too to all the volunteers who helped run the show, my co-presenters, and the many vendors who attended. Without the support of all those folks this event would not have been possible.
I also want to thank everyone who attended my PowerPivot presentation. Last Friday I did a rather lengthy overview that encapsulated much of my presentation; here are the slides that you saw at the event.
I also want to give you a quick overview of the steps I did in my demo so you can follow along. First you’ll need the bits. The PowerPivotPro has a good blog entry on where to download them from:
http://powerpivotpro.com/2009/11/18/powerpivot-ctp3-beta-download-links/
All you need are the first two items, Office 2010 Beta 2 and PowerPivot for Excel 2010 to reproduce what I did in the demo I presented.
Next you’ll need some data. I had a SQL Server instance installed and used the AdventureWorks2008 LT database available at CodePlex. The LT version is the light weight version of the full AdventureWorks sample, I chose it for it’s simplicity.
http://msftdbprodsamples.codeplex.com/
If you don’t have SQL Server on your box you can use alternate data, or check with your friendly neighborhood DBA. Many have AdventureWorks installed on a test box and might be able to help you out.
OK, so to get this far you’ve downloaded and installed everything, and have access to a dataset. Ready? Let’s go.
1. Launch Excel 201, and click the PowerPivot tab.
2. Click the PowerPivot window button.
3. Click the “From Database” button on the Home tab. In the menu select “From SQL Server”
4. The Table Import Wizard will appear. In “Server name” field, click the drop down and pick your database server. Leave it set to the “Use Windows Authentication” (unless told otherwise if you are using a database provided by a DBA). Under Database name, pick AdventureWorksLT2008. It’s probably a good idea to click the Test Connection button just to make sure everything is correct. If so press Next to continue.
5. On the next screen, leave it at the default of “Select from a list of tables and views to choose the data to import” and click Next.
6. On the next screen pick these tables: SalesOrderDetail, SalesOrderHeader, Product, Product Category, Address, Customer, CustomerAddress. Note the “Preview & Filter” button. In the future you may wish to experiment with this to filter the data you are importing, but it’s not needed for this demo. Click Finish to move on.
7. On the next screen you’ll see it importing the tables. When it gives you the Success message click Close.
8. (Note, this next step differs just slightly from the live presentation, in it I added a column but did it in a different place.) Click on the SalesOrderDetail tab. Click on a blank cell under the Add Column out to the right of the data.
9. In this example we’ll decide that 80 percent of our Line Total is cost, which means the other 20% is profit. We’ll calculate the profit by clicking in the fx bar and typing =[LineTotal]*.20
10. Right click on the “CalculatedColumn1” column header and select “Rename column” from the menu. The column header will highlight and allow you to type over it. Change the name to Profit.
11. Click on PivotTable and pick “Single Pivot Table” in the drop down menu.
12. In a pop up dialog, it will ask if you want a new or existing worksheet. Pick “New worksheet” and click OK.
13. Over on the right you will see the “Gemini Task Pane”. Gemini is the original code name for PowerPivot. To create a basic PowerPivot pivot table, follow these steps.
14. Under SalesOrderDetail, click on then drag the LineTotal and Profit fields to the Values area.
15. Under ProductCategory, drag the Name down to Row Labels.
16. Under Product drag the Name to the Row Labels area.
17. Under the Address, click and drag the CountryRegion, then StateProvince to the ColumnLabels area.
18. Drag the StateProvince to the Slicers Vertical area.
19. Your Gemini Task Pane should look something like:
20. Your spreadsheet should look something like:
At this point you have completed all of the steps as shown in the presentation. Now start playing. You can jump back to the PowerPivot data sheets by clicking the “PowerPivot window” button as shown in step 1. Back there you might try experimenting with a chart, or on the pivot table apply different effects and slicers.
Arcane Code 