Data Warehouse issues after upgrading OM 2012 SP1 to R2

After upgrading SC Operations Manager 2012 SP1 to R2 you might notice general problems related to the Data Warehouse component, including Reporting of course. This may happen in situations where on upgrade there is more than one AlertDetail_GUID table in the DW database and not all will get upgraded to get the 2 new columns - TfsWorkItemId and TfsWorkItemOwner.

If you look in the Operations Manager event logs, you will notice this error event 31565 on one of the Management Servers:

 Failed to deploy Data Warehouse component. The operation will be retried.<br>Exception 'DeploymentException': Failed to perform Data Warehouse component deployment operation: Install; Component: DataSet, Id: '0d698dff-9b7e-24d1-8a74-4657b86a59f8', Management Pack Version-dependent Id: '29a3dd22-8645-bae5-e255-9b56bf0b12a8'; Target: DataSet, Id: '23ee52b1-51fb-469b-ab18-e6b4be37ab35'. Batch ordinal: 3;<br>Exception: Sql execution failed. Error 207, Level 16, State 1, Procedure vAlertDetail, Line 18,<br>Message: Invalid column name 'TfsWorkItemId'.<br>One or more workflows were affected by this.<br>Workflow name: Microsoft.SystemCenter.DataWarehouse.Deployment.Component 

 

To resolve the issue execute this SQL Query on the OperationsManagerDW database:

 DECLARE<br>   @GuidString NVARCHAR(50),<br>   @Guid UNIQUEIDENTIFIER,<br>   @StandardDatasetTableMapRowId INT,<br>   @Statement NVARCHAR(MAX),<br>   @SchemaName SYSNAME,<br>   @TableNameSuffix SYSNAME,<br>   @BaseTableName SYSNAME,<br>   @FullTableName SYSNAME<br>SELECT @GuidString = DatasetId<br>FROM StandardDataset WITH(NOLOCK)<br>WHERE SchemaName = 'Alert'<br>SET @StandardDatasetTableMapRowId = 0<br>WHILE EXISTS (<br>   SELECT *<br>   FROM StandardDatasetTableMap AS TM<br>   WHERE (tm.StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)<br>   AND (tm.DatasetId = @GuidString)<br>) BEGIN<br>   SELECT TOP 1<br>      @StandardDatasetTableMapRowId = TM.StandardDatasetTableMapRowId,<br>      @SchemaName = SD.SchemaName,<br>      @TableNameSuffix = TM.TableNameSuffix,<br>      @BaseTableName = SDAS.BaseTableName<br>   FROM StandardDatasetTableMap AS TM<br>   JOIN StandardDataset AS SD<br>      ON TM.DatasetId = sd.DatasetId<br>   JOIN StandardDatasetAggregationStorage AS SDAS<br>      ON SDAS.DatasetId = TM.DatasetId AND SDAS.AggregationTypeId = TM.AggregationTypeId<br>   WHERE<br>      TM.StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId AND<br>      TM.DatasetId = @GUIDString AND<br>      SDAS.TableTag = 'detail' AND<br>      SDAS.DependentTableInd = 1<br>   ORDER BY tm.StandardDatasetTableMapRowId<br>   SET @FullTableName = @BaseTableName + '_' + @TableNameSuffix<br>   IF NOT EXISTS (<br>      SELECT *<br>      FROM INFORMATION_SCHEMA.COLUMNS<br>      WHERE<br>         TABLE_NAME = @FullTableName AND<br>         TABLE_SCHEMA = @SchemaName AND<br>         COLUMN_NAME = N'TfsWorkItemId'<br>   ) BEGIN<br>      SET @Statement = 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@FullTableName) + ' ADD TfsWorkItemId NVARCHAR(256) NULL'<br>      EXECUTE (@Statement)<br>   END<br>   IF NOT EXISTS (<br>      SELECT *<br>      FROM INFORMATION_SCHEMA.COLUMNS<br>      WHERE<br>         TABLE_NAME = @FullTableName AND<br>         TABLE_SCHEMA = @SchemaName AND<br>         COLUMN_NAME = N'TfsWorkItemOwner'<br>   ) BEGIN<br>      SET @Statement = 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@FullTableName) + ' ADD TfsWorkItemOwner NVARCHAR(256) NULL'<br>      EXECUTE (@Statement)<br>   END<br>END<br>EXEC StandardDatasetBuildCoverView @GUIDString, 0

 

This should get you on your way :D