Aggregate Facts in Oracle Argus Analytics

Most of the requests that we tend to include in Dashboards are Summary Reports, where in data from the fact table is used to roll up to the appropriate level against a dimension. Instead, we can create aggregate tables that contain pre-computed sums, averages and so on for a fact table at a specific hierarchical level in the dimension (e.g. Month for a Period Dimension).

This, in essence, means that we compress specific data from the base Fact into the derived Aggregate Fact for the agreed dimensions levels. We can then make use of this Aggregate Fact table to derive these Summary Reports which will be much faster (as it will contain much lesser data to process) when compared to rolling up the same data from the detail-level base Fact table thus improving the response times of the requests.

Aggregate fact tables contain same measure data like in the lowest granularity fact table but summarized on certain levels of the dimensions that we plan to use.

In Oracle Argus Analytics, we have introduced the following Aggregate Facts to increase the performance of the Dashboard Reports:

W_PVA_CASE_MN_A

This is an Aggregate Fact table for the Base Fact W_PVA_CASE_VERSION_F and contains the same aggregate measures like the Base Fact, but aggregated at the Version Receipt Month level from the Period Dimension, along with the Enterprise, Product, Study, Case Processing Site, Case Type, Case Seriousness, and Case Deletion Indicator dimensions. Whereas, in the Base Fact W_PVA_CASE_VERSION_F, the granularity is at the Case ID, Version ID level for all the measures which is in turn at the Version Receipt Date level for each version of the case.

So, if we select the Month attribute from the "Period" Presentation Folder (Period -> "Case Version - Version Receipt Date" -> Month) along with the Case Version Aggregates, the data will be selected from the Aggregate table and not from the Base Fact table. The same is true even when attributes from Enterprise, Product, Study or Case Processing Site Presentation Folders are selected or the attributes Case Type, Case Seriousness, and Case Deletion Indicator.

Whereas, If we select attribute Date from the "Period" Presentation Folder (Period -> "Case Version - Version Receipt Date" -> Day) along with the Case Version Aggregates, the data will be selected from the Base fact table, which might take longer to retrieve the results of the request.

Table 6-3 Aggregate Case Version Fact - Conformed Dimensions

Retrospective Facts	Conformed Dimensions
Aggregate Case Version Fact - Version Receipt Month	Enterprise
Aggregate Case Version Fact - Version Receipt Month	Case Processing Site
Aggregate Case Version Fact - Version Receipt Month	Product
Aggregate Case Version Fact - Version Receipt Month	Study
Aggregate Case Version Fact - Version Receipt Month	Period - Version Receipt Date (at the hierarchical level of Month in the Period dimension)
Aggregate Case Version Fact - Version Receipt Month	Case Type
Aggregate Case Version Fact - Version Receipt Month	Case Seriousness
Aggregate Case Version Fact - Version Receipt Month	Case Deletion Indicator

W_PVA_WORKFLOW_ST_A (Case Workflow State Fact)

This is a dual purpose Fact table that serves as an Aggregate Fact (not explicitly called so due to its implementation in the Oracle Argus Analytics RPD), and, it is also used as a Fact table. What this means is that, on one hand it provides meaningful aggregate metrics on the operational effectiveness on routing of cases across workflow states and on the other hand it also serves as an aggregate fact on top of the Case Routing Fact.

In terms of Aggregate Fact usage, this is a compressed form of the Case Routing Fact table and is compressed to provide aggregate metrics at the level of Enterprise ID, Case ID, Version ID and Workflow State. In the RPD this Fact table is exposed as a Presentation Folder "Case Workflow State" and can be directly used with the dimensions as mentioned in the above table, listing conformed dimensions.

W_PVA_CASE_PROCESSING_MN_A

This is an Aggregate Fact table for the Base Fact W_PVA_CASE_F and contains the same aggregate measures like the Base Fact, but aggregated at the Initial Receipt Month level from the Period Dimension, along with the Enterprise, Product, Study, Case Processing Site, Case Type, Case Seriousness and Case Deletion Indicator dimensions. Whereas, in the Base Fact W_PVA_CASE_F, the granularity is at the Case ID level for all the measures which is in turn at the Initial Receipt Date level for each case.

Table 6-4 Aggregate Case Version Fact - Conformed Dimensions

Retrospective Facts	Conformed Dimensions
Aggregate Case Fact - Initial Receipt Month	Enterprise
Aggregate Case Fact - Initial Receipt Month	Case Processing Site
Aggregate Case Fact - Initial Receipt Month	Product
Aggregate Case Fact - Initial Receipt Month	Study
Aggregate Case Fact - Initial Receipt Month	Period - Initial Receipt Date (at the hierarchical level of Month in the Period dimension)
Aggregate Case Fact - Initial Receipt Month	Case Type
Aggregate Case Fact - Initial Receipt Month	Case Seriousness
Aggregate Case Fact - Initial Receipt Month	Case Deletion Indicator

W_PVA_REPORT_SUBMSN_MN_A

This is an Aggregate Fact table for the Base Fact W_PVA_CASE_RPT_SUBMSN_F and contains the same aggregate measures like the Base Fact, but aggregated at the Report Submission or MDN Month level from the Period Dimension, along with the Enterprise, Product, Study, Case Processing Site, Report Destination, Report Assigned User Group and Report Completion Type dimensions. Whereas, in the Base Fact W_PVA_CASE_RPT_SUBMSN_F, the granularity is at the Case ID, Version ID & Report ID level for all the measures which is in turn at the Report Submission Or MDN Date level for each case.

Table 6-5 Aggregate Case Version Fact - Conformed Dimensions

Retrospective Facts	Conformed Dimensions
Aggregate Report Submission Fact - Report Submission Or MDN Month	Enterprise
Aggregate Report Submission Fact - Report Submission Or MDN Month	Case Processing Site
Aggregate Report Submission Fact - Report Submission Or MDN Month	Product
Aggregate Report Submission Fact - Report Submission Or MDN Month	Study
Aggregate Report Submission Fact - Report Submission Or MDN Month	Expedited Submission Dimensions - Report Submission Or MDN Date (at the hierarchical level of Month in the Period dimension)
Aggregate Report Submission Fact - Report Submission Or MDN Month	Report Destination
Aggregate Report Submission Fact - Report Submission Or MDN Month	Report Assigned User Group
Aggregate Report Submission Fact - Report Submission Or MDN Month	Report Completion Type