Maurice Prather, enterprise architect

Maurice Prather on BI and PowerPivot

He’s got a great accent, so he’s easy to listen to after I just had a wonderful lunch with Women in Technology.  I’m really interested in columnar storage and business intelligence, so let’s start the session!

 

Why BI?  It’s valuable, sure.  But, it’s really difficult to get it right in an enterprise.  We’re collecting data everywhere, but our users still have difficulty accessing relevant and reliable data.  IT has insufficient staff to support it.  And, etc.

 

BI has low success rates.  Most of the time, these are multi-month projects.  57% of deployments take over a year.  The average implementation time is 17 months.  The mean annual cost of BI software is high at 1.1M.  And larger and more expensive projects don’t necessarily translate into higher success rates.

 

It’s time to change our BI strategy.  He recommends two prongs:

• SharePoint as the visualization host
• PowerPivot as the data model – everything can be in SQL, but stage it in PowerPivot

Performance Point Services, Reporting Services, and Excel Services are all sitting and waiting to be tapped to their full potential in SharePoint 2010.  PowerPivot is the newest BI member in the stack.  What’s great is that it works with very large data sets (much more than Excel).  Maurice says he’s seen a billion rows as the data set in PowerPivot.  Wow.  It bridges the gap between the world’s most popular “self-service” BI tool (Excel) and more traditional systems such as Analysis Services, SQL Reporting, and etc.  It’s built exclusively to work with SharePoint.   

There are two operational realms:

Client

PowerPivot Add-In for Excel — Allows Excel clients to interact with and author workbooks.  And, it’s free!

Server

PowerPivot for SharePoint — Service application that works in conjunction with Excel Services and provides a monitoring surface.

There are over 30 million POWER users of Excel.  This is a great target audience.  PowerPivot is designed for all of IT’s BI customers, information workers, data analysts, and regular SharePoint users.

Traditional BI models, that typically take months to deploy, only fill about 5-10% of what business users actually need.  PowerPivot is self-service and spans the gap between traditional BI and agile BI.

PowerPivot extends core Excel concepts, but leverages SharePoint for security and services.  It has support for any data volume (the limit is your hardware memory), cross-data source mashups, and advanced calculation that reduces support and start-up costs.

PowerPivot is analysis services in VertiPaq mode (columnar storage), which takes your data and collapses it, making it small until you bring it back into view.  It’s designed to load and KEEP large data sets in memory.  With a theoretical processing rate of 1 TRILLION rows per minute, it’s awesome.

Here are some PowerPivot samples to download.  Here, too.  There’s some stuff in CodePlex as well.  And, here is a surprising example of PowerPivot data compression vs. Excel.  12x on disk on 8x on RAM.  and, it only gets better with larger data sets.  As an IT administrator of SharePoint 2010, it seems we would want to enable the PowerPivot service for our users.

PowerPivot is great at 80% of the use cases for BI — the dashboard.  What it is NOT:

• an ETL tool
• a suite of new controls (except for the slicer)
• designed for re-entry of data
• notification capable

It’s interesting to know that PowerPivot is not a claims aware application and must be in Classic mode to operate.  Also, the minimum requirements listed on MSDN (8GB RAM and 2 processors) don’t seem to operate in reality.  Unless you’re building a demo, you actually need a lot more.  Recommended settings for an enterprise deployment of PowerPivot for SharePoint are in the 64GB RAM and 2 dual core or 4 quad core processors range.

PowerPivot is a memory hungry hog.  It’s an in-memory database engine, so it’s important to plan your hardware, understand your data sets, and use the tools to monitor the system.  Good design techniques are extremely important.