The “Ferrari” of PowerPivot Books

October 26, 2010

A Respected Authority

A little background that is relevant:  when I left Redmond in July 2009, the DAX formula language only barely existed – the formula editor wasn’t done, nor were the many DAX functions.  So when I started the blog a year ago today, and dove into the Great Football Project, I started with the bare bones – importing data, creating slicers, etc.

When I decided to dive into DAX shortly thereafter, it wasn’t long before I discovered Marco Russo.  That name was familiar to my colleagues on the Analysis Services team, as he and Alberto Ferrari (and Chris Webb) had long been recognized as elite authorities and authors on Analysis Services.  But I was not a longtime AS team member, so the name was new to me.

Marco’s early blog and forum posts, however, endeared him to me rather quickly.  His longstanding knowledge of MDX, combined with his enthusiasm, had rocketed him to a level of DAX understanding that was frankly…  daunting.  No matter how complex the topic or the formula required, Marco knew what you needed.  In fact, he seemed *drawn* to the more complex stuff while I was still grinding out the fundamentals – Pareto/ABC analysis?  I had to go refresh myself on what that *meant* before I digested his post on how to *do* it in DAX :)

This Aggression Will Not StandMarco’s posts were a valuable resource, especially as I learned my way around a new world of calculation power.  They were useful as a reference – “how do I do X?” – and probably even more so as inspiration – “hey, this guy knows a LOT about MY product that I DON’T! – this aggression will not stand!”

In all ways, exposure to Marco was an excellent force for advancement.  It was my first introduction to the power of community .  I was honored, over time, to have some back and forth blog posts and comments exchanged with Marco.  Shortly thereafter, when I launched the PowerPivot FAQ, Marco was in the first wave of invitees to contribute.

The long awaited book arrives!

Now, about a year after those first collaborative education efforts in the community, the walking PowerPivot/SQL/AS encyclopedia known as Marco Russo is now available in a compact form factor, a 371-page book on PowerPivot, co-authored with his SQLBI.com co-founder, Alberto “V12” Ferrari.

I myself have been particularly anxious for the release of this book.  Mr Excel’s book and Denny’s book were excellent “zero to sixty” introductions and I reviewed them from the perspective of people ramping up on PowerPivot for the first time.  I have told many people that the Marco/Alberto book is the first one I am going to read primarily for my *own* benefit, and then review accordingly.

Positioning and Audience

My high level impression after reading the book jives with my expectations:  it is, at times, quite a bit more advanced than those prior books, which is a good thing for my purposes.

But it does NOT assume that you already know PowerPivot.  The book starts from scratch just like the other two books.  A good thing for the audience at large, for sure.  The first few chapters flow a lot like the first few chapters in the other books.

On net, I’d recommend this book to one of two audiences:  BI/SQL pros starting their PowerPivot journeys from scratch, and Excel/SharePoint pros who have read one or both of the other books (or have a decent amount of hands on experience already) and are ready for some more advanced techniques.

Assorted Highlights

Without giving away too much, here are some top level highlights:

  1. The little “Note” boxes throughout are HIGHLY useful.  Most of them were familiar to me, but many were not (there are probably 150+ of these in the book, so even if you are familiar with 3/4 of them, that means there are about 40 gems waiting for you).  Simply scanning every Note in the book would be a very valuable exercise.
  2. Coverage of database jargon and techniques that are both foreign and useful to Excel pros – good to see them cover this ground.
  3. In-depth explanation of data modeling, aka “how to layout and relate your source tables for best results” (more on this below).  An excellent treatment of this largely uncovered (to date) topic.
  4. Real-world recommendations that can only be gleaned from applying the technology in practice, like “don’t trust the relationship autodetect, create them yourself manually to avoid confusion.”
  5. Delivers, 100%, on the promise of teaching me new things.  In fact, reading the book on the plane was quite frustrating – all I had was my netbook, which does not have PowerPivot on it (I know, an oversight on my part – but given the size of data sets I typically work with, not getting around to the netbook install is forgivable).
  6. Some of my favorite tricks exposed!  I have a number of blog topics queued up that I never seem to get to, like how to use a slicer as a general UI device (without relating it to your other tables at all), and several of those little tricks are covered in this book.  Really, really cool stuff.

Warning:  This is Your Brain on DAX

Your Brain On DAX I want to say this loud and clear:  When it comes to stretching their brains in abstract directions, which uber-advanced DAX requires, Marco and Alberto are WICKED SMART.  Like, off-the-charts, immeasurably, smart.  Plus they have spent a professional lifetime up close and personal with the analysis services engine, as well as the team.  That presents a challenge at times, because I am not sure they realize how much they grasp and know intrinsically.

As a result, at times the book takes you from zero to sixty…  then jumps straight to one-fifty without bothering to acknowledge seventy through one-forty on the way :)

Typical experience for me when reading pages about 190+ follows:  “Yeah, I’ve done that… seen that, yeah, that makes sense… ok, never thought about that in that manner but I can see how that’s better…  WHOA what the hell just happened there…  geez I am not sure I grasp the implications…  I’m gonna need to try that on my own data when I get home in order to understand…”

…And That’s Why You Read This Book!

With the warning above out of the way, let me stress:  that power, and intelligence, is why you will want to read this book – if not now, then later.  And you will want to re-read certain sections.  And keep it handy as a reference when you encounter complex problems.

