4 Flexible Aggregate Reporting - Database

Database Architecture

BI Publisher Periodic Reporting is a customizable Argus Safety feature. The database components and changes specific to BIP reports are explained in the sections that follow.

Flexible Aggregate Reporting - Database Objects

Installing the Argus Safety database prompts for the creation of the BIP Owner schema. This schema contains all database objects needed for BI Publisher Flexible Reporting. It also has access to some Argus Safety schema objects through synonyms.

Note:

You cannot update case data from the BIP Publisher Owner schema. You can only update the Periodic Report status related tables such as CMN_REG_REPORTS, PER_REPORT_QUEUE and PER_REPORT_STS. The system updates the CASE_REG_REPORTS table for final reports. The system accesses the report blob tables from the BIP Owner to store the report output in Argus Safety.

The schema name is configurable at the time of creation. The system makes an entry in the Common Profile switches to store this schema name for reference by Argus Mart.

For report generation, this schema holds:

Tables
Views
The unzip utility function
A Java object
Packages
Database links
Database jobs

Tables

Tables in the BIP schema populate the case data:

Global Temporary tables (GTT) temporarily store data for report output generation. These are the only tables used in the BIP data model.
RM_ tables store persist data and are copies of the GTT tables. The system stores parameters in these tables based on the Persist data.
Configuration tables handle parameters and transactions.
SUPPORT tables store information such as parameters and their values, case series details, and BIP job details.

Table 4-1 provides the list of tables in the BIP Owner schema and their users.

Table 4-1 List of Tables in BIP Owner Schema

