Powerpivot has been called “the best feature to hit Excel in 20 years” by many MVP’s, developers, BI experts and excel users like myself. You’ll probably be asking yourself why is this the best feature of Excel thus far?
Well, it’s simple…
Take the following analogy
Now, let’s say that we have a business case where we need to somehow create a dynamic report that it’s just not possible with out-of-the-box excel functions (this is the sedan with no turbo).
To succeed in the task given above, multiple developers have created add-ins that work somewhat like what Powerpivot does but they charge ($$$) for those add-ins and since they (the add-ins) are not native to Excel they could potentially have bugs, crashes or incompatibility issues when it comes to sharing the workbooks or deploying them to a Sharepoint site for example.
With Powerpivot, what was once On-Demand and people paid for- to a 3rd party Developer- it’s now being offered FOR FREE, BY MICROSOFT and is here to stay and get even better.
It doesn’t end there, because Powerpivot it’s part of a bigger picture. A bigger and richer plan that comes with the following term
The Tabular Model
The tabular model or SSAS Tabular it’s basically an in-memory database in SQL Server Analysis Services. This is what BI experts, developers and IT people use to create corporate solutions in terms of BI and it’s created using Visual Studio and SSDT (SQL Server Data Tools). There’s nothing beyond this type of scalability, this is the best there is in the MS BI Stack and it’s what you’d encounter in a company that has a MS BI Solution deployed at the top of the game.
How is this related to Powerpivot and its users?
This is where the fun part comes- people always think about the Excel user as people that know how to create scenarios, create clean and visual reports and that can create simulations in excel but people have never seen the true definition of an excel user until now (thanks to Powerpivot)
Powerpivot IS the tabular model. The data model that you create inside Powerpivot it’s the same that could be used inside SSDT. (Should we call the Powerpivot users developers now? I say yes!)
Check out the video below to see how you can import your data model into SSDT and create a Tabular project.
In a short summary, you can say that now Excel users have the complete tools to create their own applications and even help the BI and Database experts to improve their systems because they are more aware now of how things are being calculated. The role of the excel user is changing to one that no longer has limitations that we used to know with traditional Excel and with so many possibilities now with VBA, Excel 2013, Sharepoint, Data Explorer and others, it’s up to our imagination to decide where we’ll be heading.
The possibilities are endless! The revolution has just started and I welcome you to become part of it.
Download the add-in for Microsoft Excel 2010 or buy a copy of Excel 2013 Professional+.