Information, tips, news and announcements about SQL Server Analysis Services and PowerPivot directly from the product team.
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.
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:
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:
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:
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.
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:
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.
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.
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!
(Gemini/PowerPivot/VertiPaq in german)
Thanks for this good article on comparison between Analysis Services and PowerPivot.
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?
Can you please compare PowerPivot and SSAS against
Qlikview and Spotfire?
They are among BI leaders, both are very
Excel-friendly, both have a mature in-memory
columnar database, excellent set of Visual Controls
and API, easy-to-use Zero-Footprint AJAX-based
WebClient, superior Development and Management tools,
superior scalability and enterprise-ready
Very intelligent treatment of the topic. Thanks a lot.
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
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.
Thank you for the good article.
It's a good approach to publish Team BI solution on an intranet portal like Sharepoint, Moreover Sharepoint also offers excellent components like analytical grid/chart etc, which also connect excellent to Analysis Service databases. Existing Analysis Service models may be leveraged thru an extended dimension model, which provide the end user with interactive analytical capabilities without programming. (example: SSAS ValuePack on www.ibax.ch)
This approach allows to deliver Team BI solutions on the common base of the existing shared dimensions using the same technology/language as used for the well integrated Operational BI.
Regarding Personal BI it's not to forget that an effective use of Excel PowerPivot requires access to good quality and well-structured data source, like its usually provided by a data warehouse or datamart.
Using Analysis Services or PowerPivot directly on top of one or multiple operational source systems becomes usually very time-consuming and complex at an unsufficient level of integration (single version of truth) and data quality.
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.
how can i automatically refresh a cube.when data updates in the table,it should affect the cube also.how can i done that
Thanks a lot for this clarifying explanations .