Tag: SharePoint 2010

Schedule Data Refresh for SSAS Connected Excel Workbooks with PowerPivot for SharePoint

Published March 24, 2015

Using Excel Services, SharePoint users have been able to share workbooks that are connected to back end data since SharePoint 2007. Typically, the connection is made to SQL Server, or to Analysis services although a wide variety of sources are available. It’s also possible to publish individual components from these workbooks anywhere within the site collection through the Excel Web Access web part. Users can navigate to a dashboard page that contains all sorts of elements including an Excel chart that is connected to back end data. Well, to be precise, it was connected to back end data, the last time the workbook was saved. The workbook itself can be refreshed, but only manually.

When you open an Excel workbook in a browser through Excel services, by default, you’ll see the visualizations and any stored data in precisely the way that the workbook was when it was last saved. If you need to see more up to date data, you can select “Refresh Connections”. If (and sometimes that’s a big if) the server and connections are set up properly, the server will fetch updated data and update the workbook.

This works well enough, but the problem is that when you, or anyone else opens the workbook again, they’ll still see the old version of the workbook, and will need to manually refresh the date again. In addition, any visualizations published elsewhere on a dashboard will also continue to show old data unless manually refreshed. If the amount of data is significant, this poses a serious performance issue to the server(s). There’s also a significant usability impact in that it’s a pretty big ask of an end user to have them constantly hitting a refresh button.

To get around this issue, one option is to set the refresh options in the data connections of the workbook. Excel Services respects these options. There are two settings that we need to be aware of, periodic refresh, and refresh on open. Connection properties can be accessed within the Excel client by selecting the Data tab, choosing Connections, then highlighting the connection in question and selecting Properties.

Periodic refresh will allow the workbook to be automatically refreshed in the background while it is opened in the browser. This can be useful when the source data is changing frequently. Refresh on opening will have the greatest impact in our scenario, as it will automatically refresh the data in the workbook whenever the file is opened. This will also work with published objects (Excel Web Access web parts) – every time that the web part is opened, the data will be automatically refreshed. This solves the usability problem above because the user no longer needs to manually update the data. However, it does not affect the server load problem.

Due to the fact that the data and visualizations retain the state that they had when the workbook was last saved, it also affects search. When the search indexer runs, it will only index the data that is saved in the workbook. It has no means of refreshing the data. Finally, in addition to the load imposed on the servers by constant refreshes, if the quantity of data being refreshed is large, users can experience significant lags when loading the file. This obviously introduces another usability option. While the refresh options in Excel are helpful, they don’t fully solve the problem. What is needed is a way to automatically open the file for editing, refresh the data, and resave it to SharePoint.

If you have ever used Power Pivot for SharePoint, you know that it can do exactly that. Power Pivot for SharePoint contains two primary elements – a specialized instance of SQL Server Analysis Services that allows users to interact with workbooks that contain embedded PowerPivot models, and a SharePoint service application that among other things, keeps those embedded models refreshed. Using the PowerPivot Gallery (enabled when PowerPivot for SharePoint is installed), you can configure a workbook’s refresh options by clicking on the icon in the Gallery view, or by selecting “Manage PowerPivot Data Refresh” in the simple All Documents view.

Data Refresh options in PowerPivot Gallery View

Data Refresh options in All Documents View

Once configured, the PowerPivot for SharePoint Service will refresh the data model in the workbook on a periodic basis (no more than once per day). The service essentially opens the workbook in edit mode, refreshes all of the data connections, and saves the workbook back to the library. If versioning is enabled, it will be saved as a new version. Unfortunately, if you’re not using a PowerPivot data model, the options are unavailable. In Gallery view, the icons are simply unavailable, and while the option is available in the All Documents view, selecting it results in an error.

On the surface, it would seem that using workbooks with PowerPivot is the only option for keeping large volumes of back-end data up to date in Excel visualizations. However, there is a small loophole that you can take advantage of.

The refresh function in PowerPivot for SharePoint refreshes all of the connections in a workbook. While this option is unavailable if the workbook has no embedded PowerPivot model, when it does, it refreshes ALL of the data connections in the workbook, whether they connect to a model, a back end SSAS server, SQL server or whatever. So therefore, if you want to keep your connected data refreshed, the solution is to add a dummy PowerPivot model to your workbook.