Let’s put it this way:  I know PowerPivot REALLY well.  From putting it in practice over the past year, I know a bunch of things that Marco and Alberto don’t know, in fact (although I will see them in a couple of weeks and swap stories, so that is short-lived, heh heh).

Dogeared PowerPivot BookSo, with that in mind, let me show you the following picture, at right.

That middle section – that’s all of the pages I have dog-eared as ASAP Priorities to try out.  Those are also the same places where my brain got thoroughly scrambled.  And that’s a GOOD thing.  Scrambled brain is an excellent indicator of expanding your capabilities.  So I’m thrilled to drill into all of this :)

All the new power that is hinted at…  it’s like PowerPivot v2 already shipped.  I am positive there are multiple things I am doing today that can be done more efficiently.  My pride is injured…  now I must go return the favor at next month’s conference in Seattle – Alberto/Marco, let’s make sure we have time to swap stories :)

 

Suggestions on how to get the most out of it

Here’s a few things I recommend as a survival guide for navigating the genius in these pages:

  1. Learn CALCULATE, ALL, and maybe even SUMX (in that order!) before digging into the DAX chapters.  The book introduces those functions in an order that I think could be a bit overwhelming.  Master the simple (and amazing) power of those functions before diving into the deep end.
  2. If you plan to use PowerPivot for SharePoint, get used to its limitations first – there are a number of features touched upon in the book that do not operate on PowerPivot for SharePoint (ex:  VBA, and Linked Table Refresh).  Since the book presents multiple techniques for every scenario, knowing up front what to ignore helps you focus on the techniques you will end up using a lot.
  3. Test techniques with your data volumes – at Pivotstream we routinely use datasets with tens of millions of rows.  Things that perform well with smaller data volumes sometimes break down at industrial scale (ex:  Rank measures, Many-to-many measures).  Also, the book gives a lot of advice on calculated column techniques – at Pivotstream we have found that calc columns should rarely be used, and we move them all into SQL instead.  We get better db size as well as measure performance as a result.  At high volumes, you want your fact tables as narrow as possible, and all imported, never calculated in PowerPivot.  Of course, getting SQL changes isn’t always an option, so feel free to ignore this advice :)

Summary, and Relationship to Other Books

In short, this is by far the deepest treatment of DAX and modeling that you will find under one cover.  That’s true today, and very likely will remain true until these guys write their next book.    It’s the holy grail of modeling books.

The SharePoint content is thin, only about 15 pages, and it’s clear that Marco/Alberto have focused a lot of time on the modeling experience (which is good, I am glad they didn’t waste much effort there, choosing to stick to their strengths).  For SharePoint stuff, you should invest in the Denny book.

I don’t recommend it as the place to start with PowerPivot.  This is the book you read to *master* it.  Starting out, I recommend either Bill’s book or Denny’s book, or both, given the different perspectives of each.

5 Comments | Observations, Trends, and Wild Speculation, PowerPivot Links | Tagged: | Permalink
Posted by powerpivotpro

PowerPivot Mind Meld: To Consult is to Train

October 14, 2010

 
"And then, Kirk, you will explain it to me"

“When you wake, you will know how to use the EARLIER() function.”

Last time, I trailed off and promised some heresy in this, my next post.

But I realized in the meantime that I left out some other important observations, and I don’t want those lost in the smoke if I start tossing grenades.  So, let’s get those out of the way first.

Does your org need PowerPivot Training, or do you need Consulting?

Traditionally, the role of consultant loosely translates to “I will perform the required tasks for you.”  (Or in the case of pure “talk” consultants, “I will tell you what *you* should do” – ironically, these people usually charge the most).

Trainers, by contrast, have a different role:  “I will teach you how to perform the required tasks yourself.”

To the customer, there are advantages to each, of course.  But with PowerPivot, given that it’s a self-service BI tool, training is a natural fit.

You should not equate “training” with “go sit in a classroom,” however.  In my experience spinning people up on PowerPivot, the best training involves a lot of “collaborative consulting.”

Hybrid:  Training Against Your Own Data and Biz Problems

My preferred approach:

  1. Cover the basics – there are some fundamentals that you just need to absorb, whether we are talking about PowerPivot for Excel or PowerPivot for SharePoint.  I have a few areas that I like to focus on specifically, rather than trying to cover everything.  The goal here is to get everyone on the same page for step 2, where the real learning takes place.
  2. Pick a business problem and dive in – once the basics are in place, we pick a business problem and start building a model.  Typically this means *I* am building the model, but unlike a consultant, I build it in front of you.  This is a means of explaining the principles learned in part 1, and fostering retention.  It also becomes the natural agenda:  I introduce new techniques and principles when they are needed, rather than up-front in some abstract format.
  3. Repeat on a few more problems – Pick another business problem.  Or a different approach to the first problem.  And dive in.  Repeat. 
  4. Backfill the remaining “patterns” – There are about 10-20 useful patterns that I use in my own work, over and over again.  Naturally, steps 1-3 cover a subset of those, but at some point it also makes sense to take a break and make sure the rest get covered as well.

The Changeover is the Proof

