Template Reporting Database Schema

The Template Reporting feature for ActiveBatch is designed to provide the ultimate transparency into a user's environment. It does this by using an external reporting database to create an abstract layer for the data contained in the ActiveBatch Job Scheduler database. With the usage of the Template Reporting ETL job, all relevant data is extracted from the transactional database and transformed into relational table in the reporting database. The Template Reporting database schema is fully documented by the Template Reporting CHM file included with the installation. This section details some key point to start working with the Template Reporting database.

 

Template Reporting Database Core Schema

 

 

Job Schedulers

 

The job schedulers table maintains a list of all job schedulers which have been synchronized with this reporting database. On initial synchronization of the job scheduler, it is assigned a unique ID which will identify all related data with that Job Scheduler. Using the ID column from the JobSchedulers table, any data from any other table can be tied back to that Job Scheduler using the corresponding JssID column. This table also contains a LastSync column which contains the last time the Template Reporting ETL Job has been run for that particular Job Scheduler.

 

Templates

 

The Templates table is the root table for all Template related data, similar to how the AllObjects table in the JSS Transactional database is the root for objects for that schema. It contains the latest information for all Templates which have been synchronized with the reporting database. I stress latest because whereas most of the other data tables are versioned on the Template's RevisionID, the Templates table only contains the last known state of the Template. There are many advantages to the Templates Table which is below.

 

  • Acts as a quick reference for what data has already been synchronized with the database

  • Provides the ability to see what version of the template is on record. If the RevisionID of the template in this table does not match the RID of the template on the JSS, they will know that the Reporting database is out of date and will need to be Synchronized to get the latest from the JSS

  • Provides the ability to see if an object has even been picked up by the Template Reporting ETL Job. If the template does not exist in this table it can be expected that there will be no data related to that template in the reporting database.

  • Acts as a simple way to get the "latest" data for any given template

  • Provides the ability to join between this table (ID, JssID, and RevisionID column) and a versioned data table to only fetch the latest revision data from that table

  • There is a view which does exactly that; see CurrentTemplatesOverview in the Views section of this document

  • Acts as a central location for managing a single templates data

  • Users can delete rows from this table and the delete will cascade to all data which is tied to this Template

 

Reference Tables

 

The TemplateTypes table, pictured above, is an example of the data storage methodology widely used in this schema. This table contains the ID and Type name of all object types in ActiveBatch (E.G. Job=3, JobPlan=13, ETC.). By providing a reference table for the object types, containing the readable type name, users looking to filter data based on object type can do so by using this table as a reference for the type IDs used in the Templates table. There are many more examples of this in the Template Reporting database schema which are covered in the Template Reporting Database CHM file.

 

Versioned Tables

 

Most of the tables in the Template Reporting database schema are versioned. This means that they will contain a historical record of each revision for every template present in the database. E.G. If a user creates an object and names it "TestObject". The user then proceeds to rename the object. When that object is stored in the Template Reporting database, there will be two records in GeneralProperties, showing that the first revision the name was "TestObject" and in the second revision it is something different. Generally, any table whose Primary key is made up of the ID, JssID, and RevisionID columns, is a versioned table.