The problem with being in a busy, dynamic workplace is that you never really have the opportunity to fully take stock before pushing on. In the case of my first stab at producing a rich reporting tool in PowerPivot that was definitely the case.
I should probably go back in time a little, I guess you may (or may not – but this is my blog to tough) want to know where my passion for this stuff came about. A long time ago and in a different life I used to produce a lot of reports, since I’m ultimately lazy (anyone who does this stuff is, they just say they’re not) I wanted to create a self-service reporting tool set. Back in 2006 and 2007 that was really done by hooking Access databases to a true database backend and then using a simple menu system. It was interesting and dare I say fun designing them, that’s really what got me into VBA, from there I ended up moving into VBS and taking a detour from reporting and going back into where I started, break/fix and solutioning.
Wow, I’m heading off topic here. I may write some more posts on my progression through the skills. The point is that three years ago when PowerPivot was first brought to my attention I could see the potential but I couldn’t work out how to leverage it. So many people just don’t get Excel and for many pivot tables just might as well be in an alien language. So it took me a while to start to venture into the realm of PowerPivot, the biggest stumbling block was of course that my head was stuck in the old style way of doing things, both for Excel and SQL (Database development/structuring). A year ago I had to start using PowerPivot after a spreadsheet got well just too big. That’s not an ideal scenario.
So I had a reporting solution that was creaking, an ITSM project to implement and reporting to migrate. The perfect storm…. But I still didn’t understand what I’m afraid I have to call the Power Tools – PowerPivot, PowerBI, Power Query and PowerView (might as well throw it in, but really I consider it more part of PowerView – PowerMap), what works best where? How should I structure my data. You could say I chickened out and went with the “easy” option, I migrated what I had but split out some of the dimensions, really I didn’t know if it would work or even if I would be able to produce something that would come close to my vision.
1.0 is very definitely and a great reporting tool, I was blown away by it, although as you can see from the title it was nothing more than a POC, there are mistakes in the fundamental data model, the PowerPivot learning curve is steady but required me to unlearn so much that it took that perfect storm of a new ITSM tool to make me re-write all the queries and then re-wrap a presentation layer. The experience actually came together finally after a number of hours bashing my head against a brick wall, my Eureka moment was actually attending a Webinar hosted by Avichal Singh. Something went click and 1.0 was born about 24 sleepless hours later.
When I say born make no mistake the “Advanced Reporting Tool” as I call it is a living entity and like Dr Frankenstein watching his creation lumber off towards the nearest village so I had to watch my creation become something more than I had intended. Suddenly Senior Management were using the report and were asking questions about performance. You see I not only gave access to summary information but also allowed them to go down into details. Suddenly they could access a wealth of data at their fingertips. The shock moment for me was when I realised I had messed up. I experienced a hardware failure just before starting the final phase you see and had to rebuild my laptop, suddenly my old data sources weren’t working, I took this as the time to take the plunge and move to PowerQuery – a mistake in my humble opinion although not for the reasons many of you would think – everyone wanting to refresh the report (it can be updated daily) has to have PowerQuery installed and enabled within Excel.
My second mistake I have trouble with, I used Excel 2013’s Timeline slicer. This is a controversial topic and one that I’ll try and give proper time to later.