PowerPivot Training Resources

I’ve been asked to provide links to some useful resources for learning about PowerPivot. Below are a list of my favorite blogs, books, and other sites to learn from.

A quick disclaimer, some of the links below are by co-workers or other people I have an affiliation with, financial or otherwise. That’s because I’m lucky enough to work with some of the best people in the field. Also, in the case of the books I’ve linked to the Kindle version where possible, mostly because I’m a Kindle junkie. There are paper versions of the books, and you are free to buy from your favorite retailer.

Books

Professional Microsoft PowerPivot for Excel and SharePoint – This book covers all aspects of PowerPivot, from using it to installing it to configuration. Everything you want to know in one volume. I wouldn’t particularly call this a beginners book however, it assumes you are competent in BI, Excel, and SharePoint.

Microsoft PowerPivot for Excel 2010: Give Your Data Meaning – This is a good first book to get, it goes deeply into the use of PowerPivot within Excel.

Practical PowerPivot and DAX Formulas for Excel 2010 – Once you are comfortable with PowerPivot, you’ll want to learn more about DAX, Data Analysis eXpressions, the set of functions used to do advanced calculations and aggregations in PowerPivot. I’m a big fan of the way the author, Art Tennick does his books. It’s the Problem – Solution approach, where he demonstrates a common problem then shows one or more ways to solve it. Art also has books on MDX and DMX you should check out.

Blogs

PowerPivot Pro – Probably one of the best blogs around, Rob and Kasper provide excellent content.

Denny Lee’s Blog – Denny works for Microsoft and is part of the PowerPivot team. He provides some really great insights, and is co-author of the Professional Microsoft PowerPivot for Excel and SharePoint book above.

PowerPivot Info – Not so much a blog but a blog aggregator, this site brings the best PowerPivot content on the web to the forefront.

Videos

Pragmatic Works Webinars – On our website we have a big catalog of past webinars (all of which are free to watch), many of which focus on PowerPivot.

Pluralsight – A little shameless self promotion here. I did a complete course on PowerPivot for Pluralsight. This includes both using PowerPivot from Excel and managing PowerPivot within SharePoint. In addition, Pluralsight has an extensive catalog of other courses you can pick from. It’s subscription bases so there is a modest fee (starts at $29 US per month last I checked) but well worth it for the training you can get. There’s also a free trial.

For a quick link direct to this post, you can use http://bit.ly/arcanepivot

PowerPivot with Pluralsight

For the last few months I’ve been cooped up here in the Arcane dungeon, working on a new project. And now that’s it’s released I can finally share all the details.

While I’m still happily employed during the days as a BI Architect for Comframe, in my spare time I’ve been acting as a technical advisor to a training company called Pluralsight.

I’ve developed a series of four training modules on PowerPivot for Excel 2010. The first module is an overview, that gives you the basic steps on creating a pivot table using PowerPivot.

Module 2 goes in-depth on working with data behind the scenes. How to add calculations, formatting data, filtering data and more.

In the third module I cover the creation and formatting of Pivot Tables, formatting them for users, using DAX (Data Analysis eXpressions), and how to use Sparklines with PowerPivot data.

In the last module we explore the charting capabilities of PowerPivot, covering various types of charts and ways to format charts. We then use the knowledge we’ve gained over the last four modules to create a realistic dashboard.

If you are already a subscriber to Pluralsight, take a look, the courses are now live. If you aren’t, take a look, Pluralsight has the best training I’ve seen on the net and it’s growing daily.

What I learned at TechEd

Last week I was at the Microsoft TechEd conference in North America, along with over 10,000 of my closest friends. I spent a lot of time in the Microsoft floor area talking to people, and came away with some interesting info about new technologies. As I’m sharing some of these at the Steel City SQL user group tonight, I thought I’d share here too.

First up is OData, the Open Data Protocol from Microsoft. It is an ATOM feed but for data. People can publish under the OData format and be able to consume the data from either a JSON or AtomPub. You can also add security, should you wish to have data available to many consumers but only on a permission basis. You can learn more at http://www.odata.org

Next up is Microsoft’s new “Dallas” project. Dallas is the code name for a data marketplace on it’s Azure platform. Through Dallas users and vendors will be able to consume / provide data feeds. Some will be free, others will be at some cost. There is a catalog through which consumers can look at the various feeds available. This is very much in it’s infancy but there are a few feeds which you can look at and preview.

Microsoft’s SQL Server 2008 R2 Parallel Data Warehouse looked interesting, although it fits a very niche market. It’s an appliance you can purchase that is essentially a rack of SQL Servers. One is the master server, and coordinates all the child servers. As a DBA you manage what appears to be a “normal” instance of a SQL Server. Behind the scenes the controller will propagate changes to the other servers in it’s hub. Scaling can be achieved by simply adding more servers to the existing rack, or additional racks as needed. PDW becomes economical starting around 10 terabytes and scales to well over 100 terabytes of data.

