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.
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:
9 Comments. Leave new
Geez Brent, you got that post out pretty quick….the seats in that room are still warm.
Ha! Yep, I don’t even bother taking notes anymore – I just write my notes into a blog post and save it when I leave the room, heh.
I hope that user education is being preached along with PowerPivot demos. It seems rare to me that you’d have more than few or a small team of people equipped to do any kind of self service BI.
Also, the question of where the data is coming from shouldn’t be a question if the data collection process is controlled in some form of standard ETL. I could see spreadmarts becoming a major problem using PowerPivot without user education and some form of data governance policy in place in your organization. The most important skill an analyst or end user can have is being able to form the business question that they’re trying to answer. Unfortunately as I said above, that just doesn’t apply to most users or even analysts in probably more than half of all organizations. I usually have tried to force The Data Warehouse Tool Kit on every analyst that I’ve worked with to get them to understand more about data and how to think about it.
I also don’t see what the point of showing off 100 million row slicing and dicing in Excel other than just as a tech demo. How could that possibly be useful to analysis in any way? How can you make sense of 100 million rows at once unless you’re using it as a base to aggregate and group off of.
And finally, I’m still not liking the memory requirements for SSAS and PowerPivot with SharePoint. 64GB to 128GB? 8GB density DIMMs are expensive.
Yeah, you need to sit through a full-blown PowerPivot session with Donald Farmer and do a Q&A. They’ve thought through a lot of the issues like scale and user training, and I think they’ve done a really good job. It’s more than I’d go through here in a comment, but I might post an entry about it later.
Quick response to one of Brett’s points: We only show 100M rows in the source window as a means of demonstrating the speed of the system. Sorting and filtering in that window has very little value for analysis purposes – we included those features mainly to help you navigate thru the data, and to help debug calc columns.
The fact that I can work with 100M rows and have it still feel and perform like a spreadsheet, even when I add a calc column, is an important point for Excel power users.
I also agree that some degree of cultural change has to take place with the adoption of PowerPivot. There will need to be some discipline and control exercised. As Brent said, we’ve spent a lot of time working through how that will happen, and built the features accordingly. I’m positive we didn’t think of everything, but it won’t be for lack of trying, and I think we’ve covered quite a bit in a single version.
I’m glad to hear some of my concerns have been addressed. I haven’t had the opportunity to sit through a demo yet but I’ll be building my own with the betas shortly.
User eduction will always be a bottleneck. Hopefully by using Excel that barrier to learning will be easier to break through.
Can someone tell me where the link is to this keynote video? I watched it once and then lost the link… uhggg.
Sure, go to http://sqlbits.com/ and scroll to the bottom. The presentation videos are on the home page.