Hi all
Problems when upgrading to SP1 in test lab.
I have two servers

SM - Windows 2008R2 SP1, SQL 2008r2 SP2
DW- Windows 2008R2 SP1, SQL 2008r2 SP2.

I successfully installed SP1 on both servers.
Data warehouse Jobs have been running, but some MPs don't sync (see below)

Error in Operations Manager log on DW server:
м :   Operations Manager
:      DataAccessLayer
:          27.01.2013 1:25:17
:   33333
:
:      
:
:  /
м:     DW12.contoso.loc
:
Data Access Layer rejected retry on SqlError:
 Request: Ral_ExecuteSql -- (statement=exec('IF OBJECT_ID(''[dbo].[WorkItemAffectedUserFact_2012_May]'') IS NULL
BEGIN
  CREATE TABLE [dbo].[WorkItemAffectedUserFact_2...), (RETURN_VALUE=0)
 Class: 16
 Number: 1779
 Message: Table 'WorkItemAffectedUserFact_2012_May' already has a primary key defined on it.
Xml :
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="DataAccessLayer" />
    <EventID Qualifiers="32768">33333</EventID>
    <Level>3</Level>
    <Task>0</Task>
    <Keywords>0x80000000000000</Keywords>
    <TimeCreated SystemTime="2013-01-26T19:25:17.000000000Z" />
    <EventRecordID>5617</EventRecordID>
    <Channel>Operations Manager</Channel>
    <Computer>DW12.contoso.loc</Computer>
    <Security />
  </System>
  <EventData>
    <Data>Ral_ExecuteSql -- (statement=exec('IF OBJECT_ID(''[dbo].[WorkItemAffectedUserFact_2012_May]'') IS NULL
BEGIN
  CREATE TABLE [dbo].[WorkItemAffectedUserFact_2...), (RETURN_VALUE=0)</Data>
    <Data>16</Data>
    <Data>1779</Data>
    <Data>Table 'WorkItemAffectedUserFact_2012_May' already has a primary key defined on it.</Data>
  </EventData>
</Event>

How to fix this error?

Hi All,

we have a similar Problem after installing SP1

OM Log on DW Server

----------------------------

Deployment related exception has been caught during ETL. This is either due to a failure in Deployment or we have exceeded the number of retry attempts. Work item will be marked as failed.

Additional information:

ETLModule: etlMod=NULL

RetryAttempts: 0

WorkItem: WorkItemId = 37310251, processModuleId = 8442, isDirty = True, BatchId = 93422, status = Failed, RetryCount = 0, ErrorCount = 88, TakenTime = 02/01/2013 11:35:27, Module = ModuleName = TransformIncidentResolutionCategory, ModuleType = System, ModuleDescription = Deployment Execution Step, ProcessCategoryName = Transform, ProcessName = Transform.Common, DeletedBatchId=0

Exception details:

Exception type: Microsoft.EnterpriseManagement.Common.ObjectNotFoundException

Exception message: An object of class ManagementPackWarehouseModuleType with ID dfdfdfdfdfdfdfdfdfdfdfdfdf6c was not found.

Stack trace:

at Microsoft.EnterpriseManagement.DataWarehouseManagement.GetWarehouseModuleType(Guid id)

at Microsoft.SystemCenter.Warehouse.Utility.Store.GetWarehouseModule(Guid id)

at Microsoft.SystemCenter.Etl.ETLModule.GetModule(WorkItem etlWI)

at Microsoft.SystemCenter.Etl.ETLModule.OnDataItem(DataItemBase dataItem, DataItemAcknowledgementCallback acknowledgedCallback, Object acknowledgedState, DataItemProcessingCompleteCallback completionCallback, Object completionState)

---------------------------

any news on this ??

THX

Markus

No news

We're also seeing the same thing.

In case it's missed in the other thread regarding this, here is what we did to resolve this:

This is caused by a problem with the management packs trying to sync to the Data Warehouse.

What's happening is that some of the data warehouse management packs are trying to redeploy after your upgrade to SP1.  As you've noticed, a handful of the management packs under the Data Warehouse wunderbar are in a "waiting" state, and the "System Center Data Warehouse Base Library" MP is in a "failed" state.  Looking at a DB trace, you can see that when the MP tries to sync, the T-SQL is querying to see if a "base" PK exists, finds that it does not, and tries to create it:

 IF

OBJECT_ID(''''[PK_ConfigItemServicedByUserFact]'''') IS NULL
BEGIN
  ALTER TABLE [dbo].[ConfigItemServicedByUserFact_2013_Jan]
  ALTER COLUMN [ConfigItemDimKey]   INT    NOT NULL ;
  ALTER TABLE [dbo].[ConfigItemServicedByUserFact_2013_Jan]
  ALTER COLUMN [ConfigItemServicedByUser_UserDimKey]   INT    NOT NULL ;
  ALTER TABLE [dbo].[ConfigItemServicedByUserFact_2013_Jan]
  ALTER COLUMN [DateKey]   INT    NOT NULL ;
  EXEC(''''ALTER TABLE [dbo].[ConfigItemServicedByUserFact_2013_Jan]
  ADD CONSTRAINT [PK_ConfigItemServicedByUserFact] PRIMARY KEY NONCLUSTERED ([ConfigItemDimKey], [ConfigItemServicedByUser_UserDimKey], [DateKey])'''');

END

That's where the error in the event log comes from.

To recover from this:

1. Create a backup of your DWDataMart, DWStagingAndConfig, and DWRepository DBs.

2. For each primary key that shows up in your event logs with the message "Message: Table 'ConfigItemServicedByUserFact_2013_Jan' already has a primary key defined on it.", delete the primary key from the DWRepository DB.  For us there were 25 of them in total that were causing the "System Center Data Warehouse Base Library" MP to fail.  This can be done with a T-SQL statement such as the following:

USE DWRepository ALTER TABLE dbo.BillableTimeHasWorkingUserFact_2013_Jan drop constraint [PK_BillableTimeHasWorkingUserFact_2013_Jan]

Note that your fact table might be a different year and/or month than what we saw, but we saw this with the following 25 fact tables:

'BillableTimeHasWorkingUserFact_2013_Jan'
'ComputerHasPrimaryUserFact_2013_Jan'
'ComputerHostsLogicalDiskFact_2013_Jan'
'ComputerHostsNetworkAdapterFact_2013_Jan'
'ComputerHostsOperatingSystemFact_2013_Jan'
'ComputerHostsPhysicalDiskFact_2013_Jan'
'ComputerHostsProcessorFact_2013_Jan'
'ConfigItemGroupContainsEntitiesFact_2013_Jan'
'ConfigItemImpactsCustomersFact_2013_Jan'
'ConfigItemOwnedByUserFact_2013_Jan'
'ConfigItemRelatesToConfigItemFact_2013_Jan'
'ConfigItemServicedByUserFact_2013_Jan' -starting
'GroupContainsConfigItemFact_2013_Jan'
'ServiceContainsConfigItemFact_2013_Jan'
'ServiceImpactsUserFact_2013_Jan'
'WorkItemAboutConfigItemFact_2013_Jan'
'WorkItemAffectedUserFact_2013_Jan'
'WorkItemAssignedToUserFact_2013_Jan'
'WorkItemCreatedByUserFact_2013_Jan'
'WorkItemGroupContainsWorkItemFact_2013_Jan'
'WorkItemHasBillableTimeFact_2013_Jan'
'WorkItemHasParentWorkItemFact_2013_Jan'
'WorkItemImpactsServiceFact_2013_Jan'
'WorkItemRelatesToConfigItemFact_2013_Jan'
'WorkItemRelatesToWorkItemFact_2013_Jan'

3. Go to the management packs under the Data Warehouse wunderbar and click on the "redeploy" task on the failed MP.

Once you delete the primary keys, the SQL call will be able to successfully create the new primary keys on the affected tables and the "System Center Data Warehouse Base Library" MP will now be imported, allowing the MPs in a "waiting" state to continue.  You will have additional primary keys that you will need to do the same with as well, as the MPs that were waiting will try to create their own primary keys.

It is very important that you check your event logs for a listing to see which tables and primary keys are affected instead of relying on my list above, as you may have different months/years.  If you no longer have these events, you can click on the "redeploy" task on the failed MP to get a new set of events written to the logs.

• Marked as answer by Yuriy Nizhnikov Sunday, February 17, 2013 7:00 PM

In case it's missed in the other thread regarding this, here is what we did to resolve this:

This is caused by a problem with the management packs trying to sync to the Data Warehouse.

What's happening is that some of the data warehouse management packs are trying to redeploy after your upgrade to SP1.  As you've noticed, a handful of the management packs under the Data Warehouse wunderbar are in a "waiting" state, and the "System Center Data Warehouse Base Library" MP is in a "failed" state.  Looking at a DB trace, you can see that when the MP tries to sync, the T-SQL is querying to see if a "base" PK exists, finds that it does not, and tries to create it:

 IF

OBJECT_ID(''''[PK_ConfigItemServicedByUserFact]'''') IS NULL
BEGIN
  ALTER TABLE [dbo].[ConfigItemServicedByUserFact_2013_Jan]
  ALTER COLUMN [ConfigItemDimKey]   INT    NOT NULL ;
  ALTER TABLE [dbo].[ConfigItemServicedByUserFact_2013_Jan]
  ALTER COLUMN [ConfigItemServicedByUser_UserDimKey]   INT    NOT NULL ;
  ALTER TABLE [dbo].[ConfigItemServicedByUserFact_2013_Jan]
  ALTER COLUMN [DateKey]   INT    NOT NULL ;
  EXEC(''''ALTER TABLE [dbo].[ConfigItemServicedByUserFact_2013_Jan]
  ADD CONSTRAINT [PK_ConfigItemServicedByUserFact] PRIMARY KEY NONCLUSTERED ([ConfigItemDimKey], [ConfigItemServicedByUser_UserDimKey], [DateKey])'''');

END

That's where the error in the event log comes from.

To recover from this:

1. Create a backup of your DWDataMart, DWStagingAndConfig, and DWRepository DBs.

2. For each primary key that shows up in your event logs with the message "Message: Table 'ConfigItemServicedByUserFact_2013_Jan' already has a primary key defined on it.", delete the primary key from the DWRepository DB.  For us there were 25 of them in total that were causing the "System Center Data Warehouse Base Library" MP to fail.  This can be done with a T-SQL statement such as the following:

USE DWRepository ALTER TABLE dbo.BillableTimeHasWorkingUserFact_2013_Jan drop constraint [PK_BillableTimeHasWorkingUserFact_2013_Jan]

Note that your fact table might be a different year and/or month than what we saw, but we saw this with the following 25 fact tables:

'BillableTimeHasWorkingUserFact_2013_Jan'
'ComputerHasPrimaryUserFact_2013_Jan'
'ComputerHostsLogicalDiskFact_2013_Jan'
'ComputerHostsNetworkAdapterFact_2013_Jan'
'ComputerHostsOperatingSystemFact_2013_Jan'
'ComputerHostsPhysicalDiskFact_2013_Jan'
'ComputerHostsProcessorFact_2013_Jan'
'ConfigItemGroupContainsEntitiesFact_2013_Jan'
'ConfigItemImpactsCustomersFact_2013_Jan'
'ConfigItemOwnedByUserFact_2013_Jan'
'ConfigItemRelatesToConfigItemFact_2013_Jan'
'ConfigItemServicedByUserFact_2013_Jan' -starting
'GroupContainsConfigItemFact_2013_Jan'
'ServiceContainsConfigItemFact_2013_Jan'
'ServiceImpactsUserFact_2013_Jan'
'WorkItemAboutConfigItemFact_2013_Jan'
'WorkItemAffectedUserFact_2013_Jan'
'WorkItemAssignedToUserFact_2013_Jan'
'WorkItemCreatedByUserFact_2013_Jan'
'WorkItemGroupContainsWorkItemFact_2013_Jan'
'WorkItemHasBillableTimeFact_2013_Jan'
'WorkItemHasParentWorkItemFact_2013_Jan'
'WorkItemImpactsServiceFact_2013_Jan'
'WorkItemRelatesToConfigItemFact_2013_Jan'
'WorkItemRelatesToWorkItemFact_2013_Jan'

3. Go to the management packs under the Data Warehouse wunderbar and click on the "redeploy" task on the failed MP.

Once you delete the primary keys, the SQL call will be able to successfully create the new primary keys on the affected tables and the "System Center Data Warehouse Base Library" MP will now be imported, allowing the MPs in a "waiting" state to continue.  You will have additional primary keys that you will need to do the same with as well, as the MPs that were waiting will try to create their own primary keys.

It is very important that you check your event logs for a listing to see which tables and primary keys are affected instead of relying on my list above, as you may have different months/years.  If you no longer have these events, you can click on the "redeploy" task on the failed MP to get a new set of events written to the logs.

• Marked as answer by Yuriy Nizhnikov Sunday, February 17, 2013 7:00 PM

In case it's missed in the other thread regarding this, here is what we did to resolve this:

This is caused by a problem with the management packs trying to sync to the Data Warehouse.

What's happening is that some of the data warehouse management packs are trying to redeploy after your upgrade to SP1.  As you've noticed, a handful of the management packs under the Data Warehouse wunderbar are in a "waiting" state, and the "System Center Data Warehouse Base Library" MP is in a "failed" state.  Looking at a DB trace, you can see that when the MP tries to sync, the T-SQL is querying to see if a "base" PK exists, finds that it does not, and tries to create it:

 IF

OBJECT_ID(''''[PK_ConfigItemServicedByUserFact]'''') IS NULL
BEGIN
  ALTER TABLE [dbo].[ConfigItemServicedByUserFact_2013_Jan]
  ALTER COLUMN [ConfigItemDimKey]   INT    NOT NULL ;
  ALTER TABLE [dbo].[ConfigItemServicedByUserFact_2013_Jan]
  ALTER COLUMN [ConfigItemServicedByUser_UserDimKey]   INT    NOT NULL ;
  ALTER TABLE [dbo].[ConfigItemServicedByUserFact_2013_Jan]
  ALTER COLUMN [DateKey]   INT    NOT NULL ;
  EXEC(''''ALTER TABLE [dbo].[ConfigItemServicedByUserFact_2013_Jan]
  ADD CONSTRAINT [PK_ConfigItemServicedByUserFact] PRIMARY KEY NONCLUSTERED ([ConfigItemDimKey], [ConfigItemServicedByUser_UserDimKey], [DateKey])'''');

END

That's where the error in the event log comes from.

To recover from this:

1. Create a backup of your DWDataMart, DWStagingAndConfig, and DWRepository DBs.

2. For each primary key that shows up in your event logs with the message "Message: Table 'ConfigItemServicedByUserFact_2013_Jan' already has a primary key defined on it.", delete the primary key from the DWRepository DB.  For us there were 25 of them in total that were causing the "System Center Data Warehouse Base Library" MP to fail.  This can be done with a T-SQL statement such as the following:

USE DWRepository ALTER TABLE dbo.BillableTimeHasWorkingUserFact_2013_Jan drop constraint [PK_BillableTimeHasWorkingUserFact_2013_Jan]

Note that your fact table might be a different year and/or month than what we saw, but we saw this with the following 25 fact tables:

'BillableTimeHasWorkingUserFact_2013_Jan'
'ComputerHasPrimaryUserFact_2013_Jan'
'ComputerHostsLogicalDiskFact_2013_Jan'
'ComputerHostsNetworkAdapterFact_2013_Jan'
'ComputerHostsOperatingSystemFact_2013_Jan'
'ComputerHostsPhysicalDiskFact_2013_Jan'
'ComputerHostsProcessorFact_2013_Jan'
'ConfigItemGroupContainsEntitiesFact_2013_Jan'
'ConfigItemImpactsCustomersFact_2013_Jan'
'ConfigItemOwnedByUserFact_2013_Jan'
'ConfigItemRelatesToConfigItemFact_2013_Jan'
'ConfigItemServicedByUserFact_2013_Jan' -starting
'GroupContainsConfigItemFact_2013_Jan'
'ServiceContainsConfigItemFact_2013_Jan'
'ServiceImpactsUserFact_2013_Jan'
'WorkItemAboutConfigItemFact_2013_Jan'
'WorkItemAffectedUserFact_2013_Jan'
'WorkItemAssignedToUserFact_2013_Jan'
'WorkItemCreatedByUserFact_2013_Jan'
'WorkItemGroupContainsWorkItemFact_2013_Jan'
'WorkItemHasBillableTimeFact_2013_Jan'
'WorkItemHasParentWorkItemFact_2013_Jan'
'WorkItemImpactsServiceFact_2013_Jan'
'WorkItemRelatesToConfigItemFact_2013_Jan'
'WorkItemRelatesToWorkItemFact_2013_Jan'

3. Go to the management packs under the Data Warehouse wunderbar and click on the "redeploy" task on the failed MP.

Once you delete the primary keys, the SQL call will be able to successfully create the new primary keys on the affected tables and the "System Center Data Warehouse Base Library" MP will now be imported, allowing the MPs in a "waiting" state to continue.  You will have additional primary keys that you will need to do the same with as well, as the MPs that were waiting will try to create their own primary keys.

It is very important that you check your event logs for a listing to see which tables and primary keys are affected instead of relying on my list above, as you may have different months/years.  If you no longer have these events, you can click on the "redeploy" task on the failed MP to get a new set of events written to the logs.

abarton425

Your solution works
thanks

This worked for me as well, however, I needed to go through this process several times (delete primary keys, re-deploy the mp, look for new errors as the other mps are synced, delete primary keys, etc).  Ended up deleting ~70 primary keys.

This worked for me, but now our Load.Common job is failing.  On the DW server, I did Get-SCDWJobModule -JobName Load.Common and saw that there were 10 failed modules:

ID          Name

2476      LoadSMDWDataMartComputerHostsProcessorFact

2511      LoadSMDWDataMartWorkItemCreatedByUserFact

2510      LoadSMDWDataMartWorkItemAssignedToUserFact

2516      LoadSMDWDataMartWorkItemHasParentWorkItemFact

2515      LoadSMDWDataMartWorkItemHasBillableTimeFact

2509      LoadSMDWDataMartWorkItemAffectedUserFact

2485      LoadSMDWDataMartConfigItemServicedByUserFact

2484      LoadSMDWDataMartConfigItemRelatesToConfigItem

2508      LoadSMDWDataMartWorkItemAboutConfigItemFact

2491      LoadSMDWDataMartGroupContainsConfigItemFact

The Event Log on the DW Server for these failed items look like this  (Event ID 33503):

An error countered while attempting to execute ETL Module:
 ETL process type: Load
 Batch ID: 200825
 Module name: LoadSMDWDataMartGroupContainsConfigItemFact
 Message: UNION ALL view 'SMDWDataMart.dbo.GroupContainsConfigItemFactvw' is not updatable because a primary key was not found on table '[SMDWDataMart].[dbo].[GroupContainsConfigItemFact_2013_Apr]'.

 Stack:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.SystemCenter.Warehouse.Utility.SqlBulkOperation.PartitionedViewUpdate(SqlConnection sourceConnection, String sourceQuery, String destinationTable, Dictionary`2 mapping, SqlConnection destinationConnection, Collection`1 pkColumns)
   at Microsoft.SystemCenter.Warehouse.Utility.SqlBulkOperation.PartitionedViewUpsert(String sourceConnectionString, String sourceQuery, String destinationTable, Dictionary`2 mapping, String destinationConnectionString, Collection`1 pkColumns, Int32& insertCount, Int32& updateCount, DomainUser sourceSecureUser, DomainUser destSecureUser, SqlResourceStore targetStore)
   at Microsoft.SystemCenter.Warehouse.Etl.ADOInterface.PartitionedViewUpsert(DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)
   at Microsoft.SystemCenter.Warehouse.Etl.ADOInterface.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)
   at Microsoft.SystemCenter.Warehouse.Etl.LoadModule.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser, Int32 loadBatchSize)
   at Microsoft.SystemCenter.Warehouse.Etl.LoadModule.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)
   at Microsoft.SystemCenter.Etl.ETLModule.OnDataItem(DataItemBase dataItem, DataItemAcknowledgementCallback acknowledgedCallback, Object acknowledgedState, DataItemProcessingCompleteCallback completionCallback, Object completionState)

Any thoughts?

Hi

we have tried a lot of things without succuess

 

http://dougsigmon.wordpress.com/2012/09/20/dwmaintenance-stalled-processes-kill-the-scsm-2012-data-warehouse/

http://ariessysadmin.blogspot.com.es/2012/11/scsm-2012-data-warehouse-cube.html

http://blogs.technet.com/b/servicemanager/archive/2010/06/07/troubleshooting-the-data-warehouse-an-overview.aspx

Nothing was working. We have opened a Support call at MS, finally they came back with the Solution to reinstall the DW. What we have done. Then the same Problem occurs again. We have added some more CPUs and increased the Memory to 32 GB on the DB Server. We have also changed the Settings for the Max used CPU's while a query is running in SSAS. It seesm that this Problem is based on a SQL Server Perfromance Problem. However strange to believe. We have running currently the DW and operational Database on the same instance. Hope it will be working now more than 4 weeks..;-)

Thinkt the next step will be to seperate the DW component from the operational DB, I mean a complete new Server fro SQL were the DW is then running. It seems also that this Problem can happend If SQL Standard is used.

However .... very very strange ....

rgds,

markus

Please try this.

http://www.scsm.se/?p=881

Step 1 Disable the Cube Processing jobs

Step 2 Manual processing of the cubes

Step 3 Re-enable the Cube Processing jobs in SCSM

or

upgrade to SP1's UR2 ASAP

Did you ever find a solution to your primary key was not found on table error?  We are now seeing it in our Production enviroment?

Hi,

In our prod env scsm 2012 R2  ,reports are not updated and the data in DWdatamart is not updated

the load.common job is failing with the below error

a
ETL Module Execution failed:
 ETL process type: Load
 Batch ID: 120162
 Module name: LoadDWDataMartIncidentTierQueueDurationFact
 Message: UNION ALL view 'DWDataMart.dbo.IncidentTierQueueDurationFactvw' is not updatable because a primary key was not found on table '[DWDataMart].[dbo].[IncidentTierQueueDurationFact_2014_Oct]'.

this is repeated for 5 tables

in our case we didnt do any upgrade from sp1 to 2012 r2

Any solution ???