Cursed spam filter!

January 6, 2010

Folks, when I first started this site, I wasn’t getting many comments.  I would check the spam filters regularly, and was surprised that, yep, everything in the spam filters was indeed spam.

Way to go spam filter!

But today I was browsing Bryce Johnson’s new blog, and discovered that he’d posted a comment here that never appeared.

So I checked the spam filters again.  And there were 6 real comments in there.  (Now approved).  I’m sure there have been more in the past, but they have expired out of the filter and are gone.

If your comments have been lost like this in the past, please let me know.  And I promise to check the spam filters regularly going forward.  Note that once I’ve approved your first comment, all others are auto-approved after that.

Server Install Cheatsheets from PowerPivot Geek

December 29, 2009

Dave Wickert has posted an updated set of PowerPivot server install steps that you might find helpful.  There is a separate walkthrough for each of multiple different server configs.  Some of these were written by the folks on the team that do this stuff all day, everyday.  Good stuff.

Check them out here. Revamped

December 15, 2009

In case you hadn’t seen, there have been big changes to the official PowerPivot site recently.

Three things in particular worth checking out:

1) Hands-On Demo of Published Workbooks Web Apps

Wanna see what a PowerPivot report can look like once it’s published to SharePoint?

No need to just look at screenshots or recorded demos – you can try it out yourself!

    Click here to interact with live PowerPivot apps!

2) Library of new videos

Michele Hart is a tech writer for the PowerPivot team, and she has quite a cool series of videos going.  She already has a dozen great videos posted, going from install of the client addin up through data import, relationship creation, etc.

   View the videos!

(The “HOW TO” videos are the new ones, at the bottom.  And don’t forget to scroll left and right thru the list).

3) Online Virtual Lab

OK, this isn’t new, but if you missed it before, be sure to check it out.  You can run the entire PowerPivot client environment without installing anything locally.  It’s pretty cool, both as a PowerPivot demo, and as a demo of the general-purpose power of virtualization and remote connections.

   Check it out here

(When exploring the lab, make sure to at least skim the instructions on the right, since it tells you where you can get data to import).

Feedback?  Let me know!

If you have any feedback on any of this official content, you can let me know on the blog here or via email ( and I will make sure it gets to the right people.

…now back to informal content :)

Microsoft Unveils New Programming Language: XL#

December 11, 2009

The wonders of WordArt

REDMOND, WA – New programming language will revolutionize business application development     

OK, yeah, that’s not a real story.  I just made it up, sorry :)

But imagine how perceptions would change if it were true, and instead of being an Office app, Excel were to morph into a professional development tool.  Maybe we’d call it Business.NET or something like that instead of XL#.  A flexible programming environment for codifying business rules, crunching raw data, and visualizing results.  With a rich Windows client runtime library as well as a webserver runtime for processing and rendering on beefy, rack-mounted server farms.

That would be pretty cool wouldn’t it?  Excel gurus could transition from their Excel environments over to Visual Studio without missing a beat, and suddenly they’d be given new job titles like “Lead Business Logic Developer,” “Senior Analytics Programmer,” or “Rapid Modeling Engineer.”

In their new roles, they might be re-org’d into IT.  But they’d retain a very close affinity with the business units themselves – no matter who they reported to, they’d sit on the boundary between IT and the business unit, serving as both diplomat and translator between those two worlds.

And the apps those folks produced would be regarded as very different animals than they are today.  No longer would they labor to produce mere documents.  They would be producing business applications.  And given that increased gravity, everyone, including this new class of developers, would treat these applications in a more disciplined manner.  They wouldn’t just be anonymous files living in shared folders or inboxes somewhere, ready to cause trouble when an external change breaks their assumptions and disrupts the business operations.

Of course, the punchline is, most everything above is already true, except for the human perceptions.

Excel files are NOT documents!  They are applications!


                  Pop Quiz for IT:  Can you spot the critical business app?

Yeah, I know.  Excel is part of the MS Office suite, just like Word, PowerPoint, Outlook, and OneNote.  So clearly, Excel files are just document content, just like those other applications, right?

Nope.  Excel is a development tool.  And no, I don’t mean “if you use VBA.”  The sheets are data structures.  Cell addresses are variable names.  Formulas are the core programming language.  Things like relative reference and formula filldown are the iterative constructs.  Charts and Pivots are data-bound controls.  Excel.exe is both the programming environment as well as the runtime.  (And now Excel Services serves those roles on the web).

