Building Integrated SQL Server Business Intelligence (BI) Solutions with Microsoft Office 2007

Problem
I am trying to understand the BI capabilities built in to the various components of Microsoft Office 2007 and figure out how to leverage them to create BI solutions for our business users.  Can you provide an overview of the capabilities?

Solution
As a starting point let's assume that you have a data warehouse built on SQL Server 2005 or SQL Server 2008 including some SQL Server Analysis Services (SSAS) cubes.  We'll frame our discussion of the BI capabilities in Microsoft Office 2007 with the following diagram: