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!


Thank you

November 24, 2009

Saturday morning, less than a month after I started this site, I took the following screenshot off of the stats page:


Wow.  That is incredibly gratifying, folks.  It helps, of course, to have a hot product like PowerPivot to talk about.

But this means a bit more to me than you might think.

This summer, my ex moved to Cleveland with the kids, which meant that I was soon to follow.  After a career of developing “big box” software while employing a heavily “in-person” style, suddenly I was across the country and wondering how I was going to remain in the game.  Cleveland is not renowned for big-box software development :)

Much of that remains to be seen – I’m still with MS, still part of the PowerPivot team, and we’re still figuring out the best fit for me going forward.  But it’s a good kind of uncertainty now.

I was worried, leaving Redmond.  I’m not worried anymore.  In fact I have not felt this recharged in years.  This site, and the people I’ve met through it, is a HUGE part of that.  If I end up leaving MS at some point, this site goes with me, which is why I am here instead of on MSDN.  This is very much my personal creation, and at my expense.  It’s worth it.

And hey, I got to take a cross-country roadtrip with the kids.  How often does that end up feeling like the practical thing to do?  Truly a once in a lifetime experience:

               Chicago Museum of Science & Industry

                                                The PowerPivotProgeny

So…  thanks folks.  I’ll keep working to make this place worth visiting.  You keep sending comments and questions :)



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 :)

At last we meet, Samurai!

November 22, 2009

“So, Lord Helmet, at last we meet again for the first time for the last time.”

-Lone Starr


Oldtimers who have been reading since the early days of this blog (cough cough October) know that the SharePoint Samurai became an instant hero of mine at the SharePoint conference.  I talked about him specifically here.

Well folks, I am excited to say that your truly has now been interviewed by the Samurai himself!

What happens when an Excel ninja meets the SharePoint Samurai?

Click here to see the full interview, as well as an end-to-end PowerPivot demo

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!)

Updated PowerPivot for SharePoint Install Steps

November 20, 2009

Some folks have been struggling a bit with the server install of the Beta.  PowerPivotGeek has some updated steps here that should help quite a bit:


Also, VidasM has been a busy beaver as well, running multiple installs, troubleshooting, and helping others.  He’s summarized his findings here:



Good stuff from the Geek

November 19, 2009

Dave Wickert posted a bunch of good stuff tonight on his blog:

How to try out PowerPivot online

PowerPivot for SharePoint Books Online Now Available

Why are domain accounts needed on PowerPivot for SharePoint?

Very good stuff, all of it.

Temperature Mashup Demo – Download the Workbook!

November 18, 2009

As promised, here is the workbook that contains everything I have demonstrated so far in the Temperature Mashup example.

       Click here to download workbook

(NOTE: I discovered a bug in the original file – the Qty per Day measure was returning incorrect results.  The version above is now is fixed.  Also no longer needs to be renamed from .DOCX).

And the updated demo walkthrus for this workbook are here:

     Joining the temperature data to my sales data

     Creating a Sales Qty Per Day Measure

PowerPivot CTP3 (Beta) Download Links

November 18, 2009


NOTE:  This is an old post from 2009 talking about PowerPivot v1 CTP3.  If you are looking for PowerPivot v2 CTP3 (and OF COURSE YOU ARE!), please see this post instead:



Old post follows:

OK folks, everything is available as of today!  Here is a collection of the links you will need to grab PowerPivot and try it out:)

Office 2010 Beta 2

You must have the most recent Beta of Excel 2010 installed prior to installing the PowerPivot addin.

Download Here

Note:  Install 64-bit if you intend to use large data volumes.

PowerPivot for Excel 2010

This is the addin required to design PowerPivot models and reports in Excel 2010.

32-bit Public Download

64-bit Public Download

Note:  the addin must match the architecture of the Office build installed – 32- or 64-bit

(Once you have Excel 2010 and the addin installed, you are good to go.  You don’t need to install SharePoint or the SharePoint addin below until you are ready to try out the server story.)

SharePoint 2010 Beta 2

MSDN Subscribers Download Here

PowerPivot for SharePoint 2010

This is the SharePoint addin that allows you to publish, share, and manage PowerPivot reports.  Requires SharePoint 2010 Beta 2, above.

MSDN Download Here

TechNet Download Here

On both sites, expand Servers, then look for SQL Server 2008 R2 – that contains the PowerPivot SharePoint addin.

More Details

On PowerPivot.com

PowerPivot Beta (CTP3) Tomorrow!

November 17, 2009

Yep.  11/18.  At http://powerpivot.com

If you’re arriving at my site for the first time, I suggest you check out the following for CTP3 inspiration:

What is PowerPivot?

Bing 48-Hour Case Study

Temperature Mashup Demo

The Great Football Project

And any questions, please post as comments :)

Introducing PowerPivot DAX Measures

November 15, 2009

Up until this point, I’ve just used DAX (PowerPivot’s extension to the Excel formula language) to create calculated columns in the PowerPivot window.

There’s a lot of utility in DAX calc columns, but that’s not where DAX really shines.  DAX measures let you do things in Pivot reports that simply weren’t possible in Excel before.

In the last video, I mentioned that merely showing raw sales amount didn’t really tell me whether my products sold better or worse in varying temperatures, since there are varying numbers of days of each temperature – perhaps the relatively low sales on Hot days is just because there weren’t many Hot days?

Enter DAX measures, a PowerPivot feature on par with relationships, slicers, and mashups.  But you might overlook them on a casual tour through the PowerPivot for Excel addin, since they live behind a single ribbon button.

DO NOT OVERLOOK DAX MEASURES!  There is literally a world of power lurking there.  I want to make absolutely sure that you grasp them – both their power as well as the “how to,” so I will spend the next couple of posts on them.

First, a word about videos vs. text and screenshots

Awhile back I asked for feedback on which format was preferred – text and screenshots, or videos.  Videos got more votes, by about two-to-one.  But my text-and-screenshot posts are getting more views than my video posts.

Clearly, those of us who are too busy to watch videos are also too busy to respond to surveys :)

I respect that – personally I think I prefer to consume text and screenshots over videos.  A video seems like a commitment, whereas scanning text is on my terms, even if on net I spend the same amount of time on either.  So here is my new philosophy, always subject to revision:

  1. I’m going to use videos whenever I am introducing something new, that benefits from the explanatory power of video
  2. Quick tips and tricks, as well as “power” techniques – I think I’m going to lean toward text and screenshots for these, unless it’s an in-depth technique
  3. Even when I use video, I’m going to try to summarize the content of the videos – not as an attempt to replicate their content, but more as a table of contents so you know why I think it’s worth your time to watch the video

So, I’ll share the videos first, and a summary after.

The Videos



(These are the fixed versions from 11/24/09)

Videos Summary

  1. Quick demo of conditional formatting and number formatting, and how surprisingly impactful they are
  2. The DAX Measure Dialog
  3. Qty per Day can be calc’d using =SUM([Quantity]) / COUNTROWS(RELATEDTABLE(DimDate))
    1. Simple formula, but how does it work?  Please consult…
  4. …The Five Golden (and Simple) Rules of DAX Measures
    1. Home Table = The table where the numeric columns are
    2. Columns always wrapped in aggregation functions like SUM()
    3. Calcs always are working against the source tables, not the pivot report
    4. Two phases:  Filter, then Calculate
    5. Think of things as if they happen cell-by-cell
  5. Once you grasp those, you can suddenly do AMAZING things