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.