(In fact, when I worked on Excel, one of the exercises we used to run through was to compare Excel to other programming platforms, and use those other platforms’ features as inspiration for new spreadsheet features.)

Excel has certainly benefited from being an Office application, so don’t get me wrong.  It would not be remotely as widely-adopted as it is today had it been marketed as a dev tool rather than as an Office app.  But I do want to point out that there as a side effect of being an Office app, the world’s most popular programming tool is perceived incorrectly.  It does not produce documents, it produces applications…  and then those applications get treated with the same informality that documents do.  Like an alien hiding amongst the stuffed animals in Drew Barrymore’s closet.

“OK Rob, I get it.  What’s your point though?”

I’m still thinking my way through this, but I’m pretty sure it is leading me somewhere worth going.

So far, I’m basically thinking that the most successful PowerPivot deployments will involve a new cultural view of the Excel professional.  A view that is at least very similar to the “what if” exercise I played out at the top of this post.

This line of thinking is also in some ways a “sequel” post to the “I” in “BI”, Part Three post.

What does everyone else think?  Are perceptions malleable enough for us to get there?

And credit where credit is due:  Dick Moffat was the inspiration for this post.  If you want to hear someone rail (quite convincingly) about the improper respect given to Excel applications and programmers, look him up on Skype or drop him a line on his blog.

Or even better, drop in at Tony Packo’s next time we get together, like we did this week with Mr. Excel.  Good times, good food, and even better conversation :)

Putting the “Intelligence” in “Business Intelligence,” Pt 3

December 10, 2009

“All we are saying, is give peace a chance.”

-J. Lennon

In Part One I described how standardized corporate BI bummed me out my first time around, then in Part Two described how PowerPivot recharged my batteries and brought me back into the fold.

This time, my first order of biz is answering the question:

Why shouldn’t all of this self-service make IT really nervous?

Short answer:  it’s all about the server – PowerPivot for SharePoint.

Think of it this way:  most of the “trouble” with Excel as a reporting and BI tool has nothing at all to do with Excel the application.  The trouble, actually, is with Excel files.

    Excel Files are the Problem With Excel BI

When you use PowerPivot for SharePoint, though, Excel Files become PowerPivot Web Applications.  And that makes all the difference in the world.

What do I mean by that?  Let’s step back for just a moment and consider the common complaints about Excel.

Excel as a reporting/BI tool – the three common complaints

1) “We don’t even know these files exist”

Yeah.  Spreadsheets can become awfully important to the business without IT ever hearing about them.

That’s because files are sneaky like that.  They hide in doclibs and shared folders, right next to the static docs produced in Word and PowerPoint.  They flow from person to person in emails.  So IT never hears about them until something goes wrong.

A published web app, by contrast, cannot hide.  It relies on the server to execute it.  And servers don’t hide like files do.  In fact, IT’s help is often required to get the server in the first place.

   PowerPivot Reports Rely on the Server to Execute

Even better, the PowerPivot server provides a usage dashboard that shows all of the PowerPivot reports published to that server, how often they are being used, by who, etc.

2) “Spreadsheets break”

Yes they do that on occasion.  But why?  It’s not like the contents in the file simply go rotten.  Most often, something external changes.  A data source that the spreadsheet relies on gets moved or renamed.  The spreadsheet author responsible for updating it each day is out sick (or leaves the company).  Another business process, policy, or business rule gets updated without consideration of the spreadsheet.

Notice how most of those are an awareness problem.  If IT knew about the spreadsheet in question, they would be much better-prepared to avoid changes that break them.

And the other source of problem – the author is not around to update them with new data – is precisely the reason why the PowerPivot server has an automatic data refresh feature that updates reports on a scheduled basis.  You know, just like all other respectable data-driven web applications.

3) “Spreadsheets get out of date, and out of hand”

This is another drawback to using files as business apps.  You create a spreadsheet, make sure it contains the latest data and logic, then you give it to people…  and immediately lose ALL control over it.  Some examples of why that isn’t fun:

  1. Those people can then modify the spreadsheet in ways that give them incorrect results (usually, this is unintentional, but on occasion is rooted in unethical motives).
  2. Your data and business logic can then walk out the door – those files contain input data and results data that are often quite sensitive.  And even the formulas themselves represent a lot of intellectual property.  All of those can leak once they are packaged in a file.  And again, that can be either accidental or malicious.
  3. Good luck pushing out updates – when you revise an existing spreadsheet, how do you make sure everyone adopts that latest version?  Older versions are sitting in dozens or potentially hundreds of inboxes and hard drives.  Ready?  Go!

