Saw an amazing BI demo this morning at the BI Conference here in Seattle. Donald Farmer showed how over 20M rows of data can be modeled and analyzed in memory. To build a model today, a DBA needs to define dimensions and fact tables, get the relationships right, define calculations, deploy it to a server, build and manage it. After that, someone can connect to it and play with the data.
What Donald showed is how a user can do all that with an Excel add-in. He started with 20 million rows in memory on a no-frills PC and built a model from scratch. Even with 20 million rows, interactive ordering, filtering and windowing and pivoting was instantaneous. It’s difficult to compare how much simpler it is building a model with actual data than it is building one with abstractions and not seeing the result at the end. You might remember the days before WYSIWYG when documents were built with formatting and font codes and not seeing how it would look until it hit the printer. This is doing the same thing to data analysis with non-trivial amounts of data – WYSIWA (what-you-see-is-what-you-analyze).
And – this bears repeating – it’s all in Excel.
The next thing was striking as well and likely will have just as much an impact – being able to publish the model to SharePoint and other people being able to access it from a URL. So normal people (and not just data geeks like me) will be able to start with vast amounts of data, build a model (without even realizing it!), analyze the data, and post the result (with all the data) to share with others.
Released on the next version of SQL Server code named Kilimanjaro which is a focused release of new BI capabilities. It will be available sometime in the first half of calendar year 2010.