PowerPivot Changeover Invariably, at some point in this process, after we’ve covered enough ground and been through the cycle a couple of times, “it” happens:  The clients take the keyboard and mouse.  You almost don’t notice it, because it’s such a natural transition.  And at first, there is still a LOT of interaction between consultant/trainer and client, as I guide, explain, help troubleshoot, introduce new techniques as needed, etc.  
 

The movie goes on and no one notices  

But an hour or two later, I typically find myself sitting quiet for minutes at a time.  And that is how it is supposed to be!  You know your data, business rules, and needs better than anyone. 

If you (or your designated Excel pros) are not “driving” before your PowerPivot consultant/trainer leaves, something has gone wrong.

Summary of Why Hybrid is Better than “Pure” Consulting or Training
 

  1. Greater Relevance – PowerPivot offers a lot of power, for a lot of different situations.  You are not realistically going to sufficiently cover every aspect of PowerPivot in 1-3 days, nor do you need to. 

    Using your own data and problems is a natural way to focus the training on the features and techniques most relevant to your business today.  You will naturally learn more over time.

  2. Better Retention – it’s a fact.  Human beings are just much more engaged with things that directly affect them (like their own data, problems, and questions).
  3. Troubleshooting Skills and Agility – especially when you first start out, you will make mistakes.  Figuring out how to fix mistakes, or even just finding a better solution, requires constructive skills that you just don’t learn in a classroom.  

    When you have encountered even a handful of real-world problems, and participated in solving them, you are in a mindset that prepares you much better for the next unknown.  (This is very much like normal Excel or any other computer skill).

  4. The Artifacts are Templates – the natural product of hybrid training is a collection of working models that speak your business language.  These are by far the most powerful kind of templates and references for future work.  As a bonus, they also serve as “proof of value” for colleagues who come along later and ask if PowerPivot can help them.
  5. Validation of Value – A subtle but important point.  How do you know, after sitting through classroom training, how much you have really retained?  Similarly, if a consultant comes in and just builds something for you, how do you know you can extend it later?  If you train on your own data, however, and are part of the process, there is little room for doubt. 

    (Plus, I suspect that only PowerPivot pros who really know their stuff are willing to conduct a rolling, dynamic training approach like this, whereas the quality level of classroom trainers and consultants who work out of sight will vary quite a bit.)

3 Comments | Observations, Trends, and Wild Speculation | Tagged: | Permalink
Posted by powerpivotpro

More PowerPivot Consulting Observations

October 12, 2010

 
One of the Greatest Movie Ending Sequences of All Time

“I’ve…  SEEN THINGS… you wouldn’t believe.”
-Roy Batty

Do you experience “lightning bolt” moments?  Instants where something just hits you so clearly that your perspective changes permanently?

Personally, I live for those moments.  They are rare, of course, but oh so worth it.

I recently spent a couple days in San Diego, setting up a client with a self-service BI system based around PowerPivot.  I came away with several new (or newly-strengthened) observations, a couple of which were delivered in that lightning bolt manner.

Sharp People + The Right Tools = A Lot Happens in Just 3-4 Hours

In order to understand the observations, you really need to know the context of what we were doing.

The lightning bolts hit late in my second and final day of working with these folks.  We’d spent about a day and a half doing hands-on training with their data sources – I find that more effective than canned training with sample data.  Of course, to cover enough ground in a short period of time, you have to jump around a lot.

So it wasn’t until the last few hours that we settled in and focused on building a single complete model, end to end.  In those last few hours, we did ALL of the following:

  1. Sketched out the goal – a particular report/model geared at a particular business purpose.
  2. Discovered flaws in the underlying SQL sources that needed to be corrected.
  3. Grabbed the DBA, explained the problems.  He fixed them in real time and gave us a new query to use.
  4. Successfully built the report envisioned in step 1 – a report that simply DID NOT EXIST anywhere at this company beforehand.
  5. Explored the report (by now I was mostly an observer, and they were driving).
  6. Determined that the report, which they had wanted for months, was not actually the report that they needed!  (Only after seeing and playing with the data was it clear that the answers were elsewhere).
  7. Sketched out a NEW model and report based on that new wisdom, and built it
  8. Explored the report using a variety of different visualizations.
  9. Confirmed some of their long-running hypotheses, and gained some new insights.
  10. Discovered mysterious anomalies in the data, investigated them, and explained them.
  11. Identified improvements that could be made on the data COLLECTION side (in their customer-facing systems) in order to enable an entirely new class of deeper insight.

Yes, ALL of that transpired in just 3-4 hours.  Rapid Iteration indeed.  In one afternoon we did what could take a year:  2 full iterations on their core model.  Plus #11 is just crazy – feedback into the operational side of the house in order to enable v3.

My Jules Winfield “moments of clarity” follow.

“Failure” is an Inevitable Part of the BI Process… so Fail Quickly

Look back at the list above.  Steps 1-5 are a cross-section of what happens in a traditional BI project:  Sketch requirements, execute, debug, deliver.  Of course, this all happened in about 90 minutes, as opposed to a few weeks or months.  I’m starting to get used to that.

But look at step 6:  How many traditional BI projects chew up weeks/months/money, deliver 100% what you asked for, but ultimately, tell you much less than you were hoping?

To implementers and consumers of traditional BI solutions, I bet that sounds a lot more familiar than you’d like.  After all, BI is as much a human process as it is a technology process.  Here’s the crux:

