"Don't be too proud of this technological terror you've constructed. The ability to destroy a planet is insignificant next to the potential of the Force." - Darth Vadar, Star Wars
Ok, so Microsoft hasn't created the means to destroy a planet. They have elevated Excel's usefulness. With PowerPivot for Excel, analysts can sift through millions records, merge multiple data sources, and create a data cube, all within Excel.
PowerPivot for Excel is a free add-in from Excel that gives Business Intelligence tools to Excel Power users. I say power users because PowerPivot isn't for the average Excel user. If you don't understand pivot tables or dimensional modeling, then you'll have a steep learning curve. If you're like me, warnings such as mine only make you more curious. You can download PowerPivot from Microsoft. After downloading the 91 Megabyte file, run the setup executable file. Then, open Excel's developer ribbon and select the add-in icon to add PowerPivot to your Excel environment.
Once installed, you'll be able to view a new ribbon and dialog (see below). In a nutshell, you pull in data from Oracle, text files, SSRS 2008 reports, or a host of other sources inside the PowerPivot window. Then, you can relate each table by using a common key in both tables (without writing a single SQL statement). You have the option of displaying the data in a pivot table, pivot table, both, or a flattened pivot table (a glorified table). That's it. Instead of using vlookups and sumifs functions, PowerPivot allows you to express your data in a pivot table (just like you would if connected to a SQL Server SSAS data cube).
Ok, so Microsoft hasn't created the means to destroy a planet. They have elevated Excel's usefulness. With PowerPivot for Excel, analysts can sift through millions records, merge multiple data sources, and create a data cube, all within Excel.
PowerPivot for Excel is a free add-in from Excel that gives Business Intelligence tools to Excel Power users. I say power users because PowerPivot isn't for the average Excel user. If you don't understand pivot tables or dimensional modeling, then you'll have a steep learning curve. If you're like me, warnings such as mine only make you more curious. You can download PowerPivot from Microsoft. After downloading the 91 Megabyte file, run the setup executable file. Then, open Excel's developer ribbon and select the add-in icon to add PowerPivot to your Excel environment.
Once installed, you'll be able to view a new ribbon and dialog (see below). In a nutshell, you pull in data from Oracle, text files, SSRS 2008 reports, or a host of other sources inside the PowerPivot window. Then, you can relate each table by using a common key in both tables (without writing a single SQL statement). You have the option of displaying the data in a pivot table, pivot table, both, or a flattened pivot table (a glorified table). That's it. Instead of using vlookups and sumifs functions, PowerPivot allows you to express your data in a pivot table (just like you would if connected to a SQL Server SSAS data cube).
PowerPivot version 1.0 was released with SQL Server 2008 R2 in May 2010. A newer version will be released with SQL Server 2012 in early 2012. Check out what's new in PowerPivot 2012 here.
When I happened upon an Excel blog that mentioned PowerPivot, I was captivated by the claim it could handle millions of records. It turns out that it can do so much more.
Microsoft states that with PowerPivot, one can:
- Take advantage of familiar Excel tools and features
- Process massive amounts of data in seconds
- Load even the largest data sets from virtually any source
- Use powerful new analytical capabilities, such as Data Analysis Expressions (DAX)
- Make the most of multi-core processors and gigabytes of memory
There are a few gotchas with PowerPivot:
- PowerPivot only works in Excel 2010. If you send your workbook to someone without Excel 2010, then they won't be able to interact with your pivot table based on PowerPivot data.
- In the corporate environment, it's best to install PowerPivot for SharePoint 2010 so you readily share your PowerPivot models with others. Microsoft is making a very good case for organizations to purchase SharePoint.
- If your organization uses Microsoft terminal servers (i.e. Citrix) to deploy windows to its employees, you'll need to do extra homework to get PowerPivot working.
- To merge data from multiple systems (for example Oracle and SQL Server), you must ensure the keys used to relate the tables have the same data type. Oracle and SQL Server don't define numbers the same way. It's best to import your data into PowerPivot as text and convert to a number within PowerPivot.
- To create a data cube, you must understand dimensional modeling. Without such knowledge, DAX functions won't make much sense.
- Design your data structure first, then pull in the data you need. It begins with one fact table and multiple dimension tables.
- Slicing data becomes tricky when you have multiple dimensions in a single pivot table
- Key concepts to understand: filter context, how to change filter context, row context, and the 35 new DAX functions.
- Version 1.0 isn't truly stable. It works...most of the time. I've experienced a few occasions where Excel claims it can't open the PowerPivot data. Sometimes, Excel can't open the PowerPivot ribbon. If you migrate from SQL Server 2005 to SQL Server 2008 R2, you'll need to re-install PowerPivot.
The gotchas can be overcome. There is active community of power users and Microsoft most valuable professionals who blog about PowerPivot everyday.
