Tool for updating non-Administrator group

BO 4.x has a possibility to customize the user interface of Web Intelligence for specific groups of users. For instance, you may want to hide "Design Mode" interface elements for report viewers who are not supposed to edit reports.

others-group-03

This can be done in Central Configuration Management in Groups:

others-group-01

others-group

The problem is that the change will be applied for all users who belong to the group. If a report designer belongs to the group, it will not be able to see the disabled interface elements even if the user belongs Administrators group.

A solution is to create a group of non administrators group (say Others) and apply user interface customization for this group.

However it is a tedious work to maintain two groups Administrators and Others. I have created a tool which allows to maintain the Others group. If a user is not Administrators group (e.g. new user) or if it is removed from Administrators group, the tool will add the user to Others group. If a user is added to Administrators group, it will be deleted from Others groups.

Here is the link to archive: othersgroup-1.0.zip

The archive includes executable and source code:
others-group-02

The batch file has the following parameters you may need to change.

The location of BusienssObjects:

set BO=C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0

BO Administrator credentials:

set BOADMIN=Administrator
set BOPASS=
set BOSERV=localhost
set BOAUTH=secEnterprise

The groups names:

set EVERYONE=Everyone
set ADMIN=Administrators
set OTHERS=Others

The connection driver "xxx" is unknown. (SLS 16000)

com.sap.sl.sdk.authoring.internal.AuthoringException: The connection driver "SAP NetWeaver BW 7.x/SAP BICS Client" is unknown. (SLS 16000)

The documentation says to use "SAP NetWeaver BW 7.x", but you have to use "SAP BW 7.x" instead. I.e.

OlapConnection connection = connectionFactory.createOlapConnection(
  connectionName, "SAP BW 7.x", "SAP BICS Client");

Other OLAP drivers just in case:

Microsoft Analysis Services 2005 ODBO
Microsoft Analysis Services 2008 ODBO
Microsoft Analysis Services 2012 ODBO
Microsoft Analysis Services 2005 XMLA
Microsoft Analysis Services 2008 XMLA
Microsoft Analysis Services 2012 XMLA
Financial Consolidation 10 for Microsoft Platform XMLA
Planning and Consolidation 7.5 for Microsoft Platform XMLA
Planning and Consolidation 10 for Microsoft Platform XMLA
Profitability and Cost Management 10 XMLA
Strategy Management 10 XMLA
Oracle OLAP XMLA
Hyperion Essbase 11.x XMLA
Hyperion Essbase 11.x Essbase API for Analysis
Teradata XMLA
SAP BW 7.x SAP BICS Client
SAP HANA SAP Hana Client

Semantic Layer SDK – check java bitness, path variable and connectivity property

There are three things that are important when you build a program that uses Semantic Layer SDK.

  • You need to include sl_sdk.jar to the class path.
  • You need to use 32 bit java
  • The system property businessobjects.connectivity.directory should be set to the connection server folder
    -Dbusinessobjects.connectivity.directory="C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer"
  • The system variable PATH should be set to win32_x86
    C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86

It is quite easy to see if sl_sdk.jar is included, your program will fail quickly with NoClassDefFoundError. It might be not so obvious with the other three. The following code may help you to check them:

System.out.println("java = " + System.getProperty("java.version") + ", " 
  + System.getProperty("sun.arch.data.model") + " bit");
System.out.println("path = " + System.getenv("PATH"));
System.out.println("businessobjects.connectivity.directory = "
  + System.getProperty("businessobjects.connectivity.directory"));

List of all relations in BusinessObjects

Below is the list of all relations in BusinessObjects 4.1 SP7. The relations are useful if you need to find dependent objects in Query Builder. A relations have a type and a name. Here is how to use it.

The relation Webi-Universe is Parent-Child, so Webi is Parent and Universe is Child. If you want to find all Webi documents (parents) that use a specific universe (child), you need to use query:

SELECT SI_NAME
FROM CI_INFOOBJECTS, CI_APPOBJECTS, CI_SYSTEMOBJECTS
WHERE PARENTS("SI_NAME='Webi-Universe'","SI_NAME='{the name of the universe}'")

