Java code to list the objects used in a Webi 4.0 document

49 Replies

The current public BO BI 4.0 SDK does not include any features that allows to list objects used in a Webi 4.0 document or access variables. But this is still possible.

UPDATE: Please consider 4.1 RESTful Web Services SDK Developer Guides and API References http://scn.sap.com/docs/DOC-27465. It might be more convenient than hacking internal SDKs.

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)

Disclaimer: Use it on your own risk

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.sdk.core.Core;
import com.businessobjects.sdk.core.context.IContext;
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;
import com.sap.sl.dictionary.DictionaryExpression;
import com.sap.sl.dictionary.Variable;
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.reporting.ReportDictionaryHelper;

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

    IEnterpriseSession enterpriseSession = null;
    try {
      System.out.println("Connecting...");
      ISessionMgr sessionMgr = CrystalEnterprise.getSessionMgr();
      enterpriseSession = sessionMgr.logon(
          "Administrator", "", "localhost", "secEnterprise");

      // Do some magic
      String serializedSession = enterpriseSession.getSerializedSession();
      SessionContext sessionContext = Utils.getSessionContextManager()
          .matchSessionContext(Deployment.DHTML, serializedSession, true);
      Locale localLocale = Locale.getDefault();
      sessionContext.getLoginInfo().set(LoginKey.LOCALE, localLocale);
      sessionContext.logonWithSerializedSession();
      IContext context = sessionContext.getCoreContext();
      IDocumentInstanceManagementService documentInstanceManagementService =
          Core.getService(IDocumentInstanceManagementService.class);

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

      for (Object object : infoObjects) {
        IInfoObject infoObject = (IInfoObject) object;
        if (getInfoObjectPathAndTitle(infoObject).startsWith("/")) {
          System.out.println("REPORT: " + infoObject.getTitle());

          IDocumentInstance doc = documentInstanceManagementService
              .openDocument(context, infoObject.getID());

          List list = ReportDictionaryHelper
              .getDictionaryObjectsFlatList(context, doc);
          if (list.size() > 0) {
            System.out.println("OBJECTS:");
            for (DictionaryExpression expr:list) {
              System.out.println(expr.getName());
            }
          }

          List vars = ReportDictionaryHelper.getDocumentVariables(context,doc);
          if (vars.size() > 0) {
            System.out.println("VARIABLES:");
            for (Variable var:vars) {
              System.out.println(var.getName());
            }
          }

          documentInstanceManagementService.closeDocument(context, doc);
          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();
  }
}

Hack of Webi prompts

2 Replies

Prompts can be used to select a value from the list of values. What about selecting a field from a list of fields? Say you have FIELD1 and FIELD2 in SOMETABLE. You want to display FIELD1 or FIELD2 depending on the user answer. You can write a long select using CASE WHEN, but that is not interesting. Here is a dirty hack 🙂

Create an object with select:

@Prompt('Select Dimension ', 'N', {'FIELD1', 'FIELD2'}, mono, constrained, persistent,)

Select SOMETABLE in the property Tables.

‘N’ is used as prompt type. This means that the values in the LoV are supposed to be numeric, so BO will unquote the selected value generating SQL.

Let’s try this in a report. Pick the object, and check the generated SQL:

When you run report, you will get a prompt:

You will see ‘foo’ in the report (which is the value of the field FIELD1)

The generated SQL:

Command line InfoStore Query Builder with export to Excel

A free command line tool that runs query against CMS database and saves the result in Excel or CSV.

Options

-cms: BO server. Default is localhost
-username: BO user name. Default is Administrator
-password: User password. Default is blank
-auth: Authentification. Default is secEnterprise

-query: CMS query (the same as you run in Query Builder). See example.
-file: CMS query will be read from the file.
You should either specify -query: or -file: but not both.

-excel: Output excel file name. It will contain two spreadsheets: one for simple properties
and another for containers. See containers.
-csv: Output cvs file for simple properties. See containers.
-csv2: Output cvs file for containers properties. See containers.
If neither -excel: nor -csv: are specified, the simple properties will be printed to the standard output in CSV format.

Example

java -jar querybuilder.jar -query:"select si_name from ci_infoobjects"

Containers

There are simple properties of CMS objects e.g. SI_ID, SI_NAME, and containers – properties that have subproperties. For instance, the property SI_PROMPTS has subproperties SI_NUM_PROMPTS, SI_PROMPT1, SI_PROMPT2 etc. In turn SI_PROMPT1 has subproperties: SI_PROMPT, SI_PROMPT_TYPE, SI_OPTIONS etc. The containers are exported in the form: infoobject id, infoobject title, property path, property value.

Download

The tool has been desupported. Check Biclever CMS Query Builder.

InfoStore Query Builder to Check Relations

23 Replies

There is a nice tool in CMC that allows to check relations between objects. Something similar can be done in the query builder with some non documented features.

For instance, to find all reports that use a universe Finance, you can write:

SELECT *
FROM ci_infoobjects, ci_appobjects, ci_systemobjects
WHERE PARENTS("si_name='Webi-Universe'","si_name='Finance'")

PARENTS has two arguments. First is the relation type, second is the reference object.

If you want to find opposite – all universes used by a report, you can write

SELECT *
FROM ci_infoobjects, ci_appobjects, ci_systemobjects
WHERE CHILDREN("si_name='Webi-Universe'","si_name='Balance Sheet'")

What are the possible types of relations (first argument)? You can find the list querying CMS:

SELECT si_name FROM ci_systemobjects WHERE si_relation_table_name='RELATIONS'

Now you can also build such queries and export result to to Excel in InfoStore Query Builder:

Problem with OpenDocument inside IFrame in Internet Explorer

2 Replies

Symptoms

It is not possible to login to BusinessObjects using Internet Explorer when the report is embedded into another web page using IFRAME and OpenDocument link.

<iframe width=100% height=100% 
src="http://SERVERNAME:8080/OpenDocument/opendoc/openDocument.jsp?sType=wid&sDocName=Balance+Sheet">

You get the login screen but nothing happens when you try to login (you are staying on the same page). The OpenDocument link works when it is opened in the browser directly.

Cause

This happens because Internet Explorer gives lower level of trust to IFRAME pages. IE calls this “third-party” content. If the page inside the IFRAME doesn’t have a Privacy Policy, its cookies are blocked (which is indicated by the eye icon in status bar, when you click on it, it shows you a list of blocked URLs). The cookies are used to store BO session. As the session cannot be saved, the user cannot login.

Resolution (Tomcat)

1. Create folder w3c in

[Business Objects]\Tomcat55\webapps\

2. Create file p3p.xml in the folder with the following code:

<META>
  <POLICY-REFERENCES>
    <POLICY-REF about="/w3c/policy.xml">
      <INCLUDE>/</INCLUDE>
      <COOKIE-INCLUDE/>
    </POLICY-REF>
  </POLICY-REFERENCES>
</META>

Fun with NULL

How to Replace a Table with a Derived Table

6 Replies

This post describes the detailed steps to replace a database table with a derived table. Also it describes how Universe Documenter can help to achieve the perfect quality even for such complex modification of a universe.

You may need to replace a table with a derived table if your universe is affected by the self-outer join issue.

We will consider the following schema:

The task is to replace BO_CUSTOMER with a derived table to avoid self join in the universe database structure.

0) Make a backup of the universe and the reports that use the universe

