Self-Service BI: Remember to Plan for the Back-End Infrastructure

Self-service business intelligence (BI) is a great thing—it brings the power of analysis to knowledge workers and decision makers who can make use of it directly. However, after hearing Microsoft’s push on self-service BI a few times and comparing that vision to the reality that most businesses are not heavily invested yet in BI, I’m sensing a disconnect. I’m concerned that there’s an implication that you can just get these new products and your BI is good to go. For example, there has been a lot of emphasis on the SQL Server 2008 R2 PowerPivot for Excel front-end tool (which is truly pretty cool). However, there’s more than a little hand waving over that ugly infrastructure part in the back where all the BI data comes from. It would be a bit too easy for an IT manager or C-title to hear about self-service BI or see a demo and think that by getting PowerPivot they are going to get instant BI. Experienced DBAs and BI professionals know it isn’t really that simple, but management could easily overlook the behind-the-scenes work in their enthusiasm to bring BI into their organizations.

Building the Back End Infrastructure

Building a BI infrastructure requires both careful planning and design. The SQL Server 2008 and 2008 R2 Enterprise and Datacenter editions provide the BI subsystems required to build this infrastructure, but someone still has to design the data warehouse and its SQL Server Analysis Services (SSAS) databases. This requires an understanding of OLAP structures like cube design, as well as an awareness of the business and the types of information that knowledge workers will need. SSAS and the BI Development Studio (BIDS) provide the tools, but the knowledge of what to build must come from your own organization. The information required by the business will guide your decisions about how to build the data warehouse. After the data warehouse is built, you need to design and implement an extraction, transformation, and load (ETL) process that will load your data warehouse with OLTP data. SQL Server Integration Services (SSIS) provides this capability. But there’s more: Remember that to implement self-service BI you’ll need an infrastructure upgrade in addition to desktop updates for the workers who will make use of it. On the infrastructure side you’ll need SQL Server 2008 R2 in order to get PowerPivot. Plus, you’ll need SharePoint 2010 in order to be able to archive and manage these PowerPivot workbooks. On the client side you’ll need Office 2010. For more on self-service BI see the October 2009 SQL Mag article “Donald Farmer Discusses the Benefits of Managed Self-Service BI,” Instant Doc 102613.

 The Devil Is in the Details

The job of a Microsoft product manager is to make new features in the product look easy and appealing. However, at the same time Microsoft tends to downplay the fact that most companies are not early adopters of new technologies. Plus, Microsoft likes to create an image of a world in which customers migrate and quickly master their new technologies. While some leading-edge customers fit this image perfectly, most businesses aren’t there yet. Most companies prefer to be latecomers to technology. They (wisely) adopt new technologies after most of the early issues have been worked out. For instance, our SQL Mag Instant Polls and your letters and feedback show that most companies are on SQL Server 2005—not SQL Server 2008 and certainly not SQL Server 2008 R2. Microsoft tends to smooth over the fact that the adoption of new technologies like PowerPivot would take quite a bit of work for most companies, and Microsoft can downplay discussing the devil in the details, which is the background work required to make the demo magic happen.

 PowerPivot is Cool: So Prep for It

Don’t get me wrong. I think PowerPivot and the concept of managed self-service BI are both ultra cool. They bring the power of data analysis into the hands of the business information workers and they allow IT to manage and protect resources like Excel spreadsheets better than ever before. However, don’t think this is going to automatically happen after a new product install. To really get the benefits of these tools you need to acquire BI expertise and have a backend BI infrastructure in place. Tools alone won’t make that happen. The missing link in self-service BI is the backend your DBAs and BI professionals need to build.