Simply open up the PowerPivot window, import some small amount of data from an external source, and save it. Once saved, the PowerPivot refresh options will appear, and you’ll be able to schedule data refresh for your workbook. You can even deselect the refresh of the source data for your dummy model, and the other connections will work just fine.

Once your workbooks are being updated automatically, your users will be presented with up-to date data on load with no delays, all dashboard visualizations will be up to date and quick to render, and the visible data will be picked up by your search crawler. All will be well with the world.

3 Comments

Reporting Services Web Part Error After SharePoint Upgrade

Published December 10, 2014

I recently completed an upgrade of SharePoint 2010 to 2013 for a customer that was using Reporting Services integrated mode fairly heavily. After the initial upgrade however, I was getting the following error whenever I tried to access a page that contained a Reporting Services report viewer web part:

Web Part Error: A Web Part or Web Form Control on this Page cannot be displayed or imported. The type Microsoft.ReportingServices.SharePoint.UI.WebParts.ReportViewerWebPart,Microsoft.ReportingServices.SharePoint.UI.WebParts, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 could not be found or it is not registered as safe.

The version of Reporting Services on the originating SharePoint 2010 farm was from SQL Server 2008 R2 (version 10) and the version in the new farm was SQL Server 2012 SP1 (version 11). The message pretty clearly indicates that it was having trouble loading a version 10 instance of the web part assembly, so I opened up the web.config file for my application, and sure enough, while there was a safecontrols entry for version 11 of the assembly, there was none for version 10. There was a binding redirect for the assembly itself (redirecting from version 10 to 11), so this was pretty clearly a bug in the installation process (SQL server install team take note!).

This would only affect content brought forth from an older SSRS system which is what I had. One solution would be to re-add all of the web parts onto the relevant pages. That would explicitly use new assembly references, but would also take a great deal of time. Another would be to directly edit the web.config files of all of the farm applications. While not generally considered best practice, this would solve the problem, especially since it corrects an oversight.

Hunting around, I came across my friend Chris O’Connor’s blog post on this very topic. Chris had this exact issue, and a rather large farm with multiple applications to deal with, so he built some PowerShell script to add these entries back in. The complete script is in Chris’ post.

In any event, regardless of the method used, adding the safe controls entry into the web.config file(s) fixes this particular issue. On to the next problem…..

4 Comments

How to Connect to PowerPivot Workbooks With PerformancePoint

Published April 25, 2014

Scorecards, KPIs, and analytic charts and grids are at the core of PerformancePoint’s value proposition. In order to effectively work with them we need to work with a multidimensional data source, which means Analysis Services. Traditionally, this has meant building OLAP cubes, which is a daunting prospect for those unfamiliar with the process. More recently, the appearance of the tabular data model in Analysis Services has lowered the bar significantly, but still requires a connection to a full blown Analysis Services server.

At the same time, the proliferation of PowerPivot, and PowerPivot for SharePoint has democratised the development and sharing of multidimensional data models for the power user. Unfortunately there is no “PowerPivot” connection type in PerformancePoint, so it would appear that the advanced PerformancePoint tooling is beyond the reach of our shared Power pivot models. The good news is that it’s not – its just not obvious as to how it can be done.

First, we need to step back a bit and talk about how PowerPivot for SharePoint works.

PowerPivot for SharePoint actually consists of two components. First, there is the Service Application that runs in the SharePoint farm that is responsible for performing data refreshes, and usage analytics. The main part however is actually an instance of Analysis Services using the tabular engine. It’s properly referred to as Analysis Services SharePoint Mode, and as of SharePoint 2013/SQL Server 2012 SP1, it can be installed standalone. However, it is most commonly installed on SharePoint front end servers.

You can see this in action by opening up SQL Server Management Studio, and connecting to the PowerPivot Instance on a SharePoint front end server. The instance is normally named PowerPivot:

In the case above, the SharePoint front end server is named NautilusSP. You can also see that there is a model being hosted by the server already. The model is named by taking a workbook, and adding a GUID to it. This is done by Excel Services the first time that a model is interacted with. For example, if we add the file Health.xlsx, which contains an embedded PowerPivot model, and immediately refresh the object explorer in Management Studio, we will see that nothing has changed. However, if we then interact with the model at all, by clicking a slicer, or opening a pivot table category, we will see that the model has been automatically created for us.

The first interaction with the model will be noticeably slower than all subsequent interactions for this reason.

