I had an interesting (annoying) error this week at a customer site. I was adding a new server to an existing SharePoint farm. This was a relatively new farm, installed a couple of months previously. This farm has SQL Server Reporting Services (SSRS) installed. The new server wasn’t running the SSRS service application, but as with any WFE server, it needed the SSRS SharePoint add-in installed.
Although the SQL Server Reporting Services add-in is included as part of the SharePoint Server prerequisites install, it’s an older version. Since in this case I was using SSRS 2012 SP1, so I needed to manually install it. However, attempting to do so resulted in a rather nasty failure error 1603, and the add-in simply refused to install. I hadn’t seen this one before.
Poking around a bit, I found that you can get this error when the installed version of the bits on a SharePoint server don’t match with what has been applied. This is the state of a server after a Cumulative Update or Service Pack has been installed, but PSConfig (the Products Configuration Wizard) has not yet been run. I then ran the configuration wizard on the new server, and it did in fact indicate that a upgrade was needed. This was a bit confusing, as I had only just installed the SharePoint bits on this machine. How did this happen?
Well, as has been my practice for some time, whenever I install SharePoint bits on a server, I immediately run Windows update to make sure that everything is nice and new. It’s a new server, that’s the worst that could happen, right? Wrong. As I was aware (but had forgotten about), in September, Microsoft started delivering SharePoint updates along with regular Windows updates. This, in my opinion, is not a good thing, and is why as pointed out by Todd Klindt, you should no longer enable auto update on your SharePoint servers. You should apparently be careful of doing manual updates as well.
My little Windows update had snuck in some SharePoint updates causing the upgrade requirement, and the problem with the SSRS add-in. In addition, since this was a multi server environment, it put this server out of step with the other servers. After bringing the rest of the servers up to the latest update level, and running PSConfig on all of them, I was able to install the SSRS add-in on the new server. All was right with the world again.
We recently performed an installation of SharePoint 2013 with SP1 for a customer using the slipstreamed ISO available on MSDN. Part of the rollout in this case involved migrating a number of reports from a standalone instance of SQL Server Reporting Services (SSRS) to SSRS running in SharePoint integrated mode. However, during the creation of some new reports, we observed something odd. The custom parameter values were not being saved for web parts that were using these reports.
SSRS web parts in SharePoint allow for a report to be displayed within a web part, a therefore in part of the page. Report parameters created in the report can be exposed as web part properties. Therefore, you can have multiple instances of the same report on different pages using different parameters. You can also connect the web part to other web parts, such as SharePoint filters, and have the parameters driven by these filter values. However, in this particular case, none of it was working. The parameter could be saved, and applied, but when the page was saved, the parameter values would revert to their defaults.
After doing some digging, I came across this discussion on the MSDN forums. The discussion indicated that there may be a regression bug in SharePoint 2013 Service Pack 1 that exhibited this behaviour, and that the application of the May 2014 Cumulative Update had helped them somewhat. We had never seen this issue with builds prior to SP1. Given that our farm had not yet gone live, we quickly acquired and applied the July 2014 CU in the hopes that it would help.
In our case, it fixed the problem completely, however there was one small negative side effect. All of the web parts that had been added to pages before the update completely lost all of their saved properties, including the report that they were pointing to. In our case, this was no big deal, but if you have a lot of these, it could be an annoyance. I also don’t know if this would happen generally with the July 2014 CU, or it was only because that the original system was affected (as I suspect).
In any event, this apparently was a regression bug introduced with SP1, and applying the July 2014 CU clearly fixed this problem for us. Our new baseline for SharePoint 2013 will not be sP1, but SP1 with the July 2014 CU.
I recently encountered a relatively puzzling circumstance on a multilingual blog site. In this scenario, the customer was using the SharePoint blog as part of their public facing web site and while the English site was working fine, the French site was not. More accurately, I should state that it wasn’t working for me, but I’ll get to that shortly.
For the most part, the French blog site was working. The posts showed up on the main page, and could be accessed. However, if you opened up the source list for the posts, either by using “All Content” or by selecting “Manage Posts”, the posts would appear, but clicking on them would result in the error “Page not found – The page you’re looking for does not exist”.
The first clue was that although the list is named “posts”, the site itself is a French site, so the actual list name is “Billets” as seen below.
This difference is a feature of SharePoint 2013, and it happens because this is a multilingual site that has been enabled for multiple language display. In my case, my user profile identifies my preferred language as English, and SharePoint automatically translates all system generated text into English. With this feature, an English system administrator can easily navigate a French site because pages like system settings are automatically translated.
Unfortunately, in the case of the blog site, the translation system gets a little overzealous. Clicking on one of the blog posts from the system view results in the error mentioned above. Why is there an error? All we need to do is to look at the URL to discover the reason.
As you can see, the list name requested is “Posts” but we know that the actual name is “Billets”. The URL itself is being translated for me. So how do we avoid this problem?
As good as the translation system is, it’s not worth the error, so we need to turn it off for this site. Doing so is as simple as turning it on. From the Site settings page, select “Language settings” under the Site Administration section.
From there, simply deselect all alternate languages, and automatic translation will no longer be performed.
Once this is done, the URL will no longer be translated, and opening posts will work. You will now need to navigate the site settings in a different language, and know that “Language settings” is the same as “Paramètres linguistiques”, but most SharePoint admins can navigate here with their eyes closed anyway.
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.
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 SETLanguage = 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.
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.