Power Pivot is an add-in to Microsoft Excel 2010 and is intended as a self-service Business Intelligence tool that allows power users to create their own models from just about any data source including Microsoft Dynamics GP. Best of all, this add-in is free of charge and can be downloaded here. Once installed, you will have a ribbon tab in Excel called ‘Power Pivot’
To add data, simply click on the ‘PowerPivot Window’ button. You can then import data from many different sources including SQL Server, RSS and flat text file among others. Once you have the data imported that you want to use, simply click on the ‘PivotTable’ button and choose how you would like to view the data
You can then slice and dice the data any way that you like. Here we display 2 charts, 1 for sales by salesperson and the other for sales by customer from GP. This literally took 3 minutes to create.
A few of the amazing things that PowerPivot allows you to do are:
1. PowerPivot is not limited to 1 million rows as Excel is. There is actually no limit on the amount of rows. PowerPivot is able to work with very large sets of data because it stores the data in memory
2. PowerPivot allows you to define relationships. In Excel, if you need to join data from two separate sheets it requires you to use the VLOOKUP function. PowerPivot uses relationships on columns and most of the time automatically recognizes such relationships
3. DAX Function language – Not only are the 80 Excel functions included in this language, but over 60 new functions are defined, including a function that accommodates fiscal years.
4. Easily create Pivot Tables and Charts using the PowerPivot field list.
PowerPivot is a powerful tool for the Excel Data Analyst taking pressure off the IT department to create reports.