Now, since this is actually an instance of Analysis Services, we should be able to connect Excel to this model, and do analyses from it. In fact, we should also be able to create a PerformancePoint data connection that points to this model, allowing to use PerformancePoint Scorecards, and analytic charts and grids. We can in fact do both things, but there is a major problem with doing so.

These models are temporary. If they haven’t been used for a period of time, they get deleted. Also, if the source workbook is updated, a new model is automatically create upon first interaction. This can be seen if we edit, and save our Health.xlsx workbook, and then open it in the browser and interact with it.

The original model will be deleted in a garbage collection process. We therefore cannot reliably target these models, as any reference will become invalid relatively quickly.

The good new is that we can use Excel to analyze these models by using the “Open New Excel Workbook” button in the PowerPivot Gallery. This is the leftmost of the three icons to the right of any workbook in the gallery.

Clicking on this action will download an odc (Office Data Connection) file, which will open up Excel, and establish a connection with the underlying model, allowing us to do further analysis on it as if it was hosted in Analysis Services (because it IS hosted in Analysis Services). This connection will work no matter what the name of the underlying model, and if the model doesn’t yet exist, it will be created.

Unfortunately, nothing like this automated connection creation exists for PerformancePoint.

To see what’s going on, we can open the connection itself within Excel, and then view its properties to find the connection string.

We can see that it is a standard Analysis Services connection string, with an interesting twist. While the value for “Initial Catalog” is in fact our temporary model, the value for “Data Source” is the URL of the Workbook. Excel Services will automatically direct calls to this workbook to the appropriate data model. if the model has been changed, it knows, and will serve the appropriate content, so our new analysis workbooks will not become invalid.

The good news is that we can use this within PerformancePoint as well. All we need to do is to open up PerformancePoint Dashboard designer, create (or edit) a connection using the Analysis Services type, and select “Use the following connection” which allows for a connection string.

For the connection string, all that we really need is the data source parameter, which is the URL to the workbook. Once entered, we see the appropriate model name in the dropdown for “Cube”. Once selected, this data connection will work like any other Analysis Services data connection. We can now build KPIs, Scorecards, and Analytic charts and grids from the model embedded in out Excel workbook, hosted in SharePoint. If the workbook changes, or the temporary model gets deleted, Excel Services will take care of recreating it on the next interaction.

It is possible to use PowerPivot for SharePoint with PerformancePoint.

2 Comments

How to Change the Language for a SharePoint Site

Published March 29, 2014

If you’ve ever worked with multiple language packs for SharePoint, you’ll know that after you add a language pack to a farm, you’ll have the option of selecting a base language for any new site that is created.

The default language will be that of the site collection, but all installed language packs will be available. All of the system generated text in that site will be presented in the language of the site. This has been true since SharePoint 2007. SharePoint 2010 introduced the MUI (Multilingual User Interface), which, if configured, allowed the user to switch the language of the system generated text. SharePoint 2013 retains the MUI, but the way it is used has changed. However, all versions of SharePoint share a common limitation.

Once a site is created, its language cannot be changed. No way, no how. Well, at least not in any supported way.

I recently encountered a situation where a customer wanted to move their Internet facing site to SharePoint 2013. It was a multilingual site that used variations. However, when it was originally set up, no language packs had been installed. Both variation sites (English and French) were based on English. Although the content in the French variation site was in French, all of the system text was in English. This obviously needed to be corrected as any system text would pop up in English. A significant investment had been made into the content, so re-creation wasn’t our first choice.

An attempt was made to use the export function (using stsadm –o export – I’m old school). While the content exported just fine, it couldn’t be imported into a newly created French site, because the source site was in English. A little bit of web searching found Mirjam’s Van Olst’s article from 2008 on how to change a site’s language. This article was written for SharePoint 2007, and described how the content database could be directly updated to change the language for one or many sites.

Unfortunately, as Mirjam correctly points out, monkeying with the content database voids your warranty, and leaves SharePoint in an unsupported state. She also points out that this approach doesn’t work well for publishing sites, which is what we were dealing with. Our goal was to wind up with a clean system, so this wasn’t going to work for us, at least not as a complete solution.

The beauty of this approach however is that if you’re willing to compromise your content database temporarily, you can literally change the language of the site. Using this approach, we were able to set the language to 1036 (French) for all sites, export the French variation, and then change it back. Now technically, we’ve edited the content database, and rendered it unsupported. However, this doesn’t matter, as we wanted to import the content into new (French) variation site in a new, untouched content database,

