PowerPivot Powered Budgets & Targets

Last week I used PowerPivot for the first time in a classic budgeting scenario. The existing, Excel based, system was straining at the seams due to recent changes in the organisation (merger, followed by lots of changes, resulting in the replacement of long-established reporting and budgeting hierarchies).

The budget process followed the, more or less, classic lines of using adjusted previous years figures to create high-level targets for coming years, agreeing those targets with various interests and then “driving” these high-level targets down various hierarchies to inform front-line staff what would be expected of them in the years to come.

PowerPivot did a marvellous job of providing the necessary figures required to set the initial budgets and to help inform those who must agree them. This involved lots of moving-annual-totals, percentage-increases and so on. A dream compared to the purely excel methods employed in the past.

The “driving down” logic was however kept outside PowerPivot, as the hierarchies involved were unbalanced in the main and the rules were complex but already existed and worked (and probably more importantly were understood and agreed by the various “interested parties”) in Excel. All that was required was the creation of the numerous “flattened cross-join tables” to support the existing logic. The various SQLite “hierarchy helpers” I detailed in my previous Handling Flat, Parent-Child and Nested Set Hierarchies post did most of the heavy lifting. As the process involved the “cross-join” of  hierarchies at various levels I used a great deal of “nested set” SQL to achieve the required result; simple enough, but did become tedious and made the resulting logic somewhat un-approachable for those with limited SQL. What I needed was another “helper function”. The CROSS_JOIN_HIER function was born!


First argument is the hierarchy table name (see previous post for description of this table). The 2nd is the output table name. This table must already exist and have at least the same number of columns as the number of subsequent arguments.

The remaining arguments specify the source of the tables (single column lists) to cross-join in order to populate the output table. The arguments can be of three types.

  • The name of an existing table. This table must consist of single column named “Name”. This allows for complex (or perhaps, simple lists in a different sort order) to be generated outside the function call.
  • A request for a list of nodes from some level within a hierarchy. Such requests can consist of one of the following:
    • Integer between 0 and 99. If the column name associated with this argument (e.g. arg 1 implies column 1 of output table, arg 2 column 2 etc…) is the same name as a valid hierarchy, fetch all nodes at that level (e.g. if 0, then fetch top level nodes, if 1, 2nd level etc..). Allows for up to 99 levels (i.e max value  98). Level 99 is special, it’ll return all “leaf nodes” i.e. those nodes with no children.
    • aNode,(+ or -)integerValue e.g. “Beer,+2”, in this case fetch all nodes 2 levels below Beer in the hierarchy corresponding with column name. The value before the comma must be a valid node name (assumes names are unique within hierarchies). A value after the comma of “-1” will fetch parent node, a value of “-2” will fetch grandparent. In both “+” and “-” if the level to navigate down/up is greater than the levels available, the last available level is returned.
    • In both cases above, the name of the column can be over-ridden by prepending the hierarchy name such that: “Product,Beer,+1” will drive down 1 level from “Beer” in the Product hierarchy. And, “Product,99” will return a leaf nodes of the Product hierarchy.
  • A comma separated list to create a “manual” level. For example, “Budget,Actual”, “2010,2011,2012” or “Beer”

The resulting single column tables are then cross-joined with the resulting multi-column table (a column for each “source” argument) which is used to populate the output table.

This is a “stored procedure” like function, so should be called using “Select function(arg1,arg2 …);” syntax.

Example: click for larger version.

Download microETL from http://www.gobansaor.com/microetl and unpack to a folder, locate alberto_hier workbook (2007/2010 format) and go to the makeBudgetTable sheet.

The microETL project  is password protected; if you need access to the code just email me I’ll send you on the password.


