What color are Santa Claus’s Pants?

July 29, 2011


Yes, this is a real product you can buy. 
And yes, it is VERY disturbing.  It also dances.  Yeah.

A bit off topic today.  Fridays are like that sometimes.  Let’s call this Friday Brain Food.

SQLRockstar’s Masterpiece

SQLRockstar, aka Tom LaRock,  has written something awesome on his blog.  It deals with interview questions and hiring and is one of the most insightful things I’ve read in a long time. 

It’s a short post – go read it now and then come back for my commentary.

My Reaction:  DBA’s and Software Testers

That post seems so out of place on a SQL blog.  It needs to be a NYTimes editorial or something.  Bravo Tom, but if you decide to make a habit of being this insightful all the time, I will frankly find it hard to be around you Smile

The part that lit me up the most, by far, was that last part:

If a person says that they are typically thought to be red but they don’t really know what color they are because they have never met Santa Claus then you have someone that is not only open to new ideas but will likely want to test those ideas thoroughly. My guess is that maybe 1 in 20 people would respond in this manner. Most folks don’t like to be the pain in the ass that points out the obvious facts to others around them.

(And not to belabor the point, but here’s the link, you really should read his post.  Oh, and Tom has grown sensitive to plagiarism over the years so I figure, why not over-link to him to be safe?  He’s been plagiarized a million times this year alone.  Go copy/paste some text off his blog into Word or anywhere else and see what happens.  Seriously, something funny does happen.)

OK, why did that part crack me up?  Because I have met these people.  And they are much more rare than 1 in 20. 

In my 13 years at Microsoft I really only met two people like that.  And they were both amazing software testers.

Thanks to a college recruiter with a particular quota to fulfill, I was miscast as a software tester at Microsoft for my first year in Redmond.  Actually, I realized in my very first WEEK that I had been routed into the wrong job, but it took a year (and some serious luck) to escape.

You see, to be a truly great test engineer, you have to come to work every single day with a belief that the product is broken.   That your coworkers who built it failed to do a good job.  You have to be contrarian to the core…  but still be able to work with others.

Two Different Flavors of Contrarian

If you know me personally, you might be thinking “well hey, Rob is QUITE contrarian,” and you have a point, but that’s a different flavor.  I have no problem with ideas and thoughts that are unpopular, and I’m less afraid to share those thoughts at times than perhaps I should be (I sent some unsolicited investment advice yesterday that comes to mind), but fundamentally I am a VERY optimistic person.  I believe things work.  I believe in the people around me.

I found it VERY difficult to be a software tester.  My biggest problem, every single day, was motivation.  Telling myself that my job actually made a difference.  Because hey, the product worked!  And more importantly, there was serious CREATION going on!  The thrill of creating things was everywhere, and I wanted to be on THAT side of the equation.  I was constantly trying to create, by disguising new product ideas as “bugs” and entering them into the system.

I still did pretty well at it, but honestly, I never sunk more than a solid hour of real work into actual software testing each day, like the guy in Office Space.  And I suspect that was true of many of the test engineers on my team.

But then there was Lawrence.

Lawrence Landauer was head and shoulders above everyone else on that team.  When Office 97 was done, he had found four times as many bugs as the person in second place on the team.  Lawrence worked LONG hours, but he never seemed to be straining himself at any particular point.  The bug tracking system (called RAID at the time, before lifesucking corporatism took hold and gave us Product Studio, and then later Team Foundation Services) was like a word processor for him.  And I am absolutely POSITIVE that if we asked Lawrence what color Santa’s pants are, he would laugh and say he’d never met Santa. 

Honorable mention in this category goes to James Rivera.  I give James a 50% chance of answering that way, depending on the day.  Oddly, James came up this week when I was talking to Bill Jelen (Mr. Excel).  And naturally, he came up because he had been the bearer of bad news.  Still got it after all these years James! Smile

