As more customers are getting aware of PowerPivot, they’re looking for more guidance on when to use it vs. SQL Server Analysis Services. This post is our view of the BI landscape and how SQL Server Analysis Services and PowerPivot satisfy needs across the organization. After going through it, hopefully you’ll also get a deeper understanding of the key design points for each.

The Business Intelligence Continuum

In our view, Business Intelligence (BI) provides business insights to all employees leading to better, faster, more relevant decisions. Microsoft recognizes decisions are made at employee, team and organization levels, and that in order to be successful, BI technology must support needs across this continuum.

Organizational BI

BI has traditionally focused on organizations to enable a broad range of activities including data acquisition and management, development and sharing of standardized metrics, consistent reporting, analysis, and prediction. In general, such solutions have the following characteristics:

• They strive to provide comprehensive and consistent view of corporate data, and standard definitions of key metrics and performance indicators. As such, building these solutions requires sophisticated data cleansing operations across heterogeneous data sources
• They are developed and closely managed by dedicated IT staff
• They frequently have large volumes of data and/or large number of users, and are built on platform that provide high availability, scalability, performance, and advanced resource management
• They include complex business logic and security which in turn requires a rich data model that allows expression of this logic
• They frequently leverage a mix of custom and industry tools that are built on well documented programming interfaces

These solutions frequently undertake formal design, development and deployment processes that span the organization and can take significant time to complete, for good reasons.

Personal BI

The BI needs of individual users are typically more immediate and focused than those required for the larger organization. In general, these solutions share the following characteristics:

• They are built and managed by Information Workers (IWs) in a very agile manner, without significant dependence on IT
• IWs prefer to build these solution using tools they are very familiar with. In fact, very frequently users export data from more controlled environments to tool like Excel so they can continue working with it more comfortably and flexibly
• The data model evolves very dynamically as IWs work through building their solutions, discover new data sources, and add new calculations to add value for analysis
• Data sources used for these solutions vary from those sanctioned by IT to those that exist only on the IW desktop. Users frequently use data they own and can change instantly to represent the scenarios they're envisioning instead of those consistent across the organization
• Data volumes are generally not as large as those in solutions deployed across organizations

The key goal of these solutions is to empower end users to make the best decisions for themselves.

Team BI

BI solutions targeted at smaller teams have characteristics of both Personal and Organizational BI, and the technology and processes used for either can be extended to cater to this need. In general:

• These solutions are created and managed either by IWs sharing their work to others within their team or as a small project by IT
• The data volumes and multi user requirements for these solutions are typically more than those required by personal BI and less that those required for organization-wide solutions
• Security specifications vary from a very coarse document-level to those that come close to 0sophistication of organization-wide solutions
• The processes used are typically more involved than those exercised by an IW for their personal solutions, but require less resources, and complete faster than organization-wide solutions
• Consistency of data and terms across the team also require more effort that a personal solution but the smaller focus typically implies much less effort than that of the organization-wide solutions
• These solutions are frequently managed less closely as the organization-wide ones, and are frequently deployed on technologies that provide management abilities which span across many solutions. However, IT needs visibility and monitoring of how data assets are being used within their organization.

As you can see, one end of the continuum is designed for an aligned organization and the other end is all about empowering end users to make decisions in a way they can best use all the information available to them. With this understanding, lets review various Analysis Services offerings.

Analysis Services Offerings

As a core component of the Microsoft BI stack, Analysis Services supports the continuum of needs described above. In order to accomplish this, Analysis Services technology is available in the following complementary offerings:

• SQL Server Analysis Services: market-leading OLAP engine available for IT professionals to build sophisticated, high performance solutions for deploying across the organization.
• PowerPivot for SharePoint: SharePoint extension which enables sharing of solutions created by IWs, in turn enabling Team BI.
• PowerPivot for Excel: an addin for Excel focused on IWs building BI solutions for themselves.

These technologies extend the reach of Microsoft BI technology throughout the enterprise in an integrated manner. In order to understand which product may be appropriate, users must first understand where their needs lie on the BI continuum. The following chart summarizes the differences between these offerings to highlight how they target the requirements described above.

Administrator's Guide to Deploying Analysis Services BI Solution

Depending on the scale and scope of the business intelligence solution that you or your organization implements, Analysis Services administrators may be responsible for one or all of the following deployment types:

Side-by-Side Installation

Customers can also install different types of Analysis Services instances side-by-side on the same computer. That is, they can install SQL Server Analysis Services and SharePoint 2010 with PowerPivot for SharePoint on the same server, together with Excel and PowerPivot for Excel on the same machine.

Summary

We hope this post clarifies how we view the landscape and the goals of each product. Feedback welcomed!