Cascade Skyline - with Microsoft Logo and Project Support header - author Brian Smith

A New SQL Server–and some different cube building errors

A New SQL Server–and some different cube building errors

  • Comments 10

At SP1 Project Server 2010 and SharePoint introduced support for SQL Server 2012.  I had a question posted on some issues a customer had hit – so I thought I’d set my server up for building an OLAP cube against SQL Server 2012 – just to see if I could.  I already knew I’d need to set up a few things – but as I wanted to see errors I just went for it – and started with a PWA instance that built successfully against my SQL Server 2008 R2 Analysis Services machine.  My Project Server databases were also in this SQL Server 2008 R2 server – so that would add some other interesting things to consider.  I should also point out that my SQL Server 2012 was sitting on a Windows Server 2012 RC machine – so not a supported scenario currently – but I like to live on the edge (I know – I should get a life instead…)

Just to recap what goes on when we request to build a cube, which will help you understand where the errors might be coming from – and there may well be other errors you could see if things are configured differently to my scenario.

  • A Cube Build request goes on the Project Server queue
  • The details of the requested configuration are picked up – and the application server contacts the Analysis Services server (firewall permitting) with the cube details.  Also the account running the queue service will need the right permissions to do this – needs to be added as server administrator on Analysis Server
  • Once the Analysis Services server has the cube details then it will know where to get the data from (Reporting DB) so makes a connection. A couple of points here – if you are using an alias for the database server from your Project Server farm then this is the server name passed to AS – so it will also need to know what the alias refers to.  This is true even if the AS machine is also the DB Server.  And the account running Analysis Services needs data reader access to the reporting DB.

So I’ve done none of that preliminary setup, so I’m expecting some errors.  The first however, was unexpected:

Error 1.

[7/3/2012 7:00 AM] Failed to build the OLAP cubes. Error: Failed to connect to the Analysis Services server <ASServerName>. Error: Deserialization failed: Requested value 'EnterpriseCore64' was not found.

This was reported by the cube build – I’ll add the ULS and Event Log errors at the foot of the posting for the search engines to digest – and talking of search engines a quick Bing gave me the answer to this first issue – SQL Server 2012 Cumulative Update 1, which contains the fix described here - http://support.microsoft.com/kb/2683293 - Error message when you use AMO to connect to SQL Server 2012 Analysis Services: "Deserialization failed: Requested value '<ServerEdition>' was not found".  The ServerEdition could be one of

  • EnterpriseCore64 (as mine was)
  • EnterpriseCore
  • BusinessIntelligence64
  • BusinessIntelligence

The Cumulative Update itself can be found at http://support.microsoft.com/kb/2679368 - Cumulative update package 1 for SQL Server 2012.

Installed the CU and moved on…

Error 2.

[7/3/2012 7:50 AM] Failed to build the OLAP cubes. Error: Failed to process the Analysis Services database Test2012 on the <ASServerName> server. Error: Errors in the back-end database access module. The provider 'SQLNCLI10' is not registered.
The following system error occurred:  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 'Resource Type', Name of 'Resource Type' was being processed.
Errors in the back-end database access module. The provider 'SQLNCLI10' is not registered.
The following system error occurred:  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 'Timesheet Status', Name of 'Timesheet Status' was being processed.
Server: The current operation was cancelled because another operation in the transaction failed.
Errors in the back-end database access module. The provider 'SQLNCLI10' is not registered.
The following system error occurred:  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 'Timesheet Period Status', Name of 'Timesheet Period Status' was being processed.

This is very similar to an error you would see even before 2012, and refers to the Native Client for SQL Server SQNCLI10 – not being registered.  This is where we need to be very specific – and the version of the native client is related to our client application code that is requesting the build than either the Analysis Services or the SQL Server version.  Regardless of if you are running 2005 or 2008 or 2012 (or anywhere in between) you will need to have the 2008 Native Client installed from here - http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=8824 - Microsoft SQL Server 2008 Feature Pack, October 2008 – and specifically the sqlncli.msi which is about half way down the page – and the direct link to the X64 package is here.  Also note that this is referring to the build FROM the Analysis Services server – and it is complaining about the Native Client being missing from there – which it needs to connect to my database server (which happens to be 2008 R2 – but that is irrelevant).  If you look on the AS server you will see that the structure of the cube has been created – so we successfully connected to the server, but it could go off to the database server to get the data.  In a fresh install against SQL Server 2012 you would also need to add the Native Client to the application server – but as it is one of the SharePoint pre-reqs this is normally taken care of.  To confirm the native client requirement you can also look at the Connection string already created on the AS Server as part of the Project Reporting data source - Provider=SQLNCLI10;Data Source=<DBServer> ;Initial Catalog=ProjectServer_Reporting;Integrated Security=SSPI;Persist Security Info=False.  I have seen some workarounds described suggesting changing the Provider here – and that would work if you were to rebuild from AS it would get over-written at the next build. 

