Year: 2012

How To Configure the Nintex Workflow Preview on Office 365

Published August 7, 2012

Anyone that has worked with my company, UnlimitedViz, knows that we’re unabashed fans of Nintex Workflow. As far as I’m concerned, it’s one of the most cost effective pieces of software I’ve ever come across. Projects that would require days or even weeks of consulting using the out of the box features in SharePoint can be accomplished in mere hours with Nintex Workflow, and what’s more, they can be accomplished by power users, not developers.

Another exceptional aspect of Nintex workflow is that is doesn’t replace the out of the box SharePoint workflow engine, it simply enhances it, through a browser based graphical designer/viewer, and multiple custom actions. This design approach really showed its value on several SharePoint/Nintex 2007 to SharePoint/Nintex 2010 upgrades that we’ve performed. We were able to upgrade entire farms and maintain in-flight workflows.

Unfortunately, as I wrote about previously, once we moved our organizational assets to Office 365, we were no longer able to use Nintex Workflow ourselves, because NWF is an on-premise solution. However, with a little redevelopment, changes in the SharePoint development model (for a reference, see Jeremy Thake’s excellent summary) open the doors for third party code to be used with Office 365 sites in addition to on-premise sites.

Happily, these changes have not gone unnoticed by the folks at Nintex, and they are one of the first vendors out of the gate with a solution in the SharePoint store. It’s just a preview of what will be coming very shortly after the release of SharePoint 2013, which is important, because Office 365 sites will be upgraded very shortly after release.

Walkthrough

It should go without saying, but in order to try the Workflow Preview, you’ll need to be using an on premises SharePoint farm that has been enabled for SharePoint 2013 workflows, or using the Office 365 preview. In our case, we’re using the Office 365 preview.

Firstly, you’ll need to add the app to the Office 365 App Catalogue. To do this, go to the Office 365 Admin screen and click on apps.

Next, click on Purchase Apps. You’ll see a yellow alert bar indicating that you don’t have permission to add apps, but as far as I can tell, it’s a bug. Click on the “Nintex Workflow Platform Preview” icon.

Once that’s done, you’ll be presented with a summary screen. When ready, click the “Add It” button.

You’ll then be presented with a summary screen indicating that you now have a site license.

At this point, you’ll need to navigate back to the site that will be using the workflows. Now that the app is available, you won’t be turning on features for a site anymore, you’ll be adding the app to any site that you want to use the workflow preview with. In this case, we’ll be adding the preview app to a basic team site. Assuming that you have the appropriate permissions, you’ll start by adding an app by clicking on Site Contents – Add an App.

The Workflow Preview should appear in the “Apps you can add” section.

Click it, and you’ll be asked if you trust it – of course you do! Click the Trust It button and in a moment the app will be added.

What’s actually happening behind the scenes is that an app subsite is being provisioned that will contain all of the assets needed for the app to work. This is done to keep the app itself isolated from the content of the site. It may take a minute, so have patience.

Once it’s ready, you’ll see “Nintex Workflow Platform Preview” comingled in with the other site lists and apps. Clicking on it takes you into the workflow designer where you can design a site workflow.

Drag and drop actions to your heart’s content, and when ready, click Publish from the ribbon, give it a name, and save it. You can now run your Nintex site workflow in Office 365. You should note that this is a small subset of actions intended for preview only, but the Nintex Live actions are available, so you can translate items, use current exchange rates, etc.

If you want to design a list workflow, simply navigate to a library or list within the site, and click on the appropriate list or library tab. Over on the right near the standard workflow button, you’ll find a “Nintex Workflow” button.

Clicking on it will once again open the designer. If you examine the URL, you’ll notice that what it’s really doing is opening the isolated app subsite and linking back to the list where the workflow will be saved.

In this case, we’ll build a quick translation workflow that will take content from the English field, use the Bing Translation action available through Nintex Live, and then write the value to the French field.

The workflow can be set to run on item creation, and then published. So that adding a new item:

Then saving it, after a few moments results in a translated value appearing:

This is early days, and we’re looking at previews on previews, but I’m quite encouraged with this. Not only will I be able to use Nintex workflows for our own Office 365 site shortly after launch, but in my opinion, this preview validates the new application model. Yes there are currently bumps, understandable, given that this is a preview on a preview, but this is functional and responsive. The folks at Nintex have been doing their homework, and appear to be hitting the ground running into the emerging Office 365 market.

1 Comment

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

Published July 23, 2012

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 docs.com, 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.

Takeaways

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.

Setting Up Reporting Services 2012 With SharePoint 2013

Published July 20, 2012

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

Install-SPRSService
Install-SPRSServiceProxy

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:

http://localhost:xxxxx/SecurityTokenServiceApplication/securitytoken.svc

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.

33 Comments

Using the new Excel 2013 Data Model with SharePoint

Published July 17, 2012

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.

Background

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.

Compatibility

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.

3 Comments

Profile Import in SharePoint 2013 – Back to the Future

Published July 17, 2012

When SharePoint 2010 was introduced, one of the major changes that it brought with it was a completely new infrastructure for working with user profiles. This infrastructure was based on the Forefront Identity Manager, and represented a fairly bold leap. With it, the integration possibilities were greatly increased, and it allowed for not only read, but write synchronization on a field by field basis (either read or write… not both!).

The problem was that it was unwieldy. Being from an agricultural background, I liken it to a combine. Useful, a lot of moving parts, and it breaks down easily. Couple this with the fact that with the initial release of SharePoint 2010, it wasn’t fully baked, and you have the recipe for what became the top support issue for SharePoint 2010 up until this point. Subsequent Service Packs and Hot Fixes have greatly improved the system (my gold standard is currently Service Pack 1 with the December 2011 Cumulative Updates), but the system does remain complex, and is arguably overkill where a simple Active Directory import is all that is required.

I put out a guide intended to simplify the steps, but the real comprehensive guide is by Spencer Harbar – I strongly recommend a read.

Well, everything old is new again. With SharePoint 2013, the product team heard these messages and brought back the simpler profile import that was in SharePoint 2007 as an option. It’s not available by default, and I don’t necessarily recommend using it (as always, it depends) but if your requirements are a simple import, then it may be for you. Here’s how to get it working.

To start with, do NOT start the User Profile Synchronization Service. This is the FIM based system, and is NOT required for the simple import to work.

Navigate to the User Profile Service Application (from Central Administration, Select Application Management, Manage Service Applications, and then your Profile Service Application). Then, select Configure Synchronization Settings from the Synchronization Section.

Then, instead of “Use SharePoint Profile Synchronization”, select “Use SharePoint Active Directory Import”, and click OK.

Once that is complete, you need to set up an import. To do that, select the “Configure Synchronization Connections” link from the Service Application page.

Then, click “Create New Connection”, and fill out the connection form accordingly.

One thing to note, and a deviation from the original SharePoint 2007 import mechanism is that the account used above MUST have the “Replicating Directory Changes” permission in Active Directory for the import to work. This is the same requirement as the 2010 synchronization, and the full synchronization service with SharePoint 2013.

Navigate back to the Profile Service Application page, and select “Start Profile Synchronization”.

Finally, Select the full synchronization option, and click OK.

After a relatively short period of time, your user profiles should be available.

Again, I don’t necessarily recommend the simpler option if your only problem is complexity, but I do think that is was wise of the product team to add this back in. If your requirements are truly import only, and you don’t have multiple identity systems, this is a quick way to get up and running. It’s also great for testing and demo environments.

18 Comments