Home Service Manager

Service manager DWH database Retention periods

Haridas_PratiharHaridas_Pratihar Customer IT Monkey ✭

We upgraded the portal from 2012 to 2019. Also we got the new servers( new environment).

I observed, DWDataMart database retention period set as 3 years and DWRepository database retention period set as 90 days. 3 years is the default retention period. Do I need to update the DWRepository database retention period as 3 years? I am looking for your feedback on this.


  • Haridas_PratiharHaridas_Pratihar Customer IT Monkey ✭

    90 days retention settings in DWRepository  database can delete the archived relationship as reporting database DWDataMart  is having three years retention period? Do I need to set it three years?

    Can someone answer my query?

  • Haridas_PratiharHaridas_Pratihar Customer IT Monkey ✭

    Can someone look into my query and provide the guidance?

  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭

    I've never heard of anyone changing the retention for DWRepository. DWDataMart of course! The reason being is that all of SCSM operates with some built in buffers for grooming and data warehousing. It all starts with:

    • SCSM Administration -> Settings -> Data Retention Settings. These define how long Work Items can hang around in SCSM aka the operational database before SCSM grooms them out, never to be seen again.
    • SCSM DW and it's associated Jobs/databases. Run about every hour or so, extracting data from SCSM/the operational DB. Given your SCSM Data Retention (noted above) is probably at least a week, having DW jobs run about every hours there is enough overlap where you don't risk losing data.
    • DWDataMart: As you no doubt know, this is the actual data warehouse for SCSM where everything ends up and as you rightly observe - defaults to a 3 year retention. Similar to Data Retention in the SCSM Operational DB, you can choose how long you want to keep data before SCSM grooms it out for good, never to be seen again.
    • DWRepository, DWStagingAndConfig: The best way to think about these databases are middlemen/temporary/translation DBs of sorts. They get the data from SCSM first and associated DW jobs massage it before pushing it into the final resting place that is DWDataMart.

    With all of that said. 90 days is perfectly acceptable for DWRepository because of its purpose. There should be no need to change this.

    Related links:

  • Haridas_PratiharHaridas_Pratihar Customer IT Monkey ✭

    Highly appreciate the details. Thank you.

Sign In or Register to comment.