“I Know SQL Queries, So Why Do I Need Power Pivot?”

September 30, 2014

Post by Rob Collie

because I am very familiar with databases and the ability to create custom SQL queries and data models using SQL Management Studio,  I struggle to see why I would need PowerPivot if I can do much of this heavy lifting using SMS.

Got This Question the Other Day, and it is LONG Overdue That I Answer It

Some of You Are Confused, Some Are Nodding

Generally speaking, I think the people reading this fall into one of a few camps:

  1. People who are early in their Power Pivot journeys, and who also do NOT know SQL (most Excel Pros fall into this camp at some point, before hopefully moving into group #2 below).
  2. People who are pretty good at Power Pivot, but do NOT know SQL (I fall into this group).
  3. People who are good at BOTH Power Pivot and SQL (this is a blessed group).
  4. People who are good at SQL but still early in their Power Pivot awareness/knowledge.

Group #4 is the “target audience” for today’s post, but it’s still relevant for groups 1-3, because we WILL get asked this same question from time to time, and it’s good for us to be able to answer.

“I Started Out as a DBA…”

SQL Queries as BI - More Common Than Really Any Other Method of "Official" BI

For Many People and Organizations, THIS is Business Intelligence
(And to a Certain Extent, This is Effective, So it Persists as a Workflow)

Let’s say you began life a a DBA.  Which means you know SQL, of course, but writing SQL is not the only thing you do as a DBA.  You’re maintaining indexes, watching for bottlenecks, talking about I/O, number of spindles, TempDB…  all that good DBA stuff that I understand at a conceptual level but have never learned to actually DO.

But one day, someone from the Business has a question.  They figure all the data required to answer it is “owned” by you, so they come to you with said question. 

And hey, it turns out that you CAN write some SQL and answer the question!  Which is pretty damn helpful and makes everyone involved feel pretty good.  (Hey, we are all still fundamentally wired for cooperation after all).  It also makes you more relevant to the front-line business, and no longer “just a cost center” from the perspective of the company’s leaders, which is VERY good for your career.

 

Read the rest of this entry »


Preview of New Website Content

September 23, 2014

Post by Rob Collie

image

The Three Primary Benefits of Power Pivot vs. “Traditional” Excel
(Click for Full-Size Version, and Please Share Your Thoughts in Comments!)

Changes Afoot

Some exciting things in the pipeline here at PowerPivotPro HQ.  Most of those will remain secret until next month, but one of them is a website overhaul – with Power Pivot adoption continuing to grow, website traffic also continuing to grow (more records fell last week!), and the site’s 5-year anniversary approaching, it’s time.

So today I’d like to get your feedback on some of the new content.

It’s all pictures, so I promise it’s light Smile

Read the rest of this entry »


Tuesday Grab Bag of Biz and Fun

September 16, 2014

Post by Rob Collie
 
mullet with labels

Today’s Post is Like a Mullet Haircut:  Business in the Front, Party in the Back

Let’s Go!

Series of quick updates today, mixture of pure fun and serious biz, sometimes simultaneously.  In this post:

  1. Download the Draft Index for the Online University – whether you are currently enrolled or not, this may be relevant.  Grab it here.
  2. Live Class in Philly – seats still open for Oct 21-22, more details here.
  3. “Secret Summit” Between Microsoft and Power Pivot early adopters last week.
  4. DAX:  The Power of Paper
  5. Yours Truly taken into police custody for Possession of Industrial-Grade Data Tools

 

Read the rest of this entry »


Has Power Pivot Changed Your Life? I Want to Know!

August 19, 2014

This face obviously represents happy.

We Want to Hear Your Cool Stories!

Unprompted Confirmation

In last week’s post I explained why I now “kick off” my training sessions with the bold promise of “Power Pivot will change not only your career, but also your LIFE.”  (Please go back and read that, briefly, if you have not – because this post won’t make as much sense otherwise).

Well, a funny thing has been happening lately – I’ve been getting emails from people saying that it HAS changed their life.  Literally in those words.

Are these people just being nice to me, confirming my bold statement?

Nope, because none of them has heard me say it before – these are people I have never met in person!

In fact, this pattern of emails was the reason why I got started writing last week’s post.  I actually intended to JUST write THIS post, the “has it changed your life” post, but along the way I realized I needed to add the background, so this got split into part two.

Please Share Your Stories!

If you are feeling like this, please leave a commentIf you’ve experienced life change as a result of Power Pivot, please leave a comment at the end of this post!

Or if you’re a bit shy about that, drop me an email.  My address isn’t hard to find or guess.

Take it Away, Rob’s Inbox!

imageThese are real emails from real readers, slightly edited to maintain their privacy.  In all cases, I am bolding the “life changing” portion of the quote:

Read the rest of this entry »


Power Pivot: a revolution in slow motion

August 14, 2014

By Avichal Singh

The first time you truly experience Power Pivot – not a demo, not some random public data set or someone else’s data – your own business data all lit up using Power Pivot. It is a transformative experience. And you know there is no turning back.

I remember that moment, I remember thinking this is HUGE, this gets BI in the hands of people who really need it. Power Pivot is surely is going to explode.

Sadly, it does not feel like that is happening :-(

As I have become more involved with the Modern Excel User Group (also on LinkedIn), I have realized that there are way too many people in buckets a) and b) below and not nearly enough in c).