8 responses to “PowerPivot Powered Budgets & Targets

  1. Mr. Datasmith,
    great article! May I ask why not go for Palo or some other MOLAP solution for budgeting? Powerpivot is quite a strange choice for this type of problem.. Was there a specific reason?

    • Ambrus,

      Yes, Palo is an ideal tool for budgeting, in fact, budgeting is the original use-case for all MOLAP tools. But pure Excel remains the primary budgeting tool for most companies. These processes may be supported in varying degrees by budgeting solutions of various kinds, but a lot of the fine-tuning still happens in Excel.

      In this case, a lot a excellent “prior art” existed in Excel, re-writing in either Palo or PowerPivot was not an option. Also, many of the “interested parties” I refer to , were not employed within the organisation, a single server based solution would not work. Communication was via the cross-join tables I describe above, passed back and forth using Excel (and in one case via hard-copy report and hand-written replies !).

      PowerPivot’s contribution was not to the budgeting model itself, something it’s ill suited to provide, but to the provision of quick and powerful analysis of historical data, including the mixing and merging of potential candidate hierarchies that Excel pivot tables are so good at (remember this was an evolving situation, existing hierarchies no longer worked).

      I’m a long time fan of MOLAP, from ESSBASE to PALO, but this was something special, this combination of ROLAP ala PowerPivot and Excel (admittedly powerfully aided by my own microETL add-in) hit a sweet spot I’ve never experienced in my 30 years in the business.


  2. Bob Phillips

    Not quite the same problem, nor resolution, but I thought PP would be great for my personal accounts. I had it in Excel, multiple tables which I joined in Excel to pivot, PP seemed a natural. It started okay, but I found that when I started to add DAX formulae, I pretty soon dragged it to it knees. This is becoming a familiar story for me, PP works great on a ton of dat if you keep it simple, introduce a fair amount of DAX and it starts to crumble. I noticed PowerPivotPro alluded to the same issue in a recent blog, but he was selling hardware, not giving solutions.

  3. Bob,

    I’ve not hit that particular wall yet as I try to keep my DAX formula as simple as possible using a combination of data re-modelling to simplify calculation (a classic star-schema is usually my guiding light) and a combination of Excel and SQLite to do what they’re good at. In other words, using the best tool for the job.

    In this case most of the complicated stuff was handled by long-proven Excel, PowerPivo’s role was as a provider of those things it does really well, moving annual totals, % changes etc. which in previous years had to painfully worked out in Excel.

    Although DAX is meant to be a declarative language in the style of Excel formula it has not yet reached the level of maturity where you just have to think about the logical result; the means to that end obviously still needs careful attention. But it’ll get there I think.


  4. Bob Phillips

    Yes, I agree with the principles, my Excel tables were star schema like, a transaction table, an account table (dimension), a date dimension (which was only as large as needed by using good Excel formulae), and so on. Originally, I joined this lot in Excel to provide a flat structure to pivot, so you can see why PP appealed. But I just kept adding to the PP model, which led to my woes.

    Perhaps I should revisit what the PP model is doing, and put that back in Excel.

    I have obviously noticed microETL, but haven’t really looked at it, maybe it is time I did

  5. Bob,

    microETL’s original use-case was to simplify joining and grouping of excel (and external large) tables in preparation for “pivottable’ing” (with the option to use detached ADO recordsets to get around pre-2007 row limits). Since by default the SQLite database used is memory based it is very fast and cost-effective at doing that sort of thing, all that’s needed is basic SQL skills. An ETL tool for PivotTables, hence the micro ETL name.

    Talking of the right tool for the job; if top-down collaborative budget and planning is the requirement (which it was in this case) then the ideal tool to use is Jedox’s Palo. But alas, we don’t always have the choice of tool and must do the best we can with what’s available.

    Such situations bring to mind the golf pro-am story where the amateur was constantly pointing out to the pro (Jack Nicklaus I think) what club to play. At a particularly tricky shot to the green, the pro had enough when his choice of cub was again “criticised”. So, he proceeded to take out every club including woods & putters, placed a ball for each one and hit every ball on to the green.


  6. Pingback: microETL’s SQL function | Gobán Saor

  7. Pingback: PowerPivot Nested Set Measures | Gobán Saor