Export (0) Print
Expand All

Comparing Classic Analysis Services and PowerPivot

SQL Server 2008 R2

This section describes the business value for each of the Analysis Services business intelligence (BI) offerings, provides an overview of how the different BI solutions complement each other, and describes the role for the IT or OLAP administrator in each type of BI solution.

The business intelligence continuum

Comparison of Analysis Services and PowerPivot

Summary of deployment requirements for administrators

BI provides business insights to all employees leading to better, faster, more relevant decisions. Microsoft recognizes that decisions are made at employee, team and organization levels, and to be successful, BI technology must support needs across this continuum.

Bi continuum means corporate, team and personal BI

Organizational BI

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

  • BI strives to provide a comprehensive and consistent view of corporate data, including standard definitions of key metrics and performance indicators. As such, building these solutions requires sophisticated data cleansing operations across heterogeneous data sources.

  • Solutions are developed and closely managed by dedicated IT staff.

  • Solutions are built on a platform that provides high availability, scalability, performance, and advanced resource management, all of which are needed to support large volumes of data and multiple users.

  • Solutions can include complex business logic and security, which in turn requires a rich data model that allows expression of this logic.

  • Solutions leverage a mix of custom and industry tools that are built on well-documented programming interfaces.

  • BI requires formal processes for planning, design, and development. Projects require the skills of many IT professionals to execute, and can take significant time to complete.

Personal BI

The BI needs of individual users are typically more immediate and focused than the needs of the larger organization. Individual BI solutions share the following characteristics:

  • Solutions are built and managed by information workers in an agile manner, without significant dependence on IT.

  • Users build solutions with tools with which they are familiar. Frequently, users export data from more controlled environments to a tool such as Excel, so that they can continue working with the data more comfortably and with flexibility.

  • The data model evolves dynamically as users build their solutions, discover new data sources, and add calculations to add value for analysis.

  • Data sources vary, from data sources sanctioned by IT, to data that exists only on the users' desktop. Users often find and use their own sources of data, which enables them to change data to reflect their own scenarios, rather than employing the representation of data that is consistent across the organization.

  • Data volumes are not as large as in solutions deployed across an organization.

The key goal of individual BI solutions is to free individuals to make the best decisions possible, with data that is either owned or controlled by the individual.

Team BI

BI solutions targeted at smaller teams have characteristics of both personal and organizational BI, and the technology and processes used for personal and organizational BI can be extended to fit the team's needs. Team BI solutions share the following characteristics:

  • Solutions are created and managed by users, who share their work with others; however, solutions might also be created and managed by IT in support of small teams.

  • Data volumes and the multi-user requirements fall between the small scale of personal BI and the large volumes of corporate BI.

  • Security specifications vary, from the need to maintain access to individual documents, to a level of sophistication approaching that of corporate security solutions.

  • A greater effort is required to maintain the consistency of data and terms across a team than for personal BI solutions; however, because teams typically have a narrower area of focus, defining and meeting standards within a small group is easier than coordinating data and terms across an organization.

  • Team BI solutions require less intensive management than corporate BI solutions, and are frequently deployed on technologies that support multiple approaches to solution management. However, these management solutions must support the need of IT to monitor how data assets are used within the organization.

As a core component of the Microsoft BI stack, Analysis Services supports the complete continuum of BI needs. To enable BI at multiple levels, Analysis Services provides the following complementary technologies:

  • SQL Server Analysis Services: Market-leading OLAP engine that enables IT professionals to build sophisticated, high-performance solutions for deployment across organizations.

  • PowerPivot for SharePoint: An extension for SharePoint that enables sharing of solutions created by users, and that supports and enriches team BI.

  • PowerPivot for Excel: An add-in for Excel 2010 that helps users to build their own BI solutions.

These solutions extend the reach of Microsoft BI technology throughout the enterprise and integrate solutions at all levels.

To better understand which solution might be appropriate for your BI need, you should first understand where your solution lies on the BI continuum: are you creating a solution for yourself, a small team, or a larger organization? After you have answered this question, you can use the following chart, which summarizes the differences between the Analysis Services BI offerings, to determine which technology best supports your requirements:


SQL Server Analysis Services

PowerPivot for Excel

PowerPivot for SharePoint

Database management and solution development tools

Solutions are developed in Business Intelligence Development Studio and managed in SQL Server Management Studio.

IT monitors how data is being used within their organization through a management dashboard."

These tools are designed for IT professionals.

Solutions are developed in Excel 2010.

All management tools in SharePoint. The administrator can manage workbooks that have been published to a SharePoint site, and can schedule automatic data refresh.

Data model

Database design is carefully planned and maintained, with a focus on standardizing what is shown to users. Changes to the schema occur slowly and are deployed through a deliberate process, which frequently includes changes to ETL processes and dependent reports.

Provides an interactive data-driven modeling experience, which enables users to bring in new data sources and extend their model, without restrictions.

Supports the sharing of data and automatic or manual refresh of data. Sharing and refresh does not affect or modify existing schemas.

Data sources

Corporate data sources are used. Significant data cleansing activities may occur during data loading windows. Data cleansing is typically performed by using professional ETL tools such as SQL Server Integration Services.

Some data sources may be managed by IT. Local data sources are also very common, such as text files, Excel workbooks, and Access databases.

PowerPivot for Excel provides specific support for these sources.

Users can schedule periodic refresh of their data by 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 multiple concurrent users.

Each user owns and maintains workbooks that contain PowerPivot data. The user can save and store the solution on his own 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 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.

Data security

Highly sophisticated dimension and cell-level security for read and writeback operations. IT can audit 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 is accessing which workbooks.


Extensive API for programmatic creation and management of objects.

No APIs are provided; all processes run in Excel. However, the client and server communicate by using the open XMLA format.

There is no programmable interface. Solutions are developed by using the PowerPivot for Excel client, and managed through the SharePoint tool set.

Depending on the scale and scope of the BI 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 organization BI.

You perform development by using Business Intelligence Development Studio.

You perform management by using SQL Server Management Studio.

PowerPivot for SharePoint

Analysis Services is installed as part of PowerPivot for SharePoint, for interacting with PowerPivot data.

You cannot use this instance to support traditional OLAP models.

You manage this instance through the SharePoint administrative tools.

PowerPivot workbooks

PowerPivot for Excel uses a local, in-memory instance of Analysis Services to PowerPivot functionality within Excel. 

This component is installed as part of PowerPivot for Excel installation.

There is no separate service to manage and you cannot manage or access this instance programmatically.

Side-by-Side Installation

You can install different types of Analysis Services instances side-by-side on the same computer. That is, you can install SQL Server 2008 R2 Analysis Services and SharePoint 2010 with PowerPivot for SharePoint on the same server, each in its own instance. For more information, see Install PowerPivot for SharePoint and Analysis Services.

For more information about managing instances of Analysis Services that are integrated with SharePoint 2010, see Server Administration (PowerPivot for SharePoint).

Community Additions

© 2014 Microsoft