如何通过Java获取OBIEE 12C分析物理查询（无需Usage Tracking日志）

Absolutely, there are reliable ways to fetch the physical SQL corresponding to OBIEE 12c analyses or dashboards via a Java program—no Usage Tracking logs required. I’ve used a couple of approaches in production, so let’s break them down:

方法1：通过OBIEE JDBC驱动直接获取 #

This is one of the simplest methods, especially if you can get hold of the logical SQL for your analysis. OBIEE’s JDBC driver exposes a method to retrieve the generated physical SQL after executing a logical query.

步骤： #

• First, grab the OBIEE JDBC driver (biee_jdbc.jar) from your OBIEE server’s $ORACLE_HOME/bifoundation/server/lib directory and add it to your Java project’s classpath.
• Use the driver to establish a connection to the BI Server, then execute the logical SQL and extract the physical SQL.

示例代码： #

import java.sql.Connection;
import java.sql.DriverManager;
import oracle.bi.jdbc.OracleBIStatement;

public class OBIEEPhysicalSQLFetcher {
    public static void main(String[] args) {
        try {
            // Load the OBIEE JDBC driver
            Class.forName("oracle.bi.jdbc.AnaJdbcDriver");
            
            // Connection details (adjust server, port, credentials as needed)
            String jdbcUrl = "jdbc:oraclebi://your-obiee-server:9703/";
            String username = "your-obiee-username";
            String password = "your-obiee-password";
            
            // Establish connection
            Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
            
            // Cast to OracleBIStatement to access OBIEE-specific methods
            OracleBIStatement obiStmt = (OracleBIStatement) conn.createStatement();
            
            // Replace this with the logical SQL from your target analysis
            String logicalSQL = "SELECT \"Sales\"\"Revenue\" FROM \"Regional Sales Analysis\"";
            
            // Execute the logical query
            obiStmt.executeQuery(logicalSQL);
            
            // Retrieve the generated physical SQL
            String physicalSQL = obiStmt.getPhysicalSQL();
            System.out.println("Generated Physical SQL:\n" + physicalSQL);
            
            // Cleanup resources
            obiStmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

方法2：使用OBIEE 12C REST API（Java调用） #

OBIEE 12c provides a comprehensive REST API that lets you interact with analyses, dashboards, and their execution results. You can use this to execute an analysis and fetch its physical SQL directly.

步骤： #

1. Authenticate to get a session token using the /api/auth/signin endpoint.
2. Execute the analysis via the /api/catalog/objects/{catalog-path}/execute endpoint, specifying that you want to include physical SQL in the response.
3. Parse the JSON response to extract the physical SQL string.

示例Java代码（用HttpClient）： #

import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.client.HttpClient;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.entity.StringEntity;
import org.apache.http.impl.client.HttpClients;
import org.apache.http.util.EntityUtils;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;

public class OBIEERestPhysicalSQL {
    public static void main(String[] args) {
        try {
            HttpClient client = HttpClients.createDefault();
            
            // Step 1: Get authentication token
            HttpPost signinPost = new HttpPost("http://your-obiee-server:9502/api/auth/signin");
            StringEntity signinPayload = new StringEntity("{\"username\":\"your-username\",\"password\":\"your-password\"}");
            signinPost.setEntity(signinPayload);
            signinPost.setHeader("Content-Type", "application/json");
            
            HttpResponse signinResp = client.execute(signinPost);
            String token = JsonParser.parseString(EntityUtils.toString(signinResp.getEntity()))
                                    .getAsJsonObject().get("token").getAsString();
            
            // Step 2: Execute the analysis and fetch physical SQL
            String analysisPath = "/shared/Sales/Regional Sales Analysis";
            HttpPost executePost = new HttpPost("http://your-obiee-server:9502/api/catalog/objects" + analysisPath.replace("/", "%2F") + "/execute");
            executePost.setHeader("Authorization", "Bearer " + token);
            executePost.setHeader("Content-Type", "application/json");
            
            // Request payload to include physical SQL
            JsonObject payload = new JsonObject();
            payload.addProperty("outputFormat", "json");
            payload.addProperty("includePhysicalSQL", true);
            executePost.setEntity(new StringEntity(payload.toString()));
            
            HttpResponse executeResp = client.execute(executePost);
            JsonObject result = JsonParser.parseString(EntityUtils.toString(executeResp.getEntity()))
                                          .getAsJsonObject();
            
            String physicalSQL = result.get("physicalSQL").getAsString();
            System.out.println("Physical SQL from REST API:\n" + physicalSQL);
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

方法3：OBIEE Java SDK（Catalog与执行API） #

If you need deeper integration with the OBIEE catalog (e.g., to dynamically fetch analyses from dashboards), you can use the OBIEE Java SDK. This requires working with catalog objects and report execution APIs.

关键点： #

• Add SDK JARs from $ORACLE_HOME/bifoundation/javaclient/lib to your classpath.
• Use BIServiceProvider to connect to Presentation Services.
• Retrieve the analysis object from the catalog, extract its logical SQL, then execute it to get the physical SQL (similar to the JDBC method, but via SDK classes).

注意事项： #

• For dashboards, you’ll need to parse the dashboard’s XML layout to find all embedded analysis references, then process each one individually.
• Ensure your service account has sufficient permissions to access the target catalog objects and execute queries.

内容的提问来源于stack exchange，提问作者Rahul Patel