Join and Synchronization in Web Intelligence

Posted by & filed under Web Intelligence.

When objects are merged in a Webi document there are two possible types of the merge operation: join and synchronization. The type depends on the objects selected in a data block. To achieve expectable result, it is important to understand the difference between these operations.

Let's consider examples of each operation.

Join

The document use two data providers. The first data provider (Reservation) has Reservation Year, Future Revenue.

The second data provider (Sales) has Invoice Year, Sales Revenue.

The list of objects in the document contains these 4 objects:

Here are the corresponding tables in the report:

We cannot use both dimensions in the same data block. If we pick one, the other is grayed. The reason for that is that BO does not know how to combine the dimensions from different data providers. It is not obvious for BO that they have the same nature (year).

To tell BO that these dimensions have the same business meaning, and that we want to merge them in the report, we need to merge the dimensions.

We merge the Invoice Year and Reservation Year.

Now we can pick Year, Sales Revenue, Future Revenue in the same table.

The values of Year are the union of Invoice Year and Reservation Year values.

Note that if we pick only measure from one data provider, all years will be displayed.

Synchronization

Let's add Sales Person to the second data provider:

The table has not changed:

If we add Sales Person to the table, there will be significant change than just adding one dimension:

  • Now the merged dimension is showing only values of Invoice Year.
  • Sales Revenue is also split by Sales Person.
  • Future Revenue is also split by Sales Person. Instead the total Future Revenue for 2012 is shown for each Sales Person for 2012.

(Note that blank values here correspond to nulls. Null is just a normal value.)

The data in the two data providers has different granularity. It is not possible to calculate Future Revenue for a specific sales person as this granularity is not available in the Reservation data provider. Therefore BO shows total for 2012 for each Sales Person in 2012.

The years for which only Reservations exist are also not displayed. It is not possible to show them as the granularity of Reservations is lower than granularity of Sales. They cannot be added with blank in the column for Sales Person. Here the blank value means that there is no sales person. But sales person is just not relevant for Reservations. (OK, in general the sales person is relevant to reservations, but according to the data providers, he is not)

This demonstrates two types of operations: Join and Synchronization. The operation depends on the dimensions selected to the data block, it does not depend on measures and details.

Two data providers are joined if only merged dimensions are selected to the data block. All combinations of dimensions from the first data provider are united with all combinations of dimensions from the second data provider. The measures are projected in the context of the merged dimensions. The details are looked up by the matching dimensions.

Two data providers are synchronized if beside the merged dimensions, one or more non-merged dimension is used in the data block. All non-merged dimensions should be from the same data provider. Let's call this data provider the leading data provider. All combinations of dimensions from the leading data provider are displayed in the data block. The measures from the secondary data provider are projected in context of dimensions from the secondary data provider and looked up by the matching merged dimensions.

(There can be more than two data providers involved)

Query

We considered two separate data providers. The operations join and synchronization are possible for a single data provider on a query level.

When we pick objects from the different contexts they are synchronized or joined on the query level.

For instance Future Revenue and Sales Revenue come from different contexts. Service is a common dimension (here, the common dimension is analogue of the merged dimension):

Therefore the queries are joined (note "Join" in the root):

The data provider with a joined queries acts as a data provider with a single query.

When beside the common dimensions, there is a dimension that does not belongs to all contexts, the queries are synchronized.

The synchronized queries are not actually synchronized when creating the data provider. The operation will be chosen by the dimensions selected in the data block. If only the common objects are used, the queries will be joined.

For instance, here you see result of the join operation, even though the synchronization operation was displayed for queries:

 

How to print SQL queries of Webi 4.0 document using Java

Posted by & filed under Business Objects, SDK.

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

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

Posted by & filed under Business Objects, SDK.

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

Posted by & filed under Web Intelligence.

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

Posted by & filed under Tools.

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

Posted by & filed under Tools.

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

Posted by & filed under Administration.

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

Posted by & filed under Oracle.

I think this is interesting:

SELECT * FROM dual WHERE (1 BETWEEN 0 AND NULL)     -- FALSE
SELECT * FROM dual WHERE (0 BETWEEN 1 AND NULL)     -- FALSE
SELECT * FROM dual WHERE NOT (1 BETWEEN 0 AND NULL) -- FALSE
SELECT * FROM dual WHERE NOT (0 BETWEEN 1 AND NULL) -- TRUE

This has a good explanation however I am still embarrassed :)

How to Replace a Table with a Derived Table

Posted by & filed under Tools, Universe Designer.

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

Posted by & filed under Universe Designer.

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.

See also

Use of Outer Joins in Combination with Self Restricting Joins
How to Replace a Table with a Derived Table