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" ?>
<configuration>
<runtime>
<disableCachingBindingFailures
enabled="1" />
</runtime>
</configuration>
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"
resolve
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
use
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.
