A New SQL Server–and some different cube building errors

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 . 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 - https://support.microsoft.com/kb/2683293 - Error message when you use AMO to connect to SQL Server 2012 Analysis Services: "Deserialization failed: Requested value ' ' was not found".  The ServerEdition could be one of EnterpriseCore64 (as mine was) EnterpriseCore BusinessIntelligence64 BusinessIntelligence The Cumulative Update itself can be found at https://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 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 - https://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= ;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 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 $'.; 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 ServiceMSSQLServerOLAPService So the connection is via the machine name.  Added the DOMAIN $ as a Login on the Databse Server with db_datareader role membership to my ProjectServer_Reporting database and continued.  If your Analysis Services is ru
nning 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"> https:// /PWA , ServiceApp:Project Server Service Application, User:DOMAINUser, PSI: Error generating Project Server cubes. Error: 'Failed to connect to the Analysis Services server . 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"> https:// /PWA , ServiceApp:Project Server Service Application, User:DOMAINUser, 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: DOMAIINUser  Correlation Id: 4c420a8a-c27c-47b8-88c9-46baaf8ab1ec  PWA Site URL: /PWA"> https:// /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= 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= 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: DOMAINUser  Correlation Id: 5984d4c1-558e-4bb2-9373-68053f894a7d  PWA Site URL: /PWA"> https:// /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= 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: DOMAINUser  Correlation Id: fdf04238-ff5a-4153-baca-723f634011cd  PWA Site URL: /brismith8100/PWA"> https:// /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= 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 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 DOMAINSMachineName$'.; 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.

More:
A New SQL Server–and some different cube building errors


Leave a comment!

You must be logged in to post a comment.