Many people like me want (or need) to do their business intelligence development work on a laptop. As someone who frequently speaks at various events or teaches classes on all subjects related to the Microsoft business intelligence stack, I need a way to run multiple server products on my laptop with reasonable performance. Once upon a time, that requirement meant only that I had to load the current version of SQL Server and the client tools of choice. In today’s post, I’ll review my latest experience with trying to make the newly released Microsoft BI products work with a Windows 7 operating system.
The entrance of Microsoft Office SharePoint Server 2007 into the BI stack complicated matters and I started using Virtual Server to establish a “suitable” environment. As part of the team that delivered a lot of education as part of the Yukon pre-launch activities (that would be SQL Server 2005 for the uninitiated), I was working with four – yes, four – virtual servers. That was a pretty brutal workload for a 2GB laptop, which worked if I was very, very careful. It could also be a finicky and unreliable configuration as I learned to my dismay at one TechEd session several years ago when I had to reboot a very carefully cached set of servers just minutes before my session started. Although it worked, it came back to life very, very slowly much to the displeasure of the audience. They couldn’t possibly have been less pleased than me.
At that moment, I resolved to get the beefiest environment I could afford and consolidate to a single virtual server. Enter the 4GB 64-bit laptop to preserve my sanity and my livelihood. Likewise, for SQL Server 2008, I managed to keep everything within a single virtual server and I could function reasonably well with this approach.
Now we have SQL Server 2008 R2 plus Office SharePoint Server 2010. That means a 64-bit operating system. Period. That means no more Virtual Server. That means I must use Hyper-V or another alternative. I’ve heard alternatives exist, but my few dabbles in this area did not yield positive results. It might have been just me having issues rather than any failure of those technologies to adequately support the requirements.
My first run at working with the new BI stack configuration was to set up a 64-bit 4GB laptop with a dual-boot to run Windows Server 2008 R2 with Hyper-V. However, I was generally not happy with running Windows Server 2008 R2 on my laptop. For one, I couldn’t put it into sleep mode, which is helpful if I want to prepare for a presentation beforehand and then walk to the podium without the need to hold my laptop in its open state along the way (my strategy at the TechEd session long, long ago). Secondly, it was finicky with projectors. I had issues from time to time and while I always eventually got it to work, I didn’t appreciate those nerve-wracking moments wondering whether this would be the time that it wouldn’t work.
Somewhere along the way, I learned that it was possible to load SharePoint 2010 in a Windows 7 which piqued my interest. I had just acquired a new laptop running Windows 7 64-bit, and thought surely running the BI stack natively on my laptop must be better than running Hyper-V. (I have not tried booting to Hyper-V VHD yet, but that’s on my list of things to try so the jury of one is still out on this approach.) Recently, I had to build up a server with the RTM versions of SQL Server 2008 R2 and Sharepoint Server 2010 and decided to follow suit on my Windows 7 Ultimate 64-bit laptop. The process is slightly different, but I’m happy to report that it IS possible, although I had some fits and starts along the way.
DISCLAIMER 1: These products are NOT intended to be run in production mode on the Windows 7 operating system. The configuration described in this post is strictly for development or learning purposes and not supported by Microsoft. If you have trouble, you will NOT get help from them. I might be able to help, but I provide no guarantees of my ability or availablity to help.
DISCLAIMER 2: The instructions below apply only to an environment in which the Windows 7 client is joined to a domain. You must log into the client machine using a valid Windows user account for that domain. Thanks to Kevin who pointed out this requirement which I had neglected to mention in my initial post (Updated May 30, 2010).
I won’t provide the step-by-step instructions in this post as there are other resources that provide these details, but I will provide an overview of my approach, point you to the relevant resources, describe some of the problems I encountered, and explain how I addressed those problems to achieve my desired goal.
Because my goal was not simply to set up SharePoint Server 2010 on my laptop, but specifically PowerPivot for SharePoint, I started out by referring to the installation instructions at the PowerPiovt-Info site, but mainly to confirm that I was performing steps in the proper sequence. I didn’t perform the steps in Part 1 because those steps are applicable only to a server operating system which I am not running on my laptop. Then, the instructions in Part 2, won’t work exactly as written for the same reason. Instead, I followed the instructions on MSDN, Setting Up the Development Environment for SharePoint 2010 on Windows Vista, Windows 7, and Windows Server 2008. In general, I found the following differences in installation steps from the steps at PowerPivot-Info:
- You must copy the SharePoint installation media to the local drive so that you can edit the config.xml to allow installation on a Windows client.
- You also have to manually install the prerequisites. The instructions provides links to each item that you must manually install and provides a command-line instruction to execute which enables required Windows features.
I will digress for a moment to save you some grief in the sequence of steps to perform. I discovered later that a missing step in the MSDN instructions is to install the November CTP Reporting Services add-in for SharePoint. When I went to test my SharePoint site (I believe I tested after I had a successful PowerPivot installation), I ran into the following error:
Could not load file or assembly ‘RSSharePointSoapProxy, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.
I was rather surprised that Reporting Services was required. Then I found an article by Alan le Marquand, Working Together: SQL Server 2008 R2 Reporting Services Integration in SharePoint 2010,that instructed readers to install the November add-in. My first reaction was, “Really?!?” But I confirmed it in another TechNet article on hardware and software requirements for SharePoint Server 2010. It doesn’t refer explicitly to the November CTP but following the link took me there. (Interestingly, I retested today and there’s no longer any reference to the November CTP. Here’s the link to download the latest and greatest Reporting Services Add-in for SharePoint Technologies 2010.) You don’t need to download the add-in anymore if you’re doing a regular server-based installation of SharePoint because it installs as part of the prerequisites automatically.
When it was time to start the installation of SharePoint, I deviated from the MSDN instructions and from the PowerPivot-Info instructions:
- On the Choose the installation you want page of the installation wizard, I chose Server Farm.
- On the Server Type page, I chose Complete.
- At the end of the installation, I did not run the configuration wizard.
Returning to the PowerPivot-Info instructions, I tried to follow the instructions in Part 3 which describe installing SQL Server 2008 R2 with the PowerPivot option. These instructions tell you to choose the New Server option on the Setup Role page where you add PowerPivot for SharePoint. However, I ran into problems with this approach and got installation errors at the end.
It wasn’t until much later as I was investigating an error that I encountered Dave Wickert’s post that installing PowerPivot for SharePoint on Windows 7 is unsupported. Uh oh. But he did want to hear about it if anyone succeeded, so I decided to take the plunge. Perseverance paid off, and I can happily inform Dave that it does work so far. I haven’t tested absolutely everything with PowerPivot for SharePoint but have successfully deployed a workbook and viewed the PowerPivot Management Dashboard. I have not yet tested the data refresh feature, but I have installed. Continue reading to see how I accomplished my objective.
I unintalled SQL Server 2008 R2 and started again. I had different problems which I don’t recollect now. However, I uninstalled again and approached installation from a different angle and my next attempt succeeded. The downside of this approach is that you must do all of the things yourself that are done automatically when you install PowerPivot as a new server. Here are the steps that I followed:
- Install SQL Server 2008 R2 to get a database engine instance installed.
- Run the SharePoint configuration wizard to set up the SharePoint databases.
- In Central Administration, create a Web application using classic mode authentication as per a TechNet article on PowerPivot Authentication and Authorization.
- Then I followed the steps I found at How to: Install PowerPivot for SharePoint on an Existing SharePoint Server. Especially important to note – you must launch setup by using Run as administrator. I did not have to manually deploy the PowerPivot solution as the instructions specify, but it’s good to know about this step because it tells you where to look in Central Administration to confirm a successful deployment.
I did spot some incorrect steps in the instructions (at the time of this writing) in How To: Configure Stored Credentials for PowerPivot Data Refresh. Specifically, in the section entitled Step 1: Create a target application and set the credentials, both steps 10 and 12 are incorrect. They tell you to provide an actual Windows user name and password on the page where you are simply defining the prompts for your application in the Secure Store Service. To add the Windows user name and password that you want to associate with the application – after you have successfully created the target application – you select the target application and then click Set credentials in the ribbon.
Lastly, I followed the instructions at How to: Install Office Data Connectivity Components on a PowerPivot server. However, I have yet to test this in my current environment.
I did have several stops and starts throughout this process and edited those out to spare you from reading non-essential information. I believe the explanation I have provided here accurately reflect the steps I followed to produce a working configuration. If you follow these steps and get a different result, please let me know so that together we can work through the issue and correct these instructions. I’m sure there are many other folks in the Microsoft BI community that will appreciate the ability to set up the BI stack in a Windows 7 environment for development or learning purposes.
18 Comments
Hi, Stacia,
A question…a while back I tried to install PowerPivot/SP2010 Integration on a Windows 7 box…and kept running into the issue of needing a domain account. Maybe I missed something in your blog post here, but how did you get around that?
Thanks,
Kevin
Ah, you raise a very good point. One which I should address in my post when I get settled down from my travels. I DO have a domain account for my laptop and was connected to the network during installation. I recall reading something about that as a problem for people and one that wasn’t front and center in my mind when I was wrestling with other aspects of the installation. I was just relieved when I got it working! In my case, no Powershell trick was required for my laptop installation. In other single-machine installations that I’ve done (for demonstration purposes), the machine I used was a domain controller to deal with that issue. Thanks for pointing that out!
Follow-up, I am guessing you used the Powershell trick….
Hi, Stacia…ok, maybe I’m being dense…but if someone is running Windows 7 Professional, without being connected to ANY network, how can that person use a domain controller?
I wouldn’t have thought you could create a domain controller and a domain account on a standalone Windows 7 machine.
Right now, I have a complete R2 and SP2010 environment with PowerPivot/SP2010 integration – on a standalong machine running Windows Server. I’d love to do the same thing on a standalone Windows 7 machine….but I’m still not sure it’s possible. (Unless I try the Powershell trick)
Thanks….
Kevin
Sorry – I wasn’t being clear. To be more specific, I have a network in which I have a server running as domain controller. My Windows 7 laptop is connected to that domain and had connectivity with the domain controller during installation.
The single-machine installations which I referenced in my last reply are all server operating systems, so I was able to create a domain controller on those machines directly.
As far as I know, you won’t be able to create a standalone installation on a Windows 7 machine following the procedures I described. I’m glad you pointed that out to save others some grief before giving it a try if they don’t have a domain controller available elsewhere.
OK, thanks, I was pretty sure you had a laptop connected to a domain.
Having said all this, I’m “close” to a purely standalone environment running Win 7 and PowerPivot and SP2010. I’ve had to do several things manually (like running New-SPConfigurationDatabase, and then installing all the SP2010 pre-reqs manually). I was then able to run the SQL R2 setup to latch onto my existing SP farm – and it ran without errors and recognized the account I specified when I ran the SharePoint management shell.
I’m on the last set of steps, to configure the PPivot unattended account and the stored credentials. I smell a new blog entry!!!
Thanks,
Kevin
Well, I got it all working – the whole 2010 BI enchilada on a standalone Windows 7 box (including PowerPivot/SP integration).
Nice job! Looking forward to your blog post. Be sure to post a link here!
[…] http://blog.datainspirations.com/2010/05/23/installing-powerpivot-for-sharepoint-on-windows-7/ […]
Hi Kevin & Stacia,
I am facing the same issue with Domain account while installing Powerpivot for sharepoint on Windows 7 Profession edition.
Pls let me know how did you resolve this with out connecting to any domain.
Thanks,
Srinivas
srinigj@gmail.com
Hi Srinivas, In my case, I had a domain account. I don’t know at this time how (or if) Kevin was able to do this without a domain account. — Stacia
I did a complete installation of SharePoint 2010 and PowerPivot on my Windows 7 laptop. A domain account is not required but you need to use PowerShell check next URL’s : http://mwaterreus.blogspot.com/2010/07/installation-of-sharepoint-and.html and http://sharepoint.microsoft.com/blogs/fromthefield/Lists/Posts/Post.aspx?ID=112
Today I’m struggling with PowerPivot gallery. When I upload an Excel File into the gallery I meet 2 problems:
– The file opens in the browser window but it fails to open in Excel (client)
– When I try to refresh all connections (browser) then I get the error “The data connection uses Windows Authentication and user credentials could not be delelegated.”
I already checked if “Security Token Service”, “Claims to Windows Token Service” were running and created a new “Secure Store Target Application” but no luck.
Any help would be welcome. Thanks.
Hi Eddy,
Thanks for sharing the URLs about using PowerShell. Unfortunately, everything that I’ve described in this post is completely unsupported by Microsoft and we’re likely to run into problems in one way or another. I don’t have a machine set up with this environment that isn’t connected to a domain, so I can’t even begin to try to duplicate your problem and offer a suggestion. It does sound like a trust issue – and is likelyy related to the fact that you’re not using a domain account based on information here:
http://powerpivotgeek.com/2010/02/08/the-data-connection-uses-windows-authentication-and-user-credentials-could-not-be-delegated/
http://msdn.microsoft.com/en-us/library/ff487975.aspx
Stacia
[…] Remember, PowerPivot requires a Server 2008 operating system but some users have managed to run it on Windows 7. […]
Yes I made it!
I created a Windows 7 virtual machine with SharePoint 2010 and PowerPivot integration!
Thanks for your tips!
Pedro
Glad you were successful! 🙂
HI Stacia,
I’m here again…:-) This time is trying to create a new machine using SQL Denali CTP 3 in spite of SQL 2008 R2, to take a look into Crescent… did you tried?
I’m failing in step 8…
Steps already done:
01.Installed Office 2010
02.Installed Denali CTP3 Multidimensional Mode
03.Installed Denali CTP3 Tabular Mode
04.Apply some Pre-requisites to make this working on Windows 7
05.Installed Visual Studio 2010 Tools for Office Runtime (Denali)
06.Installed PowerPivot Excel Add-in
07.Installed SharePoint stopping in the wizard configuration
08.Try install PowerPivot with SharePoint Integration and I have a problem with the user that I set to SSAS Service. Always used the same user in the previous steps without problems. The user is administrator.
Regards,
Pedro
Hi Pedro – I haven’t tried yet. Since it wasn’t really a supported configuration in the previous release, I wouldn’t be surprised if it’s not working in Denali in CTP3. Whether it will work at RTM remains to be seen.