Sunday, September 29, 2013

Simple jQuery Content Filter for Office 365 Public Website

I had the need recently to quickly create a content filter for the SPS Chicago session page. The page lists all the upcoming sessions in a paragraph format, something like this:

Title 1
Speaker 1
Session Abstract 1

Title 2
Speaker 2
Session Abstract 2


I wanted something simple at the top of the page that would allow users to filter the sessions based on some kind of key word.

So, I wrapped each of the sessions in a div with a class name of a descriptive term, something like this:

<div class="DemoDeveloper">
Title 1
Speaker 1
Session Abstract 1

Then I added check boxes to the top of page for each descriptive term, like this:

I wanted it so that when you checked a box, the applicable sessions were displayed. When the check box was unchecked, those sessions were hidden. So, I added the following jQuery to the page (for brevity I only show the developer check box functionality below):

        $(document).ready(function () {
            function DemoToggleView() {
                if (!$("#DemoDeveloperCheckbox").prop("checked")) {
                } else {

The "DemoToggleView" is just a function that either hides or shows divs with a class of "DemoDeveloper" depending on whether the check box is checked or unchecked. The second part of the code attaches the function "DemoToggleView" to the click event of the checkbox.

Here's what it looks like in action. Click the checkbox to toggle the developer sessions. Note that "Title 1" is wrapped in a div class of "DemoDeveloper".


Title 1
Speaker 1
Session Abstract 1

Title 2
Speaker 2
Session Abstract 2

As always, let me know what Think

[Edit] Matt Bramer refactored this into a much cleaner solution. Check it out here: .

Volume Shadow Copy Service Error while Installing Search Service Application on SharePoint 2013

If you get the following error (see below) while installing the Search Service Application on SharePoint 2013, make sure that the "SharePoint Server Search 15" (OSearch15.exe) has full control privileges to the following registry key: HKLM\System\CurrentControlSet\Services\VSS\Diag

Volume Shadow Copy Service error: Unexpected error calling routine RegOpenKeyExW(-2147483646,SYSTEM\CurrentControlSet\Services\VSS\Diag,...).  hr = 0x80070005, Access is denied.

   Initializing Writer

   Writer Class Id: {0ff1ce15-0201-0000-0000-000000000000}
   Writer Name: OSearch15 VSS Writer
   Writer Instance Name: OSearch15 Replication Service
   Writer Instance ID: {51cfa6a9-0ee0-4650-928a-c52e42b8df3b}

Wednesday, June 12, 2013

Get log entries from the SharePoint 2013 ULS using C#

Suppose you aren't happy with the accessibility of the ULS log in SharePoint 2013. Often this is what you get from users who encounter an error:

"Thanks," you say. "Could you please recreate the error and click on the technical details and copy and paste the message to me?"

When you finally get the correlation Id, you have to scour the logs to find the log entries and start tracking down the error. There are various ways to make this process easier. One way is to use some variation of the C# code below:

var ulsLogEntries = new SPULSRetriever(
      300, 1000, DateTime.Now.AddMinutes(-180)).GetULSEntries(new Guid(_correlationId));

The SPULSRetriever class is part of the Microsoft.SharePoint.Diagnostics namespace and allows you to look up log entries for a specific correlation id. There is limited SDK documentation here:

In the example above, the _correlationId variable is created earlier in the code as a string and converted to a Guid before being passed into the GetULSEntries as a parameter. The GetULSEntries method returns a collection of LogFileEntry members. The LogFileEntry members contain the properties for a single log entry.

You can then iterate through the collection and display the log entries in whatever fashion you like. An example of how you could use this is to create an application page available to the support team that allows them to input a correlation id and capture the appropriate log entries. Another possibility is just to add something to the various error pages that automatically sends the log to the support team when a user encounters an error.

Have fun! As always, let me know if you have any comments or feedback.

Thursday, April 18, 2013

SharePoint 2013 PowerPivot Component Overview

[This post was completely overhauled on 6/18/2013 to provide more accurate and detailed information]

This post provides a high level overview of the components used in a SharePoint 2013 PowerPivot installation. It includes screenshots to illustrate the components discussed and in some cases I provide links to detailed instructions on how to install and configure the specified component.

Overview of the Component Architecture

The diagram below illustrates the components discussed in this post.

In a typical PowerPivot for SharePoint 2013 installation there are two SQL Server 2012 SP1 instances (they do not have to be on the same SQL Server).
  • There is at least one database instance for the PowerPivot and Secure Store Service Applications
  • There is one Analysis Services instance for PowerPivot, which is acts as the computational engine for Excel Services PowerPivot workbooks.
There are three service applications installed on SharePoint 2013 that are used by a full installation of PowerPivot:
  • Secure Store Service Application - claims-aware authorization service that stores and retrieves credentials
  • Excel Services Service Application - Browser based Excel for SharePoint
  • PowerPivot Service Application - Primarily responsible for scheduled date refresh and usage statistics

Note that Analysis Services and Excel Services are the only things required to view PowerPivot workbooks in SharePoint 2013. The other components give you:
  • The PowerPivot Gallery
  • The administrator's dashboard
  • Usage statistics
  • Scheduled data refresh

PowerPivot Components in SQL Server 2012 SP1

The screenshot below shows a sample installation of a SQL Server Analysis Services instance used for PowerPivot on SharePoint 2013. Note that the server name ("SPS-SQL" in my example) will vary by installation, but the instance name ("POWERPIVOT") will not. The POWERPIVOT analysis service instance is installed with the SQL Server 2012 Setup Wizard.
Detailed instructions on how to setup the POWERPIVOT analysis service instance in SharePoint mode can be found here:

The screenshot below also shows the database  ("SP_PowerPivot" in my example) used by the PowerPivot service application. This database is created by the PowerPivot Configuration Tool.
Note that there is no Excel Services Database. Excel Services uses cached storage only.

PowerPivot Related Service Applications

The service applications used by PowerPivot can be seen in Central Administration (see screen shot below). The PowerPivot Configuration Tool provided by Microsoft can help you setup and configure all three of these service applications.

Detailed instructions on using the PowerPivot Configuration Tool for SharePoint 2013 can be found here:

The PowerPivot Service Application (Mid Tier Service in Health Analyzer Rules) has the following functions:

  • Hosts the refresh scheduling engine, which calls Excel Services APIs for data refresh of Excel 2013 workbooks. For Excel 2010 workbooks, the service directly performs the data model processing but continues to reply on Excel Services for loading the data model and updating the workbook.
  • Provides methods for components such as the user interface pages, to communicate with the system service.
  • Manages requests for external access to workbooks as a data source, received through the PowerPivot Web Service.
  • Scheduled data refresh request management for timer jobs and configuration pages. The service manages requests to read data in and out of the service application database and trigger data refresh with Excel Services.
  • Usage processing and related timer job.
In the Excel Services, the name of the SQL Server Analysis Services instance used to store and manage PowerPivot workbook data must be identified (see screenshot below).

In the Secure Store Service Application, the PowerPivot Unattended Account for Data Refresh must be configured. The PowerPivot Configuration Tool will create this Unattended Account for you.

PowerPivot SharePoint 2013 Solutions

The farm will have two Globally deployed farm solutions and one farm solution that is deployed to the web application where you will be hosting PowerPivot workbooks (see screenshot below). These solutions are added to the farm by the PowerPivot Configuration Tool.

Your site has to have the PowerPivot Feature enabled to store PowerPivot workbooks, use the data refresh functionality, and display workbooks in the PowerPivot Gallery.

Ultimately, a PowerPivot gallery with a few workbooks in it will look something like this:

Please let me know if this post was helpful to you or if you have a question by writing a comment.

Tuesday, March 26, 2013

PowerPivot for SharePoint 2013 Account Permissions Overview

Following is a brief overview of the permissions and accounts required for PowerPivot on SharePoint 2013. This overview is intended to provide a basic understanding what accounts are required for PowerPivot to run correctly on a SharePoint 2013 farm.

SQL Server 2012 Analysis Services (POWERPIVOT)

Sample Account Name: SPAnalysisServices

Account Type: Domain User

Required Permissions:
  • Run as account for the "SQL Server 2012 Analysis Services (POWERPIVOT)" windows service on the SQL Server application server.
  • Member of local security group: SQLServerMSASUser$<ServerName>$PowerPivot
  • Member of local security group: WSS_WPG
  • Windows service that runs on a SQL Server 2012 application server. In smaller SharePoint installations, this is usually the same server that the SharePoint database engine is installed.
  • Built in machine accounts are not allowed
  • Must be the same for all Analysis Services server instances used by a single farm

PowerPivot Service Application

Sample Account Name: SPPowerPivot

Account Type: Domain User

Required Permissions:
  • Run as account for the PowerPivot Service Application
  • Analysis Services system administrator
  • Connect, read and write to PowerPivot Service Application Database
  • Shared web service that runs under an application pool identity in a SharePoint farm
  • Built in machine accounts are not allowed
  • On smaller farms or farms that don't need the account separation, this is typically the same domain account that is used to run other Service Application on SharePoint 2013.


PowerPivot Unattended Data Refresh Account

Sample Account Name: SPDataRefresh

Account Type: Domain User

Required Permissions:
  • Must be assigned Contribute permissions on any PowerPivot workbook for which it is used
  • Read permissions on any external data sources needed for a data refresh operation
  • By default, the PowerPivot Configuration Tool configures this as the farm account
  • This can be left as the farm account if account separation is not needed and permission requirements outlined above are met. Larger farms or farms with external data sources may want a separate account for auditing and traceability.
  • If you don't use the farm account, additional permission configuration may be required on the Central Administration site and the PowerPivot Service Application Database in order for the PowerPivot reports to work in Central Administration.


Excel Services Service Application

Sample Account Name: SPExcelServices

Account Type: Domain User

Required Permissions
  • Analysis Services PowerPivot Instance system administrator
  • Excel services should be configured in advance of the PowerPivot installation.
  • On smaller farms or farms that don't need the account separation, this is typically the same domain account that is used to run other Service Application on SharePoint 2013.

Source Guidance and Additional Documentation

If you have any suggestions on how to improve the content of this post or if you just have a question, please comment below.

Wednesday, February 27, 2013

Demonstration of a jQuery implementation of the JavaScript Promise Pattern

In preparation for my talk at the last Chicago SharePoint Developers User Group meeting, I spent some time putting together a few lines of code to demonstrate jQuery reading a web service and asynchronously rendering data from the web service in a grid. The JavaScript and jQuery portion of the code is shown here for reference for anybody that wants to use it.

The grid used in this example is a jQuery grid called ParamQuery. You can find out more about it here: I simply downloaded the minified JavaScript library for paramquery and, after reading a little of the documentation, wrote the appropriate code to format my data into the table. You can see the implementation of the paramquery grid in the code below.

In the video below I demonstrate the grid. I click on a row and it loads a picture from LinkedIn using the column with the LinkedIn picture url. The grid is resizable and draggable. I then demonstrate adding a row to the SQL Azure database, reloading the page, and seeing the new data.

The jQuery grid gets the data from a completely unrelated data source. The data source has a back end Azure database with a web service that exposes data in JSON format. In the video, I simple add a row to the table and the web service does the work of exposing the data in a format I can read with my jQuery

Here is the JavaScript and jQuery for the page demonstrated in this example (in its entirety). Note that the code uses the jQuery implementation of a JavaScript Promise pattern (.deferred). It is not necessary here but was used to illustrate the pattern.

Instruction Guide for Installing SQL Server 2012 SP1 for SharePoint 2013

This instruction guide will walk you through how to install SQL Server 2012 SP1 for SharePoint 2013 on Windows Server 2012.

  • You have a clean install of Windows Server 2012 x64
  • You are installing SQL Server 2012 in a disconnected environment. That is, the SQL Server has no internet access. Note that the instructions don't vary much if you are installing in a connected environment.
  • You are doing a basic installation with minimal Reporting Services, Analysis Services, or BI tools, for example. You can always add those later.

Step 1

Install .NET 3.5 on Windows Server 2012. This is simply a Windows Server 2012 Feature. If you don't do it now, SQL Server 2012 will try to install it for you. It may fail, however, because SQL Server 2012 installation media doesn't include .NET 3.5 and it may not be able to access the appropriate installation source.

Step 2

Once you have installed .NET 3.5, start the installation of SQL Server 2012 SP1. Select the "Installation" link on the left hand side of the installer and then select "New SQL Server stand alone installation or add features to an existing installation"

Step 3

Next the setup support rule checker will run. If everything passes, it will look like this (note that I clicked "View detailed report" to look at the pretty green check boxes).

Step 4
Enter the appropriate license key if necessary

Step 5

Check the box to accept the license terms. In a disconnected environment, I do not send feature usage data to Microsoft.

Step 6

In a disconnected environment, you will not be able to download product updates from the installer and you will get this snazzy error message. Click Next

Step 7

Select the basic components to install. The Database Engine Services is obviously the reason we are installing this in the first place. So select "Database Engine Service". I usually install the complete management tools on the SQL Server (although this is not necessary) so that I can easily manage things from the server if I need to.

Note that in the image below, it says "Windows feature(s) to be turned on: Microsoft .NET Framework 3.5" You will see this if you did NOT install .NET 3.5 as I recommended in step 1.
If you did NOT install .NET 3.5 as recommended in step 1, you will also get this warning on the next screen. It clearly states that it may not be able to access Microsoft .NET Framework 3.5 (although it will try), which is why I recommend installing it in the beginning yourself.

If you did install .NET 3.5 as recommended in step 1, you should see a screen like this at this point:

Step 8

At this point, you will pick the named instance and the instance root directory. I always setup my SharePoint SQL Servers with a Named Instance. Note that you do not normally need to change the Instance ID. That will populate for you based on the Named instance. This is also where you specify the instance root directory. Normally you want this to be your data drive.

Step 9

In this step, you specify the service accounts that both the database engine and the SQL Server Agent will use. In the screen shot below, I am using the default settings. But you actually want to specify a domain account. For details, please see Todd Klindt's SharePoint 2013 service account suggestions here

If you were following Todd's suggestions, you would use YOURDOMAIN\sql_server for both the Agent and the database engine. Note also that the installer defaults the SQL Server Agent Startup Type to Manual. I usually set that to Automatic, however, because I am usually scheduling some kind of job in the environment I am working in. If you plan to schedule SQL jobs in SQL Server, set the Startup Type for the SQL Server Agent to Automatic

Step 10

The default SQL Server 2012 Collation is shown in the screen shot below. Typically, you do not want to use that collation, especially if you are upgrading a SharePoint 2010 environment.

The safest bet is to set the collation Latin1_General_CI_AS_KS_WS.

You can find more information about it here:

In order to set the correct collation, select the customize button and check the appropriate boxes shown in the second screenshot below.

Step 11
On the next screen, you will select either Windows Authentication Mode or Mixed Mode for SQL Server. Generally, unless you have a reason to do otherwise, select Windows Authentication Mode. At this point you can also add SQL Server Administrators.
Step 12
Assuming you are following all of the appropriate recommendations and requirements for your environment, you will want to specify the appropriate default locations on the Data Directories tab. In the screen shot below, I have everything point to C:, but typically you would have it broken down as follows:
DATA Drive: User database directory (this is where SharePoint databases will go. In this case, SharePoint is the user)
TEMPDB Drive: Temp DB Directory and Temp DB log directory.
LOG Drive: User database log directory.
Step 13
I typically do not enable FILESTREAM unless I am using Remote BLOB Storage.
Step 14
In a disconnected environment, I do not send Windows and SQL Error Reports to Microsoft
Step 15
One more rule check:
Step 16
Click Install:
If you have any additional notes or suggestions, please let me know!

Saturday, January 26, 2013

Manually Install Microsoft App Fabric Prerequisite for SharePoint 2013

In order to manually install Windows Server App Fabric for SharePoint 2013, first download the install file:

assuming you store the file at c:

run the following commands from an elevated PowerShell command line:

$file = “c:\WindowsServerAppFabricSetup_x64.exe”

& $file /i CacheClient”,”CachingService”,”CacheAdmin /gac
Note the double quotations around the commas.

Friday, January 4, 2013

An Essay on the Strategic Importance of the SharePoint 2013 App Model

The SharePoint 2013 app model introduced the concept of a piece of functionality that is either wholly or in part disconnected from the SharePoint platform--an app. The purpose of this post is to articulate my thoughts on the strategic importance of this technical advance in the SharePoint platform architecture.

Amit Gandhi has (at the time of this writing) created a series of three blog posts that explain:
  1. Why Apps are Needed for SharePoint 2013
  2. An Introduction to the SharePoint 2013 App Model
  3. App Hosting and Branding
Rather than restate what Amit states very clearly in his posts, I am going to assume that you have read the posts and/or understand the concepts presented there.

In my opinion, there are two key strategic elements to the creation of the App Model on SharePoint. The second is a consequence of the first.
  1. Content Storage versus Delivery: Data and functionality are no longer contained within or limited by the SharePoint platform architecture.
  2. Unique Content Delivery Arrangement: SharePoint, with the advent of the App Model, has evolved into a delivery mechanism by which companies can uniquely brand and deliver sets of web-based data and functionality. For some companies (primarily where data and/or functionality are commodities) SharePoint will be the mechanism for developing a competitive advantage. That is, to the extent it cannot be copied, the unique arrangement of data and functionality is the competitive advantage for companies that deal in commodity data and functionality.
Content Storage versus Delivery
SharePoint will, for the foreseeable future continue to store content. Increasingly, however, the storage and maintenance of content is being abstracted away from the SharePoint platform. Instead, SharePoint is evolving into an increasingly more flexible content delivery platform.

In SharePoint 2003, content was created primarily via lists and stored in SQL Server. SharePoint 2007 introduced the BDC and the ability to work with data from enterprise applications and third party systems. In SharePoint 2010, the BDC became BCS and the flexibility increased.

From SharePoint 2003 all the way through SharePoint 2010, however, data was either imported into SharePoint or it was mapped to SharePoint through the BDC and later the BCS. SharePoint 2013 and the App Model introduces a key evolution of the concept of data flexibility, however. Complex data structures no longer have to be stored in SharePoint or mapped to SharePoint to be integrated with SharePoint. Data could live in the cloud, a third party system, or anywhere, really. Ultimately, the app hosting architecture determines the limitations imposed on the sourcing and processing of the data.

The app sits inside SharePoint. It can communicate real-time with SharePoint data, authenticated user information, etc... For most practical purposes, it is fully integrated with SharePoint. However, the app infrastructure, data sourcing, and component architecture could be completely (or partly) hosted in cloud services. The app could theoretically be running and utilizing a .Net 5.0 Framework (while SharePoint 2013 runs on .Net 4.5) that leverages data from multiple third party sources, compiles and analyzes the data in a unique way, and then presents it in a SharePoint corporate intranet based on unique corporate user groupings.

The app model is an evolution in the disaggregation of the logical architectural components of the SharePoint platform. In essence, SharePoint is fine tuning its specialty (content delivery) by further abstracting its secondary purpose (content storage) through the creation of the App Model.

Unique Content Delivery Arrangement
SharePoint, as a content delivery platform, must continuously evolve to increase flexibility and foster creativity in the delivery of data and functionality. To this end, SharePoint is allowing companies that deal in commodity content to foster a competitive advantage simply by creating a unique content delivery arrangement that cannot be easily copied. In some cases, this is merely a brand. In other cases, the competitive advantage may actually reside in the precise placement of functional components. In even other cases, competitive advantage may stem from complex data analysis presented in a uniquely unified way.

In all cases, however, through the advent of the App Model, SharePoint is furthering the goal of maximum flexibility and creativity in the delivery of content.

I expect the exact strategic implications of the SharePoint App Model to play out in the marketplace for many years to come. It seems clear, however, that with SharePoint 2013 and the App Model, Microsoft hardened SharePoint's position as a content delivery platform.