If you want to find all universes (children) that are used by a specific Webi document (parent), you need to use the query:

SELECT SI_NAME
FROM CI_INFOOBJECTS, CI_APPOBJECTS, CI_SYSTEMOBJECTS
WHERE CHILDREN("SI_NAME='Webi-Universe'","SI_NAME='{the name of the webi document}'")

 

TYPE SI_NAME
Child-Parent ActionSet-Action
Child-Parent ActionUsage-Action
Parent-Child AlertNotification-User
Child-Parent Application-Action
Parent-Child Application-DependencyRule
Parent-Child Application-Relationship
Child-Parent Category-Document
Parent-Child CommonConnection-AOPresentation
Parent-Child CommonConnection-AOWorkbook
Parent-Child CommonConnection-Document
Parent-Child CommonConnection-MDAnalysis
Parent-Child CommonConnection-pQuery
Child-Parent Container-ActionUsage
Parent-Child CrystalReport-BusinessView
Child-Parent CustomRole-Object
Parent-Child DataConnection-Universe
Child-Parent DefaultObject-Application
Child-Parent Document.discussion
Parent-Child Document-BIVariant
Parent-Child Document-DSL.Universe
Parent-Child Document-Excel
Parent-Child Document-Follower
Parent-Child Document-PersistenceQuery
Parent-Child Document-UnFollower
Child-Parent DSL.DataSecurityProfile-SecuredConnections
Child-Parent DSL.SecurityProfile-Principal
Child-Parent DSL.Universe-BusinessSecurityOptions
Child-Parent DSL.Universe-BusinessSecurityProfile
Child-Parent DSL.Universe-DataSecurityOptions
Child-Parent DSL.Universe-DataSecurityProfile
Child-Parent DSL.Universe-SecuredConnections
Parent-Child EnterpriseData-Flash
Parent-Child EnterpriseNode-MON.ManagedEntityStatus
Parent-Child EnterpriseNode-Server
Parent-Child Event-AlertNotification
Parent-Child Event-Principal
Parent-Child Event-Principal-Exclusion
Parent-Child EventSource-AlertNotification
Parent-Child EventSource-Event
Parent-Child Folder-MON.ManagedEntityStatus
Child-Parent InfoObject-Shortcuts
Child-Parent Install-EnterpriseNode
Parent-Child Install-Service
Parent-Child Install-ServiceContainer
Parent-Child Manifest-ReplicableObject
Parent-Child MON.ManagedEntityStatus-MON.MonitoringEvent
Parent-Child MON.ManagedEntityStatus-MON.Subscription
Parent-Child PlatformSearchContainer-PlatformSearchObject
Child-Parent Plugin-Action
Parent-Child Profile-Principal
Parent-Child PublicationArtifacts-SourceDoc
Parent-Child Publication-ExcludedPrincipal
Parent-Child Publication-FC
Parent-Child Publication-Principal
Parent-Child Publication-PrincipalReRun
Parent-Child Publication-Profile
Parent-Child PublicationScopeBatch-Artifact
Parent-Child PublicationScopeBatchDoc-Artifact
Parent-Child QAAWS-WEBI
Child-Parent ReferringDoc-SharedDoc
Parent-Child RelationalConnection-CrystalReport
Parent-Child Relationship-Application
Child-Parent RemoteCluster-ConflictingObjects
Parent-Child ServerGroup-MON.ManagedEntityStatus
Child-Parent ServerGroup-Server
Parent-Child Server-MON.ManagedEntityStatus
Parent-Child ServiceCategory-MON.ManagedEntityStatus
Parent-Child ServiceCategory-Service
Parent-Child ServiceContainer-Server
Parent-Child Service-Server
Parent-Child Service-ServiceContainer
Parent-Child Service-ServiceDep
Parent-Child ServiceUsedBy-Service
Child-Parent Tenant-ServerGroup
Child-Parent Universe(Core)-Universe
Child-Parent Universe-UserGroup
Parent-Child UpgradeManagementTool-LogicalGroup
Parent-Child UpgradeManagementTool-Plugin
Parent-Child User-BIVariant
Parent-Child User-CommonConnection
Child-Parent User-Favorites
Parent-Child User-FavouriteMEStatus
Parent-Child UserGroup-Folder
Child-Parent UserGroup-User
Child-Parent User-Inbox
Parent-Child User-MON.ManagedEntityStatus
Parent-Child User-MON.Subscription
Child-Parent User-PersonalCategory
Parent-Child User-VisualDiffComparator
Parent-Child VISI.Lums-ManagedConnectionRel
Parent-Child VISI.Lums-UniverseRel
Parent-Child WebiFHSQL-DataConnection
Parent-Child Webi-Universe
Parent-Child XL.XcelsiusEnterprise-DataSource
Parent-Child XL.XcelsiusEnterprise-XL.Query

