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 |
Parent topic: Performance Considerations