SQL Server 2012 PowerView and PowerPivot v2

After sitting in on a recent webinar looking at new features for Microsoft PowerPivot 2012 (v2) and a new product called PowerView I could not help but be impressed. PowerPivot has been around for a while now but PowerView is a new feature part of Microsoft SQL Server 2012 that must also run with SharePoint Enterprise Server. Although I'll not be covering PowerView in this blog (I'll discuss that in my next one) it's clear to say that this is a highly intuitive and interactive reporting tool that allows you to create rich and dynamic visualisations over the web.

PowerPivot integrates with Excel and SharePoint (and is free if using the Excel side of it only). As my colleague Stuart Wallace discussed in his blog last year it's blindingly simple to use and in a quick few steps your Excel users can be setup and ready to go in no time. I'm not going to spend much time describing what PowerPivot is as my colleague covers that pretty well here, so I'll try and focus on some of the new things that are coming as part of the v2 2012 release.

• Diagram View – In the previous version of PowerPivot you would define you relationships and then view these in a list format. In v2 we now have the ability to view our relationships and tables in a diagram (a lot like our Erwin Entity relationship Diagrams). So now when you build your relationships you can print it off and view how you tables relate together in front of your desk. A picture paints a thousand words and this case it will allow users to visualise how the data fits together quickly and efficiently. Although I agree with my colleague's views on allowing users to build data models on the fly I do still feel this is very powerful feature in the right hands (with the right controls applied).
• Sort by Column – In PowerPivot v1 columns could be sorted alphabetically. Unfortunately this was the same for date fields like month or day of the week (April always came first or last). With a bit of playing around you could sort it correctly but not without some work-arounds. Now you have the ability to sort based on the dates to ensure January to December will be shown correctly. Not a massive addition, but a handy one that will cut out unnecessary work.
• KPIs – a particularly nice addition is the ability to create Key Performance Indicators. So let's say we want to create a measure object like Average or Sum of Sales to compare against a Sales Rep. You can define a range of values to fit in with a traffic light system and assign them to an icon (Red traffic light for low sales, green for high sales, etc). Once again, a very simple but effective addition.
• Hierarchies – No matter where you work you will always come across hierarchies. PowerPivot v2 now includes functionality to model parent-child relationships. We now have a PATH function to display the path of our parent child relationship in a delimited format (i.e. John is managed by Bill who is managed by Stephen will display as John, Bill, Stephen). In addition, there is a number of variants to this path function to work out the length of this hierarchy path for example or the ability to highlight whether John belongs to a parent-child relationship . I'll investigate some of these in a later blog.

Through PowerPivot, Microsoft is going to continue focussing on bringing a highly interactive and intuitive experience to all users. As you'll see in a later blog PowerView will act as a worthy addition to this idea that integrates well with your PowerPivot sources. This ad-hoc querying and analysis approach is not something new in the BI world but PowerPivot is going to tick the boxes for a number of Business Users (particularly Excel lovers). In a nutshell, let's make it easy to use with the limited amount of training. By integrating with Excel, Microsoft has a clear advantage on selling this point to potential users. I do worry sometimes when I see what can happen when users that don't fully understand data structures are let loose on data. With the correct controls and procedures in place however, PowerPivot really is an impressive tool that will allow users to gain rich insights from their data in a short space of time.