Comparing Classic Analysis Services and PowerPivot
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.
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 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.
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.
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.
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.
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.
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.
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).