If you’ve followed my twitter feed over the past few months you’ve seen that I’ve been working on a couple of pretty large financial models. These models give us the ability to change a large variety of inputs in order to predict our real estate development over the next 25 years (through the build-out of our remaining lands), as forecast the effect of the real estate sales on our operating divisions (golf course, marina and fitness centre.) To give you an idea of their scope, they consume over 170 pages of 11×17 paper when printed.

This project was actually pretty neat in that I was able to sit down with people, scope it out in full, and apply every best practice to it as I was building. I’m really proud of these, and even based my recent course for CGA off the techniques that I used in these works.

In the end, I built two parts of the finished model, and inherited a third piece that had been built by someone else.

One of techniques I used was to apply Styles to the model. (If you’re not familiar with Styles, then have a quick read of this article on my site.) As it turns out, that was a very smart move. Earlier this week I got the call that my headers needed to be changed, and I was provided an RGB colour scheme. In the model components that I built it took less than five minutes to update the Styles to the new colour scheme and the whole model was updated.

In the other model, the one I inherited, well… I’m still working on updating all the pieces. This time, however, I’m actually updating it with styles so that I’m not caught again if there is another change.

So this past Friday, I had the opportunity to go and shoot a couple of Excel webcasts that I’m doing for the Certified General Accountants. This was a pretty interesting experience, as I’m very used to doing live presentations, but I’ve never been recorded for later broadcast. The opportunity to work with a professional crew was pretty neat too, and I thought I’d share some of my experiences.

So the summary is this: Filming is like a day at the spa. It starts with a facial, and ends up with you getting your chest waxed.

The company that CGA hired to do the shoot was Blink Media Works in Vancouver. I can’t say enough about these guys. They were absolutely pro at helping me learn the methods they use to record video, and I’ve come to the conclusion that being a producer pretty much means that you’re half a technical/visionary, and half a motivational speaker. Arthur, our producer, did a great job at both pieces.

I was a little surprised at how many people we had involved in this. In addition to myself and the host, Blink had four people with us for the whole day:

• Producer
• Camera technician
• Teleprompter technician
• Makeup artist

I wasn’t surprised by the first three, but the makeup artist really surprised me. I’m not sure why, but I was expecting that you’d sort of be “done up” and that would be it. Not so.

As I mentioned above, it was like starting with a facial. Justin (my host from CGA) got to go first, then me. Cleansers, moisturizers, some base stuff to make your skin look good for the camera, and some powder to stop my forehead from shining. And apparently she didn’t really need to put much on me at all. After that, I got to watch the filming process at work as they recorded the introduction and outro for my webcasts.

And then it was my turn. I stood in front of the camera and they do a final check to make sure you look PERFECT on screen. I can honestly say that I’ve never had anybody spend so much time getting my hair to be absolutely right. And I’m not kidding here: they stopped the shoot to get one (yes one) of my hairs to go where it was supposed to. There is some serious pride in their work there!

After the makeup side, then came sound check. The camera technician hooked me up with a microphone, and then there was a lot of work to make sure that it was working well. They didn’t want to see it on camera, so it ended up taped to me under the shirt, then we had to make sure the shirt wasn’t rubbing on it or the sound went scratchy. And that’s enough to require re-shooting the scene/part.

And then we started with the actual shoot stuff. Breathe, loosen up, smile, be excited… this is where Arthur’s passion and motivational side came through. He is just like the classic director you see in a behind the scenes footage on a set. “Okay, we’re starting from here. Loosen up, deep breath, and when you’re ready… take it away KEN!”

I’ve got to hand him some real kudos here too… I’m a pretty skeptical and practical guy overall. I don’t do relaxation well, but Arthur worked really hard and was really patient with me as he was getting me to “connect” with the camera. With people it’s a lot easier. You can see the social cues, read the tone and see the questions in their eyes. Here you have a lot more room for self-doubt and self-consciousness. It took a while (far longer than I would have liked,) but eventually I hit my groove and we were able to record 5-10 minute segments without hearing “CUT!”

A couple of quick observations about film:

• They get you to smile… to the point where it feels campy and ridiculous. The whole time they tell you that it will come across as natural on the screen. While I haven’t seen my own film yet, I watched Justin’s and would have to agree that this is the case. He mentioned that he felt like he was overdoing it, but he came across really natural on screen.
• The producer sits behind, but just to the right of the camera. Letting your eyes flit to him for even a split second is enough to blemish the recording though. It is noticeable on film.

