Monday, December 30, 2013

Step by steps installing the powerpivot for sharepoint 2010

Setup server Join domain: This server will install SQL server 2008 R2, Sharepoint server 2010 (Farm mode) => PowerPivot SQL must be installed in sharepoint farm server.

Enable Application Server  and Web Server (IIS)
Continue add feature Win Server

Installing the SQL server 2008 R2 first (not include PowerPivot feature)
We use same admin account to configure for SQL
Add Current User
Installing Reporting service
Setup sharepoint server: Install software prerequiresites fisrt
Setup Sharepoint server
Input key
Check and Continue
Choose Server Farm
Choose Complete then click click Install Now
Installing finished, set default click close
Click Next
Click Yes
Choose create a new .... then click Next
Input information as below
Input Passphrase then click Next
Click Next
Click Next

Waiting then click Finish

After finish, The central admin site run to configure services, choose yes then click OK
Click Start the Winzard
Choose"Use existing ..." then click Next
Waiting some minutes, then input title, ... and click OK
Configuring finished

Setup SQL PowerPivot: Run SQL  => click to "New Installation"
Click OK
Click Install
Click Next
Click Next
Click Next
Check to " I accept ..." then click Next
Choose "SQL Server PowerPivot ..." and choose Existing Farm then click Next
Click Next
Click Next
Click Next
Click Next
Click to "Use the same ..." then input admin farm account and click OK, continue click Next
Click Add current user.. then click Next
Click Next
Click Next
Click Install
Install comple with failures
Create file notepad, paste content to file then save it to setup100.exe.config and save at Desktop
<?xml version="1.0" encoding="utf-8" ?>
    <disableCachingBindingFailures enabled="1" />
Copy this file  and paste into "C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\x64"

Remove SQL server with Instance is POWERPIVOT

Choose PowerPivot to remove

Only check feature at POWERPIVOT then click Next

Waiting Uninstall finished, then run install sql server 2008 R2 again as Administrator (Right click on Setup.exe and select “Run as Administrator”). Now we can install completed without issue

Once it is done, go to SharePoint Central Administration –> System Settings –> Manage Servers in this Farm. It shows all the servers in the farm and services running. If you see SQL Server Analysis Services and SQL Server PowerPivot System Service, your installation is successful.

Deploying PowerPivot solution packages to Web Application. Go to SharePoint Central Administration –> System Settings –> Farm Management –> Manage Farm Solutions. You should see two solutions
Click to powerpivotwebapp.wsp then choose your web app to deploy
Other Services required
Next step is starting all other necessary services required by PowerPivot. Make sure following services are running:

Excel Calculation Service
Secure Store Service
Claims to Windows token Service (default is stop => must start)
SQL Server Analysis Services
SQL Server PowerPivot System Service
These services are listed under SharePoint Central Administration –> System Settings –> Manage Services on Servers.
Creating PowerPivot Service Application
Go to SharePoint Central Administration –> Application Management –> Manage Service Applications. Click on New and select SQL Server PowerPivot Service Application.
Input Name, should be create new application pool for POWERPIVOT then click OK
Create SQL Server PowerPivot Service Application successful
PowerPivot Feature Integration for Site Collections
This is appeared under Site Collection Features and must be activated for each of the site collections. Go to your site collection and click on Site Actions –> Site Settings –> Site Collection Administration –> Site Collection Features. When the page open, find PowerPivot Services feature integration for Site Collections and activate it.
Configuring PowerPivotUnAttendedAccount for Data Refresh
PowerPivot unattended account is used for refreshing data in the workbook if user credentials are not exist with the workbook. This account is stored in SharePoint Secure Store Service. In order to continue, you need to make sure that Secure Store Service is up and running, and master key is created.
Let’s open the Secure Store Service Application. Go to SharePoint Central Administration –> Application Management –> Manage Service Applications. Click on Secure Store Service (or select it and click on Manage) link
Click to Generate New key
Input PassPhrase then click OK
Click New on ribbon
Input like this then click Next
Click Next
Input farm admin here then click ok
Next is setting credentials for PowerPivotDataRefresh application (or PowerPivot unattended account). Select the check box next to newly created application and click on Set Credentials button. It opens the Set Credentials for Secure Target Application (Individual). Settings for this screen will be;
Input farm admin account and password
Click OK. Now we need to associate the application with PowerPivot Service Application. Go to SharePoint Central Administration –> Application Management –> Manage Service Applications. Find PowerPivot Service Application and click the link (or select it and click on Manage button).
This opens PowerPivot Management Dashboard. Click on Configure Service Application Settings in Actions frame.

 It opens PowerPivot Settings. Find PowerPivot Unattended Data Refresh Account input box under Data Refresh. Type the ID of the application created in Secure Store Service, in my case, it is PowerPivotDataRefresh.