Are DBA’s Testers, or Vice Versa?

Tom closes his article with the following thought:

Most folks don’t like to be the pain in the ass that points out the obvious facts to others around them.

Then again, most folks aren’t DBAs, either.

I watch DBA’s talk to each other in Twitter a lot.  And mostly what they do, as their primary means of social interaction, is complain.  Complain complain complain.  Mostly about their coworkers, or clients.  Things like “no you can’t have admin privs on the database mr. developer” but usually things that go over my head like “look at this query the previous schmuck wrote that I have to clean up.”

But in person, these are all VERY nice people.  I meet them all the time at SQL Saturdays in particular.

The software test teams I worked on in my first year at Microsoft were just like that.  So are DBA’s really just software test engineers who have moved a bit toward the “create” side of the spectrum?  Are test engineers really just DBA’s who haven’t realized it yet?

Building, growing, and maintaining a good quality assurance organization is a huge ongoing challenge for most software teams, and I think that is mostly due to the psychological challenges involved.  There are simply not enough Lawrence and James types running around.  Understanding and embracing that may be critical to success.

Distinct Count in PowerPivot v2 – MUCH Faster

July 28, 2011

A quick update, as tonight I managed to sit down and experiment a bit with the V2 Beta for the first time in many days.

(OK, actually, I am standing up…  because I have a fantastic new treadmill desk and have walked nearly 8 miles today while working, but that’s a topic for another post).

I was working with one of our HostedPowerPivot clients today who was observing slower-than-expected slicer click performance in one of their reports.  And after some sleuthing, I found that they had a “distinct count” measure in their model, something like this:

[Measure] = COUNTROWS(DISTINCT(Table[Column]))

Now, that’s a pretty useful measure in many situations, like “does product X sell every day?”  Simply taking a count of the date column doesn’t cut it, because if it sells twice on one day and not at all the next day, the count is still 2, and you want it to be 1.

Trouble is, in PowerPivot v1, that operation can be slow.  I won’t bore you with all the details, because frankly, I don’t know all of them, heh heh.  But I do know that PowerPivot v1 does end up creating all kinds of temporary new tables in memory behind the scenes when it is evaluating the measure.

Anyway, I’d heard a long time ago that PowerPivot V2 was going to make distinct counts a lot faster, so I thought I’d try it out.

The Test Case

First I needed to create a test case that was challenging.  I don’t want to compare “fast query vs. fast query” because that often gets skewed by fixed overhead.  Much better to test a “worst case scenario.”

So, I took a 15 million row table, and added a calculated column to it.  Calculated columns in PowerPivot are much less compressed than imported columns, and are therefore more challenging for the measure engine to scan through, looking for duplicates etc. which is required for a distinct count.

And then, to make matters worse, I just used the RAND() function so that there are many unique values:


Cutting to the Chase:  The Results

A distinct count measure in PowerPivot v1 took 35 seconds to complete in my test pivot.  This was a big part of why our client was seeing slow perf today.

That same measure, in that same pivot, took less than a second in the V2 beta.

Wow.  This is gonna be nice.

One Last Note:  Two Ways to Write the Formula

PowerPivot v1 did not have a dedicated function for distinct count, you had to do the countrows of distinct thing like what I showed above.

But in PowerPivot v2, there IS a DISTINCTCOUNT() function.  So you can write your distinct count measures in two ways:

[Measure] = COUNTROWS(DISTINCT(Table[Column]))

[Measure] = DISTINCTCOUNT(Table[Column])

That new function is nice, makes for a more readable formula.  BUT…  the performance is still the same.  Either way I wrote it, the measure was sub-second fast.  Very nice.

Couldn’t Resist… Parameterized PowerPivot Report!

July 20, 2011

In the intro to David’s guest post below I mentioned some new applications we are building.  Check out a PowerPivot report where, in the browser, you can type in a SKU number and get a full dashboard for just that product!

Parameterized PowerPivot Report in SharePoint