I can say that the hardest thing about this shoot to me was working with the teleprompter. To be clear, this is in no part due to the technician, but rather due to my understanding (or lack thereof) of how they work. I prepared my script as I usually would… I kind of figured that the teleprompter would be similar to the binder I usually have… a full 8 ½ x 11 sheet of paper. Boy was I wrong!

Here are the difficulties I ran into:

• Take your full size sheet of paper that has your script outline on it and put a pad of Post-It notes on it. That’s how much of your note page you really see. It left me feeling constrained and boxed in… claustrophobic. I couldn’t see what was coming any more.
• In my presentations, I build my slides with the intention of talking around the points and fleshing them out more. It’s a real battle not to just read the teleprompter though! And with losing the peripheral vision based on the above, it makes it hard to ad-lib.
• The teleprompter had no formatting at all. No bold, no colour, no underlines, nothing. It’s just text that is all in the same size. So where my slide headers in my script were Bold and in another column, they were now in the teleprompter as normal text, the same size as everything else. This is really hard to follow, as I lost track of which were slide titles, slide points and notes that I wanted to talk about. Part way through we stopped and put in a line of asterisks before each slide point, just so that I could recognize where I was. This made it a LOT easier to transition between points and keep the flow going.
• Later, when I was getting more comfortable, I did start to expand on my points and go a bit off script. This was great, as it came out very natural, but it had issues as well. A couple of times I read a point from the teleprompter, expanded on it, then read the next point on the prompter. At that point the technician moved the prompter up and I realized that the next point was the one I had just expanded on… doh! And being that you’re on camera, there is no way to signal the tech to move the prompter up without it being caught on film. Again, an issue with having such a small window into the presentation.

Despite these issues, we got into a real groove later in the day and things sailed along pretty smoothly. I can certainly say that I’ll do a lot more work on my scripts next time. Things I’m thinking:

• More bullet points and less sentences in the script
• Shorter bullet points so they fit on one line (to get more of them on the screen)
• I’ll put in my own asterisks or come up with another was to indicate slide title or points. (Maybe ST-Slide Title Text and SP-Slide Point Text.)

The next thing that was kind of weird was that we shoot out of order. I totally expected this, but it was still odd.

In the morning we shot all the video that accompanied my PowerPoint slides. I talked about all the points, and the editors cut the slides into the video stream as I talk about them. The challenge was that the two webcasts had a bit of overlap in them. It was tough to remember what you’d said after two or three cuts, let alone when you’re doing an overlapping slide from a second webcast and you’re on your 2^nd cut there.

In the afternoon we did the computer portion. We hooked up Camtasia studio (thanks TechSmith!) to record both the audio and video of my Excel work. In addition, the camera kept rolling to record video and audio. The editors will use the audio and some of the video footage from their shoot, and cut it the excel video recorded with Camtasia. The only reason we recorded audio in the Camtasia side was strictly so that the editing department knew how to overlay the better quality audio with the Excel portion.

And then, almost as quick as it all started, it was done. We ended off the day and it was time to go… at least, once the microphone was taken off.

The camera technician was finishing up labeling film I think, so I decided to take care of that bit myself. I reached into my shirt, grabbed the tape he’d used to secure the mic, took a couple of deep breaths and RRRRIIIIIPPPPPP!

I heard the Teleprompter technician say “oh my God!” And there I was, staring through watering eyes at the massive patch of fur that was stuck to the tape… and not on my chest where it belonged. Yup… just like a day in the spa that ends up with you getting your chest waxed!

All in all (except for the last part) it was a great day, a lot of fun, and I can’t wait to see the finished product. My number one recommendation to anyone that is going to do this though? Wear an undershirt.

I recently migrated to the 64bit edition of Microsoft Office 2010. My main reason for doing this was that I wanted to make use of the RAM in my machine with PowerPivot, but it certainly didn’t come without a bit of pain. The most notable parts there were that I had to convert a bunch of 32bit API calls in my VBA to 64bit compliant calls while preserving 32bit compatibility for the other machines in my office. Despite my initial intimidation here however, some good friends helped me out and I was up and running within a few hours, and I know believe I understand how to migrate the rest of my code easily. (I may post on that in the near future.)

One challenge I did face though was that you cannot run Office x64 on the same machine as Office x86 (32 bit). To install Office x64 you must uninstall any 32bit version of Office programs first. This means Visio, Project, Office 2007, Office 2003, etc…

