Skip to content

Month: July 2012

Office Web Applications In SharePoint 2013 – Bigger, Faster, Better?

At the same time that SharePoint 2010 was released, the Office team released the Office Web Applications. These browser versions of Word, Excel, and PowerPoint were tightly integrated with SharePoint and in fact originally required SharePoint to run. To install them, you would stand up a SharePoint server, and then install the web application bits on that server. Like a service pack or CU, you would then run PSCONFIGUI to integrate the bits into the farm, and install the relevant service applications. Simple enough.

However, there were some quirks associated with them. As noted here, removing the web applications from the server would actually remove the server from the farm. Simple enough to correct, but if you uninstalled the SharePoint bits before uninstalling the Office Web Application bits, you could be left with an effectively inoperable server.

During the 2010 release lifetime however, we started to see the Office Web Applications pop up elsewhere. Initially powering Facebook’s, they could also be seen as part of Windows Live. Obviously, when Office 365 was released, they were front and center. With this increasingly broad adoption, and requirement for scale, a reliance on SharePoint as a platform made less and less sense, and now, for 2013, we get a new architecture.

Standalone Web Applications Server

The big change is that Office Web Applications is no longer reliant on SharePoint. It installs and configures independently and it can serve any number of clients, SharePoint being one, and Exchange being another. In fact, it’s now positively hostile to SharePoint and other servers – you can’t install it on a machine where the SharePoint bits are installed at all. The same goes for Exchange, Lync, SQL Server, or anything really. The following is from the TechNet document “Plan Office Web Apps Server Preview”:

Servers that run Office Web Apps Server Preview must not run any other server application. This includes Exchange Server, SharePoint Server, Lync Server, and SQL Server. If you have hardware constraints, you can run Office Web Apps Server Preview in a virtual machine instance on one of these servers.

Do not install any services or roles that depend on the Web Server (IIS) role on port 80, 443, or 809 because Office Web Apps Server Preview periodically removes web applications on these ports.

Do not install any version of Office. You must uninstall Office before you install Office Web Apps Server Preview.


The Office Web Application Server really doesn’t play well with others. It pretty well demands that it is installed in isolation from other servers, which in reality means that a small SharePoint farm will consist of 3 servers – one SharePoint front end server, one SQL server, and one Office Web Applications server, and while virtualization makes this much more palatable, it may be a bit much for some smaller organizations to swallow.

Setting It Up

The official (pre-release)  document on planning and deploying the Office Web Application server can be found here and here respectively. Originally, I had planned on putting together a step by step walkthrough, but Steve Peschka from Microsoft published this article today that does precisely that.

Steve’s article is great because it describes the process of setting up the server with both http and https. Http is fine if all traffic is internal, but if you will have any external traffic, you must set it up with SSL (if security matters at all to you..), and the server will support only one zone, you cannot use both http and https. As you would expect, setting it up for SSL is more complex than for http.

All of the setup is done with Powershell. Powershell is fantastic, and highly useful, but I don’t think that anyone would claim that it’s easy to get started with, especially for small farm administrators,

I think that it can safely be concluded that it’s significantly harder to get the Office Web Applications installed with 2013 than it was with 2010.


Anyone running a two server SharePoint farm with the Office Web Applications on 2010 will wind up with a three server farm after upgrading to SharePoint 2013. This can be done via virtualization, or by standing up another physical box. Depending on the workload, the Office Web Application server doesn’t need to be particularly powerful, but no matter what, it introduces an added level of complexity. For those that heavily leverage the server, that’s a good trade-off, but for others, perhaps not.

The Office Web Applications are a fantastic set of tools, and they have been significantly improved in 2013. I am however concerned that the lack of a smooth architectural transition path from 2010 combined with the lack of a simple setup process may keep some (smaller) organizations away from it. Of course, those organizations will also have the option of moving to Office 365 where these services will already be set up and running.

Something tells me that this may just be the point.  

Leave a Comment

Setting Up Reporting Services 2012 With SharePoint 2013

In my last post, I discussed the changes in Excel and PowerPivot as they pertain to SharePoint 2013 and SQL Server. This post will walk through the steps required to set up SQL Server Reporting Services in Integrated mode with SharePoint 2013. As was the case with the new Excel data model, you will need at least SQL Server 2012 SP1 to get this to work as I describe (it’s currently available here as CTP 3).

