C H A P T E R  8

PowerPivot As a Data Source

If I have seen further it is only by standing on the shoulders of giants.

—Sir Isaac Newton

At the most basic level, there are three tiers that comprise a business intelligence (BI) solution. The most immediately visible tier is the consumption or user interface tier, which provides a means for user actions to be translated into the information presented in filtering, formatting, or both. Examples of tools and products in this tier would include SQL Server Reporting Services, Excel, and dashboards in SharePoint.

The consumption tier can be thought of as a window into the next tier, the data tier. Source data that is derived from or generated by business activity is stored, in a method optimal for servicing analytic queries in the data tier. Products that execute the role of the data tier include Microsoft SQL Server's relational engine and the SQL Server Analysis Services (SSAS) OLAP engine. Examples of the data tier include the AdventureWorks data warehouse used in earlier examples in this book.

The final BI solution tier is the “Extract, Transform, and Load” (ETL) tier. The primary role of the ETL tier is to transport and transform data from source systems into the format required by the data tier. In earlier releases of SQL Server, this role would typically be fulfilled by SQL Server Integration Services (SSIS).

In the preceding chapters, we have focused on the ability of PowerPivot (both for Excel and SharePoint) to fulfill all three tiers. PowerPivot's in-memory SSAS runtime is the data tier. PowerPivot data connections and DAX combine to create the ETL tier. Finally, Excel PivotTables and PivotCharts comprise the consumption tier. PowerPivot can also serve as a building block for other PowerPivot solutions, leveraging, for example, separate, related solutions to create a unified analytical platform.