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!”
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*)
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. I 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.
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.
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).
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
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 [link removed due to 404] 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.
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!