image

Intro from Rob:  I may be hopped up on painkillers at the moment, but when a story needs to be told as badly as this one does, I can still spot it.  This is precisely the kind of transformation I see as the future for determined Excel Pros.  Let’s call it The Journey.  And it’s always good to hear from folks who are already on that journey, and proving that it’s a reality.  So I’m gonna get out of the way – mostly! – and let Geoff tell us his tale.

Hi there folks. I’m Geoff McNeely (@geoster on Twitter, or find me on LinkedIn) and I’ve guzzled the Kool-Aid. I’ve been using PowerPivot for several months now, I have Rob’s book, and I gave my first peer presentation on PowerPivot in May. Go ahead and call me an evangelist. I’m a believer!

So when Rob gave me the opportunity to write a guest post it was like one of those moments…you know, like will I go crawl under a rock and forget I ever aspired to write anything, or will I engage and make it happen?

I’m gonna go with “Engage, Maverick!”

clip_image002_thumb

I also did a quick search and it seems that Rob hasn’t used any Top Gun references on his site yet (note from Rob:  a damn shame indeed and I am glad you are here to correct that oversight!). So I’m gonna be the first one. Yee Haw! (By the way, my twitter handle, @geoster, is derived from Top Gun…in 1989 I was called Jester, but with a G, so it was spelled geoster – you know, like Jeff/Geoff? – so now you know how to say that *not Gee-oh-ster*)

