SQL Server 2012 Power View Installation with Sharepoint 2010

Oh Oh (Jaane Jaana the song just came to my mind)..... Installing powerview on Sharepoint 2010 with SQL server 2012 turned out to be a nightmare. Since I was doing the install on my local m/c, things became more harder than usual as I do not have enough memory to scatter around. So let me explain the steps I went through and the effort I had to exert in playing around with PowerView.
      I had SQL Server 2008 R2 installed with Sharepoint 2010 on my machine. Just for folks out there who do not want to uninstall their current SQL server version and especially if it is 2008, ensure that you install SQL Server 2008 SP2 before installing SQL Server 2012 (I am going to refer to it with the term "Denali" from this point on). If you do not install SP1 and SP2 prior to the Denali install you are in for one heck of a ride. Mark my words please do the installs prior to installing Denali. If you do install Denali without upgrading SQL server 2008 to SP2, ensure that the Denali installer does not pop up while performing the SP2 install (which it does and leads to the issue: Another installation is currently taking place, cannot perform the SQL server 2008 SP2 install). This took me three attempts to finally get SP2 installed after installing Denali in an a prior step. There is a reason behind this and I will explain as I go on..... While installing Denali, be careful at two steps:
  1. You will have to install the Analysis Services twice. Once for the common multidimensional mode that we all use and love. And the other for the Tabular model which basically represents your Dimensional model in a flattened table format.
  2. The next and most important step is while installing the Reporting Services, Ensure that you unselect the feature Reporting Service Native Mode while having the other two features Reporting Service in Sharepoint integrated mode and Reporting Service Add in for Sharepoint checked. Believe me you will have to uninstall Reporting Service and reinstall if you are not careful at this juncture. Denali does not provide you the option of shifting between modes like the good old SQL Server 2008 does with the Reporting Service Configuration manager.
Once you have these steps in place, you will have to upgrade the SHAREPOINT Server instance (this gets created while installing Sharepoint and contains all the metadata related databases that Sharepoint has) from SQL server 2008 to Denali. Remember in case you do not have SP2 installed prior to this step you will run into a brick wall and you have to install SP2 at this stage.
     &You will have to setup the SSRS application service:
1.Run the Microsoft SharePoint 2010 Management Shell as Administrator
2.Type the following PowerShell command> Install-SPRSService
3.Type the following PowerShell command> Install-SPRSServiceProxy
4.Now to start the service type the following PowerShell Command> get-spserviceinstance -all |where {$_.TypeName -like “SQL Server Reporting*”} | Start-SPServiceInstance
      Now it is time to create an SSRS reporting services application in Sharepoint 2010 Central Administration. My suggestion is to go ahead with a new application pool but you just need to have the reporting services application created with the root site that this application will be mapped to checked. Note:The reporting services application can be created by navigating to Central Administration > Application Management > Manage Services Application
After this step go to Site Actions in Central Administration, select Site Settings > Site Collection Features and enable the following features:
  1. Power View Integration Feature
  2. Report Server Integration Feature
  3. Reporting
You are good to go and deploy "*.RSDS" i.e the shared reporting services data sets or the "*.bism" i.e. the business intelligence semantic model files to Sharepoint 2010 and play around with Denali's Crescent or Powerview. It took me literally a day to work around these issues along with the fact that my system is chugging along at a snail's pace. But yup.....got Powerview installed!!!!!!!! Oops jumped the handlebar too soon on this one. The BISM cannot be created at this stage unless you install powerpivot and configure it correctly. I will probably do a writeup on this soon. Now let us go ahead with the powerpivot installation steps.... As the technet website for Microsoft states that there are two wsp files for powerpivot that comes along with the Denali install but I am not able to find these anywhere. But not to fret, these were the steps I took to ensure that a powerpivot service application was successfully created.
1. Apply Sharepoint 2010 SP1 if it has not been applied yet.
2. Download Powerpivot excel addin for Sharepoint 2012 and install. Create a new database server using the Denali installation files but during the setup mode, choose the database for powerpivot integration with sharepoint as the option and then go ahead with the install. It will create a POWERPIVOT service which will contain all the powerpivot metadata databases.
3. Next step, once this install is successfull. Go ahead and create your secure store service application. (This is in Sharepoint Central Administration --> Manage Service Applications)
4. Once your Secure Store Service is created. Go to the Denali folder (Microsoft SQL Server 2012 folder in your windows startup) and select the PowerPivot configuration tool under configuration tools. Run the configuration utility (which now deploys the powerpivot wsp solutions to Sharepoint 2010). This will also create the powerpivot service application in Sharepoint 2010.
    Navigate back to you Sharepoint document library and you will have an option for the BISM now.

Couple of notes that you need to keep account of:
A] Powerview does not work with Sharepoint when Sharepoint is in a standalone mode. This is because the powerpivot installation works only when Sharepoint is in a farm mode.
B] Ensure that you work within the corpnet of the client or company where powerview will be used.
C] Ensure that Silverlight 5 is installed as Powerview uses Silverlight 5 and above.
D] Last but not least never give up and you will eventually succeed in performing this task ;-)


ganesh said…
Such a good post. I loved it and made me even more curious to try denali.
How ever i have couple of questions and would love denali even better if it can satisfy my needs.

1) We have a huge list of reports developed using 2008R2 and they are deployed on to the server. We refer the deployed rdl files on our aspx with right parameters to show the developed report on a web page. Just wondering if this can be achieved using denali / power view.

2) I tried installing sharepoint on my windows 7 and the installation failed because its not a server. How do i approach this in order to demo it to our clients with windows 7 ON.
Ishwar Nataraj said…
Hi Ganesh,
For point 2:
So for Win 7 installation you got to change a couple of settings before going ahead with SharePoint 2010 installation. The best location for that info is at the MSDN site and the location is as follows:
For point 1:
Just to point out powerview usage --> it works in a flattened format so basically you are recreating a tabular cube so to speak (rather than the conventional multidimensional mode). Powerview basically works like a pivot excel on top of the tabular structure so you can add parameters as filters and the reports should work fine. Does that answer your question?

Pallavi said…
Very good post. Thanks for sharing.

However i am getting the below error while creating Power View report. "An error occurred while loading the model for the item or data source 'http://demo/PowerPivotGallery/test.xlsx'. Verify that the connection information is correct and that you have permissions to access the data source."

Please let me know the troubleshooting steps for this if you have. I have SharePoint 2010 installed on my machine with SQL server 2012 R2.

Thanks in Advance,

Popular posts from this blog

System.ConfigurationSettings.AppSettings is Obsolete

Branding your SharePoint site in a super fast way - Emgage

Power BI To Embed Or Not To Embed