HTTP Status 500 Bad tag encountered: 78

HTTP Status 500 – com.wedgetail.idm.sso.ProtocolException: com.wedgetail.idm.spnego.server.SpnegoException: com.dstc.security.util.asn1.Asn1Exception: Bad tag encountered: 78

Bad-tag-encountered-78

See SAP Note 1799488.

Single sign on does not work by default for LaunchPad on the server machine. This behavior is by design as the servers are unable to pass Vintela filter.

Use the following URL to access LaunchPad on the server: http://localhost:8080/BOE/BI/logonNoSso.jsp

You may want to modify the LaunchPad link properties in Start Menu to use logonNoSso.jsp.

How to Change Banner in LaunchPad

This is a quick and dirty solution to change the banner in LaunchPad.

banner-launchpad

Do not forget to backup the files before any change. See SAP note 1659690 for a better solution.

  • Stop Tomcat.
  • Replace the file C:\Program Files (x86)\SAP BusinessObjects\tomcat\webapps\BOE\WEB-INF\eclipse\plugins\webpath.InfoView\web\images\SAP_logo_reg.png with desired logo (preferred dimensions: 63 x 31 px).
  • Delete all folders under C:\Program Files (x86)\SAP BusinessObjects\tomcat\work\Catalina\localhost
  • Start Tomcat.
  • The users will need to delete temporary files in browser to see the change. In IE, Tools > Internet Options > General, Delete under Browsing history:

ie-browsing-history

Getting Started with BusinessObjects Semantic Layer SDK

This post describes how to get started with BO Semantic Layer SDK in Eclipse. It explains the steps to setup a project and provides an example of the code that imports, modifies and exports a UNX universe. You will also find instructions how to run JAR from command line.

BO Client Tools and SL SDK

You need to have SAP BusinessObjects BI platform 4.x Client Tools and it should include Semantic Layer SDK.

The Semantic Layer SDK is not included in default installation. You can check if you have it and enable it in Control Panel > Programs > Programs and Features > SAP BusinessObjects BI platform 4.x Client Tools > Uninstall/change > Modify.

Make sure that you have "Semantic Layer Java SDK"

unxdemo-07

This post assumes that the client tools are installed in the default location, namely in

C:\Program Files (x86)\SAP BusinessObjects

If your setup is different, update the paths accordingly.

Setup New Project with SL SDK

Create a new Project in Eclipse.

Right click on the project and select Build Path > Add External Archives.

unxdemo-01

Add Semantic layer SDK library sl_sdk.jar. It is located in:

C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\SL SDK\java

unxdemo-02

JRE

Make sure that the project is using 32 bit Java Runtime Environment. The best is to use Java from the BusinessObjects folder.

Right click on JRE System Library and select Properties.

unxdemo-10

Click Installed JREs and add new JRE by clicking on Add > Standard VM, and then providing the following path:

C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\jre

unxdemo-09

Select the created JRE:

unxdemo-08

The Program

Add class Program to the project and paste the following code:

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.sap.sl.sdk.authoring.cms.CmsResourceService;
import com.sap.sl.sdk.authoring.businesslayer.*;
import com.sap.sl.sdk.authoring.datafoundation.*;
import com.sap.sl.sdk.authoring.local.LocalResourceService;
import com.sap.sl.sdk.framework.SlContext;
import com.sap.sl.sdk.framework.cms.CmsSessionService;

