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.
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.
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.
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.
SQL Server Analysis Services
PowerPivot for Excel
PowerPivot for SharePoint
Development and management tools
Solutions are developed in Business Intelligence Development Studio and managed in SQL Server Management Studio. These sophisticated tools are designed for IT professionals
Solutions are developed in Excel 2010.
Management tools are integrated with SharePoint.
IWs manage workbooks that have been published to a SharePoint site, and can schedule automatic data refresh.
IT monitor how data is being used within their organization through a management dashboard.
Database design is carefully planned and maintained, with a focus on standardizing artifacts visible to users. Changes to the schema occur slowly and are deployed through a deliberate process that frequently includes changes to ETL processes as well as dependent reports.
PowerPivot for Excel supports an interactive data-driven modeling experience where users can bring in new data sources or extend their model fluidly.
PowerPivot for SharePoint is designed for sharing and refreshing data without making schema modifications.
Corporate data sources are used, significant data cleansing activities may occur during data loading windows using professional ETL tools such as SQL Server Integration Services.
Some data sources may be managed by IT. Local data sources, such as text files, Excel workbooks, and Access databases, are also very common, and PowerPivot for Excel provides specific support for these as well.
IWs can schedule periodic data source refresh using SharePoint.
Database scalability and size
Designed for scalability and central management.
IT designs and supports a few carefully planned databases, each very large in size, with many concurrent users.
Each user owns and maintains workbooks that contain PowerPivot data on their computer.
When in use, all PowerPivot data within a workbook is loaded into memory and must be less than 4GB to be saved successfully. PowerPivot data typically takes less space on disk than in memory.
Supports many small databases, with many concurrent users for each. When in use, all PowerPivot data within a workbook is loaded into memory.
Each workbook must be under 2GB.
Highly sophisticated dimension and cell level security for read and writeback operations. IT can auditing information access.
Security of PowerPivot data in the workbook is dependent on the user.
Access to published workbooks is controlled by using SharePoint security at the site or document level. IT can monitor who’s accessing which workbook.
Extensive API for programmatic creation and management of objects exists, with rich ecosystem of client applications.
End-user oriented tool, no APIs are provided.
Querying API allows any Analysis Services client to consume PowerPivot data.
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:
SQL Server Analysis Services
An installation of Analysis Services focused on Organizational BI. Development is done using Business Intelligence Development Studio, management is through SQL Server Management Studio.
PowerPivot for SharePoint
As part of PowerPivot for SharePoint, Analysis Services is installed for interacting with PowerPivot data. This instance cannot be used to support traditional OLAP models. All management of this instance is performed through SharePoint administration tools.
PowerPivot for Excel
PowerPivot for Excel uses an local, in-memory instance of Analysis Services to provide the functionality within Excel.
This component is installed as part of PowerPivot for Excel installation. There is no separate service to manage.
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.
We hope this post clarifies how we view the landscape and the goals of each product. Feedback welcomed!