Resources for Writing Custom Analytic/Reporting Queries
Issue
Resources needed for writing custom analytic/reporting queries
Solution
In general, it is not supported to query the TotalAgility product databases, as explained in the topic Querying the TotalAgility Databases. However, there is a subset of tables and columns that are documented and supported specifically for use in the context of analytics. The following product documentation links are for KTA 7.10, but the appropriate documents for each version of KTA are available from the full documentation list for that version. These documents also do not change much between versions.
TotalAgility database
Tables for Analytics – main TotalAgility database - describes the Kofax TotalAgility tables and fields used by Kofax Analytics for TotalAgility. As the document shows in more detail, the following are the only tables that are supported.
- CATEGORY
- AW_RESOURCE
- BUSINESS_PROCESS
- LIVE_ACTIVITY
- LIVE_ACTIVITY_RESOURCE
- JOB
- JOB_HISTORY
- JOB_MILESTONES
- JOB_STATE_HISTORY
- FINISHED_JOB
- FINISHED_JOB_HISTORY
- FINISHED_JOB_MILESTONES
- FINISHED_JOB_STATE_HISTORY
TotalAgility_Reporting database
- Reporting Tables – TotalAgility_Reporting database - describes the Dimension and Fact tables of the Kofax Reporting data warehouse.
- Reporting Views - TotalAgility_Reporting database - describes the sample views provided with Kofax Reporting
Kofax Analytics for TotalAgility (KAFTA) database
If using Kofax Analytics for TotalAgility (KAFTA), there is a chapter of the features guide that gives documentation for record definitions. These can either be used as base records in Insight to use when building new records, or the underlying tables can be queried in the KAFTA_Data database.
Kofax Analytics for TotalAgility Product Features Guide – Chapter 4 – Record Definitions
Undocumented tables or columns
These documents serve as the data dictionaries for the tables and columns that are supported for queries. If a table or column is not included in these documents, that means it is not supported for use.
Performance
Be aware that it is up to the author of the any custom queries to assess their performance impact. This is especially important with queries the TotalAgility tables used for live jobs and activities.
Querying data not normally included in the reporting database
The TotalAgility_Reporting database is document-centric and therefore does not include data such as job variables or folder fields. In some cases it may be desirable to use this type of information in queries.
One approach to making this data available would be to write data out to a custom database during the course of a job, and then query the custom database. Another approach would be to modify the process to add the desired data into a document field, which causes it to end up in the document field data in the reporting database.
Assistance in writing queries
Technical Support can assist with specific questions about the documented tables and columns, but not with writing or reviewing custom queries.
If you need a query to accomplish a specific purpose and not able to write it yourself, we would suggest engaging with Kofax Professional Services.
Level of Complexity
High
Applies to
| Product | Version | Build | Environment | Hardware |
|---|---|---|---|---|
| KTA | ALL |