Fundamentally, there are no real differences with how this installs when compared to installing SSRS 2012 on a SharePoint 2010 farm in SharePoint mode, so if you’ve landed here looking for 2010 information, it should be valid, but the screens will look a little different.

To start with, it’s important to understand that SSRS will install as a SharePoint service application. This obviously means that it must be installed on a machine that is part of the the SharePoint farm. What this does NOT mean is that you should install SharePoint on your SQL server and join it to the farm (please DON’T do that!). In a single SharePoint front end environment it is much better to add SSRS to your SharePoint server than it is to add SharePoint to your SQL server. Obviously, if you have a separate SharePoint application server, that’s the best place for it.

To install, obtain the SQL Server 2012 SP1 (or greater) media and mount it on tyour SharePoint server. Run the installer, choose new install and follow the prompts. Eventually you will get to the feature section screen, and assuming that machine has no prior SQL on it will look something like the following when completed.


You’ll notice that everything selected is under the Shared Features section, which means that it is not installed as part of a SQL instance. In fact, you’ll notice that we don’t have the data engine installed at all. The two Reporting Services options shown are the only items that are actually required for SSRS Integrated mode to work. As you can see, I’ve also selected SQL Server Data Tools (formerly BIDS) and Management Tools as well. I like to install these tools as a matter of course on SharePoint servers, as they can come in handy for connectivity testing or quick BI project building.

Follow the remaining prompts until the installation is complete.

Another thing that you should note is that the order of operations is important here. If you install Reporting Services – SharePoint prior to installing SharePoint on the farm, the option to create a Reporting Services application will not appear. That’s because it won’t be registered with the farm as a service application. If this happens, you can run the following PowerShell to register the Service Application


Once registered, the service application can be created as below. If you install Reporting Service – SharePoint after the server has been joined to the farm, then the above steps are taken care of for you automatically.

The next thing that you need to do is to provision the service application. From Central Administration, navigate to Manage Service applications. Then, from the new menu, Select SQL Server Reporting Services Service Application.


Fill out the resulting form as appropriate, and select OK. Make sure that you navigate to the bottom of the form and select the applications to activate SSRS on.


Once the service application and proxy have been created, click on it to access the management screen.


You’ll want to access each of the sections and fill out the appropriate options for your installation. The instructions are fairly self-explanatory, so I won’t go into them here. At a minimum, you should back up your encryption key in the key management section, Set your unattended execution account (the default account to use when no credentials are available), and your email server settings if you want to be able to deliver reports via email. If you want to enable self service subscriptions and alerts, fill out that section, and it contains instructions for setting up the SQL agent service to support it.

The most important section is System Settings, which controls the bulk of how Reporting Services will run. Clicking on it accessed the service itself, and it’s the first place that you’ll see an error if you have configuration problems. In early builds, I have seen an error similar to the following:

The requested service, ‘http://localhost:xxxxx/SecurityTokenServiceApplication/securitytoken.svc/actas’ could not be activated

(xxxxx is a local port which varies from farm to farm)

This indicated a problem with the SecurityTokenService, which you can see by accessing IIS. After doing a little poking around, I tried to access the service directly in a browser via its base url:


I was then presented with an error indicating that the server was too low on memory. The solution? Allocate more RAM. It was running with 4 GB and only SharePoint installed, but it did have most of the service applications activated. The lesson – if you want all the services to work, give your server enough memory. Bumping it to 8 GB did it in my case.

If you can access your system settings, then you should be good to go. The next step is to enable SSRS in you site collections, and I plan on doing a post on that in the very near future. Stay tuned.

Update – July 26 2012 – If you’re interested in trying out SSRS on SharePoint, or PowerPivot for SharePoint, you can use a pre-built environment that’s been set up on Cloudshare. Click here to sign up and access the environment.


Using the new Excel 2013 Data Model with SharePoint

One of the biggest new features in Excel 2013 is the ability to import and analyze massive amounts of multi tabular data. However, it’s not really all that new – if you’ve used PowerPivot in the past, you’ll already be familiar with the underlying technology. What’s really new here is that Excel can now work with these large data models without the need of an add-in. The even better news is that Excel Services in SharePoint supports this new data engine. What may be a little unclear is what is necessary to make it all work, and this post is an attempt to clarify this a little bit.