Also export a copy of the universe to CMS. This will be required on the step 9 for verification.

1) Create the derived table that contains SQL with the self join SQL in WHERE

Create table BO_CUSTOMER_ with the following SQL:

SELECT * FROM BO_CUSTOMER WHERE ACCESSUSERNAME=@VARIABLE('BOUSER')

2) Switch all joins from the original table to the derived table.

Edit the join expression. Do not change tables in the combo-box, this may screw up the select.

The self join BOEMPLOYEE.ACCESSUSERNAME=@VARIABLE(‘BOUSER’) should be removed. You will be prompted that it is used in contexts. Ignore this message.

The contexts do not need to be updated.

3) Update objects expression and the expression of the predefined conditions

Select first class in the universe.
Hit Ctrl+F.
Enter BO_CUSTOMER in the field “Find what”
Enter BO_CUSTOMER_ in the field “Replace”
Select “SQL” in the Look Also In

Sometimes Replace All works fine, but it would be more preferable to change objects one by one.

4) If the table is a fact table and aggregate awareness is used in the universe, check if you need to update aggregate navigation.

Open aggregate navigation
Select the fact table
Select option “Incompatibles only”
Check objects and predefined conditions.
If the list “Associated Incompatible Objects” is empty, you do not need to do anything.
If the list is not empty, the same incompatibilities must be defined for the derived table.

