SCSM: Data Warehouse Troubleshooting Part 1 (Jobs Fail on Missing Primary Keys)

Symptoms: The Load.Common or Transform.Common Jobs are failing in SCSM DW (Service Manager)

To find out why run this query against DWDataMart:

select WI.WorkItemId,WI.BatchId, WI.StatusId, WI.ErrorSummary from infra.WorkItem(nolock) WI where WI.ErrorSummary is not null

If you see Errors referring to missing Primary keys, like this example:

Message: UNION ALL view ‘[dbo].PowerActivityDayFactvw’ is not updatable because a primary key was not found on table ‘[dbo].[PowerActivityDayFact_2013_Jun]’.

Then you need to either rebuild the DW or re-create these Primary Keys. I have no idea why tables suddenly lose their Primary Key. This Problem however usually affects Relationship Tables and Views (Facts). With a few exceptions the Primary Key is composed of the first 3x Columns (according to Ordinal). These columns are usually the DimKey, the related item DimKey and the DateKey however if it is a “Duration” or “measure” relationship then the third column will be something like a StartDate or TimeKey. In this case you Need the first 3x columns and then the DateKey, making 4x columns in total to create the Primary key. This eventuality is covered in the script. What is not covered is the EntityManagedType and EntityRelatesToEntity relationship tables which have extra columns in the Primary Key. Also the SLAInstanceInformation relationship table has a Special Primary Key. These exceptions must be dealt with separately.

Happily though, for everything else there’s a script:

<#
Fix Data Warehouse Primary Keys Issue
#>
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sQLServer=’SCSMServer’
$sQLDBName = ‘DWDataMart’
$sQLStagingDBName = “DWStagingAndConfig”
function SQLCommand($sQLCommand,$sQLDB){
$sqlConnection.ConnectionString = “Server = $sQLServer; Database = $sQLDB;Integrated Security = True”
$sqlConnection.Open()
$sQLCmd = New-Object System.Data.SqlClient.SqlCommand
$sQLCmd.CommandText = $sQLCommand
$sQLCmd.Connection = $sqlConnection
$sQLCmd.ExecuteNonQuery()
$sqlConnection.Close()
}
function QueryTable($sQLQuery,$sQLDB){
$sqlConnection.ConnectionString = “Server = $sQLServer; Database = $sQLDB;Integrated Security = True”
$sqlConnection.Open()
$sQLCmd = New-Object System.Data.SqlClient.SqlCommand
$sQLCmd.CommandText = $sQLQuery
$sQLCmd.Connection = $sqlConnection
$sQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$sQLAdapter.SelectCommand = $sQLCmd
$dataSet = New-Object System.Data.DataSet
$sQLAdapter.Fill($dataSet)
$sqlConnection.Close()
return $dataSet
}
cls
$allMay2014FactTables = QueryTable “select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME like ‘%Fact_2014_Jun%'” $sQLDBName
foreach($table in $allMay2014FactTables[1].Tables[0]){
$tableName = $table.TABLE_NAME
$priKeyExists = QueryTable “SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), ‘IsPrimaryKey’) = 1
AND
TABLE_NAME = ‘$tableName'” $sQLDBName
if($priKeyExists[1].Tables[0] -ne $null){
“Primary Key Exists in $tableName”
}else{
“Primary Key MISSING: $tableName”
$columns = QueryTable “select COLUMN_NAME,ORDINAL_POSITION
from INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = ‘$tableName’
ORDER BY ORDINAL_POSITION” $sQLDBName
$priKey1 = $columns[1].Tables[0].Rows[0].COLUMN_NAME
$priKey2 = $columns[1].Tables[0].Rows[1].COLUMN_NAME
$priKey3 = $columns[1].Tables[0].Rows[2].COLUMN_NAME
if($priKey3 -ne ‘DateKey’){
$alterTableCMD = “ALTER TABLE [dbo].[$tableName] ADD  CONSTRAINT [PK_$tableName] PRIMARY KEY NONCLUSTERED
(
[$priKey1] ASC,
[$priKey2] ASC,
[$priKey3] ASC,
[DateKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [FileGroup2_Facts1]”
}else{
$alterTableCMD = “ALTER TABLE [dbo].[$tableName] ADD  CONSTRAINT [PK_$tableName] PRIMARY KEY NONCLUSTERED
(
[$priKey1] ASC,
[$priKey2] ASC,
[$priKey3] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [FileGroup2_Facts1]”
}
SQLCommand $alterTableCMD $sQLDBName | out-null
}
}
SQLCommand “update infra.WorkItem set ErrorSummary = NULL,StatusId=3 where ErrorSummary is not null” $sQLStagingDBName | out-null

After running this script, try resuming the Load.Common Job and check for Errors. I recommend using Mihai’s script just to clean everything up:

http://blogs.technet.com/b/mihai/archive/2013/07/03/resetting-and-running-the-service-manager-data-warehouse-jobs-separately.aspx

EDIT: MS already have a SQL Script which will do the same thing.. 🙂

https://technet.microsoft.com/en-us/library/dn299381.aspx

Advertisement