Blogs

Last year at the Microsoft SharePoint Conference in Las Vegas, Donald Farmer, Microsoft’s Principal Program Manager for SQL Server Analysis Services, pulled me up on stage to do a 5-minute bit on why BI Professionals should embrace, rather than fear, self-service Business Intelligence (AKA PowerPivot). ShareSquared was an early adopter and supporter of PowerPivot partly because it plays well with SharePoint (our bread and butter), and partly because we knew that it was going to be big!

Nevertheless, Microsoft was concerned that the notion of “self-service BI” might be off-putting to Microsoft Partners and professionals in the BI and SQL Server space.  I’m happy to report that the early jitters about PowerPivot are officially behind us as earlier this month, PowerPivot stole the show at the Professional Association for SQL Server (PASS) Conference.

PowerPivot is an add-in to Excel 2010 that transforms the process by which BI is conceived and developed.  SharePoint, Excel Services and PerformancePoint follow suit by transforming the way BI information is managed, distributed and consumed.  The combination of these technologies, along with Microsoft SQL Server, represents a radical departure from traditional BI creation and consumption.

Consider the traditional life cycle for BI development which looks something like this:

The duration of this life cycle depends on several factors including the:

• Ability to clearly articulate BI requirements prior to implementation
• Data requirements – availability, location and access methods for the data, schema mapping, any data conversion and/or manipulation that is required, etc.
• Report complexity
• Availability of IT resources required to do the implementation
• Budget approval and prioritization

The problems with the process are that:

• The BI Consumer has to know precisely what he wants before he can order it and get the initiative approved, oftentimes before he has access to the actual data
• The initial buy-in is too high; BI data and reports that would be invaluable to an organization aren’t even pursued due to the cost, time and sometimes risk associated with the cycle above
• The “raw data” is too far removed from the business people who understand it’s meaning
• Initiatives are limited by the vision of the people who order the reports as opposed to being driven by the patterns and insights that emerge from the data itself - This one’s key!!!

In contrast, PowerPivot enables Excel Power-Users to play with data and discover patterns on the fly as opposed to having to know in advance what combinations and manipulations of data might yield some new insight.  PowerPivot also enables end users to pull in diverse data sources, both internal and external to the organization, and mash them together to explore new correlations.  Moreover, there are legions of Excel Power-Users in the world who are intimately familiar with the business and the data and these resources tend to cost less than the average BI Architect.   PowerPivot lowers the buy-in, promotes creative thinking and facilitates “what-if” scenarios in the BI world that weren't possible until now due to the constraints of the technology and process.

Once PowerPivot users have created BI artifacts, they can be published, hosted, managed and consumed from within SharePoint.  Doing so automatically enables an organization to take advantage of SharePoint’s inherent mechanisms for security, publishing/centralization, workflow, content expiration, targeting – all the things we love SharePoint for!  Excel Services handles data management (refreshes, server side calculations, etc.), while the PerformancePoint Service provides KPIs, dashboards, charts and other traditional BI functionality.

All of this wizardry requires several components that comprise the Microsoft Business Intelligence stack including:

• Microsoft SQL Server - Database Engine, Analysis Services (SSAS), Reporting Services (SSRS), Integration Services (SSIS)
• Excel Services - Sharing, securing, managing, and using Excel 2010 workbooks in a SharePoint Server Web site or document library
• PerformancePoint - Key Performance Indicators (KPIs), Scorecards, Analytic Charts and Grids, Reports, Filters and Dashboards
• PowerPivot - Add-in to Excel 2010 that facilitates in memory manipulation of data, pivot tables, data management, Data Analysis Expressions “language”, etc.

As with SharePoint itself, you don’t just install all of this out of the box and start using it.  Careful consideration and planning is still required to get everything working correctly and to institute the proper governance and controls.  BI Architects and Database Developers are going to have to create the data feeds and do their part on SQL Server to make this work.  The SharePoint guys need to understand Excel Services, PerformancePoint, etc.  Bottom line, you’ll still need to have your SharePoint GamePLAN and a Business Intelligence GamePLAN before the self-service party starts!