public class Program {

  public static void main(String[] args) throws SDKException {
    String username = "Administrator";
    String password = "";
    String server = "localhost";
    String auth = "secEnterprise";
    String cmspath = "/Universes/webi universes";
    String unxname = "eFashion.unx";
    String tempFolder = ".";

    SlContext context = SlContext.create();
    IEnterpriseSession enterpriseSession = null;
    try {
      System.out.println("Connecting");
      ISessionMgr sessionMgr = CrystalEnterprise.getSessionMgr();
      enterpriseSession = sessionMgr.logon(username, password, server, auth);
      CmsSessionService cmsSessionService =
        context.getService(CmsSessionService.class);
      cmsSessionService.setSession(enterpriseSession);
      CmsResourceService cmsService = 
        context.getService(CmsResourceService.class);
      LocalResourceService localResourceService =
        context.getService(LocalResourceService.class);
      BusinessLayerFactory businessLayerFactory = 
        context.getService(BusinessLayerFactory.class);
      DataFoundationFactory dataFoundationFactory =
        context.getService(DataFoundationFactory.class);
             
      System.out.println("Importing universe"); 
      String blxPath = 
        cmsService.retrieveUniverse(cmspath + "/" + unxname, tempFolder, true);
      
      System.out.println("Loading universe");
      RelationalBusinessLayer businessLayer =
        (RelationalBusinessLayer) localResourceService.load(blxPath);
      String dfxPath = businessLayer.getDataFoundationPath();
      DataFoundation dataFoundation =
        (DataFoundation) localResourceService.load(dfxPath);

      System.out.println("Modifying data foundation");
      String sql = "select count(1) as Cnt from Shop_facts";
      dataFoundationFactory.createDerivedTable("New_table", sql, dataFoundation);
      localResourceService.save(dataFoundation, dfxPath, true);

      System.out.println("Reloading data foundation");
      businessLayer =
        (RelationalBusinessLayer) localResourceService.load(blxPath);
      
      System.out.println("Modifying business layer");
      RootFolder rootFolder = businessLayer.getRootFolder();
      Measure measure = 
        businessLayerFactory.createBlItem(Measure.class, "New Measure", rootFolder);
      RelationalBinding binding = (RelationalBinding)measure.getBinding();
      binding.setSelect("New_table.Cnt");
      localResourceService.save(businessLayer, blxPath, true);

      System.out.println("Exporting universe");
      cmsService.publish(blxPath, cmspath, true);
    } finally {
      if (context != null) {
        context.close();
      }
      if (enterpriseSession != null) {
        enterpriseSession.logoff();
      }
      System.out.println("Finished");
    }
  }
}

Run Configuration

Run the program. It will fail but a Run Configuration will be created.

Open the created run configuration in Run > Run Configurations

Add the following VM arguments

-Dbusinessobjects.connectivity.directory="C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer"

unxdemo-04

Add PATH variable with the value:

C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86

unxdemo-05

Running the Code

The code uses eFashion.unx which should be located in webi universes. So you need to convert eFashion universe to eFashion.unx in Information Design Tool.

unxdemo-05

Now we are ready to run the program.

Click the button Run. The tool should execute without error and add table New_table and a measure New measure:

unxdemo-06

Running the Code from Command Line

Export the program to Executable JAR file (e.g. unxdemo.jar) from File > Export.

Create a batch file with the following script:

SET BO=C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0
SET PATH=%BO%\win32_x86
SET JAVA=%BO%\win32_x86\jre
SET CD=%BO%\dataAccess\connectionServer
SET CP=unxdemo.jar;%BO%\SL SDK\java\sl_sdk.jar;%BO%\java\lib\*
"%JAVA%\bin\java.exe" -Dbusinessobjects.connectivity.directory="%CD%" -cp "%CP%" Program
PAUSE

Run the batch:

unxdemo-11

