Freigeben über

Comparing Analysis Services and PowerPivot

  • Artikel

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.

BIContinuum

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.

Considerations

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.

Data model

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.

Data sources

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.

Data security

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.

Programmability

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:

Product

Description

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.
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!

Comments

  • Anonymous
    March 14, 2010
    http://schindler-it.com/blog/?p=37(Gemini/PowerPivot/VertiPaq in german)
  • Anonymous
    March 15, 2010
    Thanks for this good article on comparison between Analysis Services and PowerPivot.
  • Anonymous
    March 16, 2010
    Keep up the good work. With regards to the continued development and improvement of PowerPivot - when is the next update?!  I have a known bug (nuisance) that is reportedly fixed but because I have the public generic download, I am out of the loop on builds.Also, from my readings, it is mentioned that there is no 3-D table interpretation, ie, Table 1 linked to Table 2 and Table 2 is linked to Table 3 but I can get interpret data between Table 1 and Table 3  (my interpretation is a bit rough but I hope you get the idea).  Will this be addressed (and if so when) in future roll-outs?
  • Anonymous
    March 23, 2010
    Can you please compare PowerPivot and SSAS againstQlikview and Spotfire?They are among BI leaders, both are veryExcel-friendly, both have a mature in-memorycolumnar database, excellent set of Visual Controlsand API, easy-to-use Zero-Footprint AJAX-basedWebClient, superior Development and Management tools,superior scalability and enterprise-readyData Security:http://www.qlikview.com/us/explore/productshttp://spotfire.tibco.com/products/overview/analytics-products.aspx
  • Anonymous
    March 23, 2010
    Very intelligent treatment of the topic. Thanks a lot.
  • Anonymous
    March 24, 2010
    This looks great. The one part I would like to understand is how would one be able to use power pivot to cache stuff already sitting in Analysis Services
  • Anonymous
    June 08, 2010
    What do you think about BI4Dynamics and PowerPivot. BI4Dynamics is completaly based on Microsoft technology and works perfectly with Excel, SharePoint and other Microsoft products.www.bi4dynamics.com
  • Anonymous
    July 28, 2010
    The comment has been removed
  • Anonymous
    September 02, 2010
    See:blogs.forrester.com/.../10-09-01-not_all_in_memory_analytics_tools_are_created_equal
  • Anonymous
    September 27, 2010
    Very good article!It is worth taking a look on Kyubit AnalysisPortal www.kyubit.com,which offers interesting AnalysisServices OLAP analysis and visualization web client.
  • Anonymous
    December 23, 2010
    how can i automatically refresh a cube.when data updates in the table,it should affect the cube also.how can i done that
  • Anonymous
    June 14, 2015
    Thanks a lot for this clarifying explanations .