Why do I enjoy using PowerPivot so much? Because it solves problems for me.
When I first started digging into business intelligence, I started with building data marts, and then defining cubes in SSAS, but one problem kept cropping up – having no proper user interface to expose the full capabilities of Analysis Services.
I mean, you can’t:
- shove a query window in front of end users
- predefine a set of specific MDX queries but expect users to be able to go where the data takes them (more on that in a bit)
- and you can’t require that they spend thousands on a front-end interface (that’s not BI for the masses – well, not the masses I am thinking about)
and so I tried to create my own user interface (and I hate writing UI – that’s how desperate I was)
By the way, doing that was an awesome learning experience and I created a demo-worthy app but still far too limited, too “cookie-cutter”, and not freeing enough. I could slice and dice the data, but without great time-intelligence abilities (ie. Year over Year, Parallel Period), its hard to really show the things that real BI data exploration requires….and of course, the all too common refrain from users “but now how do I get that into Excel”
But back to the point about going where the data takes you……I was reminded of that again during a demo I gave this last week where a CEO mentioned that a current billing report they create said they had dropped X subscribers but that’s all they knew and they needed to know more about the situation and would tools like PowerPivot help? My answer was “Probably”….it all depends on the granularity of the data gathered. Since talk is cheap that’s when we took a look at billing data in a PowerPivot sheet on my demo machine for a sample company, found a month where the dollars had gone down and decided to do a little green-field data exploration on the spot – What things could we find?
We started with total billed customers by month, moved to Average Revenue Per User, and Average Services Per User, with Slicers by Geographic Area, Product, and Business Class (Business vs. Residential), and really started learning more about our sample company data and its trends by approaching the data from different directions
Its a lot like Geocaching.
If you have never been geocaching and you have a GPS unit, you must check out www.geocaching.com – seriously, do it now, come back and read the rest of this later – ESPECIALLY if you have kids or buddies looking for something to do – basically people have hidden “caches” around the world (even my small city of 14,000+ people has probably 50 of them around town, they are actually everywhere around the world – but you enter your Zip code, and the website gives you the coordinates of some caches in that area – they are rated based on difficulty to find, size, difficulty of terrain – and some are pure diabolical genius.
In geocaching, the catch is that most GPS units on a cloudless sky with little trees overhead can get you to within +/- 6 feet – which for some caches still can make it challenging, and if there are many trees, etc, your precision may only be +/-15 feet…
So the trick is to approach a suspected cache from multiple directions and triangulate the best possible junction of where the data is leading you, a steady walk as your GPS unit counts down the feet to the desired coordinates until you get to within the limit of the units precision communicating with the satellite, then if you don’t see anything obvious, you start over from a distance coming in from a whole new direction
Just like BI data exploration and data mining, looking for patterns and consistencies, you need to approach the data from different sides, slicing by different criteria and checking to see if:
- Is the revenue increase due to more customers or more revenue per existing customer?
- Are changes specific to certain geographic locations, or certain products, or certain class of customer?
- How about comparing year over year to minimize seasonal affects of the business?
Until you find what you are looking for
just like Geocaching
Have a good week