To really understand what’s new, we need to understand what is (or was) available via the 2010 family of products. It’s a bit of a bumpy ride that covers 3 main product groupings, SQL Server, SharePoint, and Office, so hang on, and I’ll do my best to clarify.


When SharePoint Server 2007 was initially released, it contained Excel Services. Among other capabilities, the original Excel Services was a means of surfacing an Excel workbook, or part of a workbook to a user through a browser interface without the requirement to have Excel installed client side. It was relatively limited, and the version of Excel Services that shipped with SharePoint 2010 removed many of those limitations. The Office Web Applications that install alongside of SharePoint bring the ability to edit workbooks directly in the browser.

At approximately the same time that SharePoint 2010 was released, the SQL server team released PowerPivot for Excel. PowerPivot is a free, downloadable add in to Excel that allows Excel users to work with massive amounts of data, and to analyze it in ways previously available only to advanced OLAP systems. The reason that it can accommodate the required data volumes is because it adds a new highly compressible data engine (previously named Vertipaq, now called x-Velocity) directly into the Excel file. These PowerPivot workbooks work perfectly when you have Excel 2010 and the PowerPivot add in installed, but what happens when you want to use Excel Services to share it with your team?

To solve this problem, the SQL Server team also released PowerPivot for SharePoint and included it with SQL Server 2008 R2 (read – it’s not available out of the box with SharePoint 2010). Installing PowerPivot for SharePoint in your SharePoint farm adds a new SharePoint shared service that works with Excel Services to allow those PowerPivot enabled workbooks to be rendered up by the server. These workbooks are “live” so that users can very effectively interact with them, using slicers, etc.

The way that it does this is that it creates a local “runtime” version of Analysis Services on the server that is used to perform all of the necessary analytical calculations. Also, to keep the data fresh, PP for SP also contains a series of timer jobs and configurations that connect to the back end data sources on a periodic basis. If you’re up for it, there’s a very detailed description of the architecture here.

Pre 2013 Usage

Without getting into much detail (like I will below with 2013 ), setting up PowerPivot for SharePoint involves starting with a SharePoint farm that is enabled for Excel Services, adding the PowerPivot for Excel add-on to one or more workstations that will create at least one PowerPivot enabled workbook, and then installing PowerPivot for SharePoint onto a SharePoint server FROM the SQL Server 2008 R2 or 2012 media. After performing some (relative onerous in some cases) configuration steps, The PowerPivot enabled workbooks can be uploaded into a SharePoint library or PowerPivot gallery, and then used by anyone accessing the SharePoint environment. There are quite a few moving parts here including

  • Excel 2010
  • SharePoint Server
  • Excel Services
  • PowerPivot for SharePoint
  • SQL Server
  • Analysis Services

As with any system, the more moving parts that there are, the more opportunity there is for failure. In addition, there are a fairly broad set of skillsets required to make it all work, so any simplification is welcome.

What’s New in 2013

The big news is that the x-Velocity engine that is the underlying power behind PowerPivot is included in Excel 15 without the use of an add-in, which will undoubtedly increase its adoption. This does lead to some practical questions around how this works with SharePoint 2013 and Excel Services. Couple this with the fact that PowerPivot still exists, and the landscape can get pretty confusing.

Excel Services itself can render Excel workbooks with embedded data models, but it’s not possible to interact with them. Clicking on slicers, filters etc. simply won’t work. The workbook can be seen, but it is completely static. Any attempt to interact with it will result in an error like the following.


As with PowerPivot in SharePoint 2010, what is needed is an Analysis Services engine to perform the necessary work. The big difference with 2013 is that the engine no longer needs to be installed on the SharePoint server – you can connect to an Analysis Services server elsewhere on your network, and that server does not need to be part of your SharePoint farm.

That server does, however need to be operating in “SharePoint mode”, and it can be a little unclear as to what that means. In the end what it means is that PowerPivot for SharePoint (available on SQL 2012 media) is installed on a non-SharePoint server. However, if you try to do this using the release versions of SQL Server 2012 (or 2008 R2 for that matter) you will get an error because the SharePoint bits are not present. What is needed is a minimum of SQL Server 2012 SP1, which as of this writing is available as a Community Technology Preview V3, and can be found here. To set this up, run the SQL install on the AS machine, and at the Setup Role prompt choose SQL Server PowerPivot for SharePoint.


