One of the key problems in BI is getting your data together and cleaning it, and one of the best tools for doing this at scale would be integration services in SQL Server. However integration services is too complicated for the average business user but these users do need something to load and prepare data.  They are used to doing this in excel and so this is exactly what they use in PowerPivot for excel.

There are two parts to the process, extraction and transformation.  PowerPivot provides simple wizards to get data from a variety of sources, in fact anything that can be connected to.  New sources include SQL Azure, and Data Feeds in Reporting Services and I have a short screen cast on this here (on TechNet Edge).

Having grabbed all the data each table ends up in its own tab. The most important step to create relationships between them, where they are not automatically picked up form the sources by PowerPivot. The data in each one can be added to, by having extra columns (but NOT rows) using special excel like formula (Data Analysis eXpressions – DAX) to do lookups across the tabs. I have another screen cast on this here.

If you want to know more, there are also various demos, and videos on www.powerpivot.com