What is Powerpivot? (The Million-dollar question)

The Riddle

(The Riddler)

While the prize on whoever answers this question varies, it could potentially be worth more to any business in terms of ROI.

In short, Powerpivot is the new tool that allows/enables the business users to take a much more important role in the creation of insights that are relevant to the decision making process. While traditional BI relies almost entirely on the IT department to create the reports, understand the business logic and translate the data into an insight that could be consumed by the end users, Powerpivot transforms this into a user-driven BI solution where the business users can actually define what they really want from the IT infrastructure and be able to break the barrier of understanding between the IT person and the end user… a new beginning

If you are one of the thousands of excel users that struggles with creating new VBA codes or trying to come up with solutions that are semi-dynamic because of the limitations of traditional Excel, then Powerpivot will make your life easier in almost every way.

Wan to learn How?

  1. Instead of creating a report that can only hold 1 million rows you can create one that can hold thousands of millions of rows
  2. Why copy/pasting reports when you can connect directly to the data source and just hit refresh connection? (yes, this is possible already but now it’s integrated with the Powerpivot data model)
  3. New DAX (Data Analysis eXpressions) language helps you create powerful yet simple to write formulas that are not even close to be simulated within the traditional excel environment
  4. Upload your application (workbook with the reports) to the web (with Sharepoint) and let the IT guys handle the maintenance of the application (data refresh, authoring, access, monitor access and etc)

Technical Stuff

Powerpivot it’s an add-in, created by the guys at Microsoft, for Excel 2010 and a native feature in Excel 2013. Going more technical, the way that Powerpivot works is that it actually has an instance of the SSAS 2012 that relies entirely on the in-memory availability for the Storage of the data (tables) and for the query time it uses the CPU of the local desktop or laptop. It creates a tabular model that can be consumed using Pivot tables or even CUBE Formulas

Analogy

Having an instance of the SQL Server Analysis Services 2012 inside Excel could be compared to something like having the horsepower of a F1 (Formula 1) race car under the hood of a regular sedan that you see on the streets everyday (that’s A LOT of power!)

ppqw

Why should YOU care about Powerpivot?

  • It can help you run a better process to reach the same or even a better insight (less time consuming)
  • If you use Pivot Tables then think about this as Pivot Tables running on turbo or a supercharger (Familiar tools and environment)
  • When Powerpivot reaches his top popularity and becomes a mainstream focus, the competition to have the workforce that knows about this will be tremendous (same or even higher than the one that already exists for workforce that knows about pivot tables)
  • If you happen to run BIG data (big data for excel is above 1 million rows) then consider using Powerpivot that can hold thousands of millions of rows
  • If you happen to run out of ram or CPU because of so many look up functions like index/match, vlookup, hlookup, lookup and a really high amount of columns then consider using Powerpivot where you’ll be saving memory consumption by using relationships instead of lookup functions
  • If you’re considering a BI solution that’s cost-effective and familiar to what your business already knows

I could go on and on, but it’s best if you take a look at the videos on my youtube channel so you can see what Powerpivot can REALLY do for you.

 

EDIT: if you need a more visual approach on what’s powerpivot, check out this video that the guys @Portalfront hosting created

http://www.youtube.com/watch?v=mPK0WGM2GaE

Posted in: