| Oracle® Argus Analytics User's Guide Release 8.1 E76207-01 |
|
 Previous |
 Next |
This chapter lists the performance considerations that should be adhered to, while creating requests or dashboards.
This chapter comprises the following topics:
A conformed dimension is a set of data attributes that have been physically implemented in multiple database tables using the same structure, attributes, values, definitions and concepts in each implementation. It is always a good practice to use Conformed Dimension columns along with the Facts when we create any Requests. By using the conformed dimensions, OBIEE forms select statements that choose the correct Fact table to display accurate data.
Non-conformed dimensions imply the existence of logical and/or physical inconsistencies that should be avoided. Using Non-conformed Dimensions with the Fact leads to unwanted Table selection in the Query formation which results the Report to display null values (in case of Aggregates values) and might degrade the Performance of the Request or Report.
For example: If user selects some of the aggregate measures from Case Version Fact along with the Routing user from User Dimension and Workflow State from State Dimension, the Report does not display the aggregate measures values as both the selected Dimension attributes are Non-conformed Dimensions for the Case Version Fact.
The following table contains the details of the conformed Dimensions for the Facts we have used in Oracle Argus Analytics.
Table 6-1 Conformed Dimensions
| Retrospective | |
|---|---|
|
Facts |
Conformed Dimensions |
|
Case Fact |
Enterprise |
|
Case Processing Site |
|
|
Product |
|
|
Study |
|
|
Period - Clock Start Date |
|
|
Period - Initial Receipt Date |
|
|
Case Version Fact |
Enterprise |
|
Case Processing Site |
|
|
Product |
|
|
Study |
|
|
Period - Clock Start Date |
|
|
Period - Initial Receipt Date |
|
|
Period - Version Receipt Date |
|
|
Case Routing Fact |
Enterprise |
|
Case Processing Site |
|
|
Product |
|
|
Study |
|
|
Period - Clock Start Date |
|
|
Period - Initial Receipt Date |
|
|
Period - Version Receipt Date |
|
|
Case Workflow State Fact |
Enterprise |
|
Case Processing Site |
|
|
Product |
|
|
Study |
|
|
Period - Clock Start Date |
|
|
Period - Initial Receipt Date |
|
|
Period - Version Receipt Date |
|
|
Case Work User Fact |
Enterprise |
|
Case Processing Site |
|
|
Product |
|
|
Study |
|
|
Period - Clock Start Date |
|
|
Period - Initial Receipt Date |
|
|
Period - Version Receipt Date |
|
|
Case Work Fact |
Enterprise |
|
Case Processing Site |
|
|
Product |
|
|
Study |
|
|
Period - Clock Start Date |
|
|
Period - Initial Receipt Date |
|
|
Period - Version Receipt Date |
|
|
Expedited Submission |
Enterprise |
|
Case Processing Site |
|
|
Product |
|
|
Study |
|
|
Period - Clock Start Date |
|
|
Period - Initial Receipt Date |
|
|
Period - Version Receipt Date |
|
|
Expedited Submission Dimensions - Report Generation Date |
|
|
Expedited Submission Dimensions - Report Submission Or MDN Date |
|
|
Expedited Submission Dimensions - License |
|
|
Expedited Submission Dimensions - Report Destination |
|
|
Report Routing |
Enterprise |
|
Case Processing Site |
|
|
Product |
|
|
Study |
|
|
Current |
|
|
Facts |
Conformed Dimensions |
|
Pending Cases |
Enterprise |
|
Case Processing Site |
|
|
Product |
|
|
Study |
|
|
Period - Initial Receipt Date |
|
|
Pending Reports |
Enterprise |
|
Case Processing Site |
|
|
Product |
|
|
Study |
|
|
Period - Initial Receipt Date |
|
|
Pending Report Dimensions - License |
|
|
Pending Report Dimensions - Report Destination |
|
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-2 Aggregate Case Version Fact - Conformed Dimensions
| Retrospective | |
|---|---|
|
Facts |
Conformed Dimensions |
|
Aggregate Case Version Fact - Version Receipt Month |
Enterprise |
|
Case Processing Site |
|
|
Product |
|
|
Study |
|
|
Period - Version Receipt Date (at the hierarchical level of Month in the Period dimension) |
|
|
Case Type |
|
|
Case Seriousness |
|
|
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-3 Aggregate Case Version Fact - Conformed Dimensions
| Retrospective | |
|---|---|
|
Facts |
Conformed Dimensions |
|
Aggregate Case Fact - Initial Receipt Month |
Enterprise |
|
Case Processing Site |
|
|
Product |
|
|
Study |
|
|
Period - Initial Receipt Date (at the hierarchical level of Month in the Period dimension) |
|
|
Case Type |
|
|
Case Seriousness |
|
|
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-4 Aggregate Case Version Fact - Conformed Dimensions
| Retrospective | |
|---|---|
|
Facts |
Conformed Dimensions |
|
Aggregate Report Submission Fact - Report Submission Or MDN Month |
Enterprise |
|
Case Processing Site |
|
|
Product |
|
|
Study |
|
|
Expedited Submission Dimensions - Report Submission Or MDN Date (at the hierarchical level of Month in the Period dimension) |
|
|
Report Destination |
|
|
Report Assigned User Group |
|
|
Report Completion Type |
When we create Prompts and Filters, it is better to avoid using functions to get or modify the Prompt or Filter values such as Substring, IfNull, Cast, ValueOf and so on. Inclusion of such prompts in the dashboard pages or as filters in Requests might degrade the performance of the Requests/Dashboards associated with it. It is advisable to use the direct column values for creating Prompts and Filters.
It is a best practice to enforce the Dashboard Prompts to have default values associated with it, so that the Dashboard Requests (which have filters on the same columns) can benefit from the default values, as it will result in analyzing the request on a much smaller subset of the Fact/Aggregate Fact data.
This chapter contains a sub-chapter called Mandatory Prompts. It comprises the following sub-sections:
The prompts that are present in the dashboard can be made mandatory. By making a prompt mandatory, the particular report can be forced to use the aggregate facts, thereby improving performance significantly.
The following example lists the steps required to make the Product Group prompt as a mandatory prompt:
Login to the OBIEE application as a user with administrative rights and access to edit the dashboards. Typically, this would be the administrator.
Navigate to the folder in the catalog where prompts are saved. Example: Catalog > Shared folders > Retrospective > Case Processing History.
Click the Edit link to make the change. Example: CP History Page Prompts.
Select the field which is to be made mandatory and click the Edit icon (present on the top right of the opened screen), such as the Product Group field selected below.
An Edit Prompt dialog appears, with options to customize the prompt. To make the field mandatory, check the Require user input checkbox and click OK.
You can verify the change by checking the Required column of the field (Product Group below). Mandatory fields have a tick mark displayed against them. In the Preview window, an asterisk (*) appears before the name of the mandatory field.
Save the prompt by selecting the Save icon (highlighted below), present on the top right side of the screen.
The column configured for mandatory input is now reflected in the page-level prompt of the respective reports.
|
Note: The Apply button is disabled. This button will get enabled only after values for all the mandatory prompts present in a page have been entered. The text * Indicates mandatory field is displayed next to the mandatory prompts present in a page. |
Prompts can be set with default preferred values, based on the logged-in user. This would be very useful when the same value is supplied to a prompt by a user most of the time. Further, it can also be helpful by setting up a default value for the mandatory prompts.
The following example lists the steps required to set up the default value for the mandatory parameter called Product Group:
Navigate to the report where the default prompt value is to be saved. Go to the prompt, select the value, and click Apply.
To save the current selection, click the Page Option icon present on the top right corner of the opened report. Select Save Current Customization from the drop-down menu.
In the Save Current Customization dialog that appears, enter the customization name in the Name field and select the Make this my default for this page checkbox. Click OK to save your customized changes.
Cache is a component that improves performance by transparently storing data such that future requests for the same criteria can be served much faster. Like other application cache is not virtual memory in OBIEE. In OBIEE cache will be stored as files on the Hard Disk of the OBIEE Server in the form of files. By Default caching is enabled in the OBIEE server configuration.
For time-consuming Dashboard Pages, we can use the concept of cache seeding in OBIEE, this can be configured as a scheduled job that will ensure that the data is cached and readily available as and when the user runs the dashboard.
