Examining the Logical SQL Statements for Analyses

You use the "Analysis editor: Advanced tab" to examine the XML code and logical SQL statement that is generated for an analysis and optionally create a new analysis based on that SQL statement. Generally you need not use the features of this tab, because the vast majority of functionality for working with analyses is provided through the user interface. The following list provides examples of situations in which you might want to examine SQL statements:

• You can use the Prefix section to override any user variable or session variable that has been marked as available for updating. You can specify multiple variables to update with one line of code.

  To override variables, use the following syntax:

  SET VARIABLE var-name=value

  For example, you can set the variables that are shown in the following code line:

  SET VARIABLE "My Variable"=4, DISABLE_CACTHE_HIT=1, LOGLEVEL = 2;

  For information on variables, see "Where Can I Reference Variables?"

• You can temporarily change the logging level for an analysis for troubleshooting, even when logging is turned off for analyses. In the Prefix section of the Advanced tab, you can specify the following:

  SET VARIABLE LOGLEVEL=4;

  This specification runs the analysis at level 4, which facilitates advanced troubleshooting. The default level is 0, which indicates no logging. You can view the logs using the View Log link on the Administration: Manage Sessions page.

• You can enter your own logical SQL statements. Entering your own statements eliminates many features of Oracle BI EE (such as hierarchical columns, selection steps, groups, and formatting), but enables you to use functions such as SET operations.

• You can create a new analysis using the SQL statements from an existing analysis as your starting point.

Before working with the Advanced tab, keep the following important points in mind:

• This procedure is only for advanced users and developers that have the appropriate responsibilities to access the Advanced tab. It is assumed that you understand advanced SQL statements, have expertise working with the Oracle BI Server metadata, are familiar with the SQL information in Appendix D, "Logical SQL Reference", and understand the content and structure of the underlying data sources.

• This tab provides the Advanced SQL Clauses area, which enables you to add clauses to the SQL statements and other similarly advanced features. These features, except for the ability to specify a prefix, are disabled for analyses that contain hierarchical columns, member selections, or groups.

• The links at the top of the tab allow you to download analysis results into Microsoft Excel. You might be unable to download analysis results based on the security features that have been configured at your organization. For information, contact your administrator.

  Note: You can see the logical SQL statement that an analysis is using by inserting a logical SQL view. You can also enter SQL statements using the "Administration: Issue SQL page".