How to print SQL queries of Webi 4.0 document using Java

The current public BO BI 4.0 SDK does not include functionality that allows to print SQL queries of a Webi 4.0 document, but this is still possible.

UPDATE: BO BI 4.1 SDK contains RESTful web services for Webi. It allows you to do the below without hacks.

Disclaimer: Use it on your own risk.

The necessary libraries:

  • C:\Program Files (x86)\SAP BusinessObjects\Tomcat6\webapps\BOE\WEB-INF\eclipse\plugins\webpath.AnalyticalReporting\web\WEB-INF\lib\adv_ivcdzview.jar
  • C:\Program Files (x86)\SAP BusinessObjects\Tomcat6\webapps\BOE\WEB-INF\eclipse\plugins\webpath.AnalyticalReporting\web\WEB-INF\lib\webi_client_toolkit.jar
  • C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\java\lib\*
    (without subfolders)

The program has two classes: Program and Processor. Program is a main class that iterates through infoobjects of Webi type. Processor is printing SQL of the Webi documents. The most interesting part is Processor which uses BI 4.0 functionality for accessing data providers.

Main (Program.java)

import com.crystaldecisions.sdk.exception.SDKException;
import com.crystaldecisions.sdk.framework.CrystalEnterprise;
import com.crystaldecisions.sdk.framework.IEnterpriseSession;
import com.crystaldecisions.sdk.framework.ISessionMgr;
import com.crystaldecisions.sdk.occa.infostore.IInfoObject;
import com.crystaldecisions.sdk.occa.infostore.IInfoObjects;
import com.crystaldecisions.sdk.occa.infostore.IInfoStore;

public class Program {
   public static void main(String[] args) throws Exception {

      IEnterpriseSession enterpriseSession = null;
      try {
         // Establish connection
         System.out.println("Connecting...");
         ISessionMgr sessionMgr = CrystalEnterprise.getSessionMgr();
         enterpriseSession = sessionMgr.logon(
               "Administrator", "", "localhost", "secEnterprise");
         IInfoStore infoStore = (IInfoStore) enterpriseSession.getService("InfoStore");

         // Initialize processor (see below)
         Processor processor = new Processor(enterpriseSession);

         // Get list of Webi documents
         String cmsQuery = "select SI_NAME, SI_ID from CI_INFOOBJECTS "
               + "where SI_KIND = 'Webi' and SI_INSTANCE=0";
         IInfoObjects infoObjects = (IInfoObjects) infoStore.query(cmsQuery);

         // Process all Webi documents from a folder (root here)
         for (Object object : infoObjects) {
            IInfoObject infoObject = (IInfoObject) object;
            String path = getInfoObjectPathAndTitle(infoObject); 
            if (path.startsWith("/")) {
               System.out.println(path);
               processor.process(infoObject);
               System.out.println();
            }
         }
      } catch (SDKException ex) {
         ex.printStackTrace();
      } finally {
         if (enterpriseSession != null)
            enterpriseSession.logoff();
      }
      System.out.println("Finished!");
   }

   public static String getInfoObjectPath(IInfoObject infoObject) throws SDKException {
      String path = "";
      while (infoObject.getParentID() != 0) {
         infoObject = infoObject.getParent();
         path = "/" + infoObject.getTitle() + path;
      }
      return path;
   }

   public static String getInfoObjectPathAndTitle(IInfoObject infoObject) throws SDKException {
      return getInfoObjectPath(infoObject) + "/" + infoObject.getTitle();
   }
}

Printing (Processor.java)

import java.util.List;
import java.util.Locale;

import com.businessobjects.adv_ivcdzview.Utils;
import com.businessobjects.rebean.wi.model.engine.IDocumentInstance;
import com.businessobjects.rebean.wi.services.IDocumentInstanceManagementService;
import com.businessobjects.rebean.wi.services.IDocumentInstanceService;
import com.businessobjects.sdk.core.Core;
import com.businessobjects.sdk.core.context.IContext;
import com.crystaldecisions.sdk.exception.SDKException;
import com.crystaldecisions.sdk.framework.IEnterpriseSession;
import com.crystaldecisions.sdk.occa.infostore.IInfoObject;
import com.sap.sl.dataprovider.DataProvider;
import com.sap.sl.dataprovider.service.DataProviderService;
import com.sap.sl.queryexecutionplan.NativeQueryNode;
import com.sap.sl.queryexecutionplan.OperatorNode;
import com.sap.sl.queryexecutionplan.QueryExecutionPlan;
import com.sap.sl.queryexecutionplan.QueryExecutionPlanNode;
import com.sap.sl.sdk.workspace.service.WorkspaceContextualService;
import com.sap.sl.workspace.Workspace;
import com.sap.sl.workspace.service.WorkspaceService;
import com.sap.webi.client.toolkit.Deployment;
import com.sap.webi.client.toolkit.LoginKey;
import com.sap.webi.client.toolkit.SessionContext;
import com.sap.webi.client.toolkit.services.DSLServicesHelper;
import com.sap.webi.client.toolkit.services.ServicesHelper;

public class Processor {

   SessionContext sessionContext;
   Locale localLocale;
   IContext context;
   IDocumentInstanceManagementService dimService;
   IDocumentInstanceService diService;
   DataProviderService dpService;
   WorkspaceService wsService;

   public Processor(IEnterpriseSession enterpriseSession) throws SDKException {
      sessionContext = Utils.getSessionContextManager()
            .matchSessionContext(Deployment.DHTML, 
                  enterpriseSession.getSerializedSession(), true);

      localLocale = Locale.getDefault();
      sessionContext.getLoginInfo().set(LoginKey.LOCALE, localLocale);
      sessionContext.logonWithSerializedSession();

      context = sessionContext.getCoreContext();         
      dimService = Core.getService(IDocumentInstanceManagementService.class);
      diService = ServicesHelper.getDocumentInstanceService();

      dpService = DSLServicesHelper.getDataProviderService(context);
      wsService =  Core.getService(WorkspaceContextualService.class, context);
   }

   public void process(IInfoObject infoObject) {
      IDocumentInstance doc = dimService.openDocument(context, infoObject.getID());               
      Workspace workspace = diService.getWorkspace(context, doc);            
      List<DataProvider> listDataProvider = wsService.getDataProviders(workspace); 
      for (DataProvider provider : listDataProvider) {
         QueryExecutionPlan plan = dpService.getQueryExecutionPlan(provider, true);
         print(plan.getQueryExecPlanTree());
      }
      dimService.closeDocument(context, doc);
   }

   private static void print(QueryExecutionPlanNode planNode) {
      if (planNode instanceof NativeQueryNode) {
         NativeQueryNode queryNode = (NativeQueryNode)planNode;
         System.out.println(queryNode.getNativeQueryString());         
      } else if (planNode instanceof OperatorNode) {
         OperatorNode operatorNode = (OperatorNode)planNode;      
         for (QueryExecutionPlanNode node : operatorNode.getChildren()) {
            print(node);
         }
      }
   }
}