Added the Native Client to my Analysis Services 2012 machine and continued…

Error 3

[7/3/2012 8:33 AM] Failed to build the OLAP cubes. Error: Failed to process the Analysis Services database Test2012 on the <ASServerName> server. Error: Internal error: The operation terminated unsuccessfully.
Server: The current operation was cancelled because another operation in the transaction failed.
OLE DB error: OLE DB or ODBC error: Login failed for user 'DOMAIN\<ASServerName>$'.; 28000.
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 'Task List', Name of 'Task List' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'Start Date' attribute of the 'Task List' dimension from the 'BriSmith2012' database was being processed….

This error was longer – just repeated for different attributes of the dimension.  The key to this is the Login Failed – and in this case it refers to a machine name (the $ on the end) as my Analysis Services is running as

NT Service\MSSQLServerOLAPService

So the connection is via the machine name.  Added the DOMAIN\<MachineName>$ as a Login on the Databse Server with db_datareader role membership to my ProjectServer_Reporting database and continued.  If your Analysis Services is running under some other credentials then you may need to enter these rather than a machine name.

[7/3/2012 9:03 AM] Cube build request completed successfully.

In my installation I didn’t hit any permissions issues regarding the AS administrator, or any firewall issues – just because my server and domain setup was already taking care of these potential problems – your mileage may vary.

Overall, apart from the 1st error these are very similar, if not identical, to problems you could run into with any version of SQL Server – but hopefully a worthwhile reminder of the cube building process and the components and permissions it relies on.  Only Bing and any other search engine needs to read on from here…

Logs.

Error 1

ULS Logs – a string of errors here – the first few were Medium, then a couple of criticals – I’ve just posted a couple – these should contain anything worth finding on a search.:

07/03/2012 07:00:21.46    Microsoft.Office.Project.Server (0x27B4)    0x2834    Project Server    Analysis Cube Building    myne    Medium    PWA:/PWA">http://<Server>/PWA, ServiceApp:Project Server Service Application, User:DOMAIN\User, PSI: Error generating Project Server cubes. Error: 'Failed to connect to the Analysis Services server <ASServerName>. Error: Deserialization failed: Requested value 'EnterpriseCore64' was not found.'    4c420a8a-c27c-47b8-88c9-46baaf8ab1ec

07/03/2012 07:00:21.47    Microsoft.Office.Project.Server (0x27B4)    0x2834    Project Server    Analysis Cube Building    8swo    Medium    PWA:/PWA">http://<Server>/PWA, ServiceApp:Project Server Service Application, User:DOMAIN\User, PSI: [CBS] Status message: ProjectServer BuildOlapCubes failed with exception: Microsoft.Office.Project.PI.CBSCommon.CBSProcessException: Failed to connect to the Analysis Services server brismith12-15. Error: Deserialization failed: Requested value 'EnterpriseCore64' was not found. ---> Microsoft.AnalysisServices.XmlSerializationException: Deserialization failed: Requested value 'EnterpriseCore64' was not found. ---> System.ArgumentException: Requested value 'EnterpriseCore64' was not found.     at System.Enum.Parse(Type enumType, String value, Boolean ignoreCase)     at Microsoft.AnalysisServices.DesignXmlReader.ReadPrimitive(Type type, XmlAttributes attributes)     …

07/03/2012 07:00:21.47    Microsoft.Office.Project.Server (0x27B4)    0x2834    Project Server    Analysis Cube Building    8icz    Critical    Standard Information:PSI Entry Point:   Project User: DOMAIIN\User  Correlation Id: 4c420a8a-c27c-47b8-88c9-46baaf8ab1ec  PWA Site URL: /PWA">http://<Server>/PWA  SSP Name: Project Server Service Application  PSError: CBSASConnectionFailure (17003) Cube build failed to connect to the Analysis Services server. Verify the data source connection is valid. Error: Setting UID=19d8edf4-019c-437a-a435-2fd160b71592 ASServerName=<ASServerName> ASDBName=Test2012 ASExtraNetAddress= RangeChoice=0 PastNum=1 PastUnit=0 NextNum=1 NextUnit=0 FromDate=07/02/2012 06:55:08 ToDate=07/02/2012 06:55:08 HighPriority=True    4c420a8a-c27c-47b8-88c9-46baaf8ab1ec

