Before you enable auditing of data models, you need to identify the events that you want to track and create a database table to record the information.
Use an SQL editor to create an audit table. Since the query accesses only one database, the audit table needs to reside where the query is processed. Create columns that reflect the types of information that you want to record.
Table 242 provides a sample structure for the table named BQAUDIT. You can customize your audit table and columns to store information related to any events that you can define.
Table 242. Sample Structure for the BQAUDIT Table
Column | Data Source | Explanation/Example |
|---|
EVENT_TYPE | Text | Events which occur within the context of a query session, such as: |
USERNAME | SQL function | Database user information returned by a database SQL function, such as: |
DAY_EXECUTED | SQL function | Date, time, and duration information returned by a database SQL function, such as: CURRENT_TIMESTAMP (Red Brick) |
SQL_STMT | Interactive Reporting keyword | SQL statements generated by the user and captured from the Interactive Reporting SQL log, and returned by the keyword variable :QUERYSQL |
DATAMODEL | Interactive Reporting keyword | Data models accessed by the user and returned by the keyword variable :REPOSITORYNAME |
NUM_ROWS | Interactive Reporting keyword | Query information returned by the keyword variable :ROWSRETRIEVED |