Analysis editor: Advanced tab

Use this tab of the "Analysis editor" to examine or edit the XML code and logical SQL statement that is generated for an analysis. This tab is for use only by advanced users and developers who have the appropriate privileges. See "Examining the Logical SQL Statements for Analyses" for additional important information on working with this tab.

For more information, see:

• "Examining the Logical SQL Statements for Analyses"

• Appendix D, "Logical SQL Reference"

Components

Referencing the Results

Use this area at the top of the tab to access a link that is identified with the name of the analysis to display individual pages that contain links for refreshing, modifying, and viewing the results of the analysis. You can bookmark these pages for use as links from other web pages and portals.

This link executes for the analysis as it is currently saved. If you update the analysis later, then the link is also changed for those updates. The link is not visible if you have not yet saved an analysis.

Analysis XML

Use this area to view and modify the XML code for an analysis.

Use the following components in this area:

• XML Code Box — Use this box to view the XML code for the analysis and to enter any changes to that code.

• Apply XML — Click this button to commit the XML code modifications that you made to the catalog.

• Partial Update — Use this box to specify whether to update the SQL statement and HTML code only for views or for the entire analysis in response to an event.

  • Select either Default or Affected Views to update the SQL statement and HTML code only for the views that have been affected by an event such as drilling or sorting. These settings ensure better performance because the SQL statement and HTML code is generated only for affected views, which prevents the entire analysis from redrawing.

  • Select Entire Report to rebuild and redraw the entire analysis.

  
  

  Note: To apply this setting, you must also click the Apply SQL button at the bottom of the tab.

  
  

• Bypass Oracle BI Presentation Services Cache — Use this box to specify whether you want the query to use the Oracle BI Presentation Services Cache. In general, you should avoid selecting this box as disabling the cache has potential performance degradation issues.

  
  

  Note: To apply this setting, you must also click the Apply SQL button at the bottom of the tab.

  
  

SQL Issued

Use this read-only box to view the SQL statement that is sent to the Oracle BI Server when the analysis is executed.

New Analysis

Click this button to display the "Analysis Simple SQL Statement dialog," in which you create an analysis using the SQL statement from the current analysis as the starting point. Any hierarchical columns, selection steps, groups, or formatting that are specified in the current analysis are removed before the dialog is displayed. You can edit the SQL statement in this dialog.

Advanced SQL Clauses

Use this area to modify the SQL statement for an analysis. You can include additional SQL clauses and change the subject area or the FROM clause for certain analyses. All but one of these features are disabled for analyses that contain hierarchical columns, member selections, or groups. Only the ability to specify a prefix is enabled.

Use the following components in this area:

• Issue an Explicit Distinct — Select this box to send a SELECT DISTINCT SQL statement to the Oracle BI Server. If you deselect this box, then a simple SELECT command is sent to the Oracle BI Server. The SELECT DISTINCT command ensures that you obtain only unique results, with no duplicate values.

• Show Total value for all measures on unrelated dimensions — Select this check box to include the ENABLE_DIMENSIONALITY variable at the beginning of the SQL statement:

  SET VARIABLE ENABLE_DIMENSIONALITY = 1; 
  

  Setting ENABLE_DIMENSIONALITY may allow cross subject area queries to return non-null results for certain measure columns that would otherwise return null values when ENABLE_DIMENSIONALITY is not set. The measure columns may also contain duplicated values when ENABLE_DIMENISONALITY is set.

  
  

  Note: The Show Total value for all measures on unrelated dimensions check box applies to Oracle BI EE 11.1.1.7.16 and later versions, and might not be available in earlier versions. For more information about Oracle BI EE 11.1.1.7.16, see "New Features for 11.1.1.7.16."

  
  

• FROM — Enter either the name of the subject area to use for the analysis or enter a complex FROM clause.

• GROUP BY — Enter a comma-delimited list of columns from the analysis to use in a GROUP BY command.

• Prefix — Enter any SQL statements to be run before the SQL statement for the analysis runs. For example, you can modify the values of session variables.

• Postfix — Enter any additional SQL clauses that you would like to include with the analysis, including a full SQL statement. For example, you can affect the SQL statement for the analysis by entering JOIN, UNION, or ORDER BY commands. If you include an ORDER BY command, then you override any ORDER BY commands from the SQL statement for the analysis.

  In general, only enter statements for Postfix if you are very familiar with SQL code.

Apply SQL

Click this button to apply the:

• Partial Update box setting

• Bypass Oracle BI Presentation Services Cache box setting

• SQL statement modifications that you made

Query Performance

Use this area to set the following components:

• When view prompts are used — Use this area to specify, for views that have page edge prompts based on hierarchical columns, whether to improve query performance:

  • Exclude prompt values that would return no results — Select this option to exclude, in the page edge list, values that return no data.

    This option is recommended in most cases.

  • Include prompt values that would return no results, if includes at least one hierarchical column — Select this option to include, in the page edge list, values that return no data.

    This option is not valid for OLAP sources. It is only valid for relational physical sources.

• Query sharing — Use this area to specify whether to allow the Oracle BI Presentation Services query cache to be shared with other users:

  • Do not share query — Select this option to not allow the Presentation Services query cache to be shared with other users.

  • Share query with multiple users (may improve performance after initial run) — Select this option to allow the Presentation Services query cache to be shared with other users.

    Selecting this option improves query performance for users who access this analysis subsequent to the first user. Note that length of time that the Presentation Services query cache is available to other users depends on the cache settings at your organization. For more information on Presentation Services cache settings, see "Managing the Oracle BI Presentation Services Cache Settings" in System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

    This option is recommended for an analysis that does not contain any specific user data and that will be accessed by many users.

    You can view information about the cache for an analysis using the "Administration: Manage Sessions page."

    This option impacts usage tracking. For more information on usage tracking, see "Managing Usage Tracking" in System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

Apply

Use this button to apply the settings for the options in the Query Performance area.

Notes

• You can also enter SQL statements using the "Administration: Issue SQL page."

• To combine an analysis with a similar analysis from the same subject area or another subject area, where you can perform Set operations such as UNION or INTERSECT on the results, click the Combine results based on union, intersection, and difference operations toolbar button on the "Selected Columns pane." For more information, see "Combining Columns Using Set Operations."