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:


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


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. 


Author: Ynema Mangum

Ynema Mangum is an experienced, data-driven principal product manager of mission-critical composable infrastructure at HP Enterprise. Constantly curious, her passions draw her to emerging technologies. She joined HP 6 years ago as a solutions architect for private IT cloud computing. She then served as owner of the enterprise social collaboration domain at HP, responsible for its future direction. Prior to her current position, she was a senior product manager for the massive HP ConvergedSystem 900 for SAP HANA. At SUN, she was a product line manager for cloud computing, responsible for the requirements for common subsystems of the Sun Cloud, as well as user personas, industry analysis, and competitive research. Her product experience also includes building web based database management systems at BMC Software targeted at Oracle, Microsoft SQL Server, Sybase, DB2 and DB2 UDB. Y provides an invaluable hybrid mix of strategist, architect, product manager and product marketer with an unbendable passion for the user experience. Her entrepreneurial experience allows her to understand business as a whole and drives her to make decisions and execute quickly. As an added bonus, she is certified in ITIL v3 best practices for IT and Pragmatic Product Management. Ynema is a change agent. She considers herself a determined influencer and a connector whose collaborative nature ensures success in introducing new concepts and services into the mainstream -- even in the most complex environments. She thrives on doing what seems to be impossible, and enjoys taking calculated risks in her personal life -- snowboarding, skiing, SCUBA diving or wake boarding when the season is right.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s