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.
15 thoughts on “No VBA because PowerPivot is free!”
I love the analogy of the fighter plane and the carrier. I also LOVE the passion expressed in the post.
But, that said - you are off the mark. The reasonings attributed to the lack of VBA support are wrong.
PowerPivot does not have VBA support for the most simple reason: we just did not get to it yet.
Yes, sad but true. adding vba and programmability in general to PowerPivot is a pretty massive task that requires extensive resources.
The main issue is the need to build a proper object model for PowerPivot. The PowerPivot platform is based on Analysis Services and all the APIs are based on OLAP cubes objects. This interface is clearly not appropriate for the tabular model of PowerPivot and the new BISM.
We need to introduce a new object model and it is a pretty major effort. We are starting to solve it in the coming release with a tabular query API for the BISM. But we will still have significant work to do for the next iteration.
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.
Thanks for responding to my post. I've got to tell you something... when it comes to things like this... I LOVE being wrong. 🙂
This is such an important product, and such an important feature... it's great to see that it's not going to be overlooked.
Yes, it would take a long time to implement a VBA object model for PowerPivot if the code to make it was not written with exposed objects, properties and methods originally (but without exposing an interface). How that could happen is beyond me.
@David Hager, me too. I would have thought the object model comes first. Exposing it to VBA would require extra work, but eminently doable. I can even believe it was an improvement that didn't make the cut for release 1, but to my mind it should be right up there for release 2.
I'll admit that I don't have a solid understanding of what happens under the covers when building these full blown apps, but doesn't an object model of some kind HAVE to exist in order to link the UI buttons too? If that's the case, then the VBA side should just be providing stubs, shouldn't it?
gosh, wonder what steve/bill would have say about this exchange 🙂
I think they'd enjoy it, to be honest. 😉
Hi Ken and Amir
I have been in the BI area for 20 years starting with in memory technology on a Mac with Apple script as API. Now finally MS is on the road with PowerPivot. Automation is crucial and it is not just about refreshing one or two Excelfiles but dynamically creat a hundred of them for different users. In fact I cannot wait for an API (VBA) for PowerPivot so I made my own API in VB.net (simulating some keystrokes) which is not recommended but it works.
Amir Is there a good support resource at MS for PowerPivot?
Also is there a way to avoid PowerPivot to recount for every change (like a new measure value). I would like a button or other key function so I can wait untill the last step before recount will take place.
Handeling a dimension with many hierachy levels is a bit tricky. Is there a possibilty to keep all levels closed by default in Rawlables? Then you click on the row it opens.
With regards to the recount avoidance, try this:
-Select your PowerPivot Pivot Table
-Go to the PivotTable Tools contextual tab --> Options --> Filed List
You should now have both the PivotTable field list as well as PowerPivot's showing. On the PivotTable filed list, click "Defer Layout Update" at the bottom.
Add as many measures as you'd like and the table won't refresh till you clear that box.
Yes Amir Netz you are absolutely right, PowerPivot(PP) does not have VBA support i too agree with you, i also experienced this, It requires a proper object model to build, but its not quite easy to do because PP platform is based on Analysis Services and all the APIs are based on OLAP objects. The interface is not appropriate to the tabular model.
We have to introduce a new object model and its a toughest job. I do not have much knowledge in this topic but know a little. What Amir said is really true.
FYI, Predixion Insight integrates with PowerPivot on the client, and (as a side effect) the VBA library allows you to write code that can connect to PowerPivot, create tables from ranges/tables, and add calc columns and relationships. You can get a free trial at http://predixionsoftware.com.
(Disclaimer - I'm the CTO of Predixion Software)
Thanks Jamie. I might just have to check that out!
Tangent first. Web services stuff on skydrive is mindblowing!
First it does seem like the forces are arraying to get us our PP without Sharepoint, which for guys like you and me is cool.
However, one thing that has nagging me about this post. It really doesn't matter how much Sharepoint cost the only relevant business question is where it add value at the margins. As a finance guy, I suspect that you might know a thing or two about raising capital 🙂
Agreed on the Sharepoint costs, but that's kind of the point to me. I can't justify the cost for the benefit that it will give me. So basically, I'm trying to figure out that value at the margins, and I just don't see it.