Project Server DR Failover SQL Cube Issue

Fortunately I do not often have to enact my customer’s disaster recovery plans, but when I do I am glad for things like SQL mirroring and SharePoint database fail-over.

No plan is perfect though, in this particular case Project Server OLAP cubes would refuse to build while failed over to the mirror server, giving only the following errors;

[8/01/2013 8:35 AM] Failed to build the OLAP cubes. Error: Error Adding DataSourceView ‘Project Reporting data source’ to database ‘FullCube1_Srv2’. Error: Error Adding Table ‘MSP_TimeByDay_OlapView’ to database. Error: Login timeout expired

A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

 

This problem is clearly caused by the SQL connection string, which in the configured cube looks like this:

Provider=SQLNCLI10;Data Source=primarysqlserver;Initial Catalog=ProjectServer_Reporting;Integrated Security=SSPI;Persist Security Info=False

 

As you can see the ‘Failover Partner’ parameter is missing, at first I thought that this was a classic case of not RTFM, if you neglect the steps detailed in the TechNet article (http://technet.microsoft.com/en-us/library/ff872145.aspx) on configuring SQL server mirroring for Project Server but instead only do the SharePoint configurations (which does get things online) then some Project Server specific connection strings are not updated.

The fix would seem to be pretty simple:

Set-SPProjectWebInstance …[insert all required parameters – ie all of them]… -PrimaryDBMirrorServer mirrorsqlserver -ReportingDBMirrorServer mirrorsqlserver

(Reference: http://technet.microsoft.com/en-us/library/ff607532(v=office.14).aspx)

 

HOWEVER, this does not appear to fix the cubes, which will continue to fail with the above error on build! The above steps do update the Sample report data sources in the BI site, but even if you create a new cube it still builds without a ‘Failover Partner’ configured:

clip_image001[6]

To get to the above open SQL Management Studio and connect to your Analysis Server, then from the Cube –> Data Sources properties you can edit the data source used.

The good thing is that there is a temporary workaround while in a failed-over scenario, just manually update that connection string with the correct failover partner. The problem with this is that each time PWA builds the cube this change will be overwritten, but you could automate that if need be (or disable PWA build and schedule the build in SSAS directly).

 

Hurrah! Now I can get back to my day job.

Cube Building Errors after SP1 Installation

A customer of mine had this one after completing the upgrade to SP1 recently, basically all cube building would fail with the following error:

Your CBSRequest job failed. Its current state is FailedNotBlocking. It was 0% complete. It entered the queue at 09/02/2011 11:00:26.

[snip…]

The errors returned from the queue are as follows:

Error ID: 17007

Error ID: 26000

[snip…]

<class name="CBS message processor failed">

<error id="17007" name="CBSOlapDatabaseSetupFailure" uid="7d14c29d-a133-492b-baea-2e7c0bec444b" QueueMessageBody="Setting UID=00007829-4392-48b3-b533-5a5a4797e3c9 ASServerName=server ASDBName=FullCube1 ASExtraNetAddress= RangeChoice=0 PastNum=1 PastUnit=0 NextNum=1 NextUnit=0 FromDate=01/04/2010 00:00:00 ToDate=12/31/2011 00:00:00 HighPriority=True" Error="Error Setting Olap Database ‘FullCube1’ roles: Error: This method can only convert identity claims, and only when a logical conversion exists.&#xD;&#xA;Parameter name: encodedClaim" />

This customer is using Claims-NTLM (ie AD users via Claims) for logins and that seems to be the cause of this issue. The give away is clearly in the error: “This method can only convert identity claims”.

Solution

Fortunately the solution turned out to be rather simple, it seems that SP1 does some additional checking when setting up the Cube Roles, as a result when users in the Project Server have issues then this error is caused.

A little more info can be seen in the ULS log:

09/02/2011 12:00:33.18  Microsoft.Office.Project.Server (0x17B0) 0x1A04  SharePoint Foundation   Claims Authentication d01p Medium  ConvertWindowsClaimToWindowsPrincipalName() encountered error: Some or all identity references could not be translated.

As it turns out a number of users in PWA have left the company and in this case as no AD-sync is used some of the accounts had been deleted from Active Directory but not updated in PWA server settings.

It seems also that accounts set as “inactive” also caused this error if they were in a group with the Global “View OLAP Cubes” permission.

The fix was to remove those user accounts from the PWA groups ‘Project Managers’, ‘Portfolio Managers’ and ‘Executives’ (and any others with the above permission).

 

Finally I just tested this in a non-Claims lab, and the problem doesn’t occur, in fact the cube log identifies and lists the invalid account then continues processing, so technically I would call this one a code defect.

 

Hope that helps someone else out there!

Your First PerformancePoint Report

One of the biggest areas of advancement in Project Server 2010 is reporting, with Project now utilising; Excel Services, SQL Analysis Services, SQL Reporting Services and PerformancePoint the options are almost endless.

However most people I encounter look at that list and say; “What on earth is PerformancePoint?”, so lately I had another opportunity to flex my PPS skills and thought I might share my experience to give you a taste of how powerful yet simple to use this new thing can be.

 

Creating a Dynamic Resource Utilisation Graph using PerformancePoint

I’m going to keep this very simple, as I find PPS to be very often quite mind-boggling, so to give you a taste of it what I’ll describe here is how to create a simple equivalent to the old typical Data Analysis Resource Utilisation view.

So before you begin, make sure you have your Cube setup and built, the PerformancePoint service application setup and running in your SharePoint installation and some data to play with.

First step you need to get to the PerformancePoint Dashboard Designer, this is a nifty little web application (independent from the Office Web Apps) that is automatically installed and made available when you provision a PWA site.

To find it, open up the Business Intelligence site, and from there the quickest way to get to it is by hovering over any of the landing page images (Create Dashboards, etc) and selecting the ‘Start using PerformancePoint Services’ link.

image

You then have the icon to run Dashboard Designer:

image 

This should download and install the designer for you, and assuming that you have all the correct permissions (and that PWA is in your Internet Explorer trusted sites), you will end up with a mostly empty designer window looking something like the image below.

Now you’re ready to start creating those reports, first step setup your Data Connections by selecting the Create tab on the ribbon and selecting Data Source:

clip_image001

Select Analysis Services as the connection type, and populate the connection details and properties as required:

clip_image002

For Analysis Services datasources you should populate the Time tab to ensure that your Time dimensions are correct, something like this;

clip_image003

Finally when you are done, select save from above the ribbon to continue.

Note: This is something you will get used to with the Dashboard Designer, everything is automatically saved to the PWA BI site in their respective locations (dataconnections, reports or dashboards), with just one exception being the ‘Workspace’ which is effectively your configuration of Dashboard Designer (something I don’t usually bother saving).

Now lets move on and create our report, select PerformancePoint Content and then from the Create ribbon lets select ‘Analytic Chart’;

clip_image004

Then select your datasource just created and hit finish, then your report will open in the designer;

clip_image005

Give it a name and then lets start adding content:

  • First add some measures to the Series; Work and Actual Work.
  • Now add your Resource List dimension to the Bottom Axis.

It should look something like this;

clip_image006

If you want to expand members of a dimension select the chevron (Down arrow next to the X), and select the members in the dialog.

clip_image007

Here for dynamic dimensions like the Resource list you are better off right-clicking and selecting one of the Autoselect Members, such as All Children, like so;

clip_image008

Which now looks like:

clip_image009

Finally we need our Time dimension, add it to the Bottom Axis from the right list and use the chevron to select the desired time periods (I’m selecting months by name here, however you can use something called Named Set’s to do this dynamically for you – another blog article maybe). Finally I think it’s best to move the Resource List to the top of the series Series list and apply some filters to filter out the blanks, to give us something like:

clip_image010

Don’t forget the Edit tab on the Ribbon which has a number of settings that you’ll find handy getting your report right.

Almost there now..

Okay so now we’re ready to save and see this thing for real, so hit the save button, and lets minimise the designer and go back to our BI site in Internet Explorer.

If you open the default ‘PerformancePoint Content’ link, you should see your new report listed, select the drop-down to Display the report:

clip_image011

Final product:

clip_image012

Now this report is ready to add to any where in SharePoint using the PerformancePoint viewer web part, and the best thing is that all of the dynamic functions will be available to all users, so if someone wants to view this report in terms of Cost / Actual Cost, it’s just a few clicks away:

clip_image013

Or maybe you want to see the breakdown of a particular person’s activities using the Decomposition Tree?

clip_image014

I’d say this beats those old Data Analysis views!

That’s it for this how-to, hopefully this scratching of the surface has shown you some of the potential of PPS, keep experimenting and you’ll see very quickly how easy it is to replace those old Data Analysis views that are so 1990!

In the future I might come back and write a Part II to this one on creating your first KPI Scorecard in PPS, stay tuned..

Analysis Services 2005 and Project Server 2010 errors

Came across this issue when building my cube using SQL AS 2005 with 2010 for the first time;

[7/23/2010 9:28 AM] Failed to build the OLAP cubes. Error: Failed to process the Analysis Services database PS2010TestCube1 on the PRJDBCLUSTER server. Error: OLE DB error: OLE DB or ODBC error: Class not registered.

Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘Project Reporting data source’, Name of ‘Project Reporting data source’.

Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘PS2010TestCube1’, Name of ‘Assignment Owner’ was being processed.

Server: The operation has been cancelled.

Fortunately the solution was not too hard to come by; it seems that you need to install the SQL 2008 Native client on the SQL 2005 server in order to fix this one.

 

Hope that helps someone!