Home Service Manager

Data Warehouse jobs broken

Tim_VottaTim_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_JordanJamie_Jordan Customer IT Monkey ✭
    Answer ✓

    @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 :)

Answers

  • Jamie_JordanJamie_Jordan Customer IT Monkey ✭
    Answer ✓

    @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 :)

  • Tim_VottaTim_Votta Customer Adept IT Monkey ✭✭

    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.

  • Vyacheslav_GromovVyacheslav_Gromov Partner IT Monkey ✭

    Please can you post the script?

  • Tim_VottaTim_Votta Customer Adept IT Monkey ✭✭

    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.

  • James_JohnsonJames_Johnson Customer Advanced IT Monkey ✭✭✭

    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?

  • Tim_VottaTim_Votta Customer Adept IT Monkey ✭✭

    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.

  • Shane_WhiteShane_White Cireson Support Super IT Monkey ✭✭✭✭✭

    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

  • Tim_VottaTim_Votta Customer Adept IT Monkey ✭✭

    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.

  • Shane_WhiteShane_White Cireson Support Super IT Monkey ✭✭✭✭✭

    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

Sign In or Register to comment.