none
Powerpivot vs traditional "best practice" modeling

    Question

  • Ok so I've read all the hyper around PowerPivot and even bought the Rob Collie book. In it he stereotypes Excel "Pros" as people that often use Pivot tables.

    Wrong. I was trained never to use Pivot tables. It's bad practice as it does not automatically refresh. And the structure can change when you refresh.

    I have always used the traditional Inputs > Calculations > Output format where the calculations are done using Index/Matches .

    So why would I want to use PowerPivot other than if I have large datasets or a need different layers of aggregation?

    All the models I have every built have worked fine without a Pivot table.

    Friday, March 15, 2013 12:22 AM

Answers

  • As an experienced financial modeler I'd fundamentally agree with you that in the core calculations of a model there really isn't a place for PivotTables and before PowerPivot came along I had never used them in any respect.

    That said over the past 12 months I have used PowerPivot extensively and I'd offer the following points:

     - Fundamentally PowerPivot is more of a business intelligence / reporting / analytical tool than one for the detailed calculations used in modeling.

     - The fact that best practice modelling requires the gradual build-up of numbers in a clear, visible, systematic way is one of key attributes that allow us to audit models and spot errors. Where these calculations happen in the background in DAX the same transparency cannot be achieved.

     - Where PowerPivot can be useful in this setting is in handling source data and providing inputs.

     - You aren't forced to consume PowerPivot data through a Pivot table, there are OLAP cube formulas which are incredibly powerful. There are also settings on PowerPivot pivot tables that allow you to display rows and columns without data, this goes a long way towards eradicating Pivots that change shape.

     - I have a forecast model that contains several million rows of sales data in the background that happily sits there with little or no overhead that means I can instantly update the inputs to my model without the need for running external reports and copy and pasting the data in etc. This is undoubtedly a step forward in terms of robustness. This also kills dead all this BS we hear about Excel not being able to handle scenarios with massive data sets.

    In conclusion as an 'all-round' finance professional I believe that PowerPivot has changed the game in terms of what I can deliver using Excel although it certainly isn't the answer to all problems and I would recommend careful use in a pure modeling context.

    Jacob

     
    • Marked as answer by PuzzledGeek2 Saturday, March 16, 2013 2:54 PM
    Friday, March 15, 2013 12:59 AM

All replies

  • As an experienced financial modeler I'd fundamentally agree with you that in the core calculations of a model there really isn't a place for PivotTables and before PowerPivot came along I had never used them in any respect.

    That said over the past 12 months I have used PowerPivot extensively and I'd offer the following points:

     - Fundamentally PowerPivot is more of a business intelligence / reporting / analytical tool than one for the detailed calculations used in modeling.

     - The fact that best practice modelling requires the gradual build-up of numbers in a clear, visible, systematic way is one of key attributes that allow us to audit models and spot errors. Where these calculations happen in the background in DAX the same transparency cannot be achieved.

     - Where PowerPivot can be useful in this setting is in handling source data and providing inputs.

     - You aren't forced to consume PowerPivot data through a Pivot table, there are OLAP cube formulas which are incredibly powerful. There are also settings on PowerPivot pivot tables that allow you to display rows and columns without data, this goes a long way towards eradicating Pivots that change shape.

     - I have a forecast model that contains several million rows of sales data in the background that happily sits there with little or no overhead that means I can instantly update the inputs to my model without the need for running external reports and copy and pasting the data in etc. This is undoubtedly a step forward in terms of robustness. This also kills dead all this BS we hear about Excel not being able to handle scenarios with massive data sets.

    In conclusion as an 'all-round' finance professional I believe that PowerPivot has changed the game in terms of what I can deliver using Excel although it certainly isn't the answer to all problems and I would recommend careful use in a pure modeling context.

    Jacob

     
    • Marked as answer by PuzzledGeek2 Saturday, March 16, 2013 2:54 PM
    Friday, March 15, 2013 12:59 AM
  • All good points.Thanks.

    I tried creating a simple model and am astounded to discover that a Pie chart created from a pivot table linked to a powerpivot table that in turn is linked to an input sheet does not refresh when the data changes. You have to refresh from  within PowerPivot then refresh in Excel.

    To me this is a very big problem.

    Saturday, March 16, 2013 7:42 PM
  • This is your punishment for using Pie charts...

    Seriously though, this is the workflow in the first 2 versions of PowerPivot while it was a separate add-in and ran outside Excel.  In Excel2013, the PowerPivot engine (and associated data model) have been integrated into Excel and this double-refresh behavior for your scenario is gone.  A single refresh on the chart object will refresh the underlying data source (in your case, pull the new linked table data into the model and refresh the chart).


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Sunday, March 17, 2013 1:44 AM
    Answerer
  • Yeah I've since discovered VBA code to do a refresh in the 2010 version though it still seems to require a two step process for it to work.

    The reason why it is a big mistake by MS not to have this ability in the 2010 add on is that a lot of companies will slowly migrate to 2010. It will be a lot longer before 2013 becomes widely used in business. As I work for a consultancy it means I  have to assume most of my clients will have 2010 at best.

    Sunday, March 17, 2013 1:08 PM