I had the same problem as Carol and Enayathulla described in this post: https://social.technet.microsoft.com/Forums/systemcenter/en-US/096fd3e1-61fd-4101-a4e6-1de0c6c5ec4c/scsm-2012-transform-job-failing-in-datawarehouse?forum=systemcenterservicemanager and this script seems to have fixed it (at least for the moment):

https://blogs.technet.microsoft.com/mihai/resetting-and-running-the-service-manager-data-warehouse-jobs-separately/

Copying the script in case the link above goes away for some reason. But I recommend reading that post. It’s very informative.:

[CmdletBinding()]
Param(
[Parameter(Mandatory = $False)]
[String] $DWServer = “localhost”,
[Parameter(Mandatory = $False)]
[String] $ASServer = “localhost”,
[Parameter(Mandatory = $False)]
[String] $ASDBName = “DWASDatabase”,
[Parameter(Mandatory = $False)]
[int] $Wait = 10
)
$props = Get-ItemProperty “HKLM:\SOFTWARE\Microsoft\System Center\2010\Common\Setup”
$instdir = $props.InstallDirectory
$dwpsd = $instdir + “Microsoft.EnterpriseManagement.Warehouse.Cmdlets.psd1”
Import-Module -Name $dwpsd
$JSList = Get-SCDWJobSchedule -ComputerName $DWServer;
$JList = Get-SCDWJob -ComputerName $DWServer;
function Run-DWJob([String]$DWServer, [String]$JobName) {
Write-Host “Enabling and running Job:” $JobName;
Enable-SCDWJob -ComputerName $DWServer -JobName $JobName;
Start-SCDWJob -ComputerName $DWServer -JobName $JobName;
$currentJobStatus = Get-SCDWJob -JobName $JobName -ComputerName $DWServer | Select Status
while($currentJobStatus.Status -eq “Running”) {
Start-Sleep -s $Wait
$currentJobStatus = Get-SCDWJob -JobName $JobName -ComputerName $DWServer | Select Status
$moduleList = Get-SCDWJobModule -JobName $JobName -ComputerName $DWServer
foreach($obj in $moduleList) {
if([String]::IsNullOrEmpty($obj.ModuleErrorSummary) -ne $true) {
Write-Host “There is no need to wait anymore for Job” $JobName “because there is an error in module” $obj.ModuleName “and the error is:” $obj.ModuleErrorSummary;
exit;
}
}
}
if($currentJobStatus.Status -ne “Not Started”) {
Write-Host “There is an error with” $JobName “and we will exit this – please inspect the status”;
exit;
}
}
foreach($obj in $JSList) {
Write-Host “Disabling Schedule for Job: ” $obj.Name;
Disable-SCDWJobSchedule -ComputerName $DWServer -JobName $obj.Name;
}
foreach($obj in $JList) {
Write-Host “Stoping and disabling Job: ” $obj.Name;
Stop-SCDWJob -ComputerName $DWServer -JobName $obj.Name;
Start-Sleep -s $Wait
Disable-SCDWJob -ComputerName $DWServer -JobName $obj.Name;
}
$maintenanceList = New-Object System.Collections.ArrayList;
$MPSyncList = New-Object System.Collections.ArrayList;
$extractList = New-Object System.Collections.ArrayList;
$transformList = New-Object System.Collections.ArrayList;
$loadList = New-Object System.Collections.ArrayList;
$cubeList = New-Object System.Collections.ArrayList;
foreach($obj in $JList) {
if($obj.Name -match “Extract”) {
$extractList.Add($obj.Name) | Out-Null;
} elseif($obj.Name -match “Transform”) {
$transformList.Add($obj.Name) | Out-Null;
} elseif($obj.Name -match “Load”) {
$loadList.Add($obj.Name) | Out-Null;
} elseif($obj.Name -match “Maintenance”) {
$maintenanceList.Add($obj.Name) | Out-Null;
} elseif($obj.Name -match “MPSync”) {
$MPSyncList.Add($obj.Name) | Out-Null;
} else {
$cubeList.Add($obj.Name) | Out-Null;
}
}
foreach($obj in $maintenanceList) {
Run-DWJob $DWServer $obj;
}
foreach($obj in $MPSyncList) {
Run-DWJob $DWServer $obj;
}
foreach($obj in $extractList) {
Run-DWJob $DWServer $obj;
}
foreach($obj in $transformList) {
Run-DWJob $DWServer $obj;
}
foreach($obj in $loadList) {
Run-DWJob $DWServer $obj;
}
foreach($obj in $cubeList) {
Run-DWJob $DWServer $obj;
}
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”) | Out-Null;
$Server = New-Object Microsoft.AnalysisServices.Server;
$Server.Connect($ASServer);
$Databases = $Server.Databases;
$DWASDB = $Databases[$ASDBName];
$Dimensions = New-Object Microsoft.AnalysisServices.Dimension;
$Dimensions = $DWASDB.Dimensions;
foreach($dim in $Dimensions) {
Write-Host “Processing Cube Job” $Dim.Name;
$dim.Process(“ProcessFull”);
Start-Sleep -s $Wait;
}
foreach($obj in $JSList) {
Write-Host “Enabling Schedule for Job: ” $obj.Name;
Enable-SCDWJobSchedule -ComputerName $DWServer -JobName $obj.Name;
}
Write-Host “”;
Write-Host “FINISHED!”;