One does not simply walk into Mordor (Or How I spent my summer vacation learning Power Pivot)

 
Mordor

I Hear it’s Good to Have a Tour Guide?

Intro from Rob:  I once read that one of the producers of Fight Club had originally been “turned on” to the book by a friend.  Said friend had described the book, and its author, as an “important new voice” he had discovered.  I’m feeling very much that way about Shika Simpson, the author of today’s post.  An important new voice.  I think you will see why.

BTW folks if you use Reddit, please drop in on the Excel subreddit & “upvote” the link to this post. Shika has done a great job here and I think it’s a good opportunity to awaken some of our brethren :)

http://redd.it/1kgsog

Once upon a time, long long ago…

Once upon a time long long ago, an intern happened upon a middle manager who was keeping track of large amounts of “physical” data by…sorting it into piles on a table.

Said intern looked to the skies and cried, “This is REALLY stupid.”

In that moment an Excel junkie was born.

Hello Power Pivot World!

Greetings and salutations, all. I’m Shika Simpson (feel free to come say hi on my blog or on LinkedIn) I work in K-12 education, specifically in student assessment.

I found my way to Power Pivot after spending the better part of the last school year slogging through the unsatisfying data output from a new testing software I was put in charge of and regularly looking to the skies and crying “this is REALLY stupid”.

I’ve always been a bit of a self-starter when it comes to learning new products. One summer break I spent most of my free time in our study (the walls were stone and it was cool there…ok fine I’m a big nerd. You got me), hunched in front of my family’s massive Compaq desktop teaching myself to use Microsoft Office.

As luck would have it, my internship would soon prove that was valuable. The aforementioned middle manager was storing the information for new hires in PILES. For the positions being hired for, each applicant had to submit numerous pieces of paperwork (transcripts, medical clearance, background check, etc.) and he was sorting the applicant files based on what they had turned in.

As keeping track of this was going to be my responsibility I quickly got to work on a Access database where I could create a list of applicants with fields to tick off who had turned in what. In under a week I had catalogued all the current pending applicants (there were upwards of 500) and was able to give a daily update to him (which he then passed on to HIS boss) on the status of all the new hires with the small effort of a few mouse clicks.

And thus began my career in using my self-taught knowledge to make my superiors look awesome for their superiors.  (Note from Rob:  I am laughing, I am crying, I am nodding.)

Its the cirrrrrcle of corporate.

A few years later after I had moved on to my current workplace where I started as the administrative assistant.  While there I revolutionized the sending out of our corporate Christmas cards via mail merge. 

Because all the address labels were being typed out manually, each year.  Mostly the same addresses as last year and the year before.

I looked to the skies and cried, “This is REALLY stupid.”

Idiots

(Note from Rob:  Shika is fast entrenching herself as the poster child for the “accidental analyst” meme.  See also: accidental architect).

Clearly I’m a “work smarter, not harder” kind of girl.

But again, being prepared paid off because my then boss saw my potential, and nurtured it by putting me to work on learning and using pivot tables (original flavor). I’ll spare the details but it was a gruesome data set involving state wide standardized test data.

Pivot screencap

WE’RE HAVING SOME FUN NOW.

The next frontier

Fast forward to summer 2013.  Once again I was facing a data problem.  Excel’s “conventional weapons” were no match for it, meaning I was performing MANY manual steps, repeatedly.  Wash rinse repeat from here to the horizon.

I looked to the skies and cried “This is stupid.”

After a furious Google search I learned three things:

1. Power Pivot existed

2. There was a whole world of people doing what I do under the umbrella of BI.

3. I needed it. Now.

“See?  I told you all my venting on Twitter was productive!”

As I am wont do, I took my frustrations regarding finding appropriate learning materials for Power Pivot to twitter. I take my frustrations for everything to Twitter (70K+ tweets back me up there) and for once, I actually got something out of it. Through some kind of special magic Rob found me and decided to show me the way. In short order I had his book and a new summer project.

2108280

SPOILER ALERT. ITS REALLY HARD.  (Rob note:  I want to delete this line, but I won’t)

But it can be done.  (That’s the spirit!!)

Rob has been lovely enough to allow me to post here and I can only surmise that my purpose is to say that learning Power Pivot is a thing that can be done. Even if, like me, you just considered yourself a nerd with a thing for spreadsheets and had no idea that there was a entire new field springing up around you…populated with nerds with a thing for spreadsheets.

I’ve only been playing around with Power Pivot for a few weeks, focusing mostly on trying to dial into the “brain” of the program. Its not enough for me to just know how to do things, to have any hope of retention I’ve got to understand the why. Rob’s book has been a great help in this process.

Anyone using Power Pivot in Education?

Another aspect (and I’d LOVE to get feedback on this) that I’m really puzzling over is honing in on the best ways to use this data in the classroom. Most of the Power Pivot work of others that I’ve been able to sample has focused on accounting and business functions, but I know that there is a way to make Power Pivot education friendly.

What has proved to be most useful this far is the ability to make connections between data sources, which will allow comparisons to be made between state administered exams, our district level assessments, and maybe one day classroom tests.

The journey will be long, but I think I’m heading in the right direction.

