What I want – no – NEED in PowerPivot for Excel

I am an information worker. A big part of my job is to provide our staff and team members with useful information so that they can perform better in their jobs roles. In addition to this, as the Controller of our company, my job is also to guide the accounting department in processing, reconciling, adjusting and balancing our financial records, as well as ultimately reporting our overall results monthly. For this, we use Excel.

I don't think that's much of a surprise to anyone who follows this blog, but I think it's important to be clear on that before we start. Every skill I use to do my job has been developed in order to get our goals accomplished quicker, more accurately and more cost effectively than has been done in the past. But ultimately the fact remains stable. I am an information worker, trying to turn data into information for my team.

I am also the Director of IT for our company, responsible for making sure our IT infrastructure is solid, effective and secure. So while I have the ability to direct IT funds, I also have the responsibility to make sure that they are spent effectively to have the greatest impact at the most practical cost. I believe that this gives me a fairly balanced and pragmatic view of things, as I have to walk that line of needs vs wants vs resources.

Excel is my engine

I love Excel. When working on our information needs, I live in Excel. And I will tell you that PowerPivot is, hands down, the best thing to happen to Excel since VBA was implemented. The product opens whole new worlds of data mining capabilities for the business user, putting the power to generate real information in their hands, rather than the IT departments. While I understand the need for security and IT control, I am also very aware that the business runs on the back of the people using Excel. It is them who generate the reports that allow companies to react to issues and opportunities. It is them who act as the guard dogs and seeing eye dogs for the company assets, alerting the parties that something has gone wrong, or giving hope as to what could go right.

This post is going to walk through one of our month end reconciliation processes in a little more detail, explaining what we do, and how we do it. It's a real business process to illustrate what PowerPivot has done for us, as well as the major shortcomings that exist in the lack of a VBA object model for PowerPivot. Some things we do will make some people scratch their heads, but we have reasons why we do them the way we do. And I'm perfectly fine with explaining them, for right or for wrong, if you'd like to comment.

The process we use right now

The process I want to cover is the same one that I blogged about in Quantum Shifts With PowerPivot. It's the process we use to count and reconcile our monthly inventory count of alcohol at our golf course. I've broken this down to show the steps, who is responsible, and what process was pre- and is post- PowerPivot implementation. One thing to keep in mind is that there is a large gap in Excel skill levels between Admin and the other departments here. While we live in Excel, they have other programs that they are far more proficient in.

Here is the process for the count and reconciliation.

Step & purpose

Performed By

Prior Excel Versions

With PowerPivot

File Preparation      
  • Open the Excel File

Admin

Manual

Manual

  • Click the RollForward Button

Admin

   
  • Deletes prior month file in shared drive
 

Automated

Automated

  • Copies closing balance to opening balance
 

Automated

Automated

  • Clears data input cells
 

Automated

Automated

  • Increments working paper date
 

Automated

Automated

  • Saves file under current month's name (in shared drive)
 

Automated

Automated

  • Close the Excel File

Admin

Manual

Manual

       
Recording the counts      
  • Scan all barcoded products (using handheld scanner)

Dept

Manual

Manual

  • Count non-barcoded products by hand

Dept

Manual

Manual

  • Enter manual counts into Excel File in shared directory

Dept

Manual

Manual

  • Copy file from shared drive to accounting drive

Admin

Manual

Manual

  • Upload handheld scanner counts into Access database

Admin

Manual

Manual

       
Reconciliation      
  • Print an inventory valuation report from the Access db

Admin

Manual

N/A 1

  • Print general ledger listing for revenue accounts (6 accounts)

Admin

Manual

N/A 1

  • Print general ledger listing for expense accounts (6 accounts)

Admin

Manual

N/A 1

  • Open Excel File

Admin

Manual

Manual

  • Update linked PowerPivot Tables

Admin

N/A

Manual

  • Update PowerPivot data connections

Admin

N/A

Manual

  • Refresh all OLAP formulas

Admin

N/A

Manual

  • Manually enter inventory closing balances into the Excel file

Admin

Manual

N/A 2

  • Manually enter the current month transactions into the file

Admin

Manual

N/A 2

  • Manually enter the prior month closing balances into the file

Admin

Manual

N/A 2

  • Manually enter each day's sales for the dates between the count date and end of the month

Admin

Manual

N/A 2

  • Manually enter adjustments for inventory goods in transit

Admin

Manual

Manual

  • Review the file for data entry error issues

Admin

Manual