This approach works, and unless I’m mistaken, should be totally supported. To be clear the steps taken are:

Back up the source content database (always a good idea)
Open SQL Server Management Studio, Connect to the content database in question , and create a new query. Any of Mirjam’s update statements would work, but this one is easiest
```
UPDATE dbo.Webs SET Language = 1036
```
Immediately export the site and all subsites. In my case I used stsadm, but of course PowerShell can be used, as can Central Administration.
```
stsadm-o export –url http://xxxx.xxxx.xxx/fr-ca –filename frenchsite 
```
Once complete, set the source site back to English
```
UPDATE dbo.Webs SET Language = 1033
```
Create a new site collection in NEW content database. Create the destination site using French (or allow the variations system to create it)
Import into the destination French site
```
stsadm -o import -url http://yyy.yyyy.yyy/fr-ca -filename frenchsite.cmp
```

The source content is successfully migrated into the destination site. Now, technically, because the source database has been directly modified, it’s in an unsupported state, and should be discarded. However, I have yet to see any ill effects. The good news is that the destination content database is pristine, and therefore this approach should be supported.

While we technically haven’t changed the language for an existing site, we have achieved the goal of getting the French language content into a proper French language SharePoint site.

How to display Specific Content to Anonymous or Authenticated Users in SharePoint

Published July 6, 2013

A long time ago I wrote about the usefulness of the SPSecurityTrimmedControl in selectively displaying content based on a user’s permission level. It supports a myriad of different permission options, and my friend Marc Anderson has an excellent post in which he outlines all of the possible permission levels that can be used with this control by manipulating the PermissionsString attribute.

What is less well known about this control is that it can also be used in an application that supports anonymous access to selectively display content based on the user’s logged in state. This might be to display a custom log in button or link that should only be displayed when a user has not logged in. The way that this is accomplished is through the AuthenticationRestrictons attribute. There are three possible values:

AllUsers
AnonymousUsersOnly
AuthenticatedUsersOnly

I have no idea why the AllUsers value exists. It’s not much of a restriction. The other two values are pretty well named, so I won’t bother explaining them.

A pretty typical usage scenario might be to build a page layout that will display a content field for all users, another field exclusively for anonymous users, and another for logged in users. The exclusive fields would need to be created and added to a content type prior to the creation of the layout. A simple example of this might appear as follows:

<PublishingWebControls:RichHtmlField id="PageContent" FieldName="PublishingPageContent" DisableInputFieldLabel="true" runat="server"/>
<SharePoint:SPSecurityTrimmedControl runat="server" ID="spAuthenticated" AuthenticationRestrictions="">
    <PublishingWebControls:RichHtmlField FieldName="PageContentAuthenticated" runat="server" />
</SharePoint:SPSecurityTrimmedControl>
<SharePoint:SPSecurityTrimmedControl runat="server" ID="spUnAuthenticated" AuthenticationRestrictions="AnonymousUsersOnly">
    <PublishingWebControls:RichHtmlField FieldName="PageContentAnonymous" runat="server" />
</SharePoint:SPSecurityTrimmedControl>

In this example, the PublishingPageContent field is displayed on the page first, and depending on the logged in state, one of the remaining fields will be displayed. Of course, in order to use the control, the WebControls directive must first be added to the page:

<%@ Register Tagprefix="PublishingWebControls" Namespace="Microsoft.SharePoint.Publishing.WebControls" Assembly="Microsoft.SharePoint.Publishing, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

The above example is for SharePoint 2010, but the namespace also exists in 2013.

This is simple enough, but there is a practical problem with this approach. How do we edit the content for anonymous users on the page? Although the control is there, editors are going to be authenticated users, and therefore the content will be completely hidden from them.

To do this, we can treat the Anonymous filed like other page metadata, and include it (also) in an edit mode panel, without the spSecurityTrimmedControl.

<PublishingWebControls:EditModePanel class="ewiki-margin" runat="server">
    <PublishingWebControls:RichHtmlField FieldName="PageContentAnonymous" runat="server" />
</PublishingWebControls:EditModePanel>

The contents of the edit mode panel are only displayed when the page is in edit mode, so authors will be able to edit anonymous content and authenticated content in one step.

This control isn’t limited to publishing scenarios, but does require the Publishing namespace, and therefore requires at least SharePoint Standard license.

1 Comment