Data warehousing, OLAP, and Analysis Services for SharePoint 2010

 

Applies to: SharePoint Server 2010

This article describes data warehouses, OLAP, and Microsoft SQL Server Analysis Services (SSAS). It discusses how a data warehouse and SQL Server Analysis Services relate to the business intelligence application services in Microsoft SharePoint Server 2010. Additionally, the article discusses when you would use PowerPivot rather than SSAS.

Overview of data warehousing, OLAP, and PowerPivot and relation to SharePoint 2010

What is a data warehouse?   A data warehouse is a database that functions as a repository for storing and analyzing numeric information. Core data in the data warehouse are typically numeric values that can be summarized or aggregated and are stored in a different structure than a typical transactional database structure. One reason a database warehouse structure differs from a transactional database structure is that pulling data can otherwise be very resource-expensive. Data warehouses enable you to store aggregated data instead of performing time and resource-sensitive ad-hoc queries to return summed values, as you would perform in a transactional database to create a report. This simplified definition is explained better in many books written for data warehouse professionals.

What is OLAP and how does it relate to a data warehouse?   The term online analytical processing (OLAP) usually refers to specialized tools that make warehouse data easily available. An OLAP cube is a logical structure that defines the metadata. The term cube describes existing measure groups and dimension tables and should not be interpreted as having limited dimensions. A cube is a combination of all existing measure groups. A measure group is a group of measures that match the business logic of the data and is another logical structure that defines metadata so that client tools can access the data. Each measure group contains the detail values that are stored in the fact table (copied or dynamically retrieved values). OLAP cubes contain lots of metadata; metadata in its simplest definition is data about data. Multidimensional expressions, or MDX, is a metadata-based query language that helps you query OLAP cubes.

What is SQL Server Analysis Services (SSAS) and how does it relate to OLAP?   Microsoft SQL Server Analysis Services (SSAS), formerly known as OLAP Services, provides server technologies that help speed up query and reporting processing. Analysis Services implements OLAP with technologies that simplify and quicken the process of designing, creating, maintaining, and querying aggregate tables while avoiding data explosion issues.

How do PerformancePoint Services and Excel Services relate to data warehouses, OLAP, or SSAS?  Complex queries on OLAP cubes can produce business answers much faster than the same query on OLTP relational data. The data structures are different and used for different purposes. SSAS OLAP cubes are better for aggregating and reporting on data. SQL Server Analysis Services data supplies business intelligence authoring tools such as Microsoft Excel, PerformancePoint Dashboard Designer, and Visio with an OLAP data source.

What is PowerPivot and how does it relate to SSAS? Microsoft SQL Server 2008 R2 PowerPivot for Microsoft Excel 2010 is an extension to Microsoft Excel that adds support for large-scale data. It has an in-memory data store as an option for SQL Server Analysis Services. Multiple data sources that can be merged include corporate databases, worksheets, reports, and data feeds. PowerPivot data that is inside an Excel workbook is detected, extracted, and processed separately on Analysis Services server instances within the farm. Then Excel Services in SharePoint gives the presentation layer in a browser window. For more information, see www.powerpivot.com.

When do I use PowerPivot versus SSAS? SSAS is an OLAP engine available for IT professionals to build sophisticated, high-performance solutions to deploy across the organization. Similarly to Excel, PowerPivot for Excel is for the information workers that build BI solutions for themselves instead of for the organization. The PowerPivot file can then be published to SharePoint Server or SharePoint Foundation for the team. To learn more about the differences, see the PowerPivot Team Blog post Comparing Analysis Services and PowerPivot (https://go.microsoft.com/fwlink/p/?LinkId=192047).