Manual

  • Overwrite file in shared drive

Admin

N/A

Automated

  • Email Department supervisor (saying it is available)

Admin

N/A

Automated

  • Print entire workbook

Admin

Automated

N/A 3

  • Close Excel File
     
  • Scan entire working paper package to PDF (entire workbook plus all reports listed above = ~30 pages)

Admin

Manual

N/A 3

  • Email package to Department supervisor from Outlook

Admin

Manual

N/A

       
Variance investigation 4      
  • If variances are deemed out of line:
     
  • They product counts are investigated

Department

Manual

Manual

  • Adjustments to non-barcoded items entered in Excel

Department

Manual

Manual

  • Adjustments to barcoded items entered in Access db

Admin

Manual

Manual

  • ENTIRE RECONCILIATION PROCESS IS REPEATED

Admin

   
  • If variances deemed in line:
     
  • Department signs off as acceptable

Department

Manual

Manual

       
File Closing      
  • Re-copy file from shared drive to accounting drive

Admin

N/A

Automated

  • Entire file is reprinted

Admin

Automated

Automated

  • Adjusting Journal Entries printed

Admin

Automated

Automated

  • Journal Entries posted in Jonas Property Management System

Admin

Manual

Manual 5

 

Now, I footnoted some of those items, as they are important…

  1. The printing of the inventory valuation reports and general ledger listings are a thing of the past with PowerPivot. These reports served two purposes for us; they allowed us to manually enter the data in the cells, and also the acted as file support to show where the numbers came from if there was any question. But we don't need them any more because…
  2. We don't need to manually enter the data any more. The manual data entry cells have been replaced with OLAP formulas that pull the inventory values and G/L values out of the PowerPivot cubes. This is not only a time saver, but also an accuracy point. We did encounter errors in the past with fat-fingered entries and amounts put in the wrong lines. No more!
  3. Because we pulled the G/L and inventory data into PowerPivot, we were able to craft Pivot Tables to generate the inventory listing, as well as the General Ledger listings. Since they are now embedded in the file, we don't need to print anything. This saves at least 30 pages of printed data EVERY time we do our inventory reconciliation. Depending on the month, the reconciliation phase might go through 2-3 iterations as well, so this could quickly add up.
  4. The real beauty of this is that, in the variance investigation phase, we have been able to provide our user a LOT more information. Instead of the current month G/L and inventory, they now have access to flip the pivot tables using friendly slicers to see last month's info if they'd like. We also added a Pivot Table to show last month's count vs this month's count and the change. It would have been way too painful to get this before. As well, an entire page of key metric graphs (30 in all) that show product line results, metrics and variances for the past 12 rolling months.
  5. This part kind of sucks, but Jonas won't let me write to their databases. I understand why, but I'd sure love to automate this process too!

We estimate the net time gained in this one inventory file is about 6 man-hours per month. May not seem like a lot, but that is almost two weeks of employee time per year. When you add the fact that our staff see more information to help them better manage the products in a fraction of the efforts, it really makes you appreciate the new abilities of this powerful tool. The best part to me is that it moves the job away from processing data to interpreting results. And THAT is what we need people for.

You another really cool thing about this? Once the PowerPivot data is in the file, I can send it out wherever I need. It's like an Excel Add-in in that way: a perfect deployment. Everything I want the user to see packaged up in one nice neat package.

So what's yer bitch then?

PowerPivot has already solved all my issues, right? Sorry, but no. It's made my file sing, but it can still do better.

I am a HUGE consumer of VBA automation. HUGE. I don't believe that my users should need to perform inane predictable steps manually, nor do I want to let them. The reason for this is that if I can automate it, I can guarantee that it will be done more quickly and more accurately. It's nothing against my users at all, but humans are fallible. They make mistakes. So why let them? Especially where it adds no business value to take that risk?

The process of updating this specific file involves updating the Linked Tables, then going into the PowerPivot window and updating all the data connections as well. This should not need to be done manually. Starting from the Home tab, these actions take 6 clicks to perform. It's a waste of time. PowerPivot NEEDS a VBA API to generate automatic refresh to take this process out of the users hands and let us make it more efficient to trigger.

And right now, the standard answer to that point is that PowerPivot has scheduled refresh… in Sharepoint. I will admit that my Sharepoint knowledge is very limited, so I may end up talking out of my backside, but based on what I've been able to pick up about the product so far I would point out the following issues with using it as a solution in our environment:

  1. I don't have Sharepoint.
  2. It's very expensive to get Sharepoint. (Based on a prelim estimate, I'm looking at approximately $15,000 in software and tech time to support my company's 30 users, never mind training.)
  3. My file is not a report. It (and many of my other files) are working files that need VBA to function.

