The DSW.dbo.ttWaveProcessAreaObjectTargetReportCache table, used for Target Remediation error reports, can contain millions of records, possibly slowing performance.
Map uses the table to build Transform Remediation Rule reports to display data audit updates during the Transform Automation process. Using Target Report Caching, Transform maintains the error reports so that there is a record of the data changed by the remediation rule. This feature, which runs when one or more Remediation events are active for the Target report, caches all records from a Target report prior to the execution of remediation rules.
The table populates even if the Build Remediation Rule Reports check box is unchecked on the Map tab of the Parameters page in Console. The table caches the data from the target report view as pre values to show the pre and post values for records that are remediated in the target report.
For details on Transform Remediation Rules, see
https://dsphelp.syniti.com/706/general/Transform/Use_Cases/Register_Target_Remediation_Rules.htm
There are two recommended methods for overcoming slowed performance when this table is the suspected culprit.
Solution 1: Truncate the table
If you are not using these generated values, you can safely truncate the table.
NOTE: You can set up a service page to truncate the table on a regular basis if you do not anticipate any future need for the generated values.
Solution 2: Delete certain report data from the table
If you do not need pre values for specific report(s), you can run a query filtering on the WaveProcessAreaObjectTargetReportID value:
NOTE: This query does not perform a delete. It helps to identify which WaveProcessAreaObjectTargetReportID values are no longer needed.
USE DSW
SELECT Console.dbo.ttWave.Description AS Wave, Console.dbo.ttProcessArea.ProcessArea, Console.dbo.ttObject.Description AS Object, Console.dbo.ttTarget.Name AS Target,
dbo.ttWaveProcessAreaObjectTargetReport.TargetReport, dbo.ttWaveProcessAreaObjectTargetReportCache.WaveProcessAreaObjectTargetReportID
FROM dbo.ttWaveProcessAreaObjectTargetReportCache INNER JOIN
dbo.ttWaveProcessAreaObjectTargetReport ON
dbo.ttWaveProcessAreaObjectTargetReportCache.WaveProcessAreaObjectTargetReportID = dbo.ttWaveProcessAreaObjectTargetReport.WaveProcessAreaObjectTargetReportID INNER JOIN
dbo.ttWaveProcessAreaObjectTarget ON dbo.ttWaveProcessAreaObjectTargetReport.WaveProcessAreaObjectTargetID = dbo.ttWaveProcessAreaObjectTarget.WaveProcessAreaObjectTargetID INNER JOIN
Console.dbo.ttTarget ON dbo.ttWaveProcessAreaObjectTarget.WaveProcessAreaObjectTargetID = Console.dbo.ttTarget.WaveProcessAreaObjectTargetID INNER JOIN
Console.dbo.ttWaveProcessAreaObject ON dbo.ttWaveProcessAreaObjectTarget.WaveProcessAreaObjectID = Console.dbo.ttWaveProcessAreaObject.WaveProcessAreaObjectID INNER JOIN
Console.dbo.ttObject ON Console.dbo.ttWaveProcessAreaObject.ObjectID = Console.dbo.ttObject.ObjectID INNER JOIN
Console.dbo.ttWaveProcessArea ON Console.dbo.ttWaveProcessAreaObject.WaveProcessAreaID = Console.dbo.ttWaveProcessArea.WaveProcessAreaID INNER JOIN
Console.dbo.ttWave ON Console.dbo.ttWaveProcessArea.WaveID = Console.dbo.ttWave.WaveID INNER JOIN
Console.dbo.ttProcessArea ON Console.dbo.ttWaveProcessArea.ProcessAreaID = Console.dbo.ttProcessArea.ProcessAreaID
GROUP BY Console.dbo.ttWave.Description, Console.dbo.ttProcessArea.ProcessArea, Console.dbo.ttObject.Description, Console.dbo.ttTarget.Name, dbo.ttWaveProcessAreaObjectTargetReport.TargetReport,
dbo.ttWaveProcessAreaObjectTargetReportCache.WaveProcessAreaObjectTargetReportID