(This is not needed in our example)

3) Check if you are lucky

Try deleting the original table. If the table was removed without prompts, you finished with the table. If you see the prompt that the table is used in object definitions, do not remove the table.

The table can be used in index awareness.
The table can also be used to force join with another table.

6) Modify the objects that use join forcing

Click right button on BO_CUSTOMER
In pop-up menu select View associated objects
If no objects highlighted in class structure, go to the next step
Go through all highlighted objects
The select of such objects do not contain reference to the table BO_CUSTOMER because we replaced all BO_CUSTOMER to BO_CUSTOMER_ on the step 3.
Click Tables and change BO_CUSTOMER to BO_CUSTOMER_ holding Ctrl button.

7) Update objects for which index awareness is defined.

There is no easy way to find the objects that has index awareness in Universe Designer. You need to go through all objects and check Keys in properties:

The easy way is to build the list of index awareness using Universe Documenter

8) Save, export

9) Verify

The modification described here is very complex. It is easy to make a mistake. Luckily it is possible to use the tool Universe Documenter to identify the mistakes.

To reduce the number of differences, import the copy of the original universe and change the name of the replaced table to BO_CUSTOMER_ and export the universe back to CMS.

Import the modified copy and the corrected universes in Universe Documenter, perform diff (How to diff universes using Universe Documenter) and check all highlighted differences. The two universes must be almost identical with an exception that the BO_CUSTOMER_ in the corrected universe is a derived table.

Problem of using self joins in combination with outer joins

Problem

(It is assumed that the parameter ANSI92 of the universe is set to Yes, the parameter SELFJOINS_IN_WHERE is either not set or is set to No.)

The table A is affected by the self/outer join issue if it has a self join and it is joined to a table B with the following outer join:

If a query use these two tables, the self join SQL will be put into the outer join SQL causing the generated SQL query to ignore the self join.

If the query contains at least one inner join of A with another table, the issue will not appear because the self join will be put into the inner join SQL, so the self join will be applied to the table A. Due to this, the issue appear rarely.

Example 1

The table structure contains two tables BOEMPLOYEE and EXVENDOR. BOEMPLOYEE has a field ACCESSUSERNAME which is used to implement access control to the table.

If the query uses these two tables, the self join will be put into the outer join SQL and therefore ignored:

SELECT
  BOEMPLOYEE.NAME,
  EXVENDOR.NAME
FROM EXVENDOR RIGHT OUTER JOIN BOEMPLOYEE
  ON (BOEMPLOYEE.VENDORNUMBER=EXVENDOR.VENDORNUMBER
      AND BOEMPLOYEE.ACCESSUSERNAME = @VARIABLE('BOUSER'))

Example 2

Here we have a table EXFINANCEENTRY that is joined with BOEMPLOYEE using inner join.

In this case, the self join the self join will be put both into the outer join SQL and inner join SQL. Because it is put in the inner join, the self join is applied to the BOEMPLOYEE and the issue will not appear:

SELECT
  EXFINANCEENTRY.TRANSACTIONNUMBER,
  BOEMPLOYEE.NAME,
  EXVENDOR.NAME
FROM
  EXVENDOR RIGHT OUTER JOIN BOEMPLOYEE
  ON (BOEMPLOYEE.VENDORNUMBER=EXVENDOR.VENDORNUMBER
      AND BOEMPLOYEE.ACCESSUSERNAME = @VARIABLE('BOUSER'))
  INNER JOIN EXFINANCEENTRY
  ON (EXFINANCEENTRY.EMPLOYEENUMBER=BOEMPLOYEE.EMPLOYEENUMBER
      AND BOEMPLOYEE.ACCESSUSERNAME = @VARIABLE('BOUSER'))

