Examining the Logical SQL Statements for Analyses

This procedure is an advanced step in the process for constructing an analysis. For information, see "What Is the Process for Constructing 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 an 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".

  See Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for information on logging levels.

• 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 an 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.

To edit the XML code or examine the SQL statement that is generated for an analysis:

1. Click the "Analysis editor: Advanced tab".

2. Use the links that correspond to the analysis name in the Referencing the Results area at the top of the tab to:

   • Display pages that contain links for working with the results of the analysis.

   • Download a file for working with the results of the analysis in Excel. For information, see "Integrating an Analysis with Microsoft Excel's Internet Query Feature".

3. Use the fields in the Analysis XML area to view and modify the XML code, and click Apply XML.

   If you modify the XML code, then you affect the analysis as it is saved in the Oracle BI Presentation Catalog.

   To apply the settings for the Partial Update and the Bypass Oracle BI Presentation Services Cache boxes, you must also click the Apply SQL button at the bottom of the tab.

4. Use the read-only box in the SQL Issued area to examine the SQL statement that is sent to the Oracle BI Server when the analysis is executed.

   If you want to create an analysis using the SQL statement from the current analysis as the starting point, then click the New Analysis button. Any hierarchical columns, selection steps, groups, or formatting that are specified in the current analysis are removed.

5. Depending on the content of the analysis, use the available fields in the Advanced SQL Clauses area to change the subject area, add GROUP BY or HAVING clauses, and specify DISTINCT processing and a prefix.

6. Click Apply SQL to apply your changes.

   
   

   Note: Use care when clicking the Apply SQL button. When you do, Oracle BI EE creates a new analysis based on the SQL statement that you have added or modified. Therefore, you lose all views, formatting, and so on that you had previously created for the analysis. The XML code is also modified for the new analysis.