Click for Larger Version

Much respect to our resident Data Junkie Monkey, aka DJ Monkey, for pulling this together.

Guest Post: TOPN in PowerPivot V2

July 20, 2011

Well folks I haven’t had much time to play with the V2 Beta (aka CTP3) yet.  A lot going on over the past week:

  1. Authoring a brand new suite of models and reports for our Duane Reade DR-Direct solution
  2. A nasty crashing bug in PowerPivot for SharePoint that we’ve now learned to catch and repair
  3. I submitted a PowerPivot article to CIMA Insight magazine (for August)
  4. Running refresh tests with PowerPivot servers in Chicago pulling data from SQL in Dallas (over Internet VPN – more on this later) for the HostedPowerPivot offering

So in the meantime, David Hager has a short article to share on the new TOPN function.  I haven’t had a chance to try it yet, and I’m a little surprised that it doesn’t have special handling for ties (as he’s discovered).

David Hager on TOPN

Using the New PowerPivot Version 2 TOPN Function: Up, Down and Between

One of the new DAX functions included in the Denali PowerPivot CTP3 release is the TOPN function. This function returns a table which can be aggregated for the top n values. Prior to the release of this function, workarounds were available to achieve the same results, mainly through DAX ranking formulas (and Denali PowerPivot now has native functions for ranking too!). So, a typical DAX formula which uses the TOPN function is:


where SalesTable is the table and SalesTotal is the column in that table containing the top n results to be analyzed.

Well, that was easy enough. However, there is a systemic problem here and it’s mainly data-related. In order to understand why the TOPN function don’t work the way you might expect, read the following that comes from the Denali PowerPivot CTP3 help documentation on TOPN.

“If there is a tie, in order_by values, at the Nth row of the table, then all tied rows are returned. Then, when there are ties at the Nth row the function might return more than n rows.”

In English, that means if there are duplicate values in your data, the TOPN function will return the WRONG answer if the Nth value is adjacent to duplicates. In order to get a meaningful result then, the data must be converted so that each data point has a unique value. The question is, how do you do that and retain the integrity of the data? An Excel trick can be used here as a workaround. This method requires that each value be incremented by a very small but unique value. The best implementation of this comes in the form of a calculated column with the following formula

SalesTable[UniqueIncremValue] = 

The goal of this formula is to create a column of unique numbers add up basically to zero. There is a balance that is required in this formula between affecting data and insuring that all values in the dataset are unique. The RAND function returns a 15-place decimal, which is the limit in Excel. However, if all of the decimal places were used, the value of the number could be as high as 1 or as low as -2. By multiplying the random numbers by a very small number, the numbers will not interfere with the individual values if they themselves are not small numbers. However, if the multiplier is made too small, not all of the random numbers may be unique depending on the size of the dataset. So, if you are going to use this workaround, you need to test [UniqueIncremValue] to see if all of the values are unique. The best way to do this is to compare its distinct count with the distinct count of a primary key column in the table. If they are equal, TOPN will work for any value of N.

Now, the original formula will work fine if the new calculated column is used in place of SalesTable[SalesTotal].

SalesTable[SalesTotal]+ SalesTable[UniqueIncremValue]

It should also be noted that the TOPN function can used in a DAX like the one shown below to afford a BOTTONM result. Note that this formula takes advantage of the new DISTICTCOUNT function.

         DISTINCTCOUNT(SalesTable[SalesTotalMod])- N,

I think you can see that the TOPN function is a valuable new item for your DAX toolbox. Have fun!

PowerPivot V2 CTP3!

July 12, 2011


“Tell ya what Mom…  just lock me in here tonight.  I will be fine.”

A lot of you are already on the scent, but just in case…


CTP3 = “Public Beta”