Done. To complete the process, few setting have to be done in Excel Services.
Settings in Excel Services
Go to SharePoint Central Administration –> Application Management –> Manage Service Applications. Find Excel Services Application and open it.
 It opens Manage Excel Services Application. Click on Trusted File Locations.
Click to context menu >> Edit
Workbook Properties: Maximum Workbook Size. Set as you want. Note that maximum file size accepted by SharePoint for both Excel and PowerPivot is 2GB.
External Data: Select Trusted data connection libraries and embedded under Allow External Data.
Click OK to save settings. Now, You should be able to upload an Excel file that contains PowerPivot data into the web application and see.
Done, now we setup office 2010 and download "PowerPivot_for_Excel_x86" and install it
Create database in sql and create Table  with data as below
Create PowerPivot Galarry
PowerPivot Gallary like this

Open Excel application you will see PowerPivot Tab after setup "PowerPivot_for_Excel_x86" successful
Click to PowerPivot Window
Continue click to Ribbon "From Database" >> From SQL Server
Choose Server Name and DatabaseName as below
Click Next
Choose your table then click Finish
Importing success then click close
Data like this
At PowerPivot window >> click PowerTable >> PivotChart
Choose new or existing worksheet
Continue design PoverPivot like this
Click to Data for Sheet Chart 1 >> Click to Tab Data >> Connection
Choose PowerPivotData >> Properties
Save Excel to Sharepoint: File | Save
Upload File PowerPivot excel to PowerPivotGallary sharepoint
We try to Filter like this
Get error

We will delete PowerPivotApplication then create again => it run well
So, we update data at 8:23pm as follow
Go to PowerPivotGalary >>Click to Manage Data Refresh
Click to Configure schedule...

 Configure like this
Updating data in sql table and waiting the 8:30pm
The new data is refreshed
Some Issue
On this one, I was unable to use Excel’s “Save & Send” feature to publish a workbook to Sharepoint.
When I tried to use the “Browse for a Location” dialog, I would type in the Sharepoint URL, and got a message along the lines of “this application can’t open this location”.
Quite odd.
Turns out that you need the Desktop Experience feature turned on Windows Server 2008 R2 to allow this to work.
Onother issue if you meet: "sharepoint 2010 powerpivot an error occurred while capturing snapshots for this document"

Log off from your SharePoint machine
Login to your SharePoint machine with the SharePoint admin account. In our guide we used account <domain name>\SPAdmin
Start Internet Explorer and add your SharePoint website to the trusted website list.
Restart your machine
Login back to your machine with regular account and try to deploy another report to PowerPivot gallery. Now you should see new report in PowerPivot gallery preview.
if can not
What I did to solve this issue was actually quite simple:

Make sure local loopback is disabled on the SharePoint server
Logon with a SharePoint admin account on the SharePoint server
Open the SharePoint site and add this to the trusted sites in Internet Explorer
Reboot the machine
Now login again on the SharePoint site, upload some new PowerPivot documents and check if it works
If this won’t work, you should try editing the properties of the uploaded file

Select the PowerPivot document
Files > Edit Properties
And change the title of the document
Go back to the PowerPivot page and check if the snapshots are working
If can not
Delete Excel file in PowerPivot Gallary => Open excel powerpivot file >> Save and send to sharepoint => successfull.
PowerPivot is previewing.
Also check in centraladmin => we can see powerpivot managerment
But when you refresh powerpivot gallary, you meet

In Central Administration, in Application Management, click Manage service applications.
Click Excel Services Application.
Click Trusted File Location.
Click http:// or the location you want to configure.
In External Data, clear the checkbox for Warn on data refresh.
Click OK.
Done! My GOD will bless to all of you.