The folks at Red Gate have a new tool called SQL Search that they have released for free to the community. SQL Search is an add-on for SQL Server Management Studio that does lightening fast searches of object names in your database. Just pick the database name and term to search for and SQL Search will populate a grid with all possible matches. If you double click on the row it will navigate SSMS’s Object Explorer pane to the correct spot in the navigation tree with your object. Further, if the object is a view, stored proc, etc it will even display the SQL of the object and highlight the searched item. And did I mention it’s free?

Speaking of cool, free tools the folks at Confio have created a free version of their popular Ignite tool called IgniteFree. It is a real time performance monitoring tool that will work with not just SQL Server but Oracle and DB2 as well. They have versions of the tool that run on both Windows and Unix/Linux.

PowerPivot continues to fascinate and excite me, while I was at TechEd I won a copy of “PowerPivot for Excel and SharePoint”. I had this on my “to buy” list anyway so considered myself lucky. I’m about a sixth of the way through the book and it has been really good so far. It starts with a quick tour of the Excel piece, then walks you through the SharePoint install so you can quickly get up and running in a test environment. Later chapters delve much more deeply into PowerPivot. If you are looking for a good PowerPivot book I would recommend it.

Finally, even if you couldn’t be there you can watch the sessions from this and past Tech Ed’s. Microsoft has released them to the general public at http://www.msteched.com/

 

*FTC Discloser, I am in the “Friends of Red Gate” program where I get copies of their tools in order to test and provide feedback. In this case the disclaimer probably isn’t necessary since the SQL Search tool is freely available to all, but I’d prefer to keep things above board.

SharePoint Saturday Huntsville

It’s another Saturday, must be time for another speaking engagement. Today I’m in Huntsville Alabama speaking at their SharePoint Saturday. Now you may wonder what a SQL BI guy is doing at a SharePoint event. There are two kinds of BI professionals in the world, those who are currently working with SharePoint and those who will be working with SharePoint. I believe the converse could also be said for SharePoint people. There those who are currently hosting BI solutions, and those who will be hosting BI solutions. It’s important then, that SharePoint professionals have a clear understanding of what business intelligence is, including all the terms that get used in the BI world. In addition SharePoint 2010 contains features to support a new tool called PowerPivot. Understanding what a PowerPivot workbook is, and how their customers will be using it will give SharePoint professionals better understanding of how to host PowerPivot content.

My first session of the day is entitled “Off and Running With PowerPivot for Excel 2010″. In this session I’ll be giving an overview of PowerPivot, then we’ll be looking at how to import and manipulate data within Excel. This presentation is demo heavy but there are a few slides which can be downloaded from this link.

In my second presentation we’ll dig into the guts of data warehousing and business intelligence. Business Intelligence has its own vocabulary, during the session the audience will learn all the buzzwords around BI and dig into the data warehouse to see how these terms are implemented. This particular presentation is a slide heavy deck, unlike my previous session there is little in the way of demonstrations. We’ll briefly touch on SharePoint, understand though that this presentation centered largely around understanding what BI is so you’ll understand what your SharePoint system is being used for. The slides are available from this link.

If you are reading this after the fact, then let me thank you for coming to my presentations. I hope you found them useful, please don’t hesitate to contact me if I can be of further assistance.

Off and Running with PowerPivot for Excel 2010 at the Huntsville .Net Users Group

Tonight, April 19, I will be presenting at the Huntsville .Net Users Group. The subject will be PowerPivot for Excel 2010. Attendees will get to learn all about Microsoft’s new self-service Business Intelligence tool. Both the Excel and SharePoint pieces of PowerPivot will be discussed, plus the audience will get to see demonstrations of the new Excel add-on.

The slides in PDF format are available, PowerPivot.pdf

Spring seems to be a busy time for user groups, code camps, and SQL and SharePoint Saturdays. This Saturday, April 24 I will be speaking at the SQL Saturday in Atlanta Georgia. On May 1 I will be presenting at the Huntsville, Alabama SharePoint Saturday. The SQL Server event is already sold out, with a waiting list. There are still a few seats left for the share point Saturday event so hurry now before it gets sold out too!

SQL Saturday 29 Birmingham

We just finished up our SQL Saturday here in Birmingham Alabama. It was number 29 in the list of SQL Saturdays. First off let me cover some supporting material for the two sessions I gave. The first was an introduction to Microsoft’s new self service BI tool, PowerPivot. Attendees can download my slides here: PowerPivot Slides  You can also see all my posts so far on PowerPivot at http://arcanecode.com/category/powerpivot/ or by picking PowerPivot from the drop down over on the right side of this blog.

