A quick list of how each of these Microsoft BI tools handles the two data sources “SQL Server” (relational-based) and “Analysis Services” (multidimensional-based):

• Report Builder – Using “SQL Server”, auto-detects joins if source system has foreign-key relationships (by selected “Auto Detect” relationships on the “Design a query” screen).  Otherwise will have to create your own joins.  If use “Analysis Services”, will get a different query designer, and has the benefit of not needing to create joins as a cube has them built-in
• PowerPivot –  Using “SQL Server”, auto-detects joins if source system has foreign-key relationships (via “Select Related Tables” button on the “Table Import Wizard” screen).  Otherwise will have to create your own joins.  If use “Analysis Services”, will get a different Table Import Wizard, very similar to one in Report Builder, and has the benefit of not needing to create joins as a cube has them built-in, but the result returns just one flattened table.  I like to think of PowerPivot as essentially a way of making an analysis services cube from a relational source using Excel as the design tool
• PerformancePoint – If use “Analysis Services” has the benefit of not needing to create joins as a cube has them built-in.  “SQL Server” can only be used to represent tables as KPIs on scorecards or have them appear as data values within filters (see http://www.jamesserra.com/archive/2012/10/using-performancepoint-against-tabular-data/)
• Excel Pivot Tables – If use “Analysis Services” has the benefit of not needing to create joins as a cube has them built-in.  If use “SQL Server”, can only use one table
• Power View – Can only connect to the Tabular model and the multidimensional model (which is in CTP, see Microsoft SQL Server 2012 With Power View For Multidimensional Models CTP)
• Visual Studio Reporting Services (SSRS) - Using “SQL Server”, auto-detects joins if source system has foreign-key relationships (when adding tables on the “Query Designer” screen).  Otherwise will have to create your own joins.  If use “Analysis Services”, will get a different query designer, very similar to one in Report Builder, and has the benefit of not needing to create joins as a cube has them built-in