Refresh button text in Web Intelligence reading mode

The Refresh button in 4.1 looks quite anonymous. It is possible to get the "Refresh" text back.

refreshbutton-0

You can edit file

C:\Program Files (x86)\SAP BusinessObjects\tomcat\webapps\BOE\WEB-INF\eclipse\plugins\webpath.AnalyticalReporting\web\webiDHTML\viewer\language\en\scripts\UIDefinition.js

and change the line

,{actionId:"refreshDPMenu", text:"}

to

,{actionId:"refreshDPMenu"}

The button has actually text but it is overriden with empty string.

You will also need to update this file in tomcat work folder.

C:\BO\SAPBusinessObjects\tomcat\work\Catalina\localhost\
BOE\eclipse\plugins\webpath.AnalyticalReporting\web\webiDHTML\viewer\language\en\scripts

(The users might need to clear temporary files in order to see the change)

refreshbutton-1

PS. This is probably not the right way to do this but it seems to be the most straightforward.

Unx Documenter

There is a free tool for extracting UNX universe metadata available on http://biclever.com/software/unx-universe-documenter/. It also allows comparing universes.

unxdoc-1

unxdoc-2

unxdoc-3

Calculation contexts issue with Sum, Where and If

Make a report based on eFashion universe with [Year], [City], and [Sales revenue].

Untitled3

Now we will try to calculate total Sales revenue for Austin in 2004 but in two steps.

Where and If

Define variables as:

[Sales Austin] =[Sales revenue] Where([City]="Austin")

[Sales Austin 2004] =Sum(If [Year]="2004" Then [Sales Austin])

The expected value for [Sales Austin 2004] is 561123.

But

1) The value calculated in the Sum line for Sales Austin for some reason is 2805617 (which is 5 x Sales Austin 2004 = 5 x 561123).

2) The value calculated in the table on the right is 13498366 (Which is the total of all values in the column Sales Austin i.e. 5 x (561123 + 1003071 + 1135479) = 13498366).

Untitled

This was a result for BO3.1 SP7. In BO4.1 SP7, the result is slightly different, we will get #MULTIVALUE in the right table.

We can explicitly add [Year] to calculation context.

[Sales Austin 2004] =Sum((If [Year]="2004" Then [Sales Austin])ForEach([Year]))

This will fix the value in the right table, but not in the Sum line.

If we explicitly add [Year] and remove [City], then the result will become correct.

[Sales Austin 2004] =Sum((If [Year]="2004" Then [Sales Austin])ForEach([Year])ForAll([City]))

or

[Sales Austin 2004] =Sum((If [Year]="2004" Then [Sales Austin])In([Year]))

So the calculation context in the variable [Sales Austin 2004] is derived incorrectly. Since we have [City] in the expression, it should have been included by BO. The variable [Sales Austin] has Where([City]="Austin"), which removes [City] from context, so [City] should have been excluded. But for some reason, we have to specify this manually.

If

[Sales Austin] =If [City]="Austin" Then [Sales revenue]

[Sales Austin 2004] =Sum(If [Year]="2004" Then [Sales Austin])

This works fine unless we add sum to [Sales Austin]:

[Sales Austin] =Sum(If [City]="Austin" Then [Sales revenue])

[Sales Austin 2004] =Sum(If [Year]="2004" Then [Sales Austin])

In this case, the right table will show wrong value (13498366 in BO3 and #MULTIVALUE in BO4). We need to add [Year] to calculation context in [Sales Austin 2004].

Where

[Sales Austin] =[Sales revenue] Where ([City]="Austin")

[Sales Austin 2004] =[Sales Austin] Where ([Year]="2004")

The result will be correct if we use variable value [Sales Austin 2004] in Sum line. If we add Sum, the result in Sum line will become wrong 2805617.

You might also find it interesting that the following two formulas return different result

=Sum([Sales revenue] Where ([Year]="2004" And [City]="Austin"))

=Sum([Sales revenue] Where ([Year]="2004")Where([City]="Austin"))

The first is correct but the second will return 1683370. (I am not sure what this number is.)