Why Microsoft PowerPivot?

Microsoft Excel already has a lot of powerful features e.g. Pivot Table / Pivot Chart, Analysis ToolPak, Solver; power users can even developed their own add-in. Why do we still need PowerPivot?  Why is Microsoft so excited about PowerPivot?  What can PowerPivot deliver?

Before I answer the question, let me suggest who PowerPivot is for. PowerPivot is for advanced Excel users who need to transform enormous amount of data into meaningful information instantly.  It allows you to consolidate data from finance, human resources, operations and sales departments.  You can easily handle 10 years expenses analysis (how many records we are talking about?) and drill down to investigate different expense categories, territories or even compare the income from different industries.

Here a few things that make PowerPivot shine:

  • Row-and-column limitation in Excel – You can have more than 1 million rows of data.  If you are using 64 bit Excel and have double digits gigabytes memory, you can have tens or hundreds of millions of rows.
  • Lighting fast – It handles tens or hundreds of millions of rows around the same time as Excel handles tens or hundreds of thousands of row.  It uses the new In-Memory BI (IMBI) database and a great compression algorithm in order to significantly reduce the size of the overall data stored.
  • Data from multiple sources – You can load data from databases (Access, SQL Server, Oracle, Terradata, DB2 and Sybase), Excel, text file, OLAP Cube, reports and even data feed; virtually anything that you can think of.
  • Data Relationship – PowerPivot provides a data relationship layer that lets you integrate data from different sources so you can analyze all you data as it is from a single source, so no more VLookup!
  • Powerful new analytical capabilitiesData Analysis Expressions (DAX) is designed to work with multiple tables of data.  DAX also adds new functionality that allows you to create dynamic aggregations, making DAX formulas smart about calculating values in a PivotTable.
  • Time series calculations in pivots – there are about 40 DAX functions to handle time; e.g. Year Over Year Growth, Previous Period…etc.

I strongly encourage you to download and try both Microsoft Office 2010 and PowerPivot.  You would be as excited as me; especially the first 2 features.  See is believing!  Try to import over 10 million records into PowerPivot and you would not believe what you see.  I even repeated the same test on my 5 years old laptop which only has single core and 2GB of memory.  The result is quite encouraging; i.e. you don’t need quad core / 8GB machine to run PowerPivot.

Have fun with PowerPivot and my next blog will be discussing how you can import data into PowerPivot.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

8 Responses to Why Microsoft PowerPivot?

  1. Pingback: Excel 2010 – PowerPivot | Technologies and your business

  2. Pingback: Microsoft PowerPivot for Excel 2010 – Importing Data | Technologies and your business

  3. Pingback: Microsoft PowerPivot for Excel 2010 – Introduction | Technologies and your business

  4. Pingback: Microsoft PowerPivot for Excel 2010 – Creating Relationships Among Data | Technologies and your business

  5. Pingback: Microsoft PowerPivot for Excel 2010 – Adding Slicer | Technologies and your business

  6. Pingback: Microsoft PowerPivot for Excel 2010 – Adding Pivot Chart | Technologies and your business

  7. Pingback: Microsoft PowerPivot for Excel 2010 – Data Analysis Expressions (DAX) | Technologies and your business

  8. Pingback: Microsoft PowerPivot for Excel 2010 – Adding Pivot Table | Technologies and your business

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: