“Donald Farmer discusses the benefits of managed self-service BI”, SQL Server Magazine (http://www.sqlmag.com), October 2009, page 12
In general, I thought it was a good article and touched a lot of different topics. The only corrections/thoughts that I had were:
- Donald says “On my desktop machine, which is an 8GB machine, a hundred million rows of data is simply not a problem; billions of rows of data would be possible.” I agree with Donald on lots of things, but I think he got carried away with the capability of the VertiPaq engine. Given any definition of a practical dataset I do not think that billions rows and 8GB can be said in the same sentence. Also, please remember that if you want to get the full benefits of PowerPivot, the file size cannot be larger than 2GB or the file cannot be uploaded to SharePoint.
- In another place, Donald said that we (the PowerPivot SQL Server 2008 R2 installation process) will automatically install and configure SharePoint. All you have to do is “insert your SharePoint disc”. Unfortunately it isn’t quite that easy. What you have to do is insert the SharePoint disc and run the beginning of the SharePoint installation yourself (when SharePoint is laying down its bits) – and then you stop before you run any of the configuration wizards. Then you run SQL Server setup and we will configure SharePoint for you. We will turn on services that we feel are appropriate for PowerPivot use, e.g. Secure Store and Excel Services, and configure them as we need. Once the SQL Server setup finishes, you walk away with a fully running and properly configured SharePoint and PowerPivot server. Thus it is totally true as Donald points out, that the installer does not need to know SharePoint or how to set SharePoint up properly for PowerPivot. We do it all for you.
- At one point, towards the end, Donald pointed out that PowerPivot will consume data from any source that provides an OLEDB provider or ODBC driver. This is an important point not picked up by the SQL Magazine interviewers. SSAS has high demands for the SQL source that it is pulling data from. It has a limited number of ‘cartridges’ that it supports. PowerPivot on the other hand works with tabular data – thus it has a much wider range of data sources that it can pull data from.
- Finally, I don’t think that the article drilled deep enough into the notion of ‘managed self-service’ instead of just ‘self-service’. I think that this is an important point that was not expanded upon by the interviewers. To be able to manage the self-service environment IT needs insight into how the IW end-users are using the system. That is what is missing in the current spreadmart or “spreadsheet hell” world of today. To properly manage any infrastructure you have to know the goals, objectives and uses of it. To this end, PowerPivot implements and extensive ‘usage collection’ subsystem. For the geeks of us, look for a “A Peek Inside” article on the type of information we collect and how you might use it to better manage the PowerPivot infrastructure.
All-in-all I thought it was a great article and Donald did an outstanding job articulating the PowerPivot value proposition. Take a a look at the full article. You will enjoy it – I did!