[youtube http://www.youtube.com/watch?v=BmP9b7McyAk&hd=1]
Yee Haw! geoster’s post!

First, About Little Ol’ Me

I was just another business analyst until I began using PowerPivot to make my boss look good. Now I’m a bona fide PowerPivot evangelist and am known as a BI guy. Ah, it’s nice when our tools allow us to evolve ourselves.

In fact, I “found out” I was “BI” while interviewing for my current job. I had mentioned how I enjoyed “geeking out on data visualizations” and my boss called in his BI guy for an impromptu interview and the rest, as they say, is history.  (I’m sure glad PowerPivot was there waiting for me on the other side of that interview, but I’ll get to that in a moment.)

I have a degree in anthropology.  Yes, anthropology, a degree that just screams “data analyst.”  But it’s very helpful, I promise!  I’ve been trained to study people and their interactions, so I’m good at stakeholder engagement and requirements gathering. I’ve also spent the better part of the last 20 years working in sales and marketing operations roles. CRMs, Customer Support, Lead Routing, Conversion Tracking, Landing Pages, spreadsheets, apps, etc. Remember that kid who takes stuff apart to see how it works? That was me. I’m curious and I want to know how things work. (Though I may toss it as soon as I grok it…)

I Love Understanding Tools.  I Hate Using Them.  But with PowerPivot, the Two are One and The Same!

I’ve also spent a lot of time in the Mobile and Software Development spaces, and I’ve attempted a couple start ups – not the flaming crash and burn ones, more the quietly limp into a corner, curl up and die type – to learn that I’m not the best on executing an idea or a game plan, but I sure as hell know how to build support systems to manage processes. So yeah, that makes me a process guy more than anything.

I used to think that the tools I used meant I was “that type” of headcount. I’ve failed miserably in structured roles ranging from system admin to database manager, channel manager, project manager, and more.

You see, I LOVE to know how the tools work, but I HATE using them. Ironic? Maybe a little bit. But it was PowerPivot that really brought this home to me.

Here is a tool (PowerPivot) where KNOWING it IS USING it. There is no difference. Which means that for me I could actually USE it (without a great deal of self loathing). And using it to model complex relationships between multiple data sources (aka a Data Model) ends up being an end in itself in my current job.

What’s fascinating is that I cut my teeth way back in the day on Access 2.0 (the only MCP cert I ever held) and it’s the relational aspects of Access that I see in the PowerPivot data model that has me so jazzed. In my job I do a lot of modeling and scenarios based on our underlying data warehouses. In order to get IT to help, I have to know what I want. And I don’t even know the data models for our tons of data sources. How do I give IT a detailed and accurate request?  Play with it in PowerPivot first.  (Note from Rob:  this is a crucial point!  PowerPivot enables dramatically more effective communication, and many times completely removes the need for it, and that is magical.  See here for more on this theme.)

Playing With PowerPivot

I’m doing some work for a very large software firm in the Seattle area. Winking smileI manage a tool that routes leads from inbound web forms and events. Our tool only tracks leads while inside our system. We wanted to know “what happened” to those leads after they were handed off to sales. So I built a PowerPivot Dashboard that lets me connect to four different data sources, link them, and then peek into “what happened” after we sent them over to sales.

LeadSystem

Leads flow in from various campaign sources and our system (system 1) routes them to multiple different CRM systems (systems 2, 3, and 4) for sales follow up. Once handed off, our system is “blind and dumb” as far as what happens to the leads. For this I need to look into the data stores for each source in order to see any updates or changes from the field.

To do this I write queries that pull the data I want only and I don’t have to look through multiple columns trying to figure out what I need. Yes, I do spend 50% of my days inside of SQL Server Management Studio, so I am savvy with writing complex queries.  But when you have data on more than one server, PowerPivot is a life saver.

For me, I have three distinct SQL Server databases I connect to. The fourth system is a shared instance of Dynamics CRM so I don’t have access to the SQL Server database. For this, I export the data to Excel and massage that raw data file, ultimately linking it in via importing an Excel Spreadsheet as a data source. Then I make sure all the keys are linked in the relationship view.

Related

Source 1 is my system, including the related Activity and Opportunity links for Source 2. Source 2 is a CRM that is used by our Direct Sales teams. Source 3 is used by our SMB teams. Source 4 is used by Partners. Oh, and DateStream. Gotta love time hierarchies.

The goal from this effort is to have an “all-up” view of our lead management systems, so I’m using a ton of Calculated Fields to extract all the summaries you would normally get by inserting a pivot table (see here for more on why explicit measures rock). This means that when I’m done I can use our wonderful CUBEVALUE() and CUBEMEMBER() functions to place my dashboard data anywhere in a page, and with ANY formatting I want (see below for how this dashboard can be made to look like a Windows 8 App).

dashboard

Yes, this is an excel sheet! Think the stakeholder will be excited about getting this into Windows 8 too? Use your delivery to set up additional work. Also, PowerPivot = Prototype = Working Specs for what you can charge to build Winking smile

So now I have a great operational reporting dashboard that tells me where my leads came from, where they went, and what happened to them after they got there. And this is only scratching the surface! Like I said, I’m a convert. This was my first amateurish effort to create something using PowerPivot. The first version was a series of tabs that each had a pivot table from the data model that each fed upstream to the next level of reporting (aka relying on implicit measures). The second version converted the same data to explicit measures, and the current (we should never say ‘final’, right?) version makes the presentation of the explicit measures pretty. What else is possible? Only my imagination and my understanding of our organizations data stand in the way!

Until Next Time

In the future I hope to post about file size optimization, linking CRM data with SQL and DateStream, and trimming date fields with SQL for file size and slicer click speed.

I’ll also share my thoughts on using PowerPivot to prototype for UX and usability and for MVP development in Lean Startups as well as exploring the value of anthropology (culture, observation, fieldwork, people skills, etc.) for Excel BI Pros. I hope that you find these topics helpful/useful. Any interest expressed in the comments will certainly expedite my attention to said topic. Winking smile

clip_image004_thumb

Ice Man: You can be my wingman any time.
Maverick: No, you can be mine.

geoster: Thanks, Rob, for letting me sit in the cockpit for a minute. It was fun, and now there’s no turning back! Cheers!

This Post Has 32 Comments

  1. Excellent post, Geoster!
    An antropologist in the PPivot Comunity is a luxe!
    Do you think the “Copy&paste + VLookUp” Excel analysts are the Australopitecus of Excel users and the PowerPivot analysts, the Homo Sapiens? Pivot tables may be the fire? 😉
    This could be a good argument to develop for PPivot’s evangelization.
    Regards from Argentina,
    Paula

  2. Hi Paula, Thank you!

    That’s an interesting perspective. The evolution of the Excel Analyst. We’re coming out of the VLookup Age.

    Cheers!

  3. hi Geoff, thoroughly enjoyable post…well written and interesting. Would be curious to read more what parallels you see between your anthropology work and your BI work. And I don’t know if you have ever done the Wealth Dynamics assessment but it sounds as if you are a Mechanic…(person who loves systems. Looking forward to hearing more from you 🙂

  4. Very nice post Geoff. I thoroughly enjoyed reading it and using Cube functions to almost mask the fact that you use PowerPivot is brilliant as it can result in a better usability (as you can tightly control the space).

    I am intrigued about your statement about MVP and PowerPivot. Please write more about it.

  5. Geoff, terrific post. I felt a kinship reading this inasmuch as I was a French lit major who has basically built a career in the client service space around database marketing for direct marketers. It took years for me to understand how it was all connected, but it came to me one day when I realized I was doing well (and enjoying a line of work built on Excel that i had no business enjoying so much!) because of the critical thinking, curiosity, and listening skills that were nourished by such a non-analytical course of study. Plus, there’s something very cognitively useful in the act of learning another language. After French I took up Italian, learned it in a fraction of the time, and was rewarded with new horizons, arguably better food and way better coffee.

    With the help of this blog, the book, and a lot of trial and error I’m undergoing a total transformation that I’m now looking to translate into my own practice that i think can change the game for an underserved segment in my space. The analogy I’ve been enjoying lately is for me, Italian is to French as DAX is to Excel formulas. There’s a learning curve but it’s shorter, more fun, and deeply rewarding. Hope I have a great story to tell one day soon like you! Keep it up.

    Jude

    1. Awesome comment. I love hearing about The Journey, and how these new tools change the game for people like you and Geoff.

      I felt absolutely fantastic after reading this comment, thank you 🙂

    2. Like Rob said, it’s great to hear this! And they said Liberal Arts degrees were useless. Ha!

      PowerPivot: reinvigorating liberal arts majors across the land…

    3. Ah, j’adore le Francais. J’apprends le Francais maintenant……and what a lovely analogy to use between Italian and French. Any other specific approaches you think you find useful from learning another language (and isn’t it interesting how there are certain things that can only be said in that language). ?

  6. Oh and since there’s been a lot of discussion of “liberal arts majors turned data analysts” above, I thought I’d share this:

    The Group Program Manager of Excel at Microsoft, back in ~2004, was a guy named David Gainer. He was an English major and a former newspaper editor. No joke! Just an ENORMOUSLY positive thing for Excel, to have him stewarding it. An Excel monster and analytic to the core.

    Not to mention, his mentoring basically “made” me in a lot of ways. You’ll see him thanked in the book and mentioned repeatedly on the blog, including in this post (in which he is also pictured):

    http://www.powerpivotpro.com/2012/11/thanksgiving-weekend-bonus-post-the-cult-of-the-right-thing-and-other-microsoft-cults/

  7. Thanks again Geoff for the post. This gave me an epiphany. The epiphany was that I am capable of going deeper into the BI industry.

    I have been an accountant and analyst for years and recently started to design an excel tutoring program. However, my passion was always in creating financial information that was either “impossible” (said the clients) or that had never been done. And as a career that would be great. But I have yet to see tangible opportunity to do that.

    The part of your post where you discussed talking with executive management echoed in my head. Just days ago the VP of Finance and IT said my data model and dashboards were excellent but too bad no one knows how to define what you do. That made me take a step back and consider all that I actually do.

    Creating useful information from mountains of scattered data requires a particular kind of mind. And the way in which we communicate our ability to the people that need us will directly define our success in this industry.

Leave a Comment or Question