Skip to main content
Kofax

Reset Reporting Staging Database

Article # 3038640 - Page views: 306

Issue

Is there a way to clear out all unprocessed reporting data from the TotalAgility_Reporting_Staging database?

 

Solution

This article is regarding the TotalAgility_Reporting_Staging database which contains raw reporting data that has not been processed.  For instructions regarding the TotalAgility_Reporting database, which contains processed data, see Recreate Reporting Database.

KTA 7.9 and later

Almost everything in KTA writes raw reporting data to the TotalAgility_Reporting_Staging database.  So if reporting is enabled and any part of KTA is running, then there will be constant writes to the database that would prevent attempts from clearing out data. 

Resetting data by disabling reporting

Reporting can be disabled starting with KTA 7.9.  The primary way to reset reporting data is to disable reporting and click yes when prompted to delete data from the staging database.  The following steps would be followed:

  • In the KTA Designer go to System Settings > Database, retention and reporting > Reporting Server and then uncheck “Enable the Reporting feature”.
  • Click Yes to the question “Do you also want to delete any unprocessed Reporting data from the staging database?”
  • Disabling reporting (and thus the constant writing to the staging database) takes effect once everything is restarted.  This includes the following: TotalAgility IIS Application Pool, TotalAgility Core Worker, TotalAgility Streaming Service, Transformation Server, and the TotalAgility Reporting Service.
  • Having clicked Yes to delete staging data will have attempted to truncate the tables of the staging database.  The truncate query is executed with an unlimited timeout, however this may not be able to succeed until everything is restarted so that the constant writes to the database stop and thus there are no competing locks.
  • Once the truncate succeeds, a Table Size Report will show that the staging tables are empty.
  • At this point reporting can be reenabled if needed, which takes effect after restarting everything.

Resetting data by SQL stored procedure

Alternatively the truncate stored procedure can be executed directly, but if reporting is enabled, this still requires that everything in the environment be stopped.  The steps would be:

  • If reporting is enabled, stop everything.  This includes the following: TotalAgility IIS Application Pool, TotalAgility Core Worker, TotalAgility Streaming Service, Transformation Server, and the TotalAgility Reporting Service.
  •  Execute the stored procedure [TotalAgility_Reporting_Staging].[dbo].[sp_truncate_tables].  Again, this may not be able to execute if anything is still running and writing data to the staging database.
  • Start any services/IIS app pools that had been stopped.

KTA 7.8 and earlier

Preparation: TotalAgility_Reporting_Staging Database

Almost everything in KTA writes raw reporting data to the TotalAgility_Reporting_Staging database.  Stop all Kofax services and instances of IIS until the new database is available.

Creating and Initializing TotalAgility_Reporting_Staging Tables

At the discretion of the DBA, they can drop the existing database and then create a  new one with the same name, or just create one with a new name.  However, using a new name means that EVERY system in the KTA installation must be updated with the config utility to set the updated database name.  Thus using the same name is less error prone.

After a DBA creates a new database, the tables can be created and initialized by running the scripts that are included in the KTA install source.

{Install Source}\TotalAgility\TotalAgilityInstall\DatabaseScripts\SQL Server\Reporting\Kofax.Reporting.Analytics.Staging.SQL_TablesCreate.sql

{Install Source}\TotalAgility\TotalAgilityInstall\DatabaseScripts\SQL Server\Reporting\Kofax Kofax.Reporting.Analytics.Staging.SQL_Initialize.sql

Version Specific Steps

If the KTA installation is on a Service Pack/Fix Pack that has made changes to these databases, then those changes need to be applied as well. 

KTA 7.3

If your major version is 7.3 and you are in fix pack 2 or higher (any service pack is higher than this), then run this:

{Fix Pack Install Source}\\TotalAgilityInstall\DatabaseScripts\SQL Server\Upgrade Database Scripts\ 7.3.0.2 To 7.3.1.0 Conversion Scripts\Kofax.Reporting.Analytics.Staging.SQL_Convert.sql

Note: You should reference back to your installer of the fix pack, run the upgrade from base till the fix pack version.

 

Level of Complexity 

Moderate

 

Applies to  

Product Version Build Environment Hardware
KTA 7.3+ - - -

References

Add any references to other internal or external articles

 

Article # 3038640
  • Was this article helpful?