My challenge with this is that I teach courses and like (need) to have multiple versions of Office installed so that I can teach in the appropriate versions. I could use a full blown virtual machine, but the problem here is that I find it inconvenient when trying to teach. I can’t flip back and forth between the app and my presentation easily, my presenter mouse doesn’t work in the VM… it just doesn’t work.

So when I installed Office x64, I was a little disappointed. I really wanted to run it side by side with Office 2003 and 2007, but I couldn’t.

But then, in a discussion with one of my staff yesterday, we ended up chatting about Windows XP mode in Windows 7. It IS a virtual machine, but it allows you to run an app installed in the VM as a program from the host (Windows 7) desktop. Here’s a shot out of my start menu of the applications I installed in the Windows XP Mode VM:

So this is pretty cool. I’ve been able to have Excel 2010 x64 open and running on my laptop, and I was able to successfully launch Excel 2003, Excel 2007 and Excel 2010 (32 bit) all together.

The Windows XP mode apps all run on the virtual machine, so they are segregated from my host operating system which makes this possible. I also installed SmartIndenter and MZTools in the VM, and those both show up in the VBE for the apps when I launch it.

I’m really impressed with this. Granted it’s far from perfect application level virtualization, but it allows me to do what I need. All the issues I mentioned above are solved with this. I can run all the versions of the app together, and they seem to run seamlessly.

If you are running Windows 7 and want to check this feature out, here’s the link: http://www.microsoft.com/windows/virtual-pc/download.aspx

Last week I was working through creating some more DAX measures. And while I was successful, I still don’t feel that I’ve really truly wrapped my head around how they work. (I notice that Dick Moffat feels similarly, too.)

The biggest thing that I had to wrap my head around is that Date/Time DAX functions are completely unreliable unless you bring in your own complete table of dates. Otherwise, if there is a single date missing in your table of dates, it completely blows apart the opening balance formulas. I really struggle with this… I don’t have to build data tables in Excel to be able to use its date/time functions, and I’m not sure why I should need to for PowerPivot. At any rate, I built a table into a SQL database and import it along with the rest of my other tables now. I contains every single day from January 1, 2003 to December 31, 2011 at this point, so I can avoid that issue. (Our history goes back to 2003.)

Something else that struck me as odd is that, in order to create a measure I can only do it on a Pivot Table. Yet it links back to the tables in the PowerPivot data. When you create the measure, you get to pick which table stores the info.

So here’s what I don’t get about this. Why do I need to have a Pivot Table in place before I can kick off the “New Measure” button?

The assignment of Table name is also a bit… it’s not a mystery, it just feels weird. I see that you’ve got two options:

1. The DAX measure can be placed in any table provided that the columns referred to can be traced back through the relationship chain to the originating table. The problem I run into here is that when you’ve got 10 tables in your PowerPivot file, it can be painful to hunt down the measure definitions in the field list when you want to find them. (It also kind of defies logic to me that it doesn’t need to be somewhere.)
2. If you fully qualify your table names (as I have above), it seems that it doesn’t matter where the heck you put them. So to keep things organized I created a linked Excel table called “tblMeasures” with one cell of data. I could then assign all of my measures to that table to keep them organized. The only issue is that I’m now stuck with the message “Relationship May be Needed”. I’d sure like to be able to say “thanks, but you don’t need to bother me for this table”

Ultimately though, it would be really nice to have some kind of section/filter to display the measures vs the tables/fields.

I will also say that I find the DAX editor to be somewhat wanting after the richness of the Excel UI. Maybe it’s just me, but I don’t find the term “expression” in the Intellisense all that helpful when you’re trying to learn how to write one of these formulas. (Some kind of expression builder would be really cool, but I imagine that would also be kind of tough to implement.) The other issue I find is that these things returns tables of information, then distill pieces out by filtering, summing, etc… When you’re stuck, and something isn’t working out how you think it should, it is VERY difficult to see where you went wrong.

Breaking a formula up into multiple lines kills the Intellisense outright too, which is very frustrating. Once the formulas start to get complex, this is sometimes the only way to keep them legible. And the fact that the case for DAX isn’t updated to all caps once committed is kind of an annoyance. I don’t type in caps, but I never realized how much I appreciate Excel converting those for me. It sure makes it easier to read afterwards.

Finally why is it that every time you get a message it obscures the note in the box below?

At any rate, I’m sure I will get my head wrapped around this, it will just take time. J

Over the last couple of days I’ve been trying to build some stuff with PowerPivot and I’ve come up with some other things that I would find pretty helpful. Here’s one of them.

