DRAFT: THIS IS A DRAFT OF A WHITEPAPER CURRENTLY BEING WRITTEN. IT MAY BE UPDATED AND WE INVITE YOU TO POST COMMENTS WITH SUGGESTIONS, CORRECTIONS AND QUESTIONS TO THIS PAGE. WE WILL CONSOLIDATE THEM AND INTEGRATE THEM BACK INTO THE BODY OF THE POSTING. AT SOME FUTURE TIME THIS INFORMATION WILL BE PART OF AN OFFICIAL MICROSOFT WHITEPAPER – BUT UNTIL THEN, WE CAN ALL BENEFIT FROM YOUR INPUT. THANKS.
Use this document to setup and configure a new SharePoint 2010 farm with the SQL Server PowerPivot for SharePoint Server 2010. People in your organization/group can then publish the applications that they create using PowerPivot for Excel 2010 to different SharePoint sites, document libraries, and PowerPivot Galleries on this server.
Please note that SharePoint 2010 requires a 64-bit platform and Windows Server 2008 SP2 or later.
NOTE: As discussed below, if you are running under Windows 2008 R2 you must install QFE here (http://go.microsoft.com/fwlink/?LinkID=166231) either after or before the install. If running under Windows 2008 SP2, the SharePoint installation will stop you if you don’t have its associated hot fix installed, but it won’t stop you under R2. There are two different QFEs depending on if you are running Windows 2008 SP2 or Windows 2008 R2. If you don’t have this fix installed, you will get “Unrecognized attribute ‘allowInsecureTransport’” error in the ULS log. This will be fixed in SharePoint 2010 RTM.
1. Resources and Links
You can download Office 2010 SharePoint Beta and SQL Server 2008 R2 November CTP from:
- Office 2010 SharePoint Beta: http://www.microsoft.com/downloads/details.aspx?FamilyID=77c30c6c-47fc-416d-88e7-8122534b3f37&displaylang=en
Remember to write down your product key for this download. You will need it during SharePoint installation. - SQL Server 2008 R2 November CTP: http://www.microsoft.com/sqlserver/2008/en/us/R2Downloads.aspx
2. Install Office 2010 SharePoint Server
a. Navigate to the folder from where you downloaded Office 2010 SharePoint Server, right click PrerequisiteInstaller.exe and select Run as administrator.
b. After you complete Step a, from the SharePoint Server 2010 Build Location, run setup.
c. If you see the following message:
Install KB971831 from http://go.microsoft.com/fwlink/?LinkID=160770 and then run setup.exe.
Important: If you are running Windows Server 2008 R2 (Windows 7 Server), setup will not prompt you to install a QFE. Install KB976462 from http://go.microsoft.com/fwlink/?LinkID=166231 and then run setup.exe.
d. Accept the terms and conditions and click Next. Enter in your product key from when you downloaded the product.
e. Click Server Farm, then click Next.
f. On the Server Type tab, choose Complete.
g. On the File Location tab, leave the default settings and click Install Now.
Important: Do NOT run the configuration wizard that pops up when the install completes. SQL Server Analysis Services setup will take care of this for you. Uncheck the box that prompts you to run the config wizard and click Close. Don’t worry. As part of the New Farm installation below, SQL Server setup with SharePoint Integration will automatically run the SharePoint configuration wizard, install the required shared services, configure them, and tune SharePoint to run in an optimal manner.
3. Run SQL Setup to install AS and configure your SharePoint farm
a. From the SQL Server build location, launch setup.exe.
b. Click Installation > New installation or Add features to an existing installation. Select the default option on each page of the wizard until you reach the Installation Type page. (Choose Enter a product key and leave this blank when prompted for a key.)
![clip_image002[6]](http://powerpivotgeek.com/wp-content/uploads/2009/11/clip_image0026.jpg "clip_image002[6]")
c. Continue to click Next and select the default options.
d. On the Setup Role page, select the Analysis Services with SharePoint Integration option and then select New Farm from the drop down menu.
e. Select defaults until you reach the New SharePoint Farm Configuration page.
f. On the New SharePoint Farm Configuration, enter YOUR username and password or the account you are using to configure your server, specify a passphrase that meets the standard password requirements, for example ‘Password1’, and leave the default port number or enter a port number of your choice.
![clip_image002[8]](http://powerpivotgeek.com/wp-content/uploads/2009/11/clip_image0028.jpg "clip_image002[8]")
g. Continue through the setup and select defaults until you reach the Server Configuration page.
h. On the Server Configuration page, enter the service accounts for the various SQL Server components. While not required, we recommend that you click on use the same account for all SQL Server services and enter a domain account.
![clip_image002[10]](http://powerpivotgeek.com/wp-content/uploads/2009/11/clip_image00210.jpg "clip_image002[10]")
i. Continue selecting defaults until you get to the Database Engine Configuration dialog. Click on Add Current User to ensure that you are a SQL Server system administrator.
j. Continue selecting defaults until you get to the Analysis Services Configuration dialog.
k. On the Analysis Services configuration page, click Add Current User to ensure that your account has access to Analysis Services.
l. Continue through the rest of the Setup wizard and select the default for each option, then click Install.
4. Configure file size limits (Optional)
Out of the box, SharePoint allows you to upload files up to 50MB in size and Excel Services allows you to view files as large as 10 MB in a browser. Our new farm setup increases the Excel Services limit to 50MB. You can change these settings even further if users will be publishing and interacting with larger files.
To increase Web Application limits:
a. Navigate to SharePoint 2010 Central Administration > Application Management > Manage Web Applications.
b. Select SharePoint-80 and then, on the Ribbon, select General Settings > General Settings.
c. Change the Maximum Upload Size setting to the MB limit you want to set. (The maximum allowed by SharePoint is 2047MB.)
To increase Excel Services limits:
a. Navigate to SharePoint Central Administration > Application Management > Manage Service Applications.
b. Select your Excel Service Application.
c. On the Ribbon, click Manage.
d. Click Trusted File Locations and on the following page, select your trusted location. (Typically, this is listed as http:// in the Address column.)
e. In the Workbook Properties area, set the Maximum Workbook Size property to 2000 and the Maximum Chart or Image Size to 100 MB
5. Confirm PowerPivot Setup
a. After you install the Excel 2010 and PowerPivot for Excel 2010, use PowerPivot to create a workbook (say Test.xlsx).
b. Publish the workbook to the Shared Documents folder on the default Web site (http://machinename ).
c. Open SQL Server Management Studio, click Connect > Analysis Services and type http://<machinename>/Shared%20Documents/Test.xlsx into the Server Name field.
d. Click Connect.
A connection should appear in the Object Explorer and you should be able to browse the cube in your Excel workbook. This confirms that you have PowerPivot set up correctly.
Now that you have successfully installed and configured PowerPivot, you might want to configure Reporting Services on your SharePoint farm to enable additional functionality. Use the Reporting Services Installation and Configuration document to install and configure Reporting Services in SharePoint Integrated mode. You can then use Report Builder to create reports using PowerPivot workbooks that you have been published to a PowerPivot Gallery as data sources.
I believe that fix for Windows 2008R2 was released:
http://blogs.msdn.com/opal/archive/2009/11/16/installation-notice-for-sharepoint-2010-public-beta.aspx
The note at the top
NOTE: AT THIS TIME WE DO NOT RECOMMEND THAT YOU INSTALL ON WINDOWS SERVER 2008 R2 BECAUSE OF A MISSING QFE. ONCE IT IS RELEASED WE WILL UPDATE THESE INSTRUCTIONS.
can be removed as the fix is available.
This guide is not truly a single server install, as it requires an additional server hosting a Active Directory.
Installing AD on the same server is possible, but with complications. See http://powerpivot-info.com/post/66-step-by-step-guide-on-installing-powerpivot-for-sharepoint
True enough Rob. We didn’t include instructions for dcpromo and other steps for getting a true ’single server’ up and running. Most folks have some kind of networking infrastructure available to them. Only the geeks among us (me included) that have more computers in their homes than living people) typically worry about true ’single server’ issues. We assumed that if a true ’single server’ was your goal that you likely have already worked out the hurdles of getting the domain controller role added, etc. We attempted to focus on just the SharePoint and PowerPivot aspects to the problem and left it as an “exercise for the student” to handle the other function. Sorry if we were misleading.
I agree that running DCPROMO can be left as an exercise. But I wasn’t hinting on that. What I meant is that the article does not mention the potential installation problems with running AD on the same server and neither does it provide solutions, such as:
If you setup domain controller on the same machine that will run SharePoint, the following Windows PowerShell command would need to be run to enable Sandboxed Solutions.
Note: Script was written by Jie Li and copied from here.
$acl = Get-Acl HKLM:\System\CurrentControlSet\Control\ComputerName
$person = [System.Security.Principal.NTAccount]“Users”
$access = [System.Security.AccessControl.RegistryRights]::FullControl
$inheritance = [System.Security.AccessControl.InheritanceFlags]“ContainerInherit,ObjectInherit”
$propagation = [System.Security.AccessControl.PropagationFlags]::None
$type = [System.Security.AccessControl.AccessControlType]::Allow
$rule = New-Object System.Security.AccessControl.RegistryAccessRule($person, $access,$inheritance, $propagation, $type)
$acl.AddAccessRule($rule)
Set-Acl HKLM:\System\CurrentControlSet\Control\ComputerName $acl
Here are steps on how you can run this script:
copy script to file sp.ps1
in command line enter “PowerShell”
enter “Set-executionpolicy unrestricted” (without double quotes)
enter “C:\Scripts\sp.ps1″ (without double quotes)
enter “Set-executionpolicy restricted” (without double quotes)
enter “Exit” (without double quotes)
and
If you are running domain controller on the same machine, you are not done yet. It is very important that you monitor your setup, as you might have to do some tricks, to make it work!
If you are running this on virtual machine (Hype-V), then I would recommend that you create your machine snapshot just before your setup will reach half way, in case you will need this recovery point.
There is a know issue when you install PowerPivot on domain controller described here. Basically in the middle of the setup your service “SQL Server Analysis Services (GeminiBI)” might change logon credentials from the domain account to “Local system account”. You should prepare for that and change logon credentials back to domain account as described in the post above.
and
After installation try to create and deploy PowerPivot to SharePoint. Check if during interaction with published PowerPivot you are getting error message:
“Excel Web Access”
An error occurred during an attempt to establish a connection to the external data source. The following connections failed to refresh:
Sandbox
If you are getting error message above, then add your SharePoint administrator account (in our case account is \SPAdmin) to the active directory user group “Domain Admins” and restart your machine.
I think quite a few BI consultants will want to have a true single-server (single laptop / single VM) setup to take to customers to test it and give demonstrations of the Self-Service BI concept long before the decision is made to install it in the real production infrastructure.
In that perspective, an installation guide should have clear instructions on whether a true single setup (with AD) is possible or not.
Thanks for the input Rob. Looks very interesting.
The first item on the “sandboxed solutions” is only needed if you are going to run apps that require it. It hasn’t been my experience that this is needed for PowerPivot. The word “Sandbox” means two totally different things. Sandbox to SharePoint is an environment where you can test applications in a controlled environments. Sandbox is also an early name for embedded PowerPivot databases. The two have nothing to do with each other. But I will check my SharePoint contacts to see if the script will be needed and why.
The second item is a bug and is documented on the blog here: http://powerpivotgeek.com/2009/11/17/installing-powerpivot-for-sharepoint-on-a-domain-controller/ for those that need more information.
I haven’t see the error about EWA. But I must admit that most 1-box installations that I do, the farm service account is a domain admin. My guess is that since we use the farm service account as our service accounts also, that this is the root cause of the problem. You have the right solution, just add it to domain admins.
Again, thanks.
Just one thing to add – I think we need to physically login to the powerpivot server with the service account to manually create a profile and/or add the site to trusted lists or the preview gets messed up wit a red X. Information is on connect and forums.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=512658
To be honest I am confused about term “Sandbox”. I noticed that all SSAS databases published by PowerPivot has “Sandbox” name in it. There is also SSAS database “ITOps Sandbox…”. And then each published database has data source view “Sandbox”, so when you have data refresh issues, you see error message about “Sandbox” issues. Could you explain more about “Sandbox” term – prabably blog post about that would be very intereting to read
.
I included “Sanbox” script into my install guide, as I believe it is required. As I had other issues with installation, I believe at very least it “will not hurt”. It would be nice to get clarification if we do need it.
Thank you,
Vidas Matelis
Sure. I’d be glad to. Before it was PowerPivot, before it was Gemini, we started off as an incubation project called the “Sandbox” system. The idea was to give information workers a place for them to construct their own datasets and manipulate them. We didn’t want to call the object a ‘database’ because we didn’t know if (at that time) whether or not we would be a RDBMS (or AS) database. Thus we choose the generic name ‘Sandbox’. The name kind of stuck to represent the embedded dataset that was ultimately contained in the workbook. The name Sandbox as the name for the project was replaced by “Gemini” because the name is too descriptive of what we ultimately ended up creating. And then ultimately Gemini (a code name) was replaced by PowerPivot as our actual product name.
Parallel to this whole effort, the SharePoint team was developing a system that allows developers to construct pages and assemblies that need to be validated and tried out before they go into production. Unknown to us, they called their work creating a ‘Sandbox’ because isolates developers from the real system.
Two entirely different systems with absolutely no connection.
[...] PowerPivot for SharePoint installation on a Single server: This is the All-in-One Departmental server configuration. [...]
[...] Read more… Published Jan 04 2010, 09:46 PM by denny.lee Filed under: Sharepoint, PowerPivot [...]