Table Name	Type	Purpose
RPT_AGG_PARAMS	SUPPORT	Stores the list of parameters that are passed down to run the report.
RPT_AGG_CASE_SERIES	SUPPORT	Stores case series information.
RPT_AGG_CS_CASES	SUPPORT	Stores all cases in the case series that are selected for BIP report generation. For example, Main case series, Cumulative case series, Section 6.2 case series, Ad hoc1 case series, and so on.
RPT_AGG_BIP_JOB	SUPPORT	Stores BIP job information and other transactional data. This table data is retained without purging.
GTT_RPT_AGG_CASE	Global Temp	Stores case information.
GTT_RPT_AGG_DRUG	Global Temp	Stores drug related information.
GTT_RPT_AGG_EVENT	Global Temp	Stores event related information.
GTT_RPT_AGG_EV2DRUG	Global Temp	Stores event to drugs related assessment details.
GTT_RPT_AGG_HEALTHAUTHID	Global Temp	Stores health authority details.
GTT_RPT_AGG_UNIQ_CASES	Global Temp	Maintains a list of unique cases.
GTT_RPT_AGG_DET_LIST	Global Temp	Temporary support table that fetches assessment data.
GTT_RPT_AGG_DRUGNAMES	Global Temp	Stores the drug names for reporting.
GTT_RPT_AGG_BIP_BLOB	CONFIG	Copies and holds the report output blob between the BIP Owner schema and the BIP Metadata repository database.
RPT_AGG_JOB_EXEC_STS	CONFIG	Used to avoid multiple report jobs fetching the report output at the same time.
RM_RPT_AGG_CASE	PERSIST	Persist table for GTT_RPT_AGG_CASE.
RM_RPT_AGG_DET_LIST	PERSIST	Persist table for GTT_RPT_AGG_DET_LIST.
RM_RPT_AGG_DRUG	PERSIST	Persist table for GTT_RPT_AGG_DRUG.
RM_RPT_AGG_DRUGNAMES	PERSIST	Persist table for GTT_RPT_AGG_DRUGNAMES.
RM_RPT_AGG_EV2DRUG	PERSIST	Persist table for GTT_RPT_AGG_EV2DRUG.
RM_RPT_AGG_EVENT	PERSIST	Persist table for GTT_RPT_AGG_EVENT.
RM_RPT_AGG_HEALTHAUTHID	PERSIST	Persist table for GTT_RPT_AGG_HEALTHAUTHID.
RM_RPT_AGG_PARAMS	PERSIST	Persist table for RPT_AGG_PARAMS.
RM_RPT_AGG_UNIQ_CASES	PERSIST	Persist table for GTT_RPT_AGG_UNIQ_CASES.
RPT_AGG_PARAMS	SUPPORT	Stores the list of parameters that are passed down to run the report.
RPT_AGG_CASE_SERIES	SUPPORT	Stores case series information.
RPT_AGG_CS_CASES	SUPPORT	Stores all cases of the case series that are selected for BIP report generation. For example, Main case series, Cumulative case series, Section 6.2 case series, Ad hoc1 case series, and so on.
RPT_AGG_BIP_JOB	SUPPORT	Stores BIP job information and other transactional data. Table data is retained without purging.
GTT_RPT_AGG_CASE	Global Temp	Stores case information.
GTT_RPT_AGG_DRUG	Global Temp	Stores drug related information.
GTT_RPT_AGG_EVENT	Global Temp	Stores event related information.
GTT_RPT_AGG_EV2DRUG	Global Temp	Stores event to drugs related assessment details.
GTT_RPT_AGG_HEALTHAUTHID	Global Temp	Stores health authority details.
GTT_RPT_AGG_UNIQ_CASES	Global Temp	Maintains a list of unique cases.
GTT_RPT_AGG_DET_LIST	Global Temp	Temporary support table that fetches assessment data.
GTT_RPT_AGG_DRUGNAMES	Global Temp	Stores drug names for reporting.
GTT_RPT_AGG_BIP_BLOB	CONFIG	Copies and holds the report output blob between the BIP Owner schema and the BIP Metadata repository database.
RPT_AGG_JOB_EXEC_STS	CONFIG	Used to avoid multiple report jobs fetching the report output at the same time. This table should not contain any row when the reports are not running. If it does, the completed reports will not be pushed back into Argus Safety database.
RM_RPT_AGG_CASE	PERSIST	Persist table for GTT_RPT_AGG_CASE.
RM_RPT_AGG_DET_LIST	PERSIST	Persist table for GTT_RPT_AGG_DET_LIST.
RM_RPT_AGG_DRUG	PERSIST	Persist table for GTT_RPT_AGG_DRUG.
RM_RPT_AGG_DRUGNAMES	PERSIST	Persist table for GTT_RPT_AGG_DRUGNAMES.
RM_RPT_AGG_EV2DRUG	PERSIST	Persist table for GTT_RPT_AGG_EV2DRUG.
RM_RPT_AGG_EVENT	PERSIST	Persist table for GTT_RPT_AGG_EVENT.
RM_RPT_AGG_HEALTHAUTHID	PERSIST	Persist table for GTT_RPT_AGG_HEALTHAUTHID.
RM_RPT_AGG_PARAMS	PERSIST	Persist table for RPT_AGG_PARAMS.
RM_RPT_AGG_UNIQ_CASES	PERSIST	Persist table for GTT_RPT_AGG_UNIQ_CASES.

For details about tables, columns, and column mappings with Argus Safety, refer to the Argus Flexible Reporting Data Model.xls.

Views

The BIP Reporting data model uses the views in the BIP Owner schema to fetch data from Global Temporary tables. These views are:

V$RPT_ALL_CLINICALSUMMARY
V$RPT_CASESUMMARY
V$RPT_CLINICALSUMMARY
V$RPT_SECT61SUMMARY
V$RPT_NONINT

Additionally, the following log views are provided to enable debugging and troubleshooting:

V$RPT_ALL_CLINICALSUMMARY_LOG
V$RPT_CASESUMMARY_LOG
V$RPT_CLINICALSUMMARY_LOG
V$RPT_SECT61SUMMARY_LOG
V$RPT_NONINT_LOG

Database Link

A default database link appears when you enable BI Publisher Periodic reporting. This link is created between the BI Publisher Owner schema and the BI Publisher Repository database and copies the report output blob to the Argus Safety database.

Java Stored Procedure

The scheduled Flexible Periodic report outputs are stored in the BIP metadata repository in a compressed format. The BIP Owner schema uses a Java stored procedure to decompress the report output before storing it in the Argus Safety schema.

Packages

The BI Publisher Owner schema holds the following package types:

Utility package
Data load package
User exit package

PKG_AGG_RPT_UTIL Utility Package

The utility package holds minor utility features for the data load. Table 4-2 provides a list of functions and procedures and their usage.

Table 4-2 Utility Package Details

Procedure/Function	Usage
f_get_query_details	Populates the case series or query prompts that the user can access.
f_print_as_text	Determines the water mark.
f_get_cs_name	Returns the case series name for a Case Series ID.
f_get_agency_name	Gets the agency name for the passed Agency ID.
p_fetchrptoutput	Copies the report output data into Argus tables, updates CMN_REG_REPORTS, updates the report status tables and stores the submission details of the final report.
f_get_duration	Returns a formatted duration for printing in a report. For example, 10 days.
f_get_codelist_val	Gets the code list display value.
f_get_enterprises	Gets the Active Enterprise list on the BIP console.
f_get_cmn_profile_flag	Fetches the cmn_profile value on key.
f_ConvertBlobToClob	Converts the blob data into clob.

PKG_AGG_RPT Data Load Package

The data load package handles the data extraction and derivations that prepare the data for reporting. Table 4-3 provides the complete list of procedures and functions present in this package.

Table 4-3 Data Load Package Details

Procedure/Function	Usage
Global Variables	Describes all parameters shown or hidden on the BIP report screen as package level variables. Parameter values are automatically stored by the BIP report during execution.
Lexical Variables	Normal package variables described according to the lexical parameters used in the BIP report.
p_pop_psur_case_temp	Populates the temp table GTT_RPT_AGG_CASE.
p_pop_psur_drug_temp	Populates the temp table GTT_RPT_AGG_DRUG.
p_pop_psur_event_temp	Populates the temp table GTT_RPT_AGG_EVENT.
p_pop_psur_ev2drug_temp	Populates the table GTT_RPT_AGG_EV2DRUG.
p_pop_psur_healthauthids_temp	Populates the temp table GTT_RPT_AGG_HEALTHAUTHID.
p_pop_psur_drugnames_temp	Populates the table GTT_RPT_AGG_DRUGNAMES.
f_before_data	The main function invoked from BI Publisher. Called from the Before Report trigger of the BI Publisher report.
f_get_report_id	Retrieves the PN_REG_REPORT_ID parameter value.
p_set_report_id	Sets the PN_REG_REPORT_ID parameter value to the global variable so it can be retrieved through f_get_report_id in BIP reports.
p_check_cs_case_ctr	Checks the counts of cases needed for the trailer page.
p_ins_rpt_status	Inserts the record into PER_RPT_STATUS for log reporting.
p_upd_rpt_status	Updates the status of the report on completion with success or failure.
f_after_report	Final trigger invoked by BI Publisher.
f_get_evtseriouscr_list	Gets the event seriousness criteria list.
f_get_dose_stringlist	Generates the dose string list.
f_get_uniq_patient_id	Obtains the unique patient ID.
p_updclinicaldrugrole	Updates the clinicaldrugrole column in GTT_RPT_AGG_DRUG table.
p_update_gtt_tables	Updates the GTT tables for follow-up.
FindAggRptJobID	Local procedure that hits the BI Publisher metadata repository tables, obtains the blob data, converts into clob for easy processing, and arrives at the Job ID through the supplied parameters to the BIP reports. The Job ID is then inserted into the RPT_AGG_BIP_JOB table.
pop_user_security_tables	Populates user security tables based on the user-security level.
p_populate_cover_params	Fills in the data for the RPT_AGG_PARAMS table needed for the cover page.
p_populate_listedness	Determines and populates listedness for each case-event-product based on the chosen algorithm.
p_copy_rpt_case_series	Copies all case series required for report execution into the RPT_AGG_CASE_SERIES and RPT_AGG_CS_CASES tables.
p_populate_dlp_cases	Populates DLP cases.
p_set_lex_conditions	Handles the conditions used to set lexical parameters.
p_pop_log_tables	Populates all RM_ tables.
p_pop_concurrency_errors	Populates the Concurrency Error Handling that mentions whether the case series is modified while a report is in progress.

PKG_AGG_RPT_USER_EXIT User Exit Package

Customers can place their code directly in this package to modify the data loaded. For example, the customer can modify the loaded case data by calling the user exit p_modify_case_temp.