One of the big challenges I found was trying to figure out why my PivotTable was returning the results it was, instead of what I was expecting. Part way through my troubleshooting process I began to doubt the way I’d set up the relationships between my tables, so I decided to take a look at them. I found myself staring at this table:

The problem is that it’s really difficult to visualize data in this format, and I found myself longing for a good old Relationship diagram like we can build in Access. I did pretty much the only thing I could and reached to Visio to draw this one up:

The one side of the relationship is shown with the +, while the many is shown by the circle and lines.

This is the final version, but the mapping of the prior showed me that I’m made some errors in the relationship flow. Even this version actually turned up a couple of things… tblCOA is the centre of a couple of many to many relationships.

The challenge with this is that it took me quite a while to draw up as I had to document each link, draw the table, then move everything around so that it wasn’t a crazy mess. It would sure be nice if I could just click a button and see this in a graphical view like in Access.

After all my evangelizing (or complaining if you like), I think it’s important to acknowledge when someone does/announces something that addresses my concerns.

Post titles are the first things that come up in search engines. So if someone is searching on the question “Will PowerPivot support VBA”, they’re likely to see my last post come up in a search engine. That post holds a critical comment from Amir Netz of the PowerPivot team at Microsoft, and I don’t think it’s really fair to have it publicly advertised under the heading that is completely opposite of the truth.

So this post is short and has only two purposes:

1. To admit I was off base in some of my comments (and I’ve never been happier to admit it!)
2. To get something into the blogosphere to answer this burning question with factual information.

To recap, Amir’s comment:

PowerPivot does not have VBA support for the most simple reason: we just did not get to it yet.

…

We know how much you want and need it. We have it high on our list and you will get it. But it is a big deliverable that requires time to complete.

So there you have it. PowerPivot will get VBA. Not quite yet, but it’s coming… the only question is time.

You can read the full post here.

Yesterday Rob Collie made a post on his blog about sizing considerations for PowerPivot implementations on Sharepoint. The main thrust of the post was directed at end users and making sure that their server farms have adequate resources to efficiently process information in the nightly refreshes.

I don’t disagree with Rob’s points he made at all. In fact, from the Sharepoint side, I can see that they would want to focus on increasing the scheduled refresh efficiency to lessen (or at least stretch) the server investment. On the other hand, I don’t want Microsoft to lose sight of the fact that Sharepoint is only one part of the puzzle here.

After a comment, and a reply by Rob, I realized that our conversation was going to take his post off topic pretty quickly. Not wanting to hijack his thread, I thought I’d post my thoughts on my own blog and let the conversation continue here if anyone was interested.

My comment to Rob was:

If I were going to put my money into developing new features, it would be allowing the Excel pros to set up their scheduled refresh, which they currently can’t do without Sharepoint.

Whether it be a flag to “Refresh on open”, like you can set on PivotTables, or VBA, something is needed on the non-Sharepoint side. Personally I think VBA would be a more robust investment, as then we can at least control the refresh to some degree if we need to, (or even dissallow it during certain time periods,) but honestly, both features should be implemented.

Once I had that nailed down, THEN I’d focus on the efficiency. My reasoning for this is that you can pour huge amounts of labour into the efficiency pool forever trying to eek out an extra millisecond of performance, and nothing will ever be good enough. Consuming those resources at the expense of a critical feature does not make sense to me.

And Rob’s reply:

The only reluctance on the VBA front, I think, is that the client is free.

MS decided to give away the benefit of PowerPivot for Excel, which is a pretty big thing to give away. But certain features were reserved for the server, which is not free. When Amir first said they were planning a VBA method for local refresh, I was pretty surprised and skeptical.

They have since gone quiet on that topic. I have not asked anyone at MS about it, but my suspicion is that they have reverted to reserving refresh for the server.

With regards to the client being free, I’m not sure I totally agree. I still have to buy an Office license, which is not free. I get my Office 2010 Professional Plus licenses on through volume licensing for about $225 CDN per copy, but if you pay retail you’re looking closer to $450 right now. That may not seem like a lot per users, but they add up pretty quickly, especially when combined with the Windows 7 workstation licenses, Windows server Client Access Licenses (CALs), Terminal Server CALs, Windows Server OS licenses, and the list goes on. There is a lot of cost in software, even for a small business. In order to use PowerPivot I must have already invested in all those other products, so it’s kind of expensive free software.

While it is benevolent that MS didn’t charge extra for the PowerPivot release at this point, to justify not adding features to a product with such incredible potential is crazy. This product is what business analysts and information workers have needed for a long time.