The Application Event Log just shows a pretty generic Cube Building failure message.

Source:        Microsoft-SharePoint Products-Project Server
Date:          7/3/2012 7:00:21 AM
Event ID:      7682
Task Category: Analysis Cube Building
Level:         Error
PSError: CBSASConnectionFailure (17003)
Cube build failed to connect to the Analysis Services server. Verify the data source connection is valid. Error: Setting UID=19d8edf4-019c-437a-a435-2fd160b71592 ASServerName=<ASServerName> ASDBName=Test2012 ASExtraNetAddress= RangeChoice=0 PastNum=1 PastUnit=0 NextNum=1 NextUnit=0 FromDate=07/02/2012 06:55:08 ToDate=07/02/2012 06:55:08 HighPriority=True
Event Xml:

Error 2

ULS logs – plenty of errors here, mostly saying the same thing – so I’ll just post the critical one:

07/03/2012 07:49:52.90    Microsoft.Office.Project.Server (0x27B4)    0x12A4    Project Server    Analysis Cube Building    8icz    Critical    Standard Information:PSI Entry Point:   Project User: DOMAIN\User  Correlation Id: 5984d4c1-558e-4bb2-9373-68053f894a7d  PWA Site URL: /PWA">http://<Server>/PWA  SSP Name: Project Server Service Application  PSError: CBSOlapProcessingFailure (17004) Cube build failed during an OLAP Processing operation. CBS queued message: Setting UID=19d8edf4-019c-437a-a435-2fd160b71592 ASServerName=<ASServerName> ASDBName=Test2012 ASExtraNetAddress= RangeChoice=0 PastNum=1 PastUnit=0 NextNum=1 NextUnit=0 FromDate=07/02/2012 06:55:08 ToDate=07/02/2012 06:55:08 HighPriority=True. Error: Failed to process the Analysis Services database BriSmith2012 on the brismith12-15 server. Error: Errors in the back-end database access module. The provider 'SQLNCLI10' is not registered. The following system error occurred:  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 'Resource Type', Name of 'Resource Type' was being processed. Errors in the back-end database access module. The provider 'SQLNCLI10' is not registered. The following system error occurred:  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 'Timesheet Status', Name of 'Timesheet Status' was being processed. Server: The current operation was cancelled because another operation in the transaction failed. Errors in the back-end database access module. The provider 'SQLNCLI10' is not registered. The following system error occurred:  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 'Timesheet Period Status', Name of 'Timesheet Period Status' was being processed.    5984d4c1-558e-4bb2-9373-68053f894a7d

The Event Log entry was very similar – Source: Project Server  Event ID was 7691 and Task Category Analysis Cube Building, followed by a generic Queue failure Event ID 7704.

Error 3

Here is the critical one from the ULS logs:

07/03/2012 08:32:44.12    Microsoft.Office.Project.Server (0x27B4)    0x2CF4    Project Server    Analysis Cube Building    8icz    Critical    Standard Information:PSI Entry Point:   Project User: DOMAIN\User  Correlation Id: fdf04238-ff5a-4153-baca-723f634011cd  PWA Site URL: /brismith8100/PWA">http://<Server>/PWA  SSP Name: Project Server Service Application  PSError: CBSOlapProcessingFailure (17004) Cube build failed during an OLAP Processing operation. CBS queued message: Setting UID=19d8edf4-019c-437a-a435-2fd160b71592 ASServerName=<ASServerName> ASDBName=Test2012 ASExtraNetAddress= RangeChoice=0 PastNum=1 PastUnit=0 NextNum=1 NextUnit=0 FromDate=07/02/2012 06:55:08 ToDate=07/02/2012 06:55:08 HighPriority=True. Error: Failed to process the Analysis Services database Test2012 on the <ASServerName> server. Error: Internal error: The operation terminated unsuccessfully. Server: The current operation was cancelled because another operation in the transaction failed. OLE DB error: OLE DB or ODBC error: Login failed for user DOMAINS\MachineName$'.; 28000. 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 'Task List', Name of 'Task List' was being processed. ….