Requirements definition is only informed by what you know TODAY!  And since BI projects are by definition aimed at providing information that you currently lack, BI requirements definition always involves a certain amount of guesswork – much more guesswork, in fact, than we typically acknowledge.

So BI projects sometimes dramatically under-deliver relative to expectations, and through no fault of the players involved.  Of course, that’s little consolation when tons of time and money are down the drain.  After that amount of sunk cost, it’s awfully hard to pony up the time and money for Round Two isn’t it?

But in an effective Self-Service system with PowerPivot, Round One only took 90 minutes and four people!  We weren’t exactly discouraged – in fact we were EXCITED to have learned what we really needed to build :)

Side Effect:  Do Your Traditional BI Prototypes with PowerPivot

This is obvious and people have been saying it for awhile now, but just in case:  even if you are planning a traditional BI solution, do yourself a favor and implement Round One in PowerPivot.  Flush out all of these initial insights up front, refine requirements, and only then commit large chunks of time and money to the formal project.

…Continued Later This Week

This post is already running a bit long, so I will save the rest for the next post.

Plus, the most controversial and unapologetic observation is yet to come, and deserves its own headline.

4 Comments | Observations, Trends, and Wild Speculation | Tagged: | Permalink
Posted by powerpivotpro

1,300 Measures – Nightmare or Utopia?

September 6, 2010

  
multi-straw firehose

Which problem do you prefer, when thirsty -
Too Much Bottleneck (left) or Too Little Structure (right)?

Answer:  Neither is preferred of course!

I’m part of an email distribution list that involves a bunch of BI pros, as well as a number of the PowerPivot engineers back in Redmond.  Last week on that list, I asked a question that I knew would draw some fire:

   “How many measures can I safely put on one table in PowerPivot?”

There were 2 flavors of response:  technical on one hand, and “um, is that really a good idea?” on the other.

Technical answer:  no practical limit to # of measures

The official technical answer was good news:  the number of measures defined on a table in PowerPivot should not have any real impact on performance, unless you are using all of them in a single query of course.  Measures that aren’t part of the current report won’t drag you down, in other words.

(I am told this is in contrast to the traditional Analysis Services product, which scanned all measures at once.  That prior approach is a good thing when you are using a large percentage of your measures in a single query, but a bad thing when you are using only a small percentage.)

So I immediately dug in and built a model that has nearly 1,300 measures on a single table :)

Yes, they are real measures.

Yes, the addin seems stable with that many measures in the field list, and performance does not seem to have suffered, at least not in my initial testing.

And yes, it was tedious.  More on this in a later post.

Modeling Hygiene Answer:  Why the hell would you do that Rob?

The natural reaction from the BI/DB pros was to suspect I was doing something wrong.

Now, I know myself pretty well, and the prospect of me Doing Something Wrong can best be described as Entirely Plausible. 

So I asked a few followup questions.  And it turns out, no, there isn’t any pure philosophical flaw in what I am doing, but they were still skeptical.

Diet, Caffeine Free, Cherry, Coke Classic, Zero…  Please

diet_coke_plus_nextnature

Here’s a joke that I play with my colleagues when they are leaving the room and it NEVER gets old:

“Hey, would you mind getting me a can of Diet Caffeine Free Cherry Coke Classic Zero?”

This joke is a roundabout explanation for how we end up with 1,300 measures.

Thing is, we only have 5-6 core numerical data columns in this model.  But all the permutations are what get us.  For example, we have Sales of course.  But we also have Sales in Prior Period, and Sales from Same Period Year Ago.  Don’t forget Sales Growth.  Ooh, and Sales Growth Percentage.  And, you guessed it, Sales Growth Percentage Year Ago.  You get the idea :)

coke-zero-vanilla Traditional Analysis Services has a feature named Calculated Members that helps address this problem.  But until that feature comes to PowerPivot, well, Coke is just gonna have to make all 2^11 flavors for me.

Bottleneck vs. Discipline

One member of the list in particular took an interest in what I was doing.  His name is Thomas, and he knows 100 times more about traditional Analysis Services than I do.

His chief concern was that I was using the wrong table structure in my model, like perhaps one big single table.  As it happens, I am not.  I am using something known as a Star Schema, which is something I have shown a lot of on the blog but never really described with any clarity.

I think Thomas has an excellent point.  It is easy to start out with the wrong table structures, especially if you are coming from an Excel background.  So I think it’s time someone who understands the Excel mindset AND the need for proper table structure, um, explain that.

Dick has volunteered to do a post (or series of posts) on that topic, so I will leave that to him.

My point back to Thomas was essentially this:  the need for smart measures today is almost certainly 10x what is provided, or more.  When my consulting budget ran out on the original Football Project, and my Analysis Services pro was no longer sitting in my office all day…  well, I was not getting any more new measures.

Doesn’t mean I didn’t want new measures, or need them.  I just wasn’t going to get them.  The person with the questions (me) lacked the tools to build them himself.  That is changing now.

Conclusion

I think that hundreds of measures in a model, or at least the potential to have hundreds, is a VERY good thing.  It is a sign that the bottleneck is going away.

Now, there are consequences of course.  A 1,300 measure field list isn’t a lot of fun to navigate (although the Search box in the PowerPivot addin is a savior!), and there will of course be the danger of “I just recreated measure X without realizing I already had a measure that did the same thing.”

