Data Warehouse jobs broken
Tim_Votta
Customer Adept IT Monkey ✭✭
Apparently there was an issue with at least certain versions of SCSM 2012 where the end of time as far as the data warehouse was concerned was 11:59 PM on 11/30/20.
I was able to find this discussion which seemingly had a fix in it https://docs.microsoft.com/en-us/answers/questions/182935/scsm-2012-sp1-data-warehouse-jobs-failed-with-erro.html?page=1&pageSize=10&sort=oldest
However, I still have a number of Cireson DB items that are not updated. I don't fully understand the script that was run in step 3. More specifically, it looks like it was using a variable for all of the impacted table names but that did not seem to hit multiple Cireson DB items related to asset management. I am still seeing these items in the logs on my data warehouse management server.
ETL Module Execution failed:
ETL process type: Load
Batch ID: 1234263
Module name: LoadCMDWDataMartCiresonSupportContractRelatesToVendorFact
Message: UNION ALL view 'CMDWDataMart.dbo.CiresonSupportContractRelatesToVendorFactvw' is not updatable because a partitioning column was not found.
In the message the table name varies slightly, as far as I can tell though it is always something similar.
Anyone able to shine some light on what I need to do to get this corrected on these other DB items?
Best Answer
-
Jamie_Jordan
Customer IT Monkey ✭
@Tim_Votta I had the same problem and after running through pretty much every solution in that thread we ended up submitting a support call with Microsoft. They were able to fix it in less than an hour and our data warehouse is back up and running again. We had to run a SQL script on all the data warehouse databases that alters some table constraints. If you have the option, contacting Microsoft is probably the best way to get this fixed. If not, I think it's probably ok to attach the script here, but I don't want to be responsible if something goes wrong :)
0
Answers
@Tim_Votta I had the same problem and after running through pretty much every solution in that thread we ended up submitting a support call with Microsoft. They were able to fix it in less than an hour and our data warehouse is back up and running again. We had to run a SQL script on all the data warehouse databases that alters some table constraints. If you have the option, contacting Microsoft is probably the best way to get this fixed. If not, I think it's probably ok to attach the script here, but I don't want to be responsible if something goes wrong :)
That was what we ended up doing. MS support told us the fix is temporary and the only way to fix it long term was to upgrade to SCSM 2016 with the most recent roll up. We expect we will have to run the script monthly as the new tables are created in the Data Warehouse DB, so kind of looks like it is going to be a bit of a pain for us until our upgrade project actually starts.
Please can you post the script?
Sure, here it is. I am nowhere close to a SQL expert, but this was what worked for us. Make sure your DB is backed up first, but I mean if it is already broken probably not much to lose by trying it.
From what I could tell, it looks like the DW creates new tables for each month. We already have the tables for Feb 2021 created, so we are thinking that things will continue to work for February since we ran this script after the Feb 2021 tables already existed. We are going to be watching the DB to see when the March tables are created. Once we see those our plan is to run this again manually. As long as that works we are just going to schedule it to run as a stored procedure towards the end of each month to hopefully keep it a hands free solution at that point.
I saw the post mentioned above put when I try to run that script I get errors because I don't have any fact tables for Jan, like xyzFact_2021_Jan, it just goes Dec to Feb. Did any of you experience this and figure out how to fix it?
No we didn't have anything like that, it kept making the tables automatically for us. We have the tables for March in there now as well, those got created on 2/1. Are your data warehouse jobs at least attempting to run? I am not sure what actually creates the tables but from what we have seen it is done automatically and on the first of each month.
On a related note, even though the Feb 21 tables existed in mid-January when the above script was run it did not fix the DW jobs for February like we were expecting. We had to run the script again on Feb 1 and then things began working again. We scheduled some jobs to run in SQL on the first of each month at midnight and we are hoping that allows for it to be hands free at the moment, but we will have to see next month.
Hi Guys,
What version of SCSM are you running with what Update Rollups?
Microsoft addressed this issue in SCSM 2016 UR5:
"DateDim tables in DWDataMart are now extended until 12/31/2050."
Thanks,
Shane
We are still running 2012, it is still under extended support so we were not planning to upgrade until our next Fiscal year. Going to 2016 is not a small uplift, so we are stuck with putting duct tape on this issue until we have the funds to start the project to migrate off.
Ahh okay, yes I am aware of the lift it takes! You might need to ask Microsoft for the script to patch it up in the meantime then. I saw someone post something similar which may help:
https://docs.microsoft.com/en-us/answers/questions/182935/scsm-2012-sp1-data-warehouse-jobs-failed-with-erro.html