a) Sad smile Unaware. Have not heard of Power Pivot or Power BI
b) Disappointed smile Aware but have not truly experienced the capabilities of Power Pivot
c) Red heart In Love with Power Pivot

Note: Are there stages between b) and c)? Between experiencing Power Pivot and falling in love? Perhaps. But guided by the right hands that path should be really short :-)

Unaware: Don’t know what they are missing

For the unaware group, I do my part; talking to anyone who would lend me an ear, about Power Pivot and Power BI at any event or gathering. But overall we would need to trust the higher powers :-) within Microsoft with that task. However I will make the offerings below to the powers that be:

- Power Pivot should be a free add-in for all versions of Excel 2013, just the way it is for Excel 2010. I feel it even makes good business sense for Microsoft. Power Pivot has the potential to attract a large user base and would solidly anchor Office and Office 365 in the rapidly changing world. These users can then be up sold services like Power BI.

- Power Pivot add-in should be easily discoverable. Both Power View and Power Map have buttons as part of the standard Excel ribbon, which can be used to easily activate these tools. Not so for Power Pivot.
It takes 8 mouse clicks to activate the add-in so you can launch Power Pivot.
File > Options > Add-Ins > Click Dropdown > COM Add-Ins > Go > Select ‘Microsoft Office Power Pivot for Excel 2013’ > Ok
As the first born in the Power BI suite, it is time for Power Pivot to claim it’s rightful place on the standard Excel ribbon and it would fit perfectly under the Data tab.

Currently in Excel 2013 Desired in Excel 2013
Currently in Excel 2013: Powe Map, Power View Desired in Excel 2013: Power Pivot or Data Model

Note: There was a shift in Excel 2013 to weave in Power Pivot so closely with the tool (and call it Data Model) that potentially many users would not even need to learn or use the Power Pivot window. IMHO, that is just keeping people away from all the goodness and richness of Power Pivot. Let them come, let them explore and experience.

Aware but not in Love: Mind the Gap!

While I may feel helpless in making a dent in the Unaware group, I feel impassioned and even obligated to help the Aware but inexperienced group.

I have spoken to many Excel users and noticed that there is a gap between knowing about Power Pivot and actually trying it out yourself to experience its true power. There is a step there that many users simply cannot take. Being on the other side, it baffles me a bit. I feel like yelling “Hey, come on over, the grass IS greener on the other side!”

From Excel to Power Pivot: Mind the gap!

Read the rest of this entry »


Vending Machine or Kitchen: Two Kinds of Self-Service

August 12, 2014

Preface:  Breaking the Consulting “Rulebook”

Power Pivot Allows Us to Set a Much Higher Bar - and Then Clear It

This may seem bold:  Starting about six months ago, whenever I find myself in a room with spreadsheet/business/database people, one of the first things I tell them is that Power Pivot won’t just change their work, it will change their LIVES.  That’s right – I tell them, with a straight face, that this business technology will actually make them happier.

That violates one of the unwritten rules of consulting, which is to underpromise and overdeliver – set expectations low enough that no one ever has reason to complain or be disappointed.

