SCSM 2012 Troubleshooting: Load.CMDWDataMart Failed - Job Module(s) Failed because a primary key was not found
If CMDWDataMart / OMDWDataMart / DWDataMart has failed job modules, it's likely the table needs the primary key created and have the columns altered appropriately.
Example:
In this case we're having problems with CMDWDataMart.
The job is Load.CMDWDataMart
As you can see, the module has failed, after using PowerShell on the Data Warehouse Management Server to retrieve the job module error summary:
Get-SCDWJobModule -JobName Load.CMDWDataMart | Select-Object -ExpandProperty "ModuleErrorSummary"
It was repeating the same error:
UNION ALL view 'CMDWDataMart.dbo.CiresonPurchaseRelatesToPurchaserFactvw' is not updatable because a primary key was not found on table '[CMDWDataMart].[dbo].[CiresonPurchaseRelatesToPurchaserFact_2015_Aug]'.
Well that helps narrow it down. So now we need to see if we can find another table which has the key format we want.
Once we find the PK > Script Key As > CREATE To > New Query Editor Window we should have our PK Constraint code.
** 
**
However, we also need to ensure the columns are configured to be able to become a PK constraint.
In this case, we must mark four columns with NOT NULL.
We also use GO to ensure the statements are executed once the column changes are applied.
For our particular error, we need to execute the below statement:
USE [CMDWDataMart] GO /*** MAKE REQUIRED COLUMNS ABLE TO BECOME PRIMARY KEYS ***/ ALTER TABLE [dbo].[CiresonPurchaseRelatesToPurchaserFact_2015_Aug] ALTER COLUMN [CiresonPurchaseDimKey] int NOT NULL; ALTER TABLE [dbo].[CiresonPurchaseRelatesToPurchaserFact_2015_Aug] ALTER COLUMN [DateKey] int NOT NULL; ALTER TABLE [dbo].[CiresonPurchaseRelatesToPurchaserFact_2015_Aug] ALTER COLUMN [HourId] int NOT NULL; ALTER TABLE [dbo].[CiresonPurchaseRelatesToPurchaserFact_2015_Aug] ALTER COLUMN [PurchaseHasPurchaser_UserDimKey] int NOT NULL; GO /*** CREATE PRIMARY KEY CONSTRAINT FOR TABLE ***/ ALTER TABLE [dbo].[CiresonPurchaseRelatesToPurchaserFact_2015_Aug] ADD CONSTRAINT [PK_CiresonPurchaseRelatesToPurchaserFact_2015_Aug] PRIMARY KEY NONCLUSTERED ( [CiresonPurchaseDimKey] ASC, [PurchaseHasPurchaser_UserDimKey] ASC, [HourId] ASC, [DateKey] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FileGroup2_Facts1] GO |
Note: you must do this for all other tables that are also missing it, usually by altering the highlighted parts to meet the requirements.
So browse through the remaining tables that are used by the particular view and ensure the script is run accordingly.
Hint: This is where Ctrl + H comes in handy to change the table suffix to the correct one.
Then you can start job on the Data Warehouse Management Server:
Start-SCDWJob -JobName Load.CMDWDataMart
And view the progress:
Get-SCDWJobModule -JobName Load.CMDWDataMart
Depending on table: You may see the module go from failed > not started > running > to completed and everything fine.
However if it fails again, run the ps job module command:
Get-SCDWJobModule -JobName Load.CMDWDataMart | Select-Object -ExpandProperty "ModuleErrorSummary"
Then look at the last part to find the newest logged errors, in this case:
UNION ALL view 'CMDWDataMart.dbo.CiresonPurchaseRelatesToPurchaserFactvw' is not updatable because a partitioning column was not found.
We found the DWDataMart has the same tables and the constraints needed for each table. We recommend getting each constraint as a query and copying the dates, otherwise it's a pain to determine each date range for a month.
USE [CMDWDataMart] GO ALTER TABLE [dbo].[CiresonPurchaseRelatesToPurchaserFact_2015_Aug] WITH CHECK ADD CONSTRAINT [CiresonPurchaseRelatesToPurchaserFact_2015_Aug_Chk] CHECK (( [DateKey]>=(20150801) AND [DateKey]<=(20150831) )) GO ALTER TABLE [dbo].[CiresonPurchaseRelatesToPurchaserFact_2015_Aug] CHECK CONSTRAINT [CiresonPurchaseRelatesToPurchaserFact_2015_Aug_Chk] GO |
That's really easy to mess up, so we opened two instances of SSMS, then copied created constraint script from DWDataMart to CMDWDataMart and ran it for CMDWDataMart table.
So after that extremely tedious task of ensuring the constraints from CMDWDataMart are mirroring those from DWDataMart, we start the job again on the Data Warehouse Management Server:
Start-SCDWJob -JobName Load.CMDWDataMart
Optionally to view your errors on the Data Warehouse Management Server, on the DWMS go to Computer Management > System Tools > Event Viewer > Applications and Services Logs > Operations Manager
(We recommend clearing it and then refreshing it constantly to see if an error pops up.)
After about five minutes no error was popping up. Then after running the command:
Get-SCDWJobModule -JobName Load.CMDWDataMart
Or check SCSM Console > Data Warehouse > Data Warehouse Jobs to verify all job modules are set to Not Started.
