Install & Configure Power View for SharePoint 2013 and SQL Server 2012 multi dimensional model

In this article, I will show you detailed steps to install and configure Power View for SharePoint 2013 & SQL server 2012 SP1 enterprise editions. This configuration is tricky because, we have to be careful when installing add-ins in SharePoint web front end servers, as add-ins is be based on SQL server version.

High level steps to install & configure Power View with SharePoint 2013.

1. Install SQL Server 2012 enterprise with latest service pack1 which includes CTP3. Add Analysis services multi-dimensional model as feature for the current instance.
2. Reporting services should be SharePoint integrated mode.
3. Remove reporting services native mode if you have already installed in previous installation (optional)
4. Install SharePoint 2013 enterprise edition with latest service pack
5. Install SQL server 2012 SP1 in different instance for SharePoint Power pivot integrated mode.
6. Install SQL Server 2012 SP1 Reporting services add-in in all SharePoint web front end servers
7. Install SharePoint reporting service using PowerShell.
8. Configure/provision SharePoint reporting service application.
9. Install SQL Server 2012 SP1Power pivot add-in for SharePoint in all SP web front end servers
10. Configure Power pivot and provision SP service application.
11.  Check power pivot connectivity by creating sample power pivot report.
12.  Important trouble shooting steps.
    Detailed steps

1. Install SQL Server 2012 enterprise with latest service pack1 which includes CTP3. Add Analysis services multi-dimensional model as feature for the current instance.
   1.1 You can download SP1 from here
   1.2 Make sure you have chosen Analysis Services, this for accessing and manipulating multi-dimensional cube.
   
2. Reporting services should be SharePoint integrated mode.
   2.1 When you install SQL server, you should select the feature ‘Reporting Services - SharePoint’ & ‘Reporting Services Add-in for SharePoint products’
   
3. Remove reporting services native mode if you have already installed in previous installation (optional).
   3.1 Choose respective instance when removing the feature
   
   
   
4. Install SQL server 2012 SP1 in different instance for SQL Server Power Pivot for SharePoint
       4.1. Add account and password, this is very critical, because it will be a execution account and impersonation account when you configure Power pivot service application in SharePoint 2013.
   
   
   
   
   5. Install SharePoint 2013 enterprise edition with latest service pack in SP farm.#

           6. Install SQL Server 2012 SP1 Reporting services add-in for SharePoint 2013 in all SharePoint

               web front end servers. This is very important add-in for the entire configuration to be 

               working fine. Be careful when you download the add-in

                            

                             6.1. SQL Server 2012 enterprise SP1 – download here

                             6.2. SQL Server 2012 enterprise SP2 – download here 

                             6.3. SQL Server 2014 enterprise  – download here

            This add-in should be installed in all SharePoint 2013 web front end servers in the farm.

7. Install SharePoint reporting service using PowerShell (run as administrator)

                7.1. Launch SharePoint 2013 Management Shell

                7.2. Run following commands one after the other

                                     Install-SPRSService

                                     Install-SPRSServiceProxy

              

              

8. Configure/provision SharePoint reporting service application.

          8.1. SharePoint CA-->Manage Service Application--> New--> SQL Server Reporting Services  

                   Service Application

8.2. Make sure that, the service should be started.

8.3. Provide necessary details, based on your environment



9. Install SQL Server 2012 SP1Power pivot add-in for SharePoint 2013 in all SP web front end servers. This is very important add-in for the entire configuration to be working fine. Be careful when you download the add-in

                  9.1.SQL Server 2012 enterprise SP1 – download here

                 9.2. SQL Server 2012 enterprise SP2 – download here

                 9.3. SQL Server 2014 enterprise – download here

10. Configure Power pivot and provision SP service application.

                10.1Make sure that, the service SQL Server PowerPivot System Service should be stated.

          10.1 Launch PowerPivot for SharePoint 2013 configuration tool (run as administrator)

          

          10.2 Provide all necessary information based on your environment topology

        10.3 Click validate

11. Check power pivot connectivity by creating sample power pivot report.
        11.1.Create SharePoint site collection with ‘PowerPivot Site’ as template. CA--> 
        Application Management-->Create Site Collections

        11.2.Make sure that following site collection features are enabled.
        11.3. Browse PowerPivot gallery---> Library Settings--> Advanced Settings--> Allow Management of content types
       11.4. Add from existing content types, choose SQL Server Reporting Services Content Types and add all of them 

               11.5. Files--> New Document--> Report Data Source

11.6. Choose ‘Data Source Type’ as Microsoft BI Semantic Model for Power View
11.7. Connection string to SSAS cube, change according to your environment
Data Source=172.32.6.36;initial catalog=AdventureWorksDW2012Multidimensional-EE;cube='Mined Customers'
11.8. Use credentials as stored credentials
11.9. Test connection
 
11.10. Once done, create Power View report

Cheers!

Vamsi