Let me see if I can draw a parallel here… you’re an air force pilot. Tell me how happy you are when I deliver you a new fighter plane that goes faster than any have gone before, flies under the radar, runs on less fuel and delivers a bigger payload than anything else out there. Wait… I still need to show you how to wind the hand crank you need to start it up… What? You want the version with a key? Sorry buddy… THAT version only comes when you buy the aircraft carrier with it. WTF?

I’m in business, and I do get it. Microsoft has every right to be concerned about the dollars and the profit. I’ve got a little secret for you though… Not including VBA (or saving any other feature for the server version) isn’t going to make me buy a Sharepoint server. I plain can’t afford it. No matter how many features you plan to add to Sharepoint, I still won’t be able to buy it. I’d love to, but I don’t have the money. Period. So what have you gained? Certainly no sales… hmmm… I guess you DO have a frustrated customer though… is that good? J

Personally, I think Microsoft can afford to invest a little in this technology to KEEP Excel as the definitive spreadsheet program in the market… in fact, I think it’s in their best interest to do so.

Actually… I had a call from a company a couple of years ago who told me they could save me a bundle every year on my licensing costs. When I asked them how, they told me to switch to Open Office. He hung up on me when I asked him if they’d cover the cost of converting all my VBA macros.

The reality is that once companies get VBA into their environment, there is virtual permanent lock-in to the Microsoft software platform. VBA works in Excel on Windows (okay, and kind of does on the Mac too). I’ve seen it said before, and agree, that it is Excel VBA that holds Microsoft on our computers at work. No VBA, no need for Excel. No need for Excel, no need for Windows. We could just as easily run Open Office on Linux. Now, add PowerPivot to the mix… there isn’t an Office suite yet that has something comparable, is there? Add VBA to that and you’ve got serious lock-in. How could we ever get rid of Office (and therefore Windows?)

And if that’s still not enough reason to just package it as part of Excel at no extra cost, then maybe Microsoft should look at other ways of earning their dollars. Maybe they should only include PowerPivot with their Office Professional SKU’s (not Standard) and up the price of the product by $25 per license or so. Or offer a PowerPivot Professional license that has a few more features. (We’ve seen this with Office Starter vs the full blown Office suite.)

There’s a lot of ways that Microsoft could make money off this product if they wanted to. The business community without Sharepoint servers would eat it up in a heartbeat. But to me I still get the feeling that Microsoft only sees the potential of large licensing costs. The sad part is that, in my opinion, there are a LOT of companies that would bend over backwards to be able to use this technology to its full potential without the Sharepoint costs. There are a lot more small businesses out there than large ones, and I feel that, reasonably priced, this product would take off. I know that PowerPivot is the #1 reason I give to users to upgrade to Excel 2010 as soon as possible.

Regardless, the end message I want to deliver here is that avoiding such critical features as a VBA object model because we’d rather push someone to a server is short sighted and, in my opinion, just plain wrong. Sorry if this offends anyone, but I really feel that way.

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.

 

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

I was playing a version of 21 with my daughter this weekend, and mentioned Blackjack to her. After a few questions, I thought it might be entertaining to build a version of Blackjack for her to play… so I did. And after that, I thought, what the heck… might as well put it up on the site.

I’m not really happy with the cards (I’d rather have pictures) but I’ve got other things to do, so just called it a day with it at the current stage. If you’re interested in wasting a little time, and you have Excel 2007 or higher, you can find it here: www.excelguru.ca/node/107

Comments welcome, as always. J

I had an email exchange with Charley Kyd tonight, and I was bemoaning the fact that I couldn’t link my Excel chart titles to a range. I could have sworn you could do this, but not working with charts a ton, I just could not find a way to do this in the Excel 2010 user interface.

To me, the logical place for this kind of functionality would be in the “Select Data Source” userform. This would seem a no-brainer since it’s where you link up your series, define your chart range and axis labels as well as your legend. Why wouldn’t you be able to link your title here if it was possible?

Well, apparently, just because it isn’t in a wizard or a userform doesn’t mean it can’t be done… and done without resorting to VBA.

What I was looking for specifically was to give the following chart a dynamic title.

The data in the table is dynamic, and reflect the product line. So depending on a data validation list, I could be showing Beer, Wine or Liquor sales. Based on what Charley told me, I set up a little matrix under the chart:

Next, I:

• Select the chart title
• Press =
• Click on the cell that contains my new dynamic title

And voila!

And likewise when I change it to reflect Beer:

So am I the only person who didn’t know this? It’s not really that intuitive is it? (Thanks for the pointer, Charley!)