Donald Farmer #SQLBits keynote on PowerPivot

At SQLbits, Donald Farmer (@DonaldDotFarmer) talked about IT in terms of data, and did it with an interesting picture story from the early 20th century. Data today isn’t all that different from the messy desktops and clean library card catalogs of the last century.

Donald Farmer onstage at SQLbits

IT professionals still break into the same 3 roles that existed a century ago:

Acquiring and storing data
Validating the data quality
Managing calculations on the data

Our roles, though, conflict with those of analysts – power users who want to do this work themselves.

Letting People Build Something Cool

Analysts do their slicing and dicing in our “approved” tools, but when they bang up against the ceilings of our capabilities, what do they do? Universally, they export to Excel, and then start doing crazy things in this radically familiar environment.

To build the future of BI, Microsoft is delivering PowerPivot as an optional add-in for Excel 2010. Excel 2010 installs side-by-side with older versions, so users can install it without violating corporate policies on using older Excels. Farmer demoed PowerPivot with a >100 million row table using his laptop, sorting & dicing it in real time. This demo still hasn’t gotten old for me yet – I love seeing that. Since PowerPivot is a column-oriented data storage mechanism, it performs really well in memory. It still takes a long time to get 100 million rows into PowerPivot (over the network, for example) but once it’s there, it flies.

PowerPivot data is read-only, though – once it’s been pulled in and compressed into column-based storage, it’s pretty much stuck. If you need to make modifications, the easiest way is to use linked tables, and modify the linked ones. Farmer demoed how to create relationships with those tables, and how PowerPivot helps detect which relationships don’t violate integrity rules.

To get that data into PowerPivot, one easy way is to use RSS. Business users can click the RSS icon on new SSRS reports, and Excel will open the feed to create a PowerPivot document. From there, users can easily do their own slicing & dicing.

When we look at Excel spreadsheets from analysts, often the front couple of sheets are nicely formatted reports. The rest of the sheets are various intermediate pivot tables and aggregates hitting other sheets of source tabs. Excel’s new formulas in PowerPivot help you get those well-formatted aggregates faster with less intermediate steps.

I Made Something Cool! Now What?

Once users have built something cool in Excel, they didn’t have a solid way to share that with multiple users. Farmer says SharePoint 2010 is like the ultimate file share for Excel because:

IT knows the files are backed up
IT knows who’s building the reports
IT knows who’s accessing the files (which means we can tell which reports we may need to take over)
People can consume the data without actually having PowerPivot or even Excel – it just works in the web

PowerPivot spreadsheets with slicers look fantastic in SharePoint. I can envision people building their own report portals without BI team help. There’s still a question of where the data’s coming from and how accurate it is, but that’s the case no matter how reports are delivered anyway.

Farmer says that this new self-service BI doesn’t interfere with real BI teams – you still need real BI in order to get analytics. The self-service BI delivers the answers to the “background noise” of analytics requests – small, quick-hit requests that never end. Farmer says self-service BI isn’t for the big projects, and the big tools aren’t right for self-servie needs either.

To try out PowerPivot, hit: