Error when syncing up Analysis database

Question

All the sudden, we got an alert and realize that the automatic sync-up for Analysis database have fail. It happens with Full and Incremental sync-up as well.

We go into "ProcessAnalysisDatabase" from Administrator page then do the manual sync up and get the same error quickly after run. Can someone please share some light what happen? We suspect something wrong with backend database that running from datacenter.

Note: we have 2 application tiers and this happens on both of them.

Thx

- <Job Name="Full Analysis Database Sync" JobProcessingStatus="Idle">

- <LastRun QueueTimeUtc="2012-09-12T16:44:44.867Z" ExecutionStartTimeUtc="2012-09-12T16:44:44.883Z" EndTimeUtc="2012-09-12T16:45:10.613Z" Result="Failed">

  <ResultMessage>[Full Analysis Database Sync]: ---> AnalysisDatabaseProcessingType=Full, needCubeSchemaUpdate=True. ---> Microsoft.TeamFoundation.Server.WarehouseException: TF221122: An error occurred running job Full Analysis Database Sync for team project collection or Team Foundation server TEAM FOUNDATION. ---> Microsoft.TeamFoundation.Server.WarehouseException: Failed to Process Analysis Database 'Tfs_TFSPROD3Analysis'. ---> System.InvalidOperationException: Another 'Role' object has the 'TfsWarehouseDataReader' ID. at Microsoft.AnalysisServices.NamedComponentCollection.ValidateForAddOrInsert(NamedComponent obj) at Microsoft.AnalysisServices.NamedComponentCollection.Add(ModelComponent item) at Microsoft.TeamFoundation.Warehouse.OlapCreator.AddAccountsToRole(Database database, String roleName, IEnumerable`1 accountNames, Boolean ignoreAccountResolutionFailures) at Microsoft.TeamFoundation.Warehouse.OlapCreator.CreateOrUpdateRoles(Server server, Database database, IEnumerable`1 serviceAccountNames, Boolean ignoreServiceAccountResolutionFailures, IEnumerable`1 readerAccountNames, Boolean ignoreReaderAccountResolutionFailures) at Microsoft.TeamFoundation.Warehouse.OlapCreator.UpdateOlapSchema(Server server, IEnumerable`1 readerAccountNames, WarehouseConfig warehouseConfig) at Microsoft.TeamFoundation.Warehouse.TFSOlapProcessComponent.UpdateOlapSchema() at Microsoft.TeamFoundation.Warehouse.TFSOlapProcessComponent.ProcessOlap(AnalysisDatabaseProcessingType processingType, WarehouseChanges warehouseChanges, Boolean lastProcessingFailed, Boolean cubeSchemaUpdateNeeded) --- End of inner exception stack trace --- at Microsoft.TeamFoundation.Warehouse.TFSOlapProcessComponent.ProcessOlap(AnalysisDatabaseProcessingType processingType, WarehouseChanges warehouseChanges, Boolean lastProcessingFailed, Boolean cubeSchemaUpdateNeeded) at Microsoft.TeamFoundation.Warehouse.AnalysisDatabaseSyncJobExtension.RunInternal(TeamFoundationRequestContext requestContext, TeamFoundationJobDefinition jobDefinition, DateTime queueTime, String& resultMessage) at Microsoft.TeamFoundation.Warehouse.WarehouseJobExtension.Run(TeamFoundationRequestContext requestContext, TeamFoundationJobDefinition jobDefinition, DateTime queueTime, String& resultMessage) --- End of inner exception stack trace ---</ResultMessage>

  </LastRun>

  <NextRun QueueTimeUtc="2012-09-13T09:00:00Z" JobState="QueuedScheduled" />

  </Job>

- <Job Name="Incremental Analysis Database Sync" JobProcessingStatus="Idle">

