If you’re a regular reader of the Altius Community blogs, you’ll have seem an increasing amount of activity around PowerPivot (formerly Gemini) as a BI tool delivered as part of Office 2010, with significant enhancements when used in conjunction with the upcoming SQL Server 2008 R2 and SharePoint 2010.
PowerPivot
We had the pleasure of hosting an event in London last week with Donald Farmer, the Microsoft BI Programme Manager, presenting his thoughts on BI and how it is evolving to meet the ever-changing needs of Enterprise-consumers.
PowerPivot is a great tool to help straddle the gulf between what ‘the business’ needs in terms of access to information, familiarity in terms of how to use it etc and the IT needs around governance, control and availability. PowerPivot delivers the flexibility and power of Excel to the business user, whilst providing control and making the most of the investment in back-end quality, scalable data sources provided by IT.
Cloud Computing
One of the big changes (and the cause of a lot of turbulence) in the IT world at the moment is the concept of ‘Cloud Computing’. Microsoft’s model for this is ‘Software + Services’ (S+S) – identifying that a mix of on-premise and off-premise systems is an appropriate answer, enabling the economies of scale, flexibility and dynamism to be gained from Software + Services e.g. provisioning services more rapidly and cost effectively in ‘the cloud’.
One of the key challenges in Business Intelligence is providing access to the right information at the right time. Doing so internally with Microsoft BI is one part of that story. Integrating cloud-based data with in-house data is a large part of the S+S model and will help deliver massive gain as the model breaks down the walls between internal and external systems and information.
That’s nice, but I don’t have full S+S yet
The chances are that your organisation is using some kind of Cloudware already – whether it’s website statistics monitoring, SalesForce.com for CRM, facilities management software or one of a growing army of applications that have a tight fit for the benefits of cloud computing. Many organisations are benefitting from this model, but are struggling to integrate the information they contain with internally available data – so how do you make the most of the data already in these systems? REST and PowerPivot may be the answer.
PowerPivot – Give it a REST
REST (Representational State Transfer – but let’s ignore how techy that sounds for now) is a web services protocol that is growing in popularity as a great (and simple) way for applications to provide a common way to interact. REST provides a formal pattern for communicating with applications in a service oriented architecture (SOA), allowing you to ‘consume services’ in a logical way rather than have to know technical details of how it is implemented. SOA is widely adopted in Cloudware / software as a service applications already as a way to ‘glue’ applications together – you can use it too!
PowerPivot provides, out of the box, directly in Excel, the ability to consume REST-compliant services from anywhere, allowing non-technical users to gain access to the information within your cloud-based systems, mine it, combine it with other external (and internal) sources and use it to support business-critical decision making.
Connecting to an external service is available from the simple menu in PowerPivot (allowing you to also consume data already embedded within internal Reporting Services reports right next to external ‘Other Feeds’):
From there, you simply provide the address to the REST services that your Cloudware vendor provides:
(In this example I’m using a publicly available data on purchase orders provided as part of Microsoft’s Open Government Data Initiative and the District of Columbia’s publicly available purchasing data - http://ogdi.cloudapp.net/v1/dc/PurchaseOrders/).
From here PowerPivot handles the details of the queries and protocols for you, allowing you to connect live to the data and treat it as any other table of data within PowerPivot or Excel – making it very much a ‘power excel user’ feature. From here you can start to filter, slice-and-dice, aggregate, analyse and report. If you can use Pivot Tables, you’ll be up and running in minutes!
Built something great? Share(Point) it
With SharePoint 2010, you’ll also be able to share the PowerPivot models you’ve built with your team. Sharing & Collaborating using PowerPivot models enables them to become the one validated source of information for you and your team – delivering high-quality, connected and up-to-date, self-service BI without resorting to hundreds of versions of linked, e-mailed and out of date Excel files.
Find out more
If you’re interested in finding out how you can employ Microsoft BI – often using software your organisation is already licensed for – get in touch to discuss your needs and we can point you in the right direction.
Matt.Quinn@altiusconsulting.com