My second session of the day was an introduction to SSIS. Step by step instructions, the sample project, and the slide deck can all be found on my Code Gallery site. At the end we got a bit rushed for time, hopefully some of your questions can be answered from some of my past posts on SSIS. If not feel free to send me an e-mail (rcain at comframe.com or arcanecode at gmail.com) and I’ll see what I can do to help.

Attendees of both sessions may also find my Introduction to Data Warehousing/Business Intelligence slide deck helpful to clarify some BI terminology.

In my roles as speaker, volunteer, and event planner I had little time to take pictures, but I did grab a few at the very end of the day, I thought I’d share them here:

IMAGE_152 

Some lucky winners of books looking over the remaining stack to pick out their prize.

John Baldwin, our fearless leader is in the grey shirt all the way on the right.

IMAGE_153

Obligatory crowd shot. Dividers broke this big room down to 4 rooms where we had our sessions.

IMAGE_154

One more crowd shot, showing some of the higher end swag including a Wii, a Garmin GPS, multiple

copies of Office and Windows 7, and two copies of the SQL Server MVP Deep Dives book I coauthored.

My role in event planning was acting as the speaker coordinator. Finding quality people willing to travel to Birmingham, on their own time and expense,  to give presentations. Thanks to Sven Aelterman, Kevin Boles, Louis Davidson, Janis Griffin, Kevin Grohoske, Geoff Hiten, Rodney Landrum, Vincent Mayfield, Aaron Nelson, Barry Ralston, Joe Webb and Jim Wooley. It was their presentations that helped us draw the big crowd we did.

Speaking of the crowd, much thanks to all of you who attended. The folks in my sessions were very attentive, asked many good questions, and kept the discussion lively and interesting. I’m glad all of you came and hope to see you all next year.

I also would be remiss if I didn’t thank all of our sponsors. Without their donations we would not have been able to put on the event. Microsoft, Teksouth, Bit Wizards, Confio, Redgate, Attunity, Telerik, Intellinet, CozyRoc, Wrox, TekSystems and O’Reilly Publishing and to the SQL PASS organization.

I should also give personal note of thanks to my employer COMFRAME, for putting up with my extended lunches and letting me juggle my schedule to run errands and do other planning activities and presentation prep time.

Finally a few last thanks and kudos are definitely in order. The first should go to Vito Amato and his merry band of volunteers. They kept everyone in cold drinks, helped the speakers with their needs, answered attendees questions, checked folks in at the door, and in general did everything that needed to be done to keep the event running smoothly.

A big thanks and congratulations to John Baldwin, our fearless leader, and his right hand man Morgan Smith for taking the leadership to plan and organize the event. They worked long and hard to make the event the success it was.

If you want to keep the fun and education continuing, we’d love to have you join us at our monthly user group meetings, http://www.steelcitysql.org/. Thanks for a great SQL Saturday, and I can’t wait for next year’s!

Alabama Code Camp Mobile 2010

Last Saturday was the Alabama Code Camp, held in Mobile AL. For those unfamiliar with the Alabama Code Camps, we hold on average two a year, and they shift from city to city with different user groups acting as the host group. Other cities include Huntsville, Birmingham, and Montgomery. This time though the Lower Alabama Dot Net User Group under the leadership of Ryan Duclos hosted, and what a great event it was. Everything ran smoothly, there was plenty of drinks and pizza to go around, and some good swag to boot. A big congrats to Ryan and his team of volunteers for a great event, also thanks to Microsoft for sponsoring and the University of South Alabama for the venue.

I was kept busy at this code camp, doing three sessions. The first session was “Introduction to Microsoft PowerPivot”. The slide deck can be found at http://arcanecode.files.wordpress.com/2010/01/powerpivot_long.pdf. To see all my PowerPivot posts, simply pick it in the categories to the right or use this link: http://arcanecode.com/category/powerpivot/.

My second session was on Full Text Searching. You can find code samples and the PDF for the presentation at my code gallery site, http://code.msdn.microsoft.com/SqlServerFTS.

The final presentation was an introduction to Business Intelligence and Data Warehousing. Here is the link to the presentations slides in PDF format. As promised in the session I added the additional information for the Kimball Group book.

A quick apology for my delay in posting, a nasty head cold has had me in Zombie land since I got back. Thanks to all who attended, I appreciate you being very interactive, lots of questions, and very attentive. I look forward to the next time Mobile hosts the Alabama Code Camp.

Follow

Get every new post delivered to your Inbox.

Join 102 other followers