I understand why it’s traditionally a good practice to “set the bar low.”  I get it.  I truly do.

“Consumer with Choices” vs. “Empowered Producer” is a BIG Difference

But Power Pivot is different.  Yes, in the truly breakthrough, transformational sense – you knew I was going to say that.  But different in another critical sense as well – it requires (and incents!) the analyst types on the Business side to participate to a degree unlike with any other BI tool.

No matter how they are marketed, every other BI tool I have ever seen treats the business user as a glorified Consumer.  Yes, Business Objects and Cognos for sure, but I’m also looking at you, Tableau and Qlikview and Spotfire.

Read the rest of this entry »


The Simple Art of Being Prepared

July 15, 2014

Saluting a Mentor

image

Heikki Kanerva, Right.
(At left is Juha Niemisto, a Programmer from the Excel Team and Mutual Friend)

Taking a bit of a break today from “technical” posts and doing that whole “professional observations slash storytelling” thing.

My first real mentor at Microsoft was a guy named Heikki Kanerva.  I learned many, many things from that man, but the way in which he cared about his people, and PROTECTED his people, will always eclipse even those things.  I wouldn’t have had the time or the space to learn had I not lived in the Heikki-created “safe zone” for those crucial early years of 1997-1998.

And the charisma.  The leadership.  You know those fantasy stories about barbarian clans in the frozen wastelands of the North, where the clans have perpetually been at war with each other, but now a New and Evil Threat looms from the South, and the clans must set aside their pride and biases and band together, or face certain extermination?

In those stories, there’s always a charismatic leader who emerges to unite said clans, the Strong Leader who possesses brains, brawn, and charisma in just the right proportions.

And Heikki was pretty much the real-life embodiment of that character, in every way.  He played that role every day for the entire Office organization, and I have never seen anything like his powers since.

A Mentor’s Parting Lesson

Years later, when I no longer worked for Heikki, he passed away suddenly under tragic circumstances – a story that I may someday tell here, but not the focus for today.

But leave it to Heikki to still manage to teach AFTER departing this world.

Read the rest of this entry »


We Have a “Crush” on Verblike Reports

June 24, 2014

image

Even Very “Sophisticated” Reports/Dashboards are Often “Couch Potatoes” in Practice
(They Sit There and Expect You to Do All the Work)

Intro From Rob

I’ve been meaning to blog this for a long time – it is, after all, one of my absolute FAVORITE things to talk about.  Now, Scott beats me to it.  But he does such a good job below that I don’t have much to add, except a few graphics here and there, like the one above.

Besides, how can I not love a blog post that starts off talking about how right I am? Smile

Guest Post by Scott at Tiny Lizard

By nature, I am a rather skeptical person.  When I first hear an idea, I generally think it is probably wrong.  This isn’t one of my finer traits and  I am sure it drives Rob insane, since he has a rather impressive track record of being correct.  Thankfully, this blog entry is not about one of the times I assumed Rob was wrong…

It is actually the complete opposite.  It is one of those times when Rob told me something and it just instantly SUPER resonated with me and got stuck in me.   I suspect it will do the same for most of you.

Nouns and Verbs

imageIf you were fortunate enough to hear Rob speak recently at PASS Business Analytics Conference, you have already heard this idea.

It is super typical for us, as report authors, to generate The Report.  The Noun.  We hand it off with pride.  And The Report has all the information somebody could possibly need to make a decision.  All of it.   Row after Row. Column after Column.  Unfortunately, even with the fanciest conditional formatting in the world, it is not clear… how is somebody supposed to look at The Report and actually… do something?

Because, at the end of the day (and yes, with a handful of exceptions like keeping the SEC happy), it is our hope that somebody looks at our reports and uses it to make a decision.  To do something.  To Verb.

Read the rest of this entry »


Why Steven Levitt is Too Expensive

June 12, 2014

 
Me with personal hero Steven Levitt at last year's PASS BA conference.

“And therefore Steve, this clearly indicates that you are entirely too expensive.”

Picking on a Hero?  Sacrilege!

I had a particularly energizing “remote assist” with a client today (on GoToMeeting).  Just fantastic.  I was smiling for 90 straight minutes at the pure awesome that the two of us were conjuring out of the data.