If your method of sharing is to publish it to a PowerPivot server, though, all of that goes away:  Users can interact with the reports but cannot download the files!

    PowerPivot Server Interaction Model

No more accidental or malicious modifications.  Accidental leaks go away, and malicious leaks are confined to results data only – no input data or formulas are visible in the web reports.  And users simply cannot help but always get the latest.

See that, IT?  Those Excel users that are such a thorn in your side (and vice versa) basically have the same problems you do.  The conflict between you over the years – all you needed was better tools.

It’s going to take some adjustment of course.  But peace, cooperation, and better results are worth it :)

Putting the “Intelligence” in “Business Intelligence,” Pt 2

December 4, 2009

“I’ve always believed that the mind was the most dangerous weapon.”

-John Rambo

       Which explains the huge muscles & machine gun

       …which explains the huge muscles and machine gun, right John?

A View of BI that Henry Ford would have Loved

In part one I described how I grew a bit disheartened by BI back in 2005 – a lot of good thinking was going on, but it wasn’t the kind of thinking that got me jazzed about going to work.

Back when the term was fresh, the word “Intelligence” was so much more energizing than what I ultimately saw in practice.  Where, exactly, was this “intelligence” I’d been promised?

Instead, I saw data being squeezed into fixed molds, tightly secured, modeled by IT (and by committee) and then ultimately delivered to knowledge workers via a handful of tightly managed, supercharged data browsers.

Again, don’t get me wrong.  I understood the necessity of those kinds of stacks.  I understood why the Wild West chaos that grew in the absence of such stacks needed to be reined in.  And I had also personally worked for two teams at MS (I won’t name them) who collected usage metric data by the ton, and yet had no respectable way to explore it (both have since been addressed, but boy was it infuriating at the time).  That all made sense to me.

Bring back the human intellect!

But I had been hoping for better.  In a nutshell, it seemed like centralized BI efforts were spreading the following philosophy:  “Leaving things to human ingenuity has led to chaos, so clearly, the answer is to marginalize the role of the human beings as much as possible.”

Why does it have to be so extreme?  It doesn’t.  We all just needed to break out of our little boxes and see that there were other things possible.  PowerPivot is a HUGE step toward getting the human brain back into the loop.  Even in the short existence of this blog, I’ve shown a number of places where a human brain, operating close to a business problem, can do amazing stuff when it is cut loose.  A few examples, for refresher’s sake…

Breaking out sales data by temperature at the point of sale (using Intenet data):

              temp slicer

Joining disparate data sets that IT would never warehouse:

     PowerPivot Relationship Dialog

The Bing test team slicing/grouping their data in ways they had not even imagined the day before they tried PowerPivot:


Using conditional formatting and DAX measures to make previously unseen comparisons jump right off the page:

                        PowerPivot Conditional Formatting and DAX Measure

Creating custom calculated groupings in less than a minute using something as simple as the IF() function and a calc column:

PowerPivot Custom Grouping Formula

And then today, a friend of mine quickly discovering that he has indices in his SQL server that were not helping, just sucking resources.  (As a bonus, he learned this from the slicers themselves, he really didn’t even need a table or chart!)

                                             PowerPivot Slicers for Indices

Self-Service BI = QUALITATIVE Advances, not just Quantitative

Even for a longtime member of the team, this was a relatively recent revelation.  (Alliteration for the win!).

When we started PowerPivot, “Self Service BI” was the mantra.  It still is, to a large degree.  And for us, that generally meant “look at all these business needs that go unmet because of insufficient capacity!  We can fix that!”  So even though we never said it this way, increased Quantity of reporting/analysis was the driving force.

But more generally, we were knocking down a wall.  Yes, that wall was holding back a large quantity of data analysis and reporting.  But you never really know what’s behind a wall, until you knock it down.  Cut people loose, and not only do unmet requests suddenly become met, but previously unformulated questions get asked, and quickly answered.

That’s worth restating, so I will :)

PowerPivot Blinders Off

(As an interesting parallel, the same thing happened with us switching to an in-memory store for PowerPivot.  Take off the multi-decade straitjacket enforced by a physical rotating disk with a mechanical seeker head, and what happens?  A series of successive Eureka! moments – “Oh, YEAH, we can do THAT, TOO!  WOW!” – and by the time we were done, we had an engine whose feature set and characteristics far exceeded the original vision.)

So…  a greater quantity of questions get answered.  Questions get answered more quickly.  And perhaps most significantly, better questions get formulated and answered.

Sounds like Agility.  And dare I say it, Intelligence.