The issue in BO XI 3.1 FP3.7, FP3.8, FP4.2, FP5.2

In the mentioned FPs, the algorithm of generation of joins has changed: the self join is put only in one join SQL connected to the table. (This is error that tracked under the problem report id ADAPT01640966. SAP KB article 1743593 – Self join is ignored by SQL query generator in WebIntelligence Rich Client and Universe Designer in SAP BusinessObjects XI 3.1)

Example 3

The self join is ignored even though there is an inner join:

SELECT
  EXFINANCEENTRY.TRANSACTIONNUMBER,
  BOEMPLOYEE.NAME,
  EXVENDOR.NAME
FROM
  EXVENDOR RIGHT OUTER JOIN BOEMPLOYEE
  ON (BOEMPLOYEE.VENDORNUMBER=EXVENDOR.VENDORNUMBER
      AND BOEMPLOYEE.ACCESSUSERNAME = @VARIABLE('BOUSER'))
  INNER JOIN EXFINANCEENTRY
  ON (EXFINANCEENTRY.EMPLOYEENUMBER=BOEMPLOYEE.EMPLOYEENUMBER)

Solution

Either you using the mentioned FPs or not, the described situation is a design flaw of the universes. In such situations, the self join should be embedded into the derived table.

For the above examples, the derived table can be following

SELECT * FROM  BOEMPLOYEE WHERE ACCESSUSERNAME = @VARIABLE('BOUSER'))

The table BOEMPLOYEE should be replaced with this derived table.

Example of sub-query in Webi

How to Change Expression in Report Cells using BO Java SDK

6 Replies

Problem

“I have nearly 500 reports, each report having logo on the right up corner in all tabs, in a cell having formula “boimg://logo.bmp” and read as image URL. Now i have come up with change in logo format. Now the same formula is to be changed to “boimg://logo.jpg””.

Code

private static int replaceExpression(DocumentInstance widoc, String from, String to) {
   ReportDictionary reportDictionary = widoc.getDictionary();
   ArrayList nodes = getListOfTreeNodes(widoc.getStructure(), false);
   int n = 0;
   for (TreeNode node : nodes) {
      if (node instanceof ReportCell) {
         ReportCell reportCell = (ReportCell)node;
         ReportExpression reportExpression = reportCell.getExpr();
         if (reportExpression instanceof FormulaExpression) {
            FormulaExpression formulaExpression = (FormulaExpression)reportExpression;
            if (formulaExpression.getValue().compareTo(from) == 0){
               FormulaExpression newFormulaExpression = reportDictionary.createFormula(to);
               reportCell.setExpr(newFormulaExpression);
               n += 1;
            }
         }
      }
   }
   widoc.applyFormat();
   return n;
}

public static ArrayList getListOfTreeNodes(TreeNode root, boolean onlyLeaves) {
   ArrayList nodes = new ArrayList();
   treeNodeTraversal(root, nodes, onlyLeaves);
   return nodes;
}

private static void treeNodeTraversal(TreeNode node, ArrayList nodes, boolean onlyLeaves) {
   if (!onlyLeaves || node.isLeaf()) {
      nodes.add(node);
   }
   for (int i = 0; i < node.getChildCount(); ++i) {
      treeNodeTraversal(node.getChildAt(i), nodes, onlyLeaves);
   }
}

Source

https://bukhantsov.org/tools/ReplaceInReportCell.java

bukhantsov.org

my technical notes

Java code to list the objects used in a Webi 4.0 document

Hack of Webi prompts

Command line InfoStore Query Builder with export to Excel

Options

Example

Containers

Download

InfoStore Query Builder to Check Relations

Problem with OpenDocument inside IFrame in Internet Explorer

Symptoms

Cause

Resolution (Tomcat)

Fun with NULL

How to Replace a Table with a Derived Table

Problem of using self joins in combination with outer joins

Problem

Example 1

Example 2

The issue in BO XI 3.1 FP3.7, FP3.8, FP4.2, FP5.2

Example 3

Solution

See also

Example of sub-query in Webi

How to Change Expression in Report Cells using BO Java SDK

Problem

Code

Source