The lightning bolt realization that struck me, shortly after that meeting, was that a hero of mine might be in trouble.  (Well, only a little bit, but still – a little bit!)

Rumor is that Freakonomics author Steven Levitt charges something in the neighborhood of $30k to show up and give a 1-hour talk.  He’s the best keynote speaker I have EVER seen/heard, hands down, so I think he’s worth that, and I do NOT think he’s in trouble there.

I don’t think his book business is in trouble, either.  Again, fantastic books.  Love ‘em.  Untouchable.

But his consulting business…  hmmm…  yeah, I think Power Pivot professionals may have something to say about that heh heh.

Today’s (Redacted) Example – Cannibalization?

Read the rest of this entry »


Simple, Amazing, and Happy: A Story About Data

May 29, 2014

Last week’s two-part series on The Three Big Lies and The World Doesn’t Know Its Own Numbers got me thinking about how all of this is connected.  It’s all one big story, and I need to stitch it together.

I actually told a big part of that story in one of my sessions at the PASS BA CON earlier this month, and people seemed to genuinely both enjoy and benefit from it.  Because of that (and because I spent over 100 hours prepping that talk!), I’ve been meaning to re-record that talk for YouTube.

So I thought, hey, let’s cross the streams.  I’ll make a series of short videos that tell the entire story – including, but not limited to, what I shared at PASS.

Today I present the Introduction to the story.  Anyone who’s used Power Pivot for awhile will “recognize” the transformation I’m hinting at in this Intro, but if you’re just starting down the road, I hope you find it exciting.  Because you have some amazing discoveries just around the corner.

Please tell me what you think of this video – either here or on YouTube – because I’ll factor that into the series.  The more I know, the better I can tell the story.

(Note that we will be switching PowerPivotPro.com over to a new server this weekend, so comments might be disabled here starting Friday night).


The World Doesn’t Know Its Own Numbers

May 22, 2014

 
Picking up from Tuesday’s post about the Three Big Lies in Data

I give you…  the World’s Most Honest Scorecard!  

image

In Moments of Honest Reflection, Most Organizations Would Acknowledge that
this Describes their Relationship with Data

The Most Important Numbers Generally Don’t Exist

image

The two graphics above kinda “steal the thunder” of this point, but I’ll elaborate anyway.  One of the grandest surprises for me, after leaving Microsoft, is just how blind the world is to its own data.

Read the rest of this entry »


The 3 Big Lies of Data. (And Power Pivot vs. Power View, Power Query, Q&A, etc.)

May 20, 2014

 
How Power Pivot, Power View, and the Other Power BI Tools Relate to Each Other

Power Pivot is the Engine that Turns Data Into Information!
But We Can’t Understand This Properly Without Examining the Three Big Lies of Data

Goal:  Answer Four Frequently-Asked Questions

So many things to say this week. Let’s jump in.  Here are the questions I ultimately aim to answer, which are questions I get basically everywhere I go:

  1. How do all of the Power BI Components relate to each other?  Power Pivot, Power Query, Power View, Power Map, Q and A, etc. = Power Confusion for some folks.  I get it.
  2. Has Power Pivot become less important, now that we have all of these other new “Power *” tools?
  3. Which tool should I learn first in the Power BI family?
  4. Should I consider abandoning this stuff altogether in favor of <hot new technology X>?  Tableau, Hadoop, R, etc.

In order to answer these, first we must confront some insidious lies that we are told every day.

Examining:  The Three Big Lies of Data

We want data tools vendors to lie to usThe world of data, today, is clouded by Three Big Lies.  These lies originate with all of the tools vendors – Oracle, IBM, Tableau, etc., and yes, Microsoft too is very much playing along.

Even though the Vendors are the Purveyors of these lies, they are NOT “at fault” for them.  Because the world actually WANTS to be told these lies.  BADLY wants to be told them, in fact.  And because the audience is so receptive to these lies, the vendors naturally learn to tell them, and tell them well. 

Vendors who DON’T learn to tell these lies?  Well, those vendors don’t win many customers.  And then those vendors disappear. 

So while the lies COME from the vendors, the PROBLEM, really, is with US – the people who BUY the tools.

Read the rest of this entry »