What the heck is a CTP, you ask?  Well it’s a “Community Technology Preview.”  You know…  what we used to call a Beta.  Calling it a CTP is a much more grown-up approach.  But it’s silly IMO.  A Beta release of PowerPivot V2 is EXCITING!  “Beta” was, and always will be, a more exciting word.  Do you think the next release of Halo will have a Beta or a Community Entertainment Preview?

But I digress.  These are the important things you need to know:

  1. The public beta of PowerPivot V2 (aka “Denali”) is available!
  2. It contains MANY exciting features (preview below)
  3. Everyone can download it, but I don’t recommend putting it on your “production” computer
  4. Workbooks created in V2 cannot be edited by PowerPivot V1 (V2 “upgrades” V1 workbooks when you open them, but it’s a one-way trip)
  5. This version is buggy enough that you won’t want to use it for everyday work (technically you aren’t really allowed to either), but it does tantalize in a BIG way and already can help you out with V1 (see below)
  6. The final version won’t be available for several months, exact date unknown
  7. Download it :  http://www.microsoft.com/download/en/details.aspx?id=28150 (UPDATED link to point to the newer RC0, not CTP3.)

Take the Pepsi Challenge

I’m gonna show you two pictures and you tell me which one you prefer.  Ready?

OK – would you rather “eyeball” a PowerPivot model, trying to see which tables relate to which, using this:

PowerPivot Manage Relationships Dialog

imageContinuing the candy theme, that feature is kinda like candy corn – dull and not very inviting.  What’s that you say?  You prefer something more lively?

I agree.

So… maybe, just maybe…  you would prefer something like this little number below…


PowerPivot V2 "Denali" - Diagram View

Diagram View – Click for Bigger Version


image That’s more like it.  Let’s call this feature a Reese’s.

I debated, say, Sour Skittles.  Or Spree.  Or Fun Dip.

Yeah, Fun Dip.  There’s some nostalgia.  And the color scheme in this picture fits, too.


Make Your Own Posters for Free!

This feature is:  Fan.  Freaking.  Tastic.  In fact, this is useful ALREADY.  Why not make a copy of an existing model, load it in V2, and then use V2 to generate this picture?  Print it out, put it on your wall, reference it as you work on the model in V1!

In fact I just sent this to a HostedPowerPivot client today to help them visualize what we’ve cooperatively built in a recent engagement:


Some PowerPivot Models are More In-Depth Than Others (Again Click for Larger)

In the “looking a gift horse in the mouth” department, there are some warts with this one for sure.  It took me 15 minutes to get the tables to all fit on a single page.  The fonts are small.  And I wish there was a way to toggle between “show details for each table” and “just show a big fat table name that I can read, let me expand for details.”  Because that would fit on a screen much better.

Oh well.  I still love it, even as I immediately crave the next round of must-have improvements.  Such is life.

Another Killer Feature:  Sort-By Column!

See if you can spot the moment of awesome in this picture:

PowerPivot Sorted Labels in a Slicer!

Yeah…  that’s right.  The days of the week in the slicer are sorted CORRECTLY.  There was NO way to control sort order of labels/values in a slicer in V1 without prepending a number and doing something like “1 – Sunday” as your labels.  Blech.

In V2, no problem, here’s the feature you use:


That one doesn’t require much explanation, I think.

More to come…

There are a few other goodies that I want to share but I have a bunch of real work to do today for Pivotstream – we are developing a new suite of reports and models for our retail suite after an excellent few days of strategizing and planning.  I’m excited to share that as well – I’ve reached the point where I need about three of me to do everything I want to do. That’s a good sign of course, but boy is it tantalizing to leave so much on the table every day.

Stay tuned.  And in the meantime, if you have an extra computer, don’t wait on me.  Go download it!  In fact…

Send me your observations!

If you are messing around with the Beta (yes, BETA damnit), send me your observations.  Things you like.  Things you don’t.  Things that surprised you.  Whatever.

I am Rob.  At a place called PowerPivotPro.  Dot com.  If you get what I’m sayin.