Now, you could argue that my reconciliation process could be done in Sharepoint. I would struggle with why I should make a staff member with a rich client UI suffer the latency of inputting items in a browser, but you could make that argument. I'll admit flat out that the Variance Investigation is a prime candidate for Sharepoint implementation. No question at all. You could also maybe argue that the file wouldn't need to bounce around the network as much, but I can clean that up via VBA (and do.) But can you honestly tell me that you would spend $15,000 for those features for 30 users? I'm sorry, but that's a real tough pill for me to swallow. When I can't get approvals for $5000 projects in my IT infrastructure, I just can't see it happening.

It is also worth mentioning here that THIS file has a logical break point after the roll forward point where we save the file and close it. Most of my working papers don't. The automation process in those deals with the copying of historical data, clearing of input cells and saving, then… SCREECH! The brakes are slammed on and we need to manually refresh the PowerPivot database to continue on in the process. Wouldn't that smart a bit with you?

And one more thing too… I need ON DEMAND refresh. Not scheduled. When I'm working on a working paper at month end, and my staff tell me they posted a Journal Entry, I refresh. On demand is key.

Now, back to Excel. The reality is that I work in Excel, and I need my entire toolset there. We know that the API for programmability CAN be built, it's just a matter of how much effort and resources it takes. The point that I need Microsoft to understand is that the business analysts of the world use Excel, know Excel, and shouldn't need to learn a new product. They also shouldn't need to farm their file out to IT to get the capability built. If that's required, it plain and simply WON'T happen.

We need the refresh ability in Excel. Sharepoint too, but in Excel. The rich client that we also pay big licensing costs for. The one that runs locally under the fingers of accountants, data analysts and small business owners. That one. J

But it's not all about the refresh

Not at all. The automated refresh is certainly the most important missing piece of PowerPivot for Excel right now, but there are other things I need the ability to change with VBA. Some of those things would be:

  • Adding/modifying or removing a connection to a data source. (Most likely modifying the path by code, but I can see uses for the others too.)
  • Changing the SQL query associated with the data connection so that I can pass dynamically sourced parameters to WHERE clauses. This could be dates or department numbers for example. I'd be all over this, as I'm trying to pull in the smallest data set to do the job, keeping my file sizes down.
  • Adding new measures on the fly with VBA. Please, please give me this ability. I currently have to create a PivotTable to do this and then do it manually… why?

And those are just some that I can think of, but I can assure you that other users will have other needs. Basically, if it moves in the UI, we want to move it by magic… I mean code.

And then there's security

There's another feature that is currently missing from the PowerPivot story to some degree, and that is security. I know that they are looking at this, but honestly, my request is fairly simple, I think. I'm not really looking for full SQL server domain level security. Not at all… well okay… not till at least version 3. 😉

My current security paradigm is to make the smallest data subset I can in PowerPivot. This keeps the file size down and performance up, but it's also the way I restrict the user from getting their hands on info they shouldn't. "Ah, yes Ken, but what if they modify the query?" you ask?

Well, my first solution is that only the accounting staff have direct access to read from our SQL databases. We develop our files in our accounting directory, then when ready we move them into a directory that my end user can access, and their rights prohibit them from updating the data connections. Simple as that. They can look at the SQL query, but can't get to the database so it really doesn't help. But this is pretty poor security.

Personally, all I really need to solve that is to be able to Lock the PowerPivot project for viewing, similar to what we can do with VBA Projects. Now, it would be nice if it was actually secure, unlike VBA or worksheet protection, but something to simply stop my casual users from going in there would be a nice addition at this point, I believe.

I'm sure that, long term, the full security paradigm will come (hopefully after VBA investment though.)

In conclusion

I hope that no one interprets this as unhappiness with the product. It's far from that. PowerPivot is opening doors for us that we only dreamed of in years past. I had a meeting with my team today about a process we believe we can now automate which would save us about 16-20 man hours per month; a process where automation was impossible before PowerPivot. That's HUGE! But programmatic control of PowerPivot would be a key part of that as well.

