My Conversion to PowerPivot: Guest Post By Geoff McNeely

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

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!

32 Responses to My Conversion to PowerPivot: Guest Post By Geoff McNeely

  1. Paula says:

    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. Geoff McNeely says:

    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. Anne says:

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

    • Geoff McNeely says:

      Hi Anne, thanks for your feedback! What in particular about the parallels would you like to read about? There are a ton and I’d love some focus on where to start :)

      I’m savvy with Strength Finders, Myers-Briggs, Fascinate and others. Never heard of Wealth Dynamics. I’ll have to check that out.

      BTW Rob, I’m a junkie for taking assessments and correlating the results to how folks function and work, and what they focus on. I’m curious what the overall tendencies are for Excel BI folks.

      • Anne Walsh says:

        Geoff, my question to you would be ..what would be the one or two approaches you learned in anthropology that you find consistently useful in your BI work. Am a fellow assessment junkie as well :-) – done Strength Finders, Myers-Briggs (INFJ), haven’t come across Fascinate. Another one you might like would be Enneagram (I’m a Two) and Kolbe (to assess your conative style). And yes, what an interesting question that is – are there any tendencies amongst Excel BI folks….

      • Anne says:

        Geoff, hm I really felt that you were a Scanner (did you identify a particular one e.g Serial Specialist, Serial Master…). and what a wonderful thing that is! I’ve got another one of her books on order at the moment..delighted it resonated with you . The “N” of my INFJ kicking in there :-)

        • Anne, I still have 100 pages to go but just got to Plate Spinners and I am ABSOLUTELY one of those. SO refreshing to learn about the patterns that I am living each day! Thanks again!

  4. Siraj Samsudeen says:

    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.

    • Geoff McNeely says:

      Hi Siraj, and thank you!

      A vote for MVP! Yes! What angle would you care to hear about? What is your perspective? As I mentioned in my comment for Anne, I could go a number of ways with a post on that, so focusing on your interests will help me refine the idea.

      • Hi Geoff, I do not know much about MVP. I know the concept, but I could not see how you could use PowerPivot on MVP. So, whatever you plan to share is fine!

        • Geoff McNeely says:

          Hi Siraj,

          What I’m mulling over is this: many products have an inherent data model, whether it is user information and transaction data, or user info and related info.

          My hypothesis is that when developing a new product you can use something to mock up the UI and provide the surface user experience, and then use PowerPivot to model the solution “beneath the sheets” to see how you might deliver up the data to be rendered in the UI.

          In this way, using some sketches and a PowerPivot data model you could build a functional prototype of your solution relatively quickly and get feedback on it in real time.

          Then once all questions are answered this simple, modest Excel file could be handed over to your engineering team as a fairly complete specification for what the app will do.

          Like I said, I’m fleshing it out. i’ll look forward to reading your comments! :)
          Geoff

          • Siraj Samsudeen says:

            Hi Geoff, I see what you are saying. In fact, I was thinking along similar lines in using PowerPivot as a spec tool and a prototype tool. I look forward to readng your thoughts once you finally flesh it out.

          • Geoff McNeely says:

            Cool!

            Also, if your solution is a mashup and dependent on other data (say, you have customer data in SalesForce.com, and you want to build out a new application on top of that data), you have a way to pull that data into PowerPivot to quickly iterate on it. Yep, I am getting the gist of the next post together already. See you in a couple weeks! :)

          • timrodman says:

            Geoff,

            I just had this experience a couple of weeks ago of using Excel as a mockup tool. I needed to make some changes to our ERP system in order to capture additional information for some freight spend analysis. In the past I would have created the database tables, created the front-end screens, then showed them to the users.

            This time around I mocked it up in Excel with sample data (each worksheet mimicked one of the new screens), then created a pivot worksheet to display what the report will look like. Nothing fancy here and I probably didn’t even need to use PowerPivot, just a regular pivot, but it saved me a lot of time by giving me the ability to have more design discussions before running off and writing a bunch of code. Inevitably we needed to make some tweaks and it was much easier making the tweaks to my Excel file than it would have been to rebuild the screens with the new logic.

            This takes a little more time on the front-end, but saves a lot of time on the backend.

          • Siraj Samsudeen says:

            Hi Tom, this sounds cool. Definitely a simple and very powerful idea.

  5. Jude says:

    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

    • powerpivotpro says:

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

    • Geoff McNeely says:

      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…

    • Anne Walsh says:

      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). ?

      • Jude Hoffner says:

        Anne, I think your last point is itself an answer to your question. The corollary is that certain things are expressed differently way in each language. Simple example – in english we say “I am Hungry” whereas in French you say “I have hunger”. Seems like a meaningless difference in a tiny context, but I’ve longed believed there’s something really profound in those subtly different ways to express the same concept. You get by in another language when you transliterate, you are proficient when you translate, you are fluent when you translate effortlessly and discern subtleties. And by fluent I mean kicking butt.

        So here goes another analogy: DB pros are to transliteration as Excel/Powerpivot pros are to translation. Rob had a great post about the traits of a great power pivot pro, and I think this analogy continues in the spirit of what he invoked in that post.

        Thanks for making me think about this today!

        • powerpivotpro says:

          Jude if you have any interest in sharing it, I’d love to have your “story” here on the blog much like Geoff’s. These are the things we need to be sharing and hearing.

          • Jude Hoffner says:

            Rob, would be happy to share and am flattered you offered. If it isn’t too much of an imposition, I would like your permission to summarize what I’ve got going on and what the vision is first offline (By offline I of course mean online. Email.) You can determine whether it still merits any ink on your blog and I would be eternally grateful for your opinion as to whether the opportunity I perceive and am working to seize truly exists. Or whether it’s all just a big mirage.

        • Anne says:

          Yes, I love those subtle language differences – e.g in Gaelic you say “hunger is upon me.(Tá ocras orm).the same sentence construction you use for wearing something….and of course fluency just means it flows out of you :-)

  6. powerpivotpro says:

    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. Stephen says:

    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.

    • Thanks for sharing your story Stephen! These are the mini-revolutions that I know Rob has been monitoring. Shoot! I need to finish my registration for his class! It closes today! #easilydistracted

Leave a Comment or Question