Reporting: Internal or Exported Data Source

Reporting options

There are three methods for running reports against K2's usage and audit data and each of these methods can be targeted either to the internal data store or an external data mirror:

1. In the simplest case, a report is selected from the Reports Menu in KeyConfigure and then the underlying report module uses SQL queries to fetch data from the remote KeyServer using its native protocol.

2. The same report modules used by KeyConfigure can also be hosted from the optional KeyReporter web server component, but in this case the user interface for invoking and displaying reports is handled by a web browser – KeyReporter has no visible interface of its own.

3. A general purpose reporting tool can be used as a third option for report generation. This option allows complete customization of report layout and the underlying SQL queries. Note: a general report tool depends on ODBC with the Sassafras ks-ODBC driver in order to communicate to the KeyServer as target data source.


Data Export

All the reporting options illustrated above use SQL queries to request data records directly from the KeyServer data store, but the queries can just as easily be pointed to a mirror of this same data hosted on a dedicated database server or dbms. First, the mirror must be created by configuring KeyServer to continuously export its data using an appropriate ODBC driver for the particular dbms. Then each of the report tools can make use the same ODBC driver in order to access data from the mirror instead of accessing data directly from KeyServer:


Mix and Match

Although the diagrams above illustrate all three reporting tools pointed to either internal or mirrored data, nothing prevents the use of internal data for some purposes and mirrored data for others. Also note that KeyServer includes a handful of "export modules" that are designed to mirror data to a few specific database servers without going through ODBC. But for report generation using KeyConfigure or KeyReporter to query mirrored data, ODBC is the only route. Typically, the connection method for a general purpose reporting tool will likewise be through ODBC – unless the tool is actually a integral component of the dbms.

There are just a few common reasons to consider a exporting to a data mirror. Probably most common is the need for a custom report that links KeyServer data to other data hosted on a corporate dbms. Exporting the KeyServer data to the same dbms can facilitate server-side joins which will often give better performance than the same report configured to extract data from two separate sources. At very large sites, exporting to a corporate dbms running on dedicated hardware may provide significantly better reporting performance, especially when attempting to run multiple reports simultaneously against large audit or usage data sets. But even when addressing slow report performance on large data sets, exporting may not be the only solution - the ability to schedule standard report modules for overnight execution using KeyReporter can often make long execution times a non-issue.