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);
         }
      }
   }
}

5 thoughts on “How to print SQL queries of Webi 4.0 document using Java

  1. Verakso

    Is this also possible in BO 3.1?
    Or is it specific to 4.0 and Java?

    I was just wondering if this could be done in eg. c#

    Like

    Reply
  2. Adithya

    HI Bukhant

    Our requirement is in BO 4.0. We use BICS connections to create our reports. The BW query names are gonna change. So I want to update multiple BO reports to the new BEx query. Basically repoiniting reports to new queries. Is this possible in 4.0??

    Like

    Reply
  3. Bernd Martin

    Hi Bukhant,

    a friend is asking me if I could support him with Java, but I could not get the adv_ivcdzview.jar, webi_client_toolkit.jar and may some other files for your example. Could you please send them to me?
    I have not installed Webi, we only want to see if this could be a solution for his customer
    to listen reports in a user-friendly way.
    Thank you very much.
    Have a nice day.

    Bernd Martin

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s