Quantum shifts with PowerPivot


I've been pretty up front with the productivity gains that I saw when I embraced VBA years ago. In fact, my About Me page on my website spells it out pretty clearly:

In the fall of 2002, the resort's management company decided to cut labour costs in the administration department. Overall, 2.5 full time equivalent positions were cut out of a 7.5 person office (yes 33% of the labour used) within one month, with the department expected to produce the same amount and quality of work. During a desperate shuffle of tasks to other departments, and dropping of tasks that were low priority, I realized that automation was going to be the only answer.

From there, I launched myself into VBA (Excel's programming language) full force. Automating as many tasks as possible, the administration office has been able to perform virtually the same task load as before the cuts, and the annual labour savings can be estimated somewhere between $20,000 and $40,000 per year. Suffice it to say, I love VBA, and am still amazed by the things it can do.

Revolution #1 - The Power of VBA

My embracing VBA caused some revolutionary changes in our processes. Many of the macros I wrote I now consider rather basic, but they made a huge difference to our productivity. We do a full working paper file set every month end, reconciling about 100 accounts monthly. Our first macros were controlled by two buttons in each file and basically allowed us to automate the following:

  • RollForward Button
    • Copy month end balances to opening balances
    • Clear out the data entry fields
    • Update the date to a new month
    • Save the file under a new (correct) file name
    • Created a folder for the new file if one didn't exist
  • PrintAll Button
    • Printed a copy of the journal entries (but only if required)
    • Printed a letter sized copy of our leadsheet (if required) to attach to the JE for support
    • Printed a legal sized copy of the leadsheet
    • Printed any supporting schedules (inventory listings, etc..)

Now some of the files were a bit more complicated, but even that simple level of automation allowed us to do a huge amount more work. The macros were consistent, eliminating much user error, and quick, allowing us to focus on doing work, rather than manually rolling things forward.

A specific case of needed improvement

For the last few years we've been working with these macros, trying to figure out how to amp some things up and become more efficient. Over the past couple of weeks I've spent some time working on a file that has been ticking along since the early automation days – one of our liquor inventories.

In the past, this inventory's process was as follows:

  • Preparation
    • Open the Excel file
    • Click the RollForward button
  • Inventory
    • Scan the inventory counts using a handheld scanner
    • Departments also record the inventory date and some counts in the Excel file directly
    • Upload our handheld scanner counts into an Access database (that I built)
    • Print an inventory valuation report from the Access database
    • Manually enter the inventory closing balances into the Excel file
  • Reconciliations
    • Print the general ledger listings for the appropriate revenue and expense listings (6 product lines)
    • Manually enter the current month transactions into the file
    • Manually enter the prior month closing balances into the file
    • Manually enter each day's sales for the dates between the count date and end of the month
  • Review
    • The whole package is scanned and sent to the department for review

I'll be completely honest, the file is weak and has long needed some better information in it to help with the review portion. The problem has always been getting good information into the file efficiently in a usable format. With the volumes of other work that are going on, it's simply been too much work to do.

Enter PowerPivot… and the next Revolution

In case you haven't worked it out yet, PowerPivot is allowing me to start taking this file to the next level. I've been waiting for this for a long time, and some of my dreams are finally coming to fruition with this new technology. So what did I do?

I've added a PowerPivot database to my file. This database pulls information from 4 different sources; 2 Access databases, a SQL Server database, and an Excel list.

In fact, there are actually three separate tables sourced from my BI Database: Chart of Accounts, Budget_Ops and Budget_Internal.

So what, right? Well here's why it's all important. Remember how I had to print my general ledger listing, walk to the printer to get it, then enter it manually enter the number into the spreadsheet? Not any more. Now I just hit Refresh. Why? Because the PowerPivot database is an OLAP database, which means we can use Excel's CUBE functions to pull data directly from the database via a formula. In fact, this formula returns the value of my beer sales for the current month:

=CUBEVALUE("PowerPivot Data",

CUBEMEMBER("PowerPivot Data","[Actuals_GL].[GeneralLedgerAccountNumber].&["&VLOOKUP(A9,tblAccounts,3,FALSE)&"]"),

CUBEMEMBER("PowerPivot Data","[Actuals_GL].[Month].&["&TEXT(Date,"yyyy-mm")&"]"),

CUBEMEMBER("PowerPivot Data","[Measures].[Sum of Amount]"))

This fixes a major problem that I've had in the past. No more fat-fingered entry of numbers, no more forgotten entries. And even better? I can use a similar formula to pull out the value of the Beer inventory for the month. Mark the importance of that… it's from a different database.

I've also now loaded up this file, not only with the closing value of the inventory for one of my report tables, but a full inventory listing in a Pivot Table. No longer do we have to run the inventory listing from Access and email it down with the Excel file. It's embedded.

I've also put in a G/L Listing in a Pivot Table as well. This was never there before either. It actually makes use of a relationship that I defined in PowerPivot between the Chart of Accounts (from my Access BI database) and the Jonas Actual Transactions table (from my SQL database), as well as a relationship with a table of dates from an Excel worksheet. The relationship allows me to display the data with the account names, and drive the drilldowns via date slicers.

But the crowning achievement to me is that I now have access to my budget information in addition to the rest. This means that I can now provide a full dashboard of micro graphs that tells us what is happening in the department. The process of sourcing this data in the past was just far too painful to consider.

So from a rather stripped file that reported numbers in an ugly accounting format, we're turning this into an intelligent application capable of identifying why there is an issue in the inventory. (G/L Listing, Inventory Listing, Micro-graph dashboard, and all delivered quickly without having to enter information manually.) My team member who works on the inventory with the department figures that this will save him about 1.5 hours per month in processing and investigation time. While it may not seem like a lot, this is HUGE to us. It's 1.5 hours when we need them the most.

Is it all roses?

Lord no, not at all. I have an active thread open in the MSDN forums, trying to work out how to use a date/time DAX formula in a PowerPivot measure. The point of that is that I want to be able to use "OpeningBalance" as a measure in my formula:

=CUBEVALUE("PowerPivot Data",

CUBEMEMBER("PowerPivot Data","[Actuals_GL].[GeneralLedgerAccountNumber].&["&VLOOKUP(A9,tblAccounts,3,FALSE)&"]"),

CUBEMEMBER("PowerPivot Data","[Actuals_GL].[Month].&["&TEXT(Date,"yyyy-mm")&"]"),

CUBEMEMBER("PowerPivot Data","[Measures].[Opening Balance]"))

This is not easy stuff to me at this point. (I'm kind of hoping that I'll look back on this and laugh in about 6 months.) I'm still very much learning how to work with PowerPivot here, but this has huge potential.

Could I have done this without PowerPivot?

I should be up front here. Some of what I'm doing now could have been done outside PowerPivot. If I had solid SQL skills, I could certainly have created custom SQL queries and leveraged them against each database… at least… I think I could. I do still write some of my SQL to try and get the data source as narrow as possible, but I'm not sure that I'd be able to write the SQL to link the account names with the account numbers from my Jonas (SQL) database. If I could, then I suppose I could have hooked up a PivotTable for each query and used GETPIVOTDATA formulas to pull the data out.


Despite the fact that I may have been able to pull this off without PowerPivot, this approach is WAY easier. Plain and simple: I don't need to be a SQL expert. That's the whole point to this product… it puts the ability to mine data into the hands of business pros, rather than IT experts.



16 thoughts on “Quantum shifts with PowerPivot

  1. Not arguing against PP Ken, but why did you have to print those numbers. Why did you not just run ADO against the Access database and suck the numbers in directly? It would have been fairly easy, as I understand your scenario, to have read all 3 tables and joined them in Excel. I agree PP might be making that a degree simpler now, and you say you don't need SQL skills, but you do need DAX skills, and as you are finding DAX (like MDX) is different, and needs a slightly different mindset.

    As far as I can see, you can't get at the PP cube from VBA, I see this as a potential drawback for us.

    And again, PP is for self-service BI; you control the situation so you are not really talking self-service. I said to you before, and I still think it is more appropriate for you, chuck the lot into SQL Server and build a cube there, you would be fragmenting your data far less.

    In fact PP off of AS is another interestng avenue.

  2. Hi Bob,
    Good questions.
    Some of it is a historical mindset drawback, to be fair. I could have got at my Access databases. So theoretically I could have pulled my inventories, prior month budgets and actuals in. Until very recently, our daily numbers weren't in an accessible format, although they're now in a SQL database that we can target. So I could have worked around these.
    The challenge that I ran into when exploring is that the SQL database is provided by the software vendor and they won't let me add defined cubes. At least, if I make any modifications to the database it voids my support contract, which also covers my updates. Not a risk that I can possibly take with this software. So I had to deal with cubes outside of their SQL instant. I'm guessing that it may be possible, but don't know for sure. And the rest of my databases are all Access bound.
    To be completely honest, I actually wrote VBA functions myself to mimic cube functions in the past. They were pretty slow though, so this will be way better.
    The best part of all, though, is that my staff find the PowerPivot route accessible. They're not IT geeks, and don't know SQL, but are eager to learn DAX.
    With regards to lack of VBA support, I totally agree. This is a HUGE issue for us going forward, and one that I am hoping gets addressed in the short term.

  3. Just thinking out loud, but what does the contract cover, the system or the whole SQL Server? If the former, could you take nightly copies and build an AS cube from that. In AS you get cube security, and hierarchies, neither of which are in PP at present.

    It may be my IT upbringing, but I still see PP more of a sandbox, where you try out things that eventually you will settle on and then add to AS, or where one persons has a specific set of data that nobody else uses and they use PP to join that to other data (AS or SQL Server direct, Access, or whatever), but shared data should always be in the enterprise system in my view. And you, you Ken Puls, control it, so you can do it properly.

    I think PP is great, but DAX is not simple, simpler than MDX by a mile, but not simple, and no VBA is so bad. I am attending a SQLBI conference in December that Donald Farmer will be at, I will nag him about VBA.

  4. Well, the SQL server is mine. It houses a few databases, Jonas being one of them. The problem is that Jonas won't support their software any more if I mess with their database at all. With regards to taking nightly copies, the problem is that we need to work with this live. When I'm at month end/quarter end, I need to be able to make an adjustment, and refresh the entire thing. (I'll reconcile one account, post an adjustment to it against another account, then go reconcile that account. I've got to be able to be linking to updated data, not last night's copy.)

    With regards to AS security, that's a good point. I actually control my security in a different way right now. My SQL server requires windows authentication, to which our accounting office has access. My Access databases all sit in accounting folders on the network, to which only we have access. With PowerPivot, I stripped my queries down to pull only the specific data I want to share with others. When they access this file, they have no ability to refresh the data from the source (we do, they don't), but they have access to any historical info that they need to review. So yes, AS security may be better, but this is quick, easy, and suitable for our purposes.

    Your point on the Sandbox/prototyping is interesting too. When referring to the OLAP cube in the beta version, the cube was actually called "Sandbox", not "PowerPivot". I agree that it can be a prototyping tool for AS, certainly, just as Access can be a prototyping tool for SQL server. But I think that both can stand on their own as well.

    For reference, I do actually have some budget $ put aside for next year to take my Access BI database and move it to SQL server. It remains to be seen if I can incorporate AS into it as well.

    When you see Donald Farmer, pester him about that VBA refresh too, would you? That will unlock some massive doors for this product, even if it's the only bits they plumb into the object model. (I hope it's not, as we need more, but that would be the most important first step.)

  5. I agree Ken, there is a place in any BI toolbox for a free-standing dataset that can be amalgamated with enterprise data in PP, for scenario modelling and the like, but where that data is volatile, not likely to become part of the business model.

    When I said a nightly copy, it doesn't have to be nightly, you could have an on-demand update. The BI toolkit we provide is a daily scheduled process, but allows for a rebuild of the staging DW on demand, and a rebuild of the cube on demand (as long as you get these things efficient, it is totally feasible). By taking a copy, you can leave Jonas alone, and happily create a DW from the copy, then an OLAP cube.

    Another thought, and I may be way off-base here because I am not an accountant, but couldn't you use Excel and PP to 'post' those adjustments and reconcile in Excel, and then when all is good, post to the real database. You could log all Excel/PP adjustments so as to create an update file automatically.

    As to the VBA, I am feeling the pain now. I wanted to count the number of items in a slicer list in PP pivot table. The selected items is easy with CUBESET, but not the 'all' items. In an OLAP pivot, there are two ways, walk the cube or run an MDX query. If I could walk the PP cube, that would be great (just thought, can I run MDX query against it in VBA, must try that).

  6. Doh! I don't think I can run the MDX against the PP data, I know the cube name, but I have no idea what the dimensions are called.

  7. Just noticed, the OLAP PivotTables extensions are avaiolable to a PP pivot, so you can get some details from this.

  8. Hi,
    What is AS?

    You could have linked the tables in another Access DB and then use the built the cube with excel no? - might be a bit slow though!

  9. AS, or SSAS, is Analysis Services Ross.

    You could do it all in Access, but when you have SQL Server and PP that seems the wrong way to go, to my mind. Also, you need Excel 2003 to build an Access cube, not everyone keeps a copy of that nowadays.

  10. ah, Analysis Services, of course.

    Have they taken it out of the new versions, I didn't know that! I'm so behind the times!


  11. Did I say that had taken AS out of newer versions? If so, that is an error, it is still there with the higher-end SQL Server editions

    Oh, do you mean that cube building functionality? Yes, that is gone in 2007 and 2010.

  12. Pingback: Contextures Blog » Spreadsheet Day 2010 — Top 5 Excel Tips

  13. Pingback: The Ken Puls Blog » Blog Archive » What I want – no – NEED in PowerPivot for Excel

  14. Pingback: Spreadsheet Day 2010 — Top 5 Excel Tips « Contextures Blog

  15. Pingback: Spreadsheet Day 2010 — Top 5 Excel Tips - Contextures Blog

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Posts