Copy exe as text via clipboard to a remote desktop

Sometimes you are not allowed to transfer files between machines but you can transfer text via clipboard. A little hack how to transfer a file using a standard Windows utility certutil.

Create a text file encoding the file

certutil -encode file.exe file.txt

Open and copy text

Create a new file on the remote desktop and paste the text

Decode the file into exe

certutil -decode file.txt file.exe

"Relationship would not be a tree after update so bailing"

Sometimes a connection cannot be promoted by Promotion Management with the error:

Resolution Status=Copied, Dependency Status=All the required dependencies are included., Commit Status=Commit attempted and failed., Promotion Status=Failure : Relationship would not be a tree after update so bailing.

It may help to promote connection using BiarEngine. This can display a better message explicitly saying object's ID causing the dependency issue, for instance:

2

Weird issue in Webi

A query is based on eFashion universe and returns two objects: measure [Promotion Cost USD] and dimension [Duration].

The variable [Cost] is defined as

=If [Duration]>0 Then [Promotion Cost USD]/[Duration]

The following formula in report’s cell returns #MULTIVALUE

=Sum([Cost])

If I just replace the variable with the expression, it will return correct value

=Sum(If [Duration]>0 Then [Promotion Cost USD]/[Duration])

The following formula also returns correct value

=Sum([Cost] ForEach([Duration]))

context issue

97 Things Every Software Architect Should Know – Notes

Notes from the book "97 Things Every Software Architect Should Know"

Read more…

Example of an Indexed View in SQL Server

CREATE VIEW C_FINANCEENTRYPV WITH SCHEMABINDING AS
  SELECT
     ACCOUNTNUMBER
    ,ENTRYDATE
    ,Sum(IsNull(FINANCEENTRY.DEBITBASE,0))  DEBITBASE
    ,Sum(IsNull(FINANCEENTRY.CREDITBASE,0)) CREDITBASE
    ,Count_Big(*) CNT
  FROM
    dbo.FINANCEENTRY FINANCEENTRY
GROUP BY 
    ACCOUNTNUMBER,
    ENTRYDATE
GO
CREATE UNIQUE CLUSTERED INDEX C_FINANCEENTRYPV_01 ON C_FINANCEENTRYPV (
  ACCOUNTNUMBER,
  ENTRYDATE
)
GO
SELECT * FROM C_FINANCEENTRYPV WITH (NOEXPAND)

How to Use Reposcan to Repair CMS and FRS

This post describes the steps to repair CMS database and Filestore using SAP BusinessObjects reposcan.exe.

Note that this may delete some items from CMS or Filestore if the dependencies are not found. Make sure that you have made necessary backups before applying reposcan.

You can run the tool without repairing option (or with -repair off). This will scan the environment and make the report of inconsistencies which will be saved to the output folder.

1. Create folder C:\Reposcan
2. Create file reposcan.ini in the folder

-dbdriver sqlanywheredatabasesubsystem 
-connect "UID=dba;PWD=1-Password;DSN=BI4_CMS_DSN"
-dbkey "[[qoNXDD3yCYuHLyTpqyj9Cw]]"
-inputfrsdir "C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\FileStore\Input"
-outputfrsdir "C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\FileStore\Output"
-outputdir "C:\Reposcan"
-repair on

For Oracle, the first two line should be:

-dbdriver oracledatabasesubsystem 
-connect "UID=<user>;PWD=<password>;DSN=<dsn>"

For SQL Server:

-dbdriver sqlserverdatabasesubsystem 
-connect "UID=<user>;PWD=<password>;DSN=<dsn>"

The value for dbkey can be found in Central Configuration Manager under CMS Cluster Key Configuration:
sia
Here you can also see DNS under CMS System Database Configuration.

3. Create file reposcan.bat

"C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win64_x64\reposcan.exe" -optionsfile "C:\Reposcan\reposcan.ini"
pause

4. Run the file reposcan.bat
reposcan

You can get the list of all commands running the reposcan without options:

"C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win64_x64\reposcan.exe"

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

How to Allow Remote TCP/IP Connections for SQL Server

  1. SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for SQLEXPRESS > TCP/IP
    TCP/IP Enabled
    IPAll > TCP Dynamic Ports is blank
    IPAll > TCP Port = 1433
  2. SQL Server Management Studio > Server Properties > Security
    Server authentication = SQL Server and Windows Authentication mode
  3. Services > SQL Server Browser
    Enable and start
  4. Windows Firewall with Advanced Security > Inbound Rules > New Rule… > Port
    Allow the connection for the port 1433

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

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