But those are TOOLS issues.  Microsoft can build better field lists for us, with better ways to organize and view things, etc.  They will have to.

Don’t limit me arbitrarily.  Sooner or later, all 1,300 measures in this new model WILL be used.  Not all in the same report of course.  But they will. 

We could have built these measures on an as-needed basis rather than all at once, but guess what?  They would be less consistent, more prone to error, etc. – building them all at once was actually MORE disciplined :)

So, BI pros, my advice to you is to embrace the fact that the number of measures is about to grow, a LOT, in the average model.  And that is a Good Thing.

But as Thomas points out, correctly, in his latest blog post, the Excel folks can learn a TON from the BI community in terms of discipline and technique.  So let’s keep the conversation going :)

Leave a Comment » | Observations, Trends, and Wild Speculation | Tagged: | Permalink
Posted by powerpivotpro

An XL Pro’s Plea for More XL Pro-Focused Content

July 22, 2010

 
Just Don't Call Our Minds Simple, OK?

“Don’t you, forget about me.”

-Excel Pros Everywhere

From the beginning I’ve been talking about three different kinds of PowerPivot professionals:  Excel pros, SharePoint pros, and Database pros, and how they will all need to cooperate in order to get the most out of the system.

As far as I can tell, though, the majority of visitors to this site are from the Database camp (I lump Business Intelligence pros into this bucket).  Not surprising perhaps, since PowerPivot is a product of the SQL team at MS, and marketed heavily at conferences that db pros attend.

But for every db pro in an organization, there may be more than 100 Excel pros.  So, I think it’s clear that the PowerPivot message (and messaging) have a long way to go yet (it WAS just released, after all).

John Constant is one of the Excel pros who are “early to the party,” and has been sifting through all of the available PowerPivot materials for many months now, both the official MS stuff and the community offerings.

He raises some very valid points, things that I lose sight of at times.  It’s SO much simpler to grab a sample db from the SQL team and use it for blog examples for instance.  Even the Great Football Project starts with such a data source.

But I firmly believe that an organization’s success with PowerPivot rests in part on very competent training for the Excel pros…  and part of “competent” is “tailored to the Excel pro’s existing knowledge and viewpoints.”  In fact, I’d be out providing that kind of training this month if it weren’t for being very busy, the good kind of busy, applying PowerPivot for Pivotstream’s customers.

Without further preamble, I give you the words of John Constant:

PowerPivot vs the Excel Power user

Many moons ago, I can’t even remember where, I saw a comment…  Microsoft is coming out with a new business intelligence service… code named .. Gemini.  I was intrigued.  I had recently been tasked of coming up with some sort of data table /system  / process / spreadsheet / Excel voodoo to help put our business (and the competition) in perspective.  Hmm… a new tool you say.  So I delved into any links or articles I could find.  I saw the Donald Farmer video of Gemini sorting through millions of rows of video rentals – updating the charts/tables on the fly and .. sigh.. it was like love at first site.  I signed up for the beta at the first opportunity – archaic business software be damned! .  I was fortunate enough back in early fall 2009 to get my initial private beta Office 2010 release.  I was having a field day with the sparklines and the soon to be essential slicers, waiting with baited breath for the initial Gemini release.

