The core idea behind PowerPivot is deceptively simple: Since people use Excel as a generic data analysis tool, why not make Excel into a front end for data analysis from any number of sources, not just the app itself? This way, they don’t have to learn an entirely new program; they can leverage all the skills and habits they’ve built up with Excel. The end result is PowerPivot, and I see it as a needed area of expertise for Excel mavens from now on.
PowerPivot comes in two basic versions: a desktop version for Excel 2010, which works with 32-bit or 64-bit versions of that program, and a SharePoint 2010 edition, which also requires SQL Server 2008 R2 .
Both versions present the same end-user interface: an Excel workbook that is actually a window into the data being manipulated. That workbook can be opened in Excel 2007, but only as a static snapshot; you can’t edit or otherwise interact with the underlying data, since only Excel 2010 has the under-the-hood wizardry needed to talk to PowerPivot.
PowerPivot data can be obtained from just about any source you can name: an existing database (SQL Server or Access), a SQL Server Reporting Services report, an Atom feed from the Internet, any OLEDB/ODBC source, or plain old flat text.
Tables created in Excel can also be linked directly into PowerPivot so that the spreadsheet remains editable while PowerPivot can continue consuming the data in it for analysis. Data can be refreshed manually or on a schedule; the latter is especially useful if you’re pulling in data from an external URL and want to create snapshots several times a day.
When you want to create a report, click on the PivotTable icon and pick one of several kinds of report designs: charts, tables, flattened PivotTable data, or some combination of the two. PivotCharts let you use live data selectors (“slicers”) to narrow or widen the scope of the data presented by simply clicking labels within.
The chart and table formatting options are the same as in Excel itself, so those tools should seem familiar. What takes some getting used to is how to modify the criterion for each axis of data; you might need to do a bit of right-clicking and playing with the sort/filter/summarize options to get the right results
.
PowerPivot also sports its own formula language, DAX (short for Data Analysis Expression). DAX is syntactically similar to the formula expressions we’ve been crafting in Excel for years now, but it’s designed to work with data columns and entire tables rather than ranges and individual cells. It’s a way to perform some analysis that would normally be done in a SQL statement (such as comparing relational values across multiple columns) in an environment that many number crunchers are more familiar with.
Note that DAX isn’t a substitute for the old formula language, but a complement to it; odds are you’ll still be using all of your existing Excel macro tricks in conjunction with PowerPivot.
Source: InfoWorld