15 Responses to One does not simply walk into Mordor (Or How I spent my summer vacation learning Power Pivot)

  1. Kellan says:

    I love reading these blogs to learn about new ways of using this very powerful tool but I enjoyed reading this article because I have totally been there. It is unbelievable though with the right combination of curiosity and educational direction what can be accomplished using PowerPivot inside Excel and SP. Keep digging Shika! It gets easier (and “funner”)!!

    Regards,
    Kellan

  2. powerpivotpro says:

    I’m taking off my Editor hat and donning my Reader hat.

    “Shikas” are one of the world’s great, “under the radar” natural resources. But not under the radar for too much longer I think.

    Note to Shika and her ilk: continue arming yourselves with improved skillset. Advertise and promote yourselves as appropriate. Be ready.

    Employers and entrepreneurs: find these people now and get them to help you change the world. Before your competitors do :)

  3. betsig250 says:

    Amen to this post…

    This comment is pure GOLD.
    “And thus began my career in using my self-taught knowledge to make my superiors look awesome for their superiors”

    As for your educational needs, I think taking what schools give as the “standardized” tests like MEAP or whatever your state mandates and start corroborating student performance based on which teachers they had could be a nice start. Or some kind of Jocks vs Nerds style analysis seeing if kids in sports or not does affect grades. (Or find out if teachers do give out better grades to the high end jocks)

    • shikagoland says:

      The problem now is that even though my focus is student assessment data…I woke up this morning thinking of ways to tie it into all sorts of things because of your comment. I did some work with our PE department two years ago for a grant program and…whoo boy. I think I just created a new project for our athletic director! Please watch out for my next post, “How to Lose Friends and Alienate Gym Teachers”.

  4. Jason says:

    Its nice to read about someone else in education using powerpivot. I find my views to be very similar to “Shikas”. I have had a hard time finding information / examples for education in the powerpivot blogs out there. I have the great task of building all of the data models for the University in which I work. Mind you I’ve only been working with powerpivot for about 5 months now.

    Shika, some of the different ways that I am using powerpivot in higher education is to track retention rates, graduation rates, enrollment #’s, I am currently working on a lifecycle model that will be able to follow a student from application to graduation and allow us to put a $ figure on how time and money each student took or generated for us.

    PS: Rob’s book and blog have helped me in ways I can’t begin to explain.

    • shikagoland says:

      Jason, thats a fantastic idea. I have four schools that I work for with students ranging from 6-12th grade and I think it’d be a great idea to track the students that move from our middle school into the high schools. I can access some basic data for incoming students from other districts into our high schools as well…I feel…I feel a project starting to form in my head.

      And I echo your statement about Rob’s book/blog being so helpful. Reading about what other people are doing with it really inspires me to keep at it.

  5. Tony the IT Ops Guy says:

    I am proud to say I know Shika. The soon to be Power Pivot guru of the educational world!!!

  6. XSzil says:

    Chicks RULE!! Fabulous post, Shika. I’ve done a bit of Power Pivot fantasy-experimenting in the academic world, specifically in the area of university hospital / patient safety research. Yes. Academics need better data management. I’m afraid I’ve seen those piles of paper too. In my case the experiment has been slow going, but not even because of piles of data –if only! Rather, it’s a new area of research so data simply does not exist yet – it hasn’t been collected, or they just don’t even KNOW what’s out there… But once all that data IS finally unearthed, and perhaps massaged into a workable format under the loving guidance of a Power Pivot expert.. HELLO, data mashups! They’ll eat it up, too, being scientific minded, curious (almost to a fault) about what all the data means. Anyway, keep it up! :)

    • shikagoland says:

      “academics need better data management”
      You said a mouthful. I actually may keep that as a possible title for my memoirs. Because one of the many hats I wear in my day job involves grants (aka begging for money professionally) I know all too well that academics are crap at collecting data. I have spent many frustrating mornings on the phone with an English department head saying “but…I SENT YOU A SPREADSHEET JUST FILL IT IN” before breaking down into tears and just figuring out a way to get the info without them. I’m pretty sure at least half of my gray hairs are a result of trying to cull data. Its…a thing. But as you said, when its all put together they love it. Its what keeps me going.

  7. powerpivotpro says:

    BTW folks if you use Reddit, please drop in over there to the Excel subreddit and “upvote” the link to this post. Shika has done a great job of an intro here and I think it’s a good opportunity to awaken some of our brethren :)

    http://redd.it/1kgsog

  8. Great post! I look forward to more guest posts from Shika (and more about Power Pivot in education too)

  9. Mike cadieux says:

    Great post. I have a project for you.
    As a father of an inbound high school freshman, I sat through a couple school presentations this past year. Initially we had decided to go with the local catholic high school at roughly $10k annually. But at the last minute my child got selected as part of a multi town student swap program into an exceptional public school in the next town over.

    This got me thinking about a powerpivot data model, that could show me the ROI associated with high school education, where I could ultimately see if my 40k would have produced more scholarship monies, and a high level of acceptance for better colleges, for my child. Obviously, there is an assumption that my child would perform at the same level in either school…..but,with that said, I ould think this would be fascinating. How does the ROI differ across gender, race, by state, ethnicity, etc… Does it fluctuate over time, and if so why? Is it because of applicant pools being larger in some years, or smaller, is ROI better for public school education in good economic years because colleges are flush with funds to recruit with… So many questions.
    After spending the last couple of months playing around with powerpivot I have to believe this data is available. Just a thought on future work to show the value of your programs, and return on hard work for the kids in GPA, etc….
    Regards mike

Leave a Comment or Question