We've been given a taste of a product with an incredibly bright future. And that taste was just so good, I want the whole thing, and I want it now. Actually, not now, but RIGHT NOW. I want to roll this stuff into a fully cooked solution that works the way we do, and that involves automation and VBA. I want to make use of the software we have, not be sold on more that has umpteen features we don't need just to get one feature we do need. I want to use this to maximize my time savings so that I can learn the skills in the rest of the BI story that Microsoft is developing for us. J

12 thoughts on “What I want – no – NEED in PowerPivot for Excel

  1. Hi Ken,

    I’m in the opposite situation. Where I work, we have SharePoint and people here love it.

    Well, most people. It’s got a lot going for it, no argument from me there. But people try to do everything in SharePoint. And/or InfoPath.

    Eyes glaze over if I ever say we can do a better job in Excel (depending on the work of course) and with a lot less development time...

    Meanwhile, I had problems setting up PowerPivot due to a bad install. Would love to play with it, I should try downloading it again when I get a chance.

    BTW, say Hi to Deanna and tell her I’m sorry for not replying on Facebook the other day (It’s banned at work and by the time I get home, it's a long day already...)

  2. @Andrew,

    People? Business people or IT people? My experience is that people who do any sort of development within these two groups often have very different responses.

  3. Hi Bob,

    Both actually, it's a sad case of go with the flow.

    Unfortunately I see business people (the ones who actually make the company profitable) let down by overly complex, needlessly expensive and obscure solutions provided by the IT department. At least where I am anyway.

    I also see a lot of "IT" people who have no real passion for their job and serving their own interests rather than the company...

  4. Andrew,

    Sorry, meant to post back earlier. Great to hear from you, and happy belated b-day!

    You really need to get a good PowerPivot update up and running. It's AWESOME software, and you'd love the power it gives you.

    Re the people with no passion, I think that exists everywhere in any industry. The challenge is getting past it.

    Re the "do everything in Sharepoint" aspect, I guess you could say I'm guilty of the opposite. I use what I know, and no doubt so do they. The difference is that they have both tools (Office & Sharepoint), and only have one. Given the ability to get Sharepoint here for a reasonable cost I'm sure that we'd put it to good use. The issue is that currently we don't see enough benefit to justify the cost.

  5. Hello Mr E,

    I think business people everywhere will utter the same complaints, and most IT departments I see are inward looking rather than understanding that they only exist because of the business.

    But don't just pin the whole problem on IT, most business people I know are also serving their own interests, corporate culture today dictates that it is so.

    And there is the rub, because of this situation; IT feel that the business is unstructured and not prepared to listen to the professionals regarding systems; business people feel that IT are dogmatic, more interested in the technology than the solutions. So many business people just get on with it and bypass IT where and when they can, so although we have see some very creative solutions, ultimately we have the worst o all worlds.

    But the important thing for me is that MS have to realise that PowerPivot can be a tremendous asset in companies that don't subscribe wholesale to the MS stack, and many will not have SharePoint, will not have BIDS, but they should not be overlooked when improving PowerPivot - they will probably be the majority.

  6. Pingback: Power to the Pivot « The WorkerThread Blog

  7. Ken,
    If You still reads the comments here I will add my 2 cent opinion:

    As with all kind of tools; they solve some needs but not all needs. As with every new tool from MSFT; the first version is stable but no APIs are made available while the second version tend to open up for programming.

    With PowerPivot we get an excellent tool in Excel to visualize large amount of data. By using it together with the present data presentation tools in Excel we actually get an excellent and cheap BI-solutions.

    OK, SharePoint is a great tool for data presentation and data exchange. But the price to buy and also to run it can be very difficult to justify, especially among small and medium sized companies.

    Thanks for an interesting article,
    Dennis

  8. Hey Dennis,

    True enough. 🙂

    My biggest concern is that the API is going to get left for v3 while the focus goes back into the Sharepoint realm. Those big companies have a lot of pull with MS in my opinion, and seem to be able to influence their decisions to a great degree. We can understand that since they control a lot of licensing dollars, so MS would want to keep them happy.

    I've seen Rob Collie say that PowerPivot is a tool for "Excel pros" though, and the lack of an API is a major issue for them. I'm just trying to sound that horn loud and clear, as this is an awesome product, but will be truly awesome with the programability. The v2 release can't come fast enough for me, but the MS development/release cycle is measured in years. This is a piece we really can't wait 3-5 years to get in my opinion.

  9. I think there will be an API in the next release, but my worry is how much will be exposed through the API. Everything I read suggests that MS are looking at pushing PowerPivot back, towards the development arena. You saw Mariano's comments on LinkedIn, he kept saying we hear you, then reverted to telling us how it will be even better using BIDS and SharePoint. I saw Rob Collie on twitter saying '...
    increasingly thinking that #PowerPivot hw planning should be overwhelmingly focused on scheduled refresh needs (not interactive usage) ...'.

    I still don't think they get it.

    BTW, an absolute must have in PP, sorting on custom lists in the pivot.

  10. Hi guys (Ken, Bob and Dennis) - I met Colo in August, he is fine but it looks like our forum is gone forever. Some hacker pretty well wiped it out...

    Dennis, I agree with what you say about SharePoint. Even for a large company, maintenance and developments costs are high. Considering the amount of money and time required, I sometimes wonder if it is worth having.

    Bob, my problem is that I (am seemingly one of the few that) want to help the business. It gives me a warm and fuzzy feeling, not too mention makes me feel useful. My problem is the people and policies that obstruct me from doing that. I guess even with motivated IT staff, without proper direction, they and the business are wanting. At least I'm getting paid 🙂

    Ken, did you know I had to ask my wife how old I was? Scary!

  11. Okay, I've come up with a couple more things:
    -I'd like a quick keyboard shortcut to the PowerPivot window. We use Alt+F11 for the VBE, and Alt+F12 doesn't appear to be in use. I'd suggest that's a good candidate.
    -When adding tables to PowerPivot, I connect to the database and get a list of all the database tables. It would sure be nice to be able to type part/all of the table name in to narrow down the list.

  12. Hi Ken,

    I feel your pain. I have been associated with PowerPivot since the first tech preview, did several postings on the PowerPivot FAQ site and have and a good appreciation of its power. Every so often, I have to remind myself of the following - pertaining to version 1 of the product:
    1) The client is **not** designed for Excel Pros with VBA knowledge. It is designed for the office power user who can build Excel models & understand PivotTables but who are not VBA savvy.
    2) A key goal was IT oversight of user-created Excel models - particularly those that mushroom through a department. SharePoint was the obvious solution here.

    Of course, I've left out a lot of detail, but I think that the above two points are key.

    Your point on whether PowerPivot is free is one that others (including myself) have argued. The short answer is that it is a free download 🙂 If an organization isn't already invested or doesn't plan to invest in SharePoint 2010 Enterprise, it is indeed a significant expense. So much so that it would be totally pointless to deploy SharePoint just to support PowerPivot. It also isn't free if you didn't pan on upgrading to Office Pro or higher.

    On the issue of refresh - there is no mechanism in Excel to update anything other than a directly connected source. For example, an OLAP PivotTable can't do anything to refresh the data warehouse from which the OLAP data was derived, and no amount of VBA can overcome this issue. The situation is exactly the same with a PowerPivot PivotTable with data sourced through SharePoint.

    On the issue of dates - This is one area where every Excel user gets tripped up, and I was no exception. PowerPivot data is essentially OLAP data, and when published to a SharePoint server, aggregations are done on the server. Therefore, like OLAP data, date aggregations must be predefined in the model - the user can't do an aggregation as if he or she were working with a regular local PivotTable data. Also, because we're working with OLAP data, dimensions are always text, so there isn't any date field to aggregate anyway.

    The problem is that the developers have made date aggregations impossibly difficult - not only for the target audience, but also for more experienced Excel users. After all, with typical OLAP data, the user simply drag fields from the date dimension that some BI pro already built. The next version simply must include a date table wizard that can generate a date table. The date table would then be associated with an imported table that has a date field. Updating the date table should be automatic when there is new data in the associated table.

    Currently, with every PowerPivot solution I create, I start with a template file with a bunch of date tables (Order date, Invoice date, Ship date and so on). Each table includes every day for the next ten years or so, and includes fields for calendar year, month, day, semester, fiscal year, week and so on. I also use a table to create a custom sorting list. One or more of the tables are linked to tables imported into PowerPivot. One advantage over the standard PivotTable date grouping is that one can create any kind of date field, like fiscal year or semester.

    On VBA - Personally, I haven't had an aching need to use VBA with PowerPivot. As previous discussed, it can't do anything about refreshing indirect source data and I can't picture having to create measures in the fly. Since VBA will only run on the client anyway, it's just as easy to add new measures using DAX formulas. I might be missing your point here.

    Among other things (and I have a good list), for v2, I'd like to see support for PowerPivot models in Analysis Services (of course, this would also require AS to support IMDBs). This removes the dependancy on SharePoint for many solutions.

Leave a Reply

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