Event log same as before the - Source: Project Server Event ID was 7691 and Task Category Analysis Cube Building, followed by a generic Queue failure Event ID 7704.

Leave a Comment
  • Please add 5 and 5 and type the answer here:
  • Post
  • Hi Brian,

    thanks for this helpful summary on an installation with SQL 2012. There is however another small thing to consider:

    Project Server 2010 also needs the Microsoft SQL Server 2008 R2 Analysis Management Objects from www.microsoft.com/.../details.aspx.

    I know, it's listed in the requirements, but when you set up single-box test machines, at least I get in the habit of skipping those SQL components since they are installed with SQL anyway. But since Project Server needs those components as 2008(R2), they are of course missing on single-boxes with SQL 2012.

    The error message when building the cube is pretty concise there and tells you that this component is missing. Still, in a click-and-run scenario that you described, it would be helpful to add that this is required.

    Kind regards and thanks again,

    Adrian

  • Very good point Adrian - and in my case I was just pointing my normal test box to a new Analysis Servies machine - so already had the components - but yes, you will certainly need those.  Same for 2013 by the way, in case anyone is playing around withe the Preview.

    Best regards,

    Brian.

  • We has the problem described in the beginning of June, while setup reporting on Project server 2010 + SQL 2012.

    So we discovered for Cube builder correct work with SQL Server 2012 is required:

    1. Install on the Project server (application server in the farm) SQLSERVER2008_ASAMO10.msi - (AMO is the replacement for the old DSO) version 10.00... (10.56.. - already did not work); the package which is included in the 2012 Feature Pack - does not fit.

    2. Install on the SQL server - sqlncli.msi:  SQLNativeClient version 10.1 (namely 10.1, not 10.0), as SQL 2012 has a driver 11.

  • Hey Brian,

    We are getting the same error #2 outlined above.  I've installed the SQL Server Native Client 2008 on our Application Server, but apprehensive to install on our Analysis Server running 2012 due to it being on a shared environment.  Do you have any other workarounds for this error?  Has Microsoft certified a fix for this issue?  It seems odd having to run SQL Server Native Client 2008 on a 2012 Box.  

    Thank you!

  • There is no fix needed Importek as there is nothing broken.  We require the specific native client - as it is native to the version of our components - so it is designed the SQL Server team for multiple versions of native client to co-exist.

    Best regards,

    Brian.

  • Thanks Brian.

    Our DBA is concerned with having to support multiple versions of SQL Server Native Client on our SQL/Analysis Server.  Isn't the 2012 Native Client backwards compatible with earlier versions?

    I would also think it would be documented somewhere in the setup guides (technet.microsoft.com/.../jj943761.aspx).  The guide does reference installing SQL Server 2008 AMO for all versions of SQL but do not mention the Native Client versions.  

  • Hi Importek, it isn't always needed if you are working with SQL DB and AS on the same server - so I guess that is why the omission.  Also it isn't so much about backward compatibility of the native client (which can talk to older DB versions) but more lack of forward compatibility of our data access objects doing the cube build requests.  We call a specific version - I'm not aware of any ability to make the newer version pretend it is the older one.  I'm not aware of any issues with having multiple versions installed - they have different names.

    Best regards,

    Brian.

  • Hi Brian,

    just an additional input for your great article:

    At Error 3 you point out that the service account of SQL Analysis Services needs db_datareader permission on the reporting database. If Database and Analysis Services run on the same machine the error says that domain\computername$ lacks permissions and this is also displayed in application eventlog. But the permission needs to be granted to ntservice\msolap$instancename to make it work.

    Regards

    Christoph

  • Hello Brian,

    I have changed the alias name for the application server and PWA is now has the new link and it opens up fine, but when Try to open the reports using Excel web services, it throw an error and tries to connect to old Apps Server name.error is on Trusted location, I changed the URLS in the Excel services and also secure services but no go.

  • Hi Neethu, if you take a look at blogs.msdn.com/.../project-server-2010-business-intelligence-center-behind-the-scenes.aspx I think the same workaround should get things working for you.  Obviously make sure you have backups and copies of any specific custom reports you have created before trying this.

    Best regards,

    Brian.

Page 1 of 1 (10 items)