When Do I Use PowerPivot for Excel?
If you are considering using Excel 2010 to crunch data, you should use PowerPivot. It is a free download. PowerPivot for Excel enhances Excel’s capabilities in many different ways:
It provides the ability to work with complex data in a relational way, using tables.
It uses the in-memory Analysis Services Engine, the VertiPaq engine, to work with huge amounts of data without the limitations of Excel.
It offers many ways to acquire data. You can gather data from many different data sources—from relational databases such as SQL Server, Oracle, Teradata, cubes in SQL Server Analysis Services, text files, and data feeds, and you can even copy and paste! This data is then embedded into the workbook.
It provides an easy way to work with the data embedded in the workbook by using DAX, which is a powerful expression language to define calculations. DAX is designed to be similar to Excel formulas, for ease of use, but adds greater processing power that can work with large amounts of data.