- <LastRun QueueTimeUtc="2012-09-12T16:36:09.903Z" ExecutionStartTimeUtc="2012-09-12T16:36:11.837Z" EndTimeUtc="2012-09-12T16:36:37.633Z" Result="Failed">

  <ResultMessage>[Incremental Analysis Database Sync]: ---> AnalysisDatabaseProcessingType=Full, needCubeSchemaUpdate=True. ---> Microsoft.TeamFoundation.Server.WarehouseException: TF221122: An error occurred running job Incremental Analysis Database Sync for team project collection or Team Foundation server TEAM FOUNDATION. ---> Microsoft.TeamFoundation.Server.WarehouseException: Failed to Process Analysis Database 'Tfs_TFSPROD3Analysis'. ---> System.InvalidOperationException: Another 'Role' object has the 'TfsWarehouseDataReader' ID. at Microsoft.AnalysisServices.NamedComponentCollection.ValidateForAddOrInsert(NamedComponent obj) at Microsoft.AnalysisServices.NamedComponentCollection.Add(ModelComponent item) at Microsoft.TeamFoundation.Warehouse.OlapCreator.AddAccountsToRole(Database database, String roleName, IEnumerable`1 accountNames, Boolean ignoreAccountResolutionFailures) at Microsoft.TeamFoundation.Warehouse.OlapCreator.CreateOrUpdateRoles(Server server, Database database, IEnumerable`1 serviceAccountNames, Boolean ignoreServiceAccountResolutionFailures, IEnumerable`1 readerAccountNames, Boolean ignoreReaderAccountResolutionFailures) at Microsoft.TeamFoundation.Warehouse.OlapCreator.UpdateOlapSchema(Server server, IEnumerable`1 readerAccountNames, WarehouseConfig warehouseConfig) at Microsoft.TeamFoundation.Warehouse.TFSOlapProcessComponent.UpdateOlapSchema() at Microsoft.TeamFoundation.Warehouse.TFSOlapProcessComponent.ProcessOlap(AnalysisDatabaseProcessingType processingType, WarehouseChanges warehouseChanges, Boolean lastProcessingFailed, Boolean cubeSchemaUpdateNeeded) --- End of inner exception stack trace --- at Microsoft.TeamFoundation.Warehouse.TFSOlapProcessComponent.ProcessOlap(AnalysisDatabaseProcessingType processingType, WarehouseChanges warehouseChanges, Boolean lastProcessingFailed, Boolean cubeSchemaUpdateNeeded) at Microsoft.TeamFoundation.Warehouse.AnalysisDatabaseSyncJobExtension.RunInternal(TeamFoundationRequestContext requestContext, TeamFoundationJobDefinition jobDefinition, DateTime queueTime, String& resultMessage) at Microsoft.TeamFoundation.Warehouse.WarehouseJobExtension.Run(TeamFoundationRequestContext requestContext, TeamFoundationJobDefinition jobDefinition, DateTime queueTime, String& resultMessage) --- End of inner exception stack trace ---</ResultMessage>

  </LastRun>

  <NextRun QueueTimeUtc="2012-09-12T18:32:04.79Z" JobState="QueuedScheduled" />

  </Job>

Answer 1

Hi garynguyen,
Thanks for your post!

From the these errors, seems that some changes to the TFS Schema . The TFS_Analysis Schema becomes out of sync with TFS_Warehouse, you might need to rebuild the cube and warehouse. Depending on the amount of data in the data warehouse, the rebuild operation can take several hours to finish, during which time reports are not available.

For how to rebuild the Data Warehouse and Analysis Services Cube, please refer to http://msdn.microsoft.com/en-us/library/cc668753.aspx

For how to resolve Schema Conflicts that are occurring in the Data Warehouse, please use this link Resolving Schema Conflicts That Are Occurring in the Data Warehouse

Hope it helps!

Best Regards,

---

Cathy Kong [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Never mind, we have found the problem. When we log into the analysis database, then realize that someone has rename the role of "TfsWarehouseDataReader". We has named it back and do the manual Incremental/Full sync. They both work.

Answer 3

Hi garynguyen,

I am glad to hear you got it work, thanks for sharing the experiences here.

Best Regards,

---

Cathy Kong [MSFT]
MSDN Community Support | Feedback to us