When customer is interested in reducing of the DB space, he is able to configure data cleanup.
Warning! Be aware that the data is cleaned up forever and beyond recovery. So, data cleanup should be performed conscious.

Global Settings



Data cleanup is performed by data loader according to the configured settings in the table [Reporting]<span style="color: #808080">.</span>[_Setting_DataCleanup]
Be default the values of the table are:



DataCategory

XmlDayToKeep

EtlDayToKeep

ReportingMonthToKeep

ReportingDateToKeep

REPORT

60

90

NULL

NULL

ASTATE

5

90

NULL

NULL

ASTATE – tables that contain agent state data.
REPORT – tables that contain data about conversations and their transfers, forwards, recordings, modality, task completion information, etc.
XmlDayToKeep – cleans up XML data
EtlDayToKeep – cleans up ETL data
ReportingMonthToKeep and ReportingDateToKeep – cleans up reporting data


Script to set default values:

   MERGE INTO [Reporting].[_Setting_DataCleanup] AS Target
    USING (VALUES ('REPORT', 60, 90, NULL, NULL)
                , ('ASTATE', 5, 90, NULL, NULL)) AS Source ([DataCategory], [XmlDayToKeep], [EtlDayToKeep], [ReportingMonthToKeep], [ReportingDateToKeep])
    ON Target.[DataCategory] = Source.[DataCategory]
    WHEN MATCHED THEN UPDATE SET 
   [XmlDayToKeep] = Source.[XmlDayToKeep]
 , [EtlDayToKeep] = Source.[EtlDayToKeep]
 , [ReportingMonthToKeep] = Source.[ReportingMonthToKeep]
 , [ReportingDateToKeep]  = Source.[ReportingDateToKeep];

Rules of the reporting data cleaned up:

  • when XmlDayToKeep is configured, the data is cleaned up before (today's day - configured amount of days).
  • when EtlDayToKeep is configured, the data is cleaned up before (today's day - configured amount of days).
  • the default values of ReportingMonthToKeep and ReportingDateToKeep are NULL.
  • NULL for ReportingMonthToKeep and ReportingDateToKeep means that data is never cleaned up.
  • when ReportingDateToKeep is configured, the data is cleaned up before this date (the data of the configured date stays)
  • when ReportingMonthToKeep is configured, the data is cleaned up before (today's month - configured amount of months). E.g. when 0 is configured, the data before current month is cleaned up, only the data of current month stays.
  • when both ReportingMonthToKeep and ReportingDateToKeep are configured, they are compared and data is cleaned up before the most elder date.
  • ReportingDateToKeep configuration should be in format YYYY-MM-DD. ReportingDateToKeep setting is not inclusive, in order to cleanup reporting including date, you should specify next date.
  • ReportingMonthToKeep configuration should be a number.

    Tenant cleanup Settings


    Once you system has at least one Tenant you may enable per tenant cleanup.
    Value with SetupKey CLEANUP_PER_TENANT in [Reporting].[_SysSetup] corresponds to enable -1 or disable – 0 tenant cleanup settings:
    Example: Enable cleanup per tenant
    UPDATE [Reporting].[_SysSetup] SET SetupValue = 1 WHERE SetupKey = 'CLEANUP_PER_TENANT'
    Tenant Cleanup settings cover only 2 values: ReportingMonthToKeep and ReportingDateToKeep. Xml and ETL data settings are global for the system.
    It is important to remember that Tenant cleanup setting may be only reduce period of reporting data storage.
    Tenant data cleanup is performed by data loader according to the configured settings in the table [Reporting].[_Setting_TenantDataCleanup]
    Be default the TenantName, ReportingMonthToKeep and ReportingDateToKeep are NULL


    TenantId

    TenantName

    ReportingMonthToKeep

    ReportingDateToKeep

    2A45A4C2-C072-4376-A126-BDC5E07A055B

    NULL

    NULL

    NULL


    TenantId is inserted in the [Reporting]<span style="color: #808080">.</span>[_Setting_TenantDataCleanup] on tenant creation.
    In order to configure cleanup per tenant you should assign values to TenantName and one of the values fields ReportingMonthToKeep or ReportingDateToKeep.
    Example:
    UPDATE [Reporting].[_Setting_TenantDataCleanup] SET
    TenantName = '01 SALES SERVICE',
    ReportingDateToKeep = '2020-07-16'
    If TenantName is not configured or does not correspond to TenantId the setting is skipped.


    Complete Data Cleanup

    At some cases it is necessary delete all historical reporting from database. For this cleanup settings has to be adjusted as following:
  • XmlDayToKeep = -1
  • EtlDayToKeep = -1
  • ReportingMonthToKeep = -1
  • ReportingDateToKeep = NULL



SQL update script:
UPDATE dc SET
XmlDayToKeep = -1
, EtlDayToKeep = -1
, ReportingMonthToKeep = -1
, ReportingDateToKeep = NULL
FROM [Reporting].[_Setting_DataCleanup] dc
This settings will force cleanup of all historical data from [etl] and [reporting] tables.
Warning! On Large databases complete data cleanup may take significant time and lead to transaction log growth.
In order to increase cleanup performance and avoid significant transaction log growth we strongly recommend switch database recovery mode to SIMPLE before executing data cleanup.


Do not forget to set back to default values after cleanup is comlete!

Executing Data Cleanup



By default DataCleanup is included into integration job [Reporting].[usp_DataLoader] stored procedure. And you do not need execute or schedule data cleanup manually.



Disabling Data Cleanup



For custom purposes you may need disable cleanup steps of the DataLoader and execute them manually. To achieve this update [IsEnabled] value in table [Reporting].[_DataLoaderCustomSteps] for the step you want to enable/disable.



StepId

RelatedStepId

Description

IsEnabled

32627338-DBCE-480B-B50D-40173616186B

NULL

XML_CLEANUP

1

C353ABF3-F9EB-4520-8D03-76A2645CE4E0

NULL

ETL_CLEANUP

1

3DFBFD3E-3A1C-448C-827E-B76927A49DEA

NULL

REPORTING_CLEANUP

1



For example: disable XML_CLEANUP step.
UPDATE [Reporting].[_DataLoaderCustomSteps]
SET [IsEnabled] = 0
WHERE [StepId] = '32627338-DBCE-480B-B50D-40173616186B'



Manual execution of Data Cleanup



If Data Cleanup is excluded from data loader manual cleanup execution may be needed. Use following code.
--XML_CLEANUP
EXEC [Reporting].[usp_Dataloader_Cleanup_XmlEvent] @IntegrationLogId = 1
--ETL_CLEANUP
EXEC [Reporting].[usp_DataLoader_Cleanup_EtlData] @IntegrationLogId = 1
--REPORTING_CLEANUP
EXEC [Reporting].[usp_DataLoader_Cleanup_Reporting] @IntegrationLogId = 1
This script execute cleanup procedures regardless table [Reporting].[_DataLoaderCustomSteps] settings.