The Microsoft talk was great.  It COULD be a standalone product.  It was meant for Excel power users and for IT and Data Administrators and Managers and so much more… but I’ve heard the talk before.  Don’t even get me started on the failings of Mappoint….   But low and behold – Gemini, with a few hiccups worked.  Sure if you linked in or brought in new data, it may corrupt all your work and you would have to start from scratch, but it’s beta.  Silly, silly beta.  It will grow up.  Sure there’s a new language that looks something like native excel formulas and there’s stuff that you take for granted working with Excel pivot data that you can’t do with Gemini… but it’s beta.. silly silly beta. It’s still growing.. be patient.  Help is on the way.  And help arrived.  There’s PowerPivotPro and PowerPivot.com  and Kasper and a host of other dedicated people who truly believe in the product.  And they do things to help build the community like having SQL workshops and handing out lovely diagrams (http://sqlcat.com/blogs/technicalnotes/image_4AFAE1C3.png) as a prize.

Errr… whoa..  hold on.  Did I mention the initial Microsoft talk?  Did I mention how Power users could use this product to help themselves and their company.  I understand the importance of SQL.  I understand the concept of Cubes; I’ve heard of OLAP (once or twice) but .. did you see that picture?!!  That’s a prize?!  Are you trying to scare away users?!   Let’s go back to Powerpivot.com, the ‘home’ of powerpivot (the grown up name of Gemini).   What’s that first video – the first public introduction of Powerpivot?  It shows a poweruser and PowerPivot for Excel.    Sure, it mentions Sharepoint but the debate continues – who is PowerPivot for? What about the power user?   – the grunts in the field who want to make the most of this product?  I know (reading some postings) there isn’t a large target population, but for some reason I feel like a Who in Whoville.. “We’re here!  We’re Here…. WE’RE HERE!!!!”  And what do I see …  honourable intentions of many esteemed PowerPivot bloggers aimed at….  SQL, OLAP, Business Intelligence users, like speaking to the already converted.

Don’t believe me?  Read your forums, your FAQ’s.  How are the samples set up?  Well you have FactInventory, FactSales, DimDate, DimThis, Dimthat – Dim Witted!   These examples and their structures aren’t what the normal excel user uses – or understands.  I’ve got Sales, I’ve got product, I’ve got territories, I’ve got stuff labeled poorly because they’ve been set up by someone who knew some programming about a decade ago… in other words, I have real word – DIM-LESS – data and structure.  It’s taken a few brick walls but I’ve hit my head enough times that the examples are sinking in but what about all those people that the ‘talk’ is supposed to reach?  Those who really have no experience with SQL or know of Cubes or data structure tables?  What about those people who can’t even consider getting Sharepoint because they are with a small business that doesn’t have the resources (financial or otherwise?).

I know why Microsoft is “selling” PowerPivot. I don’t fault them for that and I know the majority of users will have IT, Data Admin backgrounds, who will be setting up PivotViewers, and templates and services logs- all those lovely bells and whistles and the examples that will cater to those who deal with FactTables and DimData.  And then there were the Excel power users…  seeing a potentially great product aimed at the institutions, the data centres, the IT gods…   sigh…. 

I do not fault the Farmer’s the Collie’s, the Jonge’s, the Russo’s and the many many other dedicated PowerPivot supporters in the world – I thank them for all their hard work.  PowerPivot is a great product with great potential for many people, but just remember the little guy in the equation, the ones that someone deemed somewhere should have access to this power.  Bring it down a level from time to time… walk the walk and talk our talk and remember the Who’s… “we’re here!  We’re HERE… WE’RE HERE!!!”.

Powerpivot user – johncon aka Mongo41 on Twitter

18 Comments | Observations, Trends, and Wild Speculation | Tagged: | Permalink
Posted by powerpivotpro

TechEd / BI Conference Slides

June 17, 2010

I’ve received requests for the slide deck that I presented with Dave Wickert at Microsoft TechEd / BI Conference last week.

Well, here ya go, the whole slide deck right here:

PowerPivot Best Practices from TechEd

Yeah, that’s right.  One slide.

You see, it was an “Interactive” session, which is basically “we jam people in a room and they pepper us with questions for 90 minutes.”  The ground rules given to us were “1-2 slides to set the tone, and then after that it’s all audience-directed.”

Going in, I had mixed feelings about that format.  I mean, there’s a real danger that people won’t be prepared to ask questions, and that the conversation won’t get going.  In fact, in many other Interactive sessions, the speakers decided to ignore the ground rules altogether and simply present.  But it seemed like a potentially entertaining approach, and hey, Dave and I were so busy going in that this minimal preparation thing sounded pretty damn good.  So we rolled the dice.

Well, it turned out EXTREMELY well.  We started 15 minutes early, ran over the end time, and were answering fantastic, high quality questions non stop.  It was awesome.

The bad news is, I don’t have a good way to share that discussion after the fact.  We basically would need to do it again, and sadly, they did not record the session.

So for now, all I can really do is offer the teaser above.

Leave a Comment » | Observations, Trends, and Wild Speculation | Tagged: | Permalink
Posted by powerpivotpro

PowerPivot observations from TechEd/BI Conf, Pt 1

June 11, 2010

 
Kasper and Rob in The Big Easy

 

“Ouch”

-Rob’s feet, knees, brain, and liver

Hi folks.  On my way back from the MS BI Conference (and TechEd) in New Orleans.  Had a great time, almost too much to report.  Here’s a sample:

  1. Met Kasper and Denny for the first time!
  2. Met a bunch of other PowerPivot community members like Vidas Matelis, Marco Russo, Stacia Misner, and Andrew Brust…  a bunch of SQL celebrities, like BrentO, SQLRockstar, and BuckWoody…  and a fascinating individual named Jimmy who Kasper and I hope to introduce to all of you soon.
  3. Presented a session on Best Practices for PowerPivot (with Dave Wickert) where they literally turned away as many people as they let in – we were all seated, room packed, people standing in the back, and doors closed 15 minutes before scheduled start…  so we just went ahead and started 15 minutes early.  Never had an experience like that, ever.  Even Denny Lee was locked out.
  4. Had several private meetings with members of the PowerPivot team, learned a bunch of things (some of which I can share, some I can’t, but everything bodes well for us)
  5. Talked with dozens of attendees, got their thoughts on PowerPivot, answered questions on PowerPivot, and staffed Microsoft’s PowerPivot booth on three different days.

So, from all of that, what did I learn?  Here ya go, as compressed as I can get it:

IT attitudes toward Excel are shifting dramatically

Excel Rebellion Circa 1977

“The more you tighten your grip against Excel, the more data will slip through your fingers.”

6-7 years ago when I attended BI conferences, Excel was widely regarded as the enemy, and there were even sessions titled things like “how to get your users out of Excel.”  The prevailing sentiment was that a responsible BI practitioner had a duty to replace Excel with dedicated BI tools like Cognos or Business Objects.

There has been nothing short of a seismic shift since then.  I’d like to think that has something to do with our efforts in Excel 2007 to legitimize Excel as a BI tool, but honestly, I think it’s just that the realization has sunk in…  no one is ever getting rid of Excel.  Furthermore I think it’s more than just capitulation – at this point most people realize that getting rid of Excel would be a bad idea even if it were possible.

Some favorite quotes from IT/BI pros:

“Fact is I get most of my best analytics ideas from my Excel users’ workbooks”
”We’ve been decriminalizing the use of ad hoc Excel”
”Data just wants to be free, data will find a way”
”Rob I am really impressed with your jumpshot, you could play near the highest levels in Holland were you in shape.”

(OK that last one was from Kasper – I felt obligated to include that since he kinda beat me in four out of five games and I need to save face.)

I very much think it’s time to revisit a few of my favorites posts on this topic, so if you have started reading recently, I encourage you to check out:

Microsoft Unveils New Programming Language XL#
Putting the “Intelligence” in “Business Intelligence,” Part 1
Putting the “Intelligence” in “Business Intelligence,” Pt 2
Putting the “Intelligence” in “Business Intelligence,” Pt 3

PowerPivot is arriving at precisely the right moment.  BI and IT pros are embracing it EXTREMELY eagerly.  Much more than we dared hope back when I worked on the engineering team in Redmond, and more than they realize even now I believe.  They are prepared to cooperate with their Excel users in order to bring about a more efficient data culture.  Which brings me to the next point…

PowerPivot does NOT need viral adoption!

novirus_sticker-p217678350895040539qjcl_400 Are you listening, PowerPivot team?  I’m going to keep standing up on every soapbox and hilltop I can find, screaming as loud as I can, that PowerPivot does NOT require grass roots adoption in order to be broadly adopted at a record pace!  That perceived need for bottom-up adoption lingers within the team from the early days, and it will be a shame, both for Microsoft and for the rest of us, if they continue to think that way.

Why?  For one, they will build the wrong feature set going forward if viral adoption is still a goal.  Originally for example, data cleaning features were intended to be included in v1.  The thinking here was that the Excel users could not remotely rely on IT to help them, or to provide them the right kinds of data, or even the right kinds of access.  That simply is NOT turning out to be true.

And if the team spends a ton of time building features that aren’t needed, it will subtract from the quality of the other features we get, and/or replace other features altogether.

Second, the marketing message is noisy today.  This dual “top-down through IT and bottom up through Excel user empowerment” message is not being well absorbed by the MS field.  By far, the Excel component is easier for the MS field to understand and repeat.  So it gets a lot more air time than the top-down message.  And as a result, an IT team that otherwise would have been enthusiastically receptive to the first message only get exposed to the “Excel gone wild” message and recoil from it.

This truly is a case of subtraction by addition and should be avoided :)

