Using PowerPivot with SQL Azure

Far and away the number one Business Intelligence client in the world is Microsoft Excel. While there are tons of data visualization tools out there, Excel is hands down the leader since it is both familiar to users and very powerful. Developers have built tons of business intelligence (BI) apps in Excel using connections to data warehouses (or cubes) and letting the users go crazy with pivot tables and charts.

Things are about to get even better with project Gemini or now  Microsoft SQL Server PowerPivot (we rebel and just call it PowerPivot).  PowerPivot is an add-in for Excel 2010 or Sharepoint. PowerPivot for Excel is a data analyses tool that allows you to connect to a database, download data and store it in a local data engine (VertiPaq) so you can slice and dice it to your heart’s content using familiar Excel tools such as pivot tables and charts. (You can then send it up to SharePoint if you like, but let’s just focus on Excel for now.) PowerPivot works in-memory using your local PC’s processing power and is optimized to handle millions of rows in memory on cheap commodity PCs. The VertiPaq OLAP Engine that is part of PowerPivot  compresses and manages millions of rows of data in memory for you.

Read more Part 1 ...

Read more Part 2 ...