Table 4-4 describes the procedures and functions in the user exit package.

Table 4-4 User Exit Package Details

Procedure/Function	Usage
p_modify_case_temp	Called at the end of the case population procedure. You can customize the populated cases here.
p_modify_event_temp	Called at the end of the event population procedure. You can customize the populated events here.
p_modify_drug_temp	Called at the end of the drug population procedure. You can customize the populated drugs here.
p_modify_evt_assess_temp	Called at the end of the assessment population procedure. You can customize the populated assessment here.
p_modify_healthauthids_temp	Called at the end of the health authority details population procedure. You can customize the populated health authority IDs here.
p_modify_drugnames_temp	Called at the end of the drug name details population procedure. You can customize the populated drug name here.
p_modify_rm_case_temp	Called after loading the RM_RPT_AGG_CASE table.
p_modify_rm_event_temp	Called after loading the RM_RPT_AGG_EVENT table.
p_modify_rm_drug_tem	Called after loading the RM_RPT_AGG_DRUG table.
p_modify_rm_evt_assess_temp	Called after loading the RM_RPT_AGG_EV2DRUG table.
p_modify_rm_healthauthids_temp	Called after loading the RM_RPT_AGG_HEALTHAUTHID table.
p_modify_rm_drugnames_temp	Called after loading the RM_RPT_AGG_DRUGNAMES table.

Figure 4-1 General Structure of a User Exit

Description of ''Figure 4-1 General Structure of a User Exit''

Database Jobs

The BI Publisher Owner holds the following database jobs. You must create these jobs manually during the installation and configuration of Flexible Periodic Reports.

For more information on these, refer to the Argus Safety Installation Guide.

Report Output Pusher

This job decompresses and pushes the report output from BI Publisher metadata through the utility procedure p_fetchreportoutput.

The output is connected to the configuration using the configuration ID, BIP report name, and the draft/final option. The new output replaces the output with the same combination of key values. Oracle recommends you execute this job every 3 minutes. However, you can customize execution according to your needs.

For large customers who run multiple concurrent reports, you can execute job runs every 3-10 minutes. For small customers who run only a few reports the whole day, you can execute runs on an hourly basis.

If required, you can customize the job to push the completed report output to other data sources instead of the Argus Safety database.

Persist Data Cleaner

A database job is needed to purge the data present in the Persist (RM) tables that exceeds the persist duration mentioned during the report scheduling or as mentioned in the Argus Console.

This job is not needed if you prefer not to use the Persist data mechanism. This can be set to run once a day or once a week based on the data load.

Argus Application Schema Objects

The Argus Safety Application schema (argus_app) has been enhanced to support BI Publisher Aggregate Reporting.

Tables

Table 4-5 describes the database tables added to the Argus Safety database to handle the internal operations related to BI Publisher Periodic Reporting.

Table 4-5 Argus Safety Database tables

Table Name	Purpose
CFG_RPT_AGG_PARAMS	Contains the parameters passed for each report, segregates the parameters that are part of the report header, and selects the default values.
SAFETY_ERR_LOG	Stores errors, warnings and debugs that occur during execution. Pushes errors into the Argus Safety error log.
CFG_BIP_REPORT_PARAMETERS	Stores the list of parameters that are passed in through the BI Publisher WebServices API.

Packages

Packages related to case series have been updated to store case series data.

The GSS_UTIL package has been updated to find if a user's access has expired.

A new package, pkg_rpt_log, is called from BIP packages and stores the error, warning and debug messages in the SAFETY_ERR_LOG table. Additionally, it stores the error messages in the traditional Argus Safety error log tables by calling Pkg_Console_Common.p_error_log.

Access Grants to BI Publisher Owner

You must provide access to the Argus database objects so the BIP schema can access them.

SELECT access for Case related, Code list, and Configuration tables.
SELECT, INSERT, UPDATE access for process tables such as CMN_REG_REPORTS, PER_RPT_QUEUE, PER_RPT_STS, CMN_SUB_REPORTS, and so on.
EXECUTE access for various common packages such as PKG_RLS and GSS_UTIL.

Note:

You need access for compiling BIP Packages in the BIP Owner schema.