Unless you need it, you don’t need to install the relational engine. No, you’re not really using PowerPivot at this point, but it’s the option you need to pick. I suspect that the name may change by the time SQL Server 2012 SP1 is released, but for now, that’s what it is.

Follow the remaining prompts to complete the installation. After the installation completed, resist the urge to run either of the PowerPivot configuration tools. These are used if we actually ARE using full PowerPivot (see below), and that will require a SharePoint server. If you run it on a non SP farm machine, things can get very messed up.

Once that is set up, you need to tell Excel services to use it. Navigate to your Excel Services service application in Central Administration, and select “Data Model Settings”.


The options here are pretty limited…what’s your Analysis Services server? Click on Add Server, and add your Analysis Services (PowerPivot for SharePoint) server. Unless you’ve changed it, PowerPivot for SharePoint will have installed in a non default instance named POWERPIVOT, so the server name will take the form SERVERNAMEPOWERPIVOT, as below


You may need to run an iisreset on your SharePoint front end servers, but once this is complete, you’re ready to test. To do that, you’ll need to start up Excel 2013, and import multiple tables from a data source. This triggers creation of the model.


Once imported (it may take a bit), create a pivot table and add an interactive element (a slicer is perfect). Test it out, and if it’s working, save it to a SharePoint document library.


Close Excel, navigate to the library, and click on the workbook. It should open in the browser. Once open, trigger the interactivity element (click the slicer) and if it works, you have everything configured properly.


If you want to have a look under the covers, start SQL Server Management studio, and create an Analysis Services connection to your instance (defined above). You should see a new model created for your workbook.


If it’s not there, make sure that you’ve interacted with the workbook – it doesn’t get created until the first interaction.

Whither PowerPivot?

Now that the engine is baked into Excel, is there any need for PowerPivot any longer? As we’ve seen, for simple analysis, no. However, if we want to do anything advanced, like filtering import data, modifying the model, creating complex relationships, or using DAX (the tabular answer to MDX), the answer is emphatically yes.

On the client side, PowerPivot is actually included with Excel – no download required anymore. It is disabled by default, and is enabled through the Excel Com Add in interface. You can view this as your individual or team model designer. With it you can create complex models and then share them out via SharePoint. Optionally these very same models can be imported into Analysis Services projects if/when they become mission critical, or too large. Another nice thing is that models created with PowerPivot can be shared on a SharePoint farm that is not using the full PowerPivot for SharePoint (but does need “Analysis Services in SharePoint mode – confused yet?).

On the Server side, we can install PowerPivot for SharePoint, just as we did with SharePoint 2010. The reason that we would want to do this is to gain access to PP for SP features like the Pivot Gallery, but primarily to access refresh capabilities. In the scenarios that I’ve described above, using Excel Services, the data that we’re using is relatively static. Data is imported when creating the model, and is used for analysis, but there is no mechanism to refresh the data in the model in the way that Analysis Services can. PowerPivot for SharePoint offers this capability in SharePoint 2010, and this carries forward with SharePoint 2013.

Configuring PP for SharePoint 2013 is beyond the scope of this post, but I will likely address it in a future post.


With Office 2013 we have the Third major release of PowerPivot, and the data model on the second platform for SharePoint. It’s not likely that anyone will upgrade all of their applications immediately, so the question is, what works where? The good news is that the core engine is relatively unchanged, and models created with any versions of PowerPivot should work well with Excel Services 2013 (and Analysis Services SharePoint mode). Conversely, models created with Excel 2013 whether with or without the PowerPivot 2013 add-in should work with previous versions of SharePoint. I haven’t yet had the chance to try out all of the various permutations and combinations, and would like to hear what your experience is.

Hopefully, this helps clear up some of the confusion around the Excel BI features in SharePoint 2013. Hopefully the naming in the SQL media installation gets cleaned up by release.

Update – July 26 2012 – If you’re interested in trying out any of the tools that I mention below, you can use a pre-built environment that’s been set up on CloudShare. Click here to sign up and access the environment.