A quick list of how each of these Microsoft BI tools handles the two data sources “SQL Server” (relational-based) and “Analysis Services” (cubed-based, which can be the multidimensional model or the tabular model).  Note that any reporting tool that supports the multidimensional model (which uses MDX) also can be used against a tabular model (which supports both DAX and MDX):

• 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.  Report Builder uses a MDX query designer to connect to a multidimensional or tabular cube.  There is not a way for Report Builder to use DAX (see Support DMX Queries in Report Builder to support DAX queries with parameters)
• Power Pivot –  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.  Can then create a PivotTable that uses the 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 Power Pivot as essentially a way of making an analysis services cube from a relational source using Excel as the design tool.  Power Pivot uses a MDX query designer to connect to a multidimensional or tabular cube.  There is not a way for Power Pivot to use DAX
• 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 https://www.jamesserra.com/archive/2012/10/using-performancepoint-against-tabular-data/).  PerformancePoint uses MDX to connect to a multidimensional or tabular cube (see CREATING A PERFORMANCEPOINT DASHBOARD USING A SQL 2012 SSAS TABULAR INSTANCE)
• Excel PivotTables – If use “Analysis Services” has the benefit of not needing to create joins as a cube has them built-in.  Excel uses MDX to retrieve fields and data values when you choose items from a PivotTable Fields list that is connected to a multidimensional or tabular cube.  If use “SQL Server”, can select multiple tables and will auto-detect joins if source system has foreign-key relationships (via “Select Related Tables” button on the “Data Connection Wizard” screen).  Make sure to select “Import relationships between selected tables”.  See Create a PivotTable to analyze data in multiple tables Excel 2013
• Power View – Can connect to the Tabular model and just recently to the multidimensional model via SQL Server 2012 SP1 CU4 (see Power View for Multidimensional Models Released!).  Power View uses DAX to connect to a multidimensional or tabular cube (CU4 gives the multidimensional model support for DAX queries).  The Tabular model connection can be to a SSAS tabular model or a Power Pivot workbook.  With Power View Excel 2013 you can import data into a data model and create a Power View report against this data model.  The source of this imported data can be any relational database such as SQL Server, SQL Azure, or Microsoft Access.  In addition, Power View can also use an external data model from an external data source such as other Power Pivot workbooks or SSAS tabular models.
• 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.  SSRS uses a MDX query designer by default to connect to a multidimensional or tabular cube, but you can switch to a DAX query designer via the “Command Type DMX” button (see Create SSRS Report using DAX)

Here a quick list of each reporting tool and whether is works with a relational data model (RDM) and/or a cube:

• Excel PivotTables (SSAS Cube and Relational)
• SQL Server Reporting Services (SSRS) (SSAS Cube and Relational)
• Report Builder (SSAS Cube and Relational)
• Power Pivot (Relational)
• PerformancePoint Services (PPS) (SSAS Cube)
• Power View SharePoint (SSAS Cube and Power Pivot)
• Power View Excel 2013 (SSAS Cube [Tabular only] and Power Pivot)
• Data Mining (SSAS Cube [Multidimensional only])