I’m glad to be back in BI.

Coming in Pt Three:  Blending the Strengths vs. Turning Back the Clock

When I started to write about this particular topic, I thought it was just going to be a single post.  It turned out to be more :)

So to keep things from running too lengthy, I’m gonna stop here and pick it up next week, when I will cover how this empowerment does NOT mean a reversion to the Wild West of Data.

Some recent PowerPivot links

December 4, 2009

There have been some excellent PowerPivot posts floating around recently, and some of them may have slipped under the radar during the holiday week.  A few highlights:

Samurai Video!

First, in case you missed it, yours truly did an end-to-end interview and demo video with the SharePoint Samurai:

Tuning PowerPivot Reports to Serve as Data Sources

So far I have just been focusing on how to turn raw data into killer reports.  But PowerPivot workbooks are also fully queryable data sources for other applications, like Reporting Services and PerformancePoint.  There are a few tricks to make this work smoothly.  RussCh from the PerformancePoint team provides an excellent treatment of this topic here:

PowerPivot Meets Dallas!

Wow, killer new vid from the Niederlander Nullifier himself, Kasper de Jonge :)  PowerPivot against cloud data!

Why PowerPivot for SharePoint?

Dave Wickert lays out why the server component of PowerPivot is not something to ignore.

Using PowerPivot for Commerce Server Analysis

Gaël Duhamel shows that this is really easy and effective:

Putting the “Intelligence” in “Business Intelligence,” Part 1

December 1, 2009

I remember the first time I heard the term “Business Intelligence.”  I had been working on Excel for awhile at that point and loved crunching data, but boy, “Business Intelligence” sure had an intriguing sound to it.  It conjured up visions of darkened CIA situation rooms, Tom Clancy novels, technology that bordered on sci-fi, and a young Alec Baldwin (who in subsequent movies, transformed into a not so young Harrison Ford, then into Ben Affleck, and now… Captain Kirk?).

                        Some faces look more intelligent than others

“Intelligence” sounded like a brand new direction, an empowering evolution to the Excel toolset that already turns mere mortals into business saviors.  “Sign me up,” I said, and became the lead program manager in charge of Excel 2007’s BI feature set.

Two years later, I was sick of BI and leaving the Excel team to do something less corporate.  It all had just turned out to be so much more formulaic and rote than what the term “Business Intelligence” had promised.  In short, here is what I had learned:

What I Had Learned about BI, Circa 2005

  1. “Real” BI was the domain of the IT department, which rightly sought to standardize as much as possible
  2. Excel usage, while empowering, was something that IT often regarded as a liability
  3. Real BI tools were usually just the visible component of a highly premeditated, IT-prescribed, multi-layer stack
  4. Excel users themselves had zero interest in “real” BI tools, and were disappointed that we’d done so much BI work in Excel 2007 instead of other spreadsheet features

I want to be absolutely, 100% clear:  I did not disagree with anything I had learned.  By then I had seen, with my own eyes, the real-world factors driving those trends, and had become convinced they were necessary.  There was nothing wrongheaded about standardization, centralization, and discipline.

No, I was not opposed to what I had learned.  Instead, I was just really disheartened by it.  The whole thing was like Kryptonite to a personality like mine.  I am very much “of the people and for the people.”  Big, top-down, standardizing efforts are not my cup of tea.  If it wasn’t their goal to drain all of the fun and creativity out of things, it certainly seemed like an accepted side effect.  If these things are necessary, was my thought, so be it, but let someone else lead that charge.

So off I went, in 2005, to pursue things that better suited my democratic mindset.

Lo and behold, a couple years later, I was back in BI.  And excited about it.  PowerPivot, of course, was what brought me back.  But I want to be more specific than that, and will do that in part two.

Why BI spending defies recessions

November 27, 2009

“The thing is, winning covers up a lot of sins.”

-Former Chicago Bull BJ Armstrong, when asked why Phil Jackson was such a great coach

With that interview response, BJ Armstrong became one of my favorite NBA players of all time.  The honesty, the subtlety, and the guts that it took to say that… I loved it.  Phil Jackson had been lionized for years after winning so many titles with the Bulls, and later the Lakers.  And here was a bit player from those Bulls teams basically saying, “Phil wasn’t as impressive as you guys made him out to be.”

So, Phil Jackson looked like a genius to outsiders.  But the most important thing, as implied by BJ Armstrong, was that Phil Jackson had coached a man named Michael Jordan.  Jordan was going to make any coach look good, and a coach with Jordan on his team could do things that would lead other teams (and coaches) to ruin.

Phil continued that particular brand of wisdom in later years, by agreeing to coach the Lakers, who had Kobe Bryant and Shaquille O’Neal.  We never saw Phil take a terrible team and try to rescue it.  Maybe Phil truly was wise, and understood his own good fortune better than the media did.

What does this has to do with BI?  “Winning covers up a lot of sins.”  During boom years, such as the mid to late 90’s and the mid 2000’s, everyone made money.  Financial advisers all looked like geniuses during those years, no matter what stocks/funds they picked.  Real estate investors were whiz kids.  And consumer spending was so hot, you didn’t have to run a tight ship in order to make money.  A rising tide raises all boats.

When we were planning the feature set for Excel 2007, I remember us looking at IT spending trends during the dot-com bust.  Overall IT spend was sharply down.  And all segments of that spending were down as well.  Except BI.  BI spending was up.

The same thing is happening now, and for the same reasons.  Winning covers up a lot of sins.  And adversity demands efficiency.  Effective BI investments are orders of magnitude cheaper than making bad business decisions.

As a software pro, there’s no place I’d rather be than business intelligence.


Kasper de Jonge Video

November 24, 2009

I just “met” Kasper a few weeks ago via Twitter, and quickly found out he’s a basketball nut – he plays the game a lot, and watches NBA broadcasts at some very strange hours :)

I challenge you to watch this video, listen to his voice, and imagine him as anything other than a seven-foot-tall shot-blocking monster in the lane.  Yeah, he claims he is six-foot-three and a perimeter shooter.  Sure he is.  Self-report is a notoriously unreliable survey technique Kasper, so I’ll just stick to extrapolating wildly from small amounts of unrelated data, OK? :)

Jokes aside, it’s a great video.  He shows off some things that I have not, such as pulling data into PowerPivot from an SSAS cube, and using the Remove Duplicates feature in Excel to clean one of his tables.  Bravo!

Fixed Version of the “Qty per Day” Measure

November 24, 2009

“Unless I am wrong, and I am never wrong, they are headed dead into the Fire Swamp.”

-Prince Humperdink

A funny thing happened today.  Kasper de Jonge asked a couple of questions (in comments) about the Introduction to DAX Measures post.  I answered the “easy” question, and then went off to find the answer to the difficult questions.

Heh heh, I had them backwards.  The “easy” question led to an in-depth discussion with Howie and Marius from the DAX team, during which I discovered an error in the “Qty per Day” measure.

I’ve uploaded fixed versions of the videos, so anyone watching for the first time (or re-watching now) will get the proper information.

And the workbook file available here is also fixed.

If you watched the vids already and don’t want to watch them again, here’s a quick summary of what I changed:

  1. Remember the golden rule, “Filter then Calculate?” 
  2. Filter only impacts the “home” table (aka Fact table).  So far so good.
  3. But the filtered home table is never then used to then filter other tables during the Calculate phase.  Not even if you use RELATEDTABLE().  So for example:
    1. In my former example, I used COUNTROWS(RELATEDTABLE(DimDate)) as the denominator of the measure
    2. That expression, however, was returning the grand total number of rows in DimDate, no matter how the pivot was sliced.  (Because of point 2 above)
  4. The fix was to use the [OrderDateKey] column from the FactInternetSales table (the home table) and do a COUNTROWS of the DISTINCT set of values in that column.
  5. Note that if there had been a slicer (or row/column field) from the DimDate table in the pivot, then the DimDate table would have been filtered, but:
    1. Not as a result of the Fact table being filtered
    2. It is strictly filtered only by fields in the pivot that came from the DimDate table
    3. And it is therefore much less filtered than the Fact table, which is filtered by all of the fields in the pivot
  6. Along the way I ran into the fact that the DISTINCT function requires an explicit reference to TableName[ColumnName], and does not accept the shorter [ColumnName] only syntax.

…yeah, the revised videos might still be worth a look :)

The Coming Week

November 21, 2009

A bit of a short week coming up with the Thanksgiving holiday – I am headed home to Florida on Wednesday (Gator football game as a bonus!).

…but I’ve got a couple cool things in the queue to post before leaving, starting on Monday :)

In the meantime, there’s a lot going on elsewhere that I’d like to make sure everyone is seeing.

Two new official PowerPivot blog posts:

     Scheduled and Automatic Data Refresh

     Report Gallery on SharePoint

And one from Excel Services:

     Expanded support for rendering XL client features (including Pics, yay!)