PowerPivot was the Buzz of the BI Conference

Even Wears the Right Color I expected PowerPivot to grab an outsized share of attention at the conference, but multiple people pointed out to me that it basically took over.  People from other companies stopped by at the PowerPivot booth to find out was going on, because everyone coming to their booth was asking things like “how does this integrate with PowerPivot?”

And the Microsoft messaging mirrored that.  PowerPivot dominated the BI keynote on Tuesday – we were all kinda surprised to basically only see PowerPivot demos for 90 minutes (seriously, the other MS BI teams had to be a little irritated by that.  You can watch the keynote here and see what I mean, and maybe see us smiling ear to ear in the first row).  It dominated the overall session count.  It showed up in sessions that weren’t about PowerPivot.

Kasper looked at me at one point and said “I really like the names of our websites.”  To which I replied, “I really like what we’ve been studying and practicing for the last nine months.”  OK for me it’s been several years.  Take THAT Kasper! :)

Kasper’s stuck on a plane for the next 24 hours and can’t fight back.  Muhaha.  OK, final observation for part one…

PowerPivot is evolving into a “Gateway Drug” in SQL11

That’s right, PowerPivot leads to stronger tools.  If you watched the keynote demos above, you saw something that’s pretty exciting once it sets in.  Remember that the Analysis Services product has existed for over ten years as a toolset that people like me weren’t able to use.  You could learn the query language MDX, but when I discovered that even a simple IF() statement required a PhD, I decided to do other things with my time.

PowerPivot, by contrast, is the kind of canvas on which I can paint.  Keep in mind, however, that PowerPivot is really TWO things:  1) It’s a toolset where someone like me can build models, applications, and reports   and 2) It’s the Vertipaq engine, the thing that makes monstrous compression and performance possible.

In theory, I guess, the PowerPivot toolset – the Excel addin, the DAX language, the integration with SharePoint – was possible without the Vertipaq engine.  Probably would have been too slow and unwieldy to gain much traction, but possible

The real question though was what Microsoft was going to do about the traditional Analysis Services product.  The existing AS product did NOT get equipped with Vertipaq in the 2008 R2 release, so in many ways the BI pros have been quite jealous of us :)   It was obvious that Vertipaq was going to find its way into AS proper in a future release, but it was not clear what the resulting product would look like.

OK, here ya go:  It’s gonna look like PowerPivot.  With an even larger data capacity.  And lots of features that we don’t currently have.  With no SharePoint dependency and no requirement that you embed the models in Excel files. 

And ok, to make it look industrial strength, they’re gonna put it in Visual Studio.  But that’s a minor detail to me, more cosmetic than anything.  I’m told it will support DAX, and generally use all the same sorts of Excel-user-friendly UI gestures that we are used to, as if the Excel addin were dropped into the Visual Studio frame.  No requirement that we change to MDX and other concepts in order to “upsize” into the more powerful future version.

Best news of the conference.

6 Comments | Observations, Trends, and Wild Speculation | Tagged: | Permalink
Posted by powerpivotpro

The PowerPivot job posting I mentioned earlier

June 1, 2010

Posted by Rob

Here it is:

image

That was posted nearly two months before PowerPivot was released :)

It was available last week but as of today the listing appears to have been taken down, filled.

Which one of you fine folks grabbed it? :)

2 Comments | Observations, Trends, and Wild Speculation | Tagged: | Permalink
Posted by powerpivotpro

Up for air… briefly

May 27, 2010

 
Mini-Posters-Elvis---Vegas-73722

“A little less blogger nation, a little more action please.”
-The King

Folks, I’ve been up to my eyeballs in PowerPivot these days.  Which ironically has resulted in less writing about PowerPivot.

Quite simply, the release of PowerPivot v1 to MSDN was like a huge starting gun going off.  Suddenly, all that theory became “ok, let’s put it into practice…  on 10 different fronts.”

I’ve been on so many exciting phone calls and web meetings lately.  Lots of very cool customer and partner opportunities.

Oh, and tons of “real” work, too.  Which has left less time for blogging.

But I do love the nifty community we’ve got going here and will never neglect it for long stretches.  So, while I burn the candle at all three ends, I thought I’d share some quick observations and experiences, rapid-fire style:

  1. Microblogging has its place – when I don’t have time for a full blog post, I tend to drop something into my Twitter account.  If you’re not a Twitter person, you can see what I mean here.  And if you are a “tweeter",” well, you can click there, too :)
  2. I fit 200 Million rows, a 63 GB SQL database, into a 1.3 GB PowerPivot Workbook.  Yeah, no kidding.  50x compression.  And some of the queries against the resulting workbook ran in less than 3 seconds.
  3. Another db with 120 Million rows exceeded the 2 GB file size limit – different data set.  See?  There really is no set rule of thumb for how much compression to expect.
  4. Removing irrelevant columns can dramatically shrink your file – this is well documented elsewhere, but removing columns from your data before importing them can have a HUGE impact on file size.  It can be surprisingly non-linear, too – remove one column out of ten and file shrinks by 50%.
  5. Seems like deleting columns after the fact does NOT shrink the file by as much – I have not verified this scientifically.  But I *seem* to get better file size savings by never importing columns in the first place, as opposed to deleting them post-import.
  6. Early response to PivotStream’s new offering on the PowerPivot platform has been VERY positive – yesterday we demo’d a new set of interactive dashboards, built on PowerPivot, to an existing customer who currently is served by one of our other technologies.  It got a big thumbs up, we are moving ahead with a full conversion.  And another customer is likely just a few days away from making a switch from their existing on-premise solution, too.
  7. In general, MS might not need viral adoption – when we were building the product we often talked about how bottom-up adoption by Excel users was crucial, that top-down adoption would be slow.  So far, I am seeing zero indication of that.  Everywhere I go, everywhere I demo PowerPivot, the organization I’m talking to does a collective “yes, please.”  Maybe MS could have charged for the client addin after all.  Shhh, don’t tell them.
  8. I’m seeing more willingness than in the past to embrace the new Office wave – normally, the new wave of Office products takes years to seep into corporations.  That will still be true in a lot of places.  But about 2/3 of the places I go, I find eager willingness to roll out the new products, even if only on a limited, departmental basis, in order to get PowerPivot.
  9. Any serious work on PowerPivot measures requires Notepad++ – seriously, I don’t know how I ever got by without it.
  10. Running a PowerPivot server farm is hard work – I have newfound respect for IT pros everywhere.  Don’t go it alone, folks.  We’ve brought some heavyweight talents on board to help out with SharePoint, farm architecture, and Integration Services.  I can’t imagine where we’d be without them.

-rob out

5 Comments | Observations, Trends, and Wild Speculation, Quick Tips & Tricks | Tagged: | Permalink
Posted by powerpivotpro

PowerPivot Interest By Industry

May 4, 2010

For the past few months, I’ve been tracking visitors to this site, as well as to the FAQ site, and periodically going thru and tagging them by industry.

I thought maybe everyone else would be interested, so here ya go, the top 15 industries:

PowerPivot Visitors by Industry Chart

Note #1:  this just reflects people I can recognize – for most visitors, I only see their ISP name.  And anyone who reads strictly over RSS or Email Subscription, I don’t see them either.  But it’s still a reasonable comparison of interest across industries.

Note #2:  this just reflects visitors from the U.S.  Given the amount of manual tagging work involved, I decided not to track the international traffic, which overall outweighs the U.S. by quite a bit.

Any surprises?  For me, here are a few:

  1. Healthcare – I had no idea that they were quite this embracing of cutting edge analysis/reporting technology
  2. Financial Services – yes, this makes total sense.  But given my perception that most visitors so far have been from the Business Intelligence crowd and not the Excel pro crowd, well, this makes me second guess that assumption.
  3. Government – wow, you wouldn’t believe the names I see.  From City/County level all the way up thru Federal.

5 Comments | Observations, Trends, and Wild Speculation | Tagged: | Permalink
Posted by powerpivotpro

« Previous Entries
Next Entries »