Compare Analysis Services and PowerPivot
NOTE: This page was translated from this Spanish version by St. Joseph Lopez.
 |
This article is available in other languages: |
More and more customers are getting to know about PowerPivot and seek more guidance on when to use it with SQL Server Analysis Services. This post should help see in the world of the Microsoft BI how both SQL Server Analysis Services and PowerPivot help to meet the needs of enterprise businesses, as well as to discover the key differences between each technology.
Microsoft wants to give support to the three levels so they can make decisions: employee (staff BI), team (Team BI), and enterprise-wide (Organizational BI).
BI in the enterprise
BI has traditionally focused on companies to allow a wide range of activities that include data (ETL) integration and management, development and sharing of standardized indicators (KPI's), consistent notification, as well as analysis and forecasting using data mining techniques. Commonly, these solutions have the following characteristics:
- Provide a global and coherent vision of corporate data, and the standard definitions of measures and key performance indicators. As such, the construction of these solutions requires sophisticated data clean-up operations by heterogeneous data sources.
-They develop and closely managed by dedicated IT staff.
Often have large volumes of data and / or a large number of users, and are integrated into platforms that offer high availability, scalability, performance and advanced management of resources.
It is the complex of the business logic and security, which in turn requires a comprehensive data model that allows the expression of this logic.
BI staff
Individual users of BI needs are more immediate and concrete than necessary for the entire organization.In general, these solutions share the following characteristics:
- They are built and managed by the workers of the information (IWS) in a very agile manner, without significant dependence on their IT departments.
The IWs prefer to build these solutions using the tools with which they are very familiar. In fact, often more controlled environments data users give a tool like Excel, so that they can continue to work with more convenience and flexibility.
The data model evolves very dynamically to the IWS, discover new sources of data, and create new calculations to add value for the analysis, is a fundamental part of their daily work.
Data sources used for these solutions vary from those used by the Organization, since they exist only on the desktop of the IWS. Users often use data from your property and can be changed instantly to represent feacientemente the scenarios for which is making a forecast, rather than those that are consistent across the organization.
The volume of data is typically not as large as the solutions implemented by enterprise solutions.
The main objective of these solutions is to empower end users so that they can make the best decisions for themselves.
BI in work teams
BI solutions aimed at the smaller working teams have characteristics both personal and business bi, and technology and the processes used can be comprehensive to meet these needs.In general:
- These solutions are created and managed by IWS to share their work to others within your team or as a small project of TI.
The volumes of data and multiple needs of the users of these solutions are usually more than those required by IB staff and less than necessary for enterprise-wide solutions.
The processes used tend to be more complicated that exercised by the IWS in their personal solutions that they require fewer resources and faster than the range solutions organization
The consistency of data and the terms of the entire team, also require more effort than a personal solution, but children usually involve one much smaller than the business solution effort.
These solutions are often managed by managers of the teams, and are often deployed with technologies that provide management capabilities, which extend across many solutions.However, they need you visibility and control, for the correct use of the assets within the organization.
As we see, at one extreme we have the design throughout an organization aligned with the other, it is intended to give more power to end users so they can make decisions using the information available to them.With this understanding, it enables you to review several analyses of service offerings.
Analysis Services Offerings
As a central component of the stack of Microsoft BI solutions, Analysis Services provides all the needs described above. To achieve this, Analysis Services technology has available the following complementary offerings:
Considerations |
SQL Server Analysis Services |
PowerPivot for Excel |
PowerPivot for SharePoint |
| Development and management tools | Develop solutions 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.
|
Administration tools integrated into SharePoint.
IWS management books that have been published on a SharePoint site, and can automatically schedule the update of data. Keep track of how the data are being used in your organization through a management dashboard. |
| Data model
|
Database design is carefully planned and maintained, with a focus on the normalization of the visible artifacts for users. Schema changes occur slowly and are deployed through a deliberate process that often includes changes in ETL processes, as well as the reports of dependents. |
PowerPivot for Excel supports a set of interactive data-driven experience of modelling, where users can enter new data sources or expand its model in a smooth manner. | PowerPivot for SharePoint is designed to share and update data, without any schema changes. |
| Sources of data
|
Corporate use of data sources; significant data cleanup activities can occur during the load of Windows data using professional tools for ETL, 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 also provides specific support for these. | IWS can periodically schedule updated source data using SharePoint. |
| Scalability of data bases and the size of them. | Designed for scalability and central management.
It designs and supports some carefully planned databases; each is very large, with many concurrent users. |
Each user owns and maintains the books that contain PowerPivot data on your computer.
During its use, all PowerPivot data in a work book is loaded into memory and must be less than 4 GB that have been saved correctly. PowerPivot data normally occupies less disk space than in memory. |
It supports many small databases, with many concurrent users in each case. During its use, all PowerPivot data in a book is loaded into memory.
Each book must be less than 2 GB. |
| Data security
|
Very sophisticated dimensions and cells to read security and rewriting operations. An audit of it can access the information. | PowerPivot data in the book depends on the user. | Access to the published books is controlled through the use of SharePoint security on the site or document level. You can monitor who can access each book. |
| Programmability
|
Extensive API for the creation of programs and management of objects exists, with the rich ecosystem of client applications. | End-user oriented tool; does not give the API. | Query API allows any Analysis Services client to consume PowerPivot data. |
The Analysis Services Administrator's Guide for implementing BI solutions
Depending on the magnitude and scope of the solution of business intelligence that you or your organization carried out, the administrators of Analysis Services administrators can be responsible for one or all the following implementation types:
Product |
Description |
SQL Server Analysis Services |
An installation of Analysis Services centralized in the organization. Development is done using Business Intelligence Development Studio (BIDS) in Visual Studio; the management is through SQL Server Management Studio. |
PowerPivot for SharePoint |
As part of PowerPivot for SharePoint, Analysis Services is installed to interact with the PowerPivot data. This instance may not be used to support traditional OLAP models. All this instance management is performed through SharePoint Administration Tools. |
PowerPivot for Excel |
PowerPivot for Excel uses a local instance of Analysis Services to provide the functionality of Excel's memory.
This component is installed as part of the PowerPivot for Excel installation. There is no separate service to manage. |