Author Archives: dmytro

Image with Hyperlink in Webi report

We want to add hyperlink to an image in Webi document.

Here I am using an image with name UserLogo1.png that has size 165 x 25.

Copy your image to the server to the folder:

C:\BusinessObjects\BusinessObjects Enterprise 12.0\images

Create a cell and set properties to the following.

Text: <a href=’http://google.com‘ style=’width:165px; height:25px;’ target=’_blank’> </a>

Width: 165

Height: 25

Read cell content as: Hyperlink

Background image: boimg://UserLogo1.png

image hyperlink in webi

(It does not work in PDF and Excel)

SAP BO SL SDK 4.1 Interface requested not found : csLIB

I have struggled quite a lot with the error “Interface requested not found : csLIB” when trying to open a semantic layer using SAP BO SL SDK 4.1. It was quite a complex issue so I will summarize it here in case someone has a similar problem.

Here I am using a local universe Test with SQL Server connection.

The code is simple.

import com.sap.sl.sdk.authoring.businesslayer.RelationalBusinessLayer;
import com.sap.sl.sdk.authoring.local.LocalResourceService;
import com.sap.sl.sdk.framework.SlContext;

public class Test {
  public static void main(String[] args) {
    SlContext context = SlContext.create();
    String businessLayerPath = ".\\Test.blx";
    LocalResourceService service = context.getService(LocalResourceService.class);
    RelationalBusinessLayer businessLayer = 
                        (RelationalBusinessLayer) service.load(businessLayerPath);
    service.close(businessLayer);
    context.close();
    System.out.println("OK");
  }
}

Batch for compilation and execution

@echo off
set JAVA_HOME=C:/Program Files (x86)/Java/jdk1.6.0_45
set JAVA="%JAVA_HOME%/bin/java"
set JAVAC="%JAVA_HOME%/bin/javac"

set BO=C:/SAP BusinessObjects/SAP BusinessObjects Enterprise XI 4.0
set CS=%BO%/dataAccess/connectionServer
set CP=%BO%/SL SDK/java/sl_sdk.jar;%BO%/java/lib/*

set PATH=%BO%/win32_x86

%JAVAC% -classpath "%CP%" Test.java
%JAVA% -Dbusinessobjects.connectivity.directory="%CS%" -classpath "%CP%" Test
pause

Everything was done according to SL SDK documentation but I still got the error:

Exception in thread “main” com.sap.tools.commons.exception.NestedException: Interface requested not found : csLIB
Caused by: com.sap.connectivity.cs.core.CSError: Interface requested not found : csLIB
Caused by: java.lang.UnsatisfiedLinkError: C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\cs_jni.dll: The specified procedure could not be found

I started investigating DLL loading process using Process Monitor and found that the library cs_jni.dll depends on the library icuin30.dll from win32_x86. However I had an older version in the folder C:\Windows\SysWOW64\. And since the system directories are checked before going through directories in the PATH variables, a wrong version was picked.

I have overwritten the icu??30.dll libraries in SysWOW64 with the libraries from win32_x86 and the code started to work.

(The “icu” stands for “International Components for Unicode”.)

I do not know the impact of the change to other application, use it at your own risk. If you are going to try it, make backup of the existing files.

Format date in Batch yyyyMMdd

The usual approach to build a timestamp for a log files in a batch is to use standard Windows utilities date and time. However the format of the output depends on locale and it is almost not possible to make the script which runs on any machine. A solution might be to create a small java tool.

Source file (datetime.java):

import java.text.SimpleDateFormat;
import java.util.Calendar;
public class datetime {
  public static void main(String[] args) {
    System.out.println(new SimpleDateFormat("yyyyMMdd_HHmmss")
          .format(Calendar.getInstance().getTime()));
  }
}

Compilation (you need JDK for this):

javac datatime.java

The code will be compiled into datatime.class.

Use in a batch file:

java datetime>logdate.txt
set /p STAMP=<logdate.txt
set LOG=C:\logs\%STAMP%.log
call Main.bat > %LOG%

Find MenuID and RoleID corresponding to ComponentID in Maconomy Portal

Component/Role/Menu

SELECT
  COMPONENTID
, ROLEID
, MENUID
FROM
  MENUITEM
WHERE
  COMPONENTID LIKE '%::BPM::%'

DFME:

SELECT DISTINCT
  COMPONENT.COMPONENTID
, COMPONENT.LABEL COMPONENT_LABEL
, MENU.NAME MENU_NAME
, USERROLE.NAME USERROLE_NAME
, FIELDATTRIBUTE.ATTRIBUTEVALUE
FROM MENUITEM 
INNER JOIN COMPONENT
ON MENUITEM.COMPONENTID = COMPONENT.COMPONENTID
INNER JOIN MENU
ON MENUITEM.MENUID = MENU.MENUID
INNER JOIN USERROLE
ON USERROLE.ROLEID = MENUITEM.ROLEID
LEFT JOIN FIELDATTRIBUTE
ON COMPONENT.COMPONENTID = FIELDATTRIBUTE.COMPONENTID
AND UPPER(FIELDATTRIBUTE.FIELDID) = 'REPORTID'
AND FIELDATTRIBUTE.ATTRIBUTENAME='label'
WHERE COMPONENT.COMPONENTID LIKE '%::BPM::%'

SELECT DISTINCT
  COMPONENT.COMPONENTID
, COMPONENT.LABEL COMPONENT_LABEL
, FIELDATTRIBUTE.ATTRIBUTEVALUE
FROM COMPONENT
LEFT JOIN FIELDATTRIBUTE
ON COMPONENT.COMPONENTID = FIELDATTRIBUTE.COMPONENTID
AND UPPER(FIELDATTRIBUTE.FIELDID) = 'ID'
AND FIELDATTRIBUTE.ATTRIBUTENAME='label'
WHERE COMPONENT.COMPONENTID LIKE '%::BPM::%'

BPM MScript components assigned to at least one role

SELECT
  COMPONENTID
, MSCRIPTFILENAME
FROM MSCRIPTCOMPONENT 
WHERE MSCRIPTFILENAME LIKE '%/BPM/%'
AND COMPONENTID IN (SELECT COMPONENTID FROM MENUITEM)

Archiving Pentaho log table in SQL Server

Pentaho Data Integration displays only the last 50 log entries but anytime you open a transformation it has to read the whole log table. When the log table gets large, opening a transformation may take long time and PDI freezes for this time.

A solution is to archive old entries from the log table.

Create an archive table ETL_LOG_ARCHIVE with the structure identical to the structure of the log table ETL_LOG:

SELECT * INTO ETL_LOG_ARCHIVE FROM ETL_LOG WHERE 0=1

Move old rows from ETL_LOG to ETL_LOG_ARCHIVE:

DELETE FROM ETL_LOG
OUTPUT DELETED.* INTO ETL_LOG_ARCHIVE
WHERE ID_BATCH IN (
   SELECT ID_BATCH FROM 
   (
      SELECT 
         ID_BATCH, 
         ROW_NUMBER() OVER (
            PARTITION BY TRANSNAME 
            ORDER BY ID_BATCH DESC) RN 
         FROM ETL_LOG
   ) A
   WHERE RN > 50
) 
AND ID_BATCH NOT IN (
   SELECT MAX(ID_BATCH) 
   FROM ETL_LOG
   WHERE STATUS='end' 
   GROUP BY TRANSNAME
)

We want to keep the last 50 rows for each transformation. But also we need to keep the last successful execution for each transformation. Otherwise there might be a problem with incremental load if a transformation has been failing more than 50 times.

How to find running SQL queries – Oracle

Running queries with performance metrics

SELECT
  S.SQL_ID,
  S.MODULE, 
  S.SQL_TEXT, 
  U.USERNAME, 
  ROUND((S.DISK_READS/DECODE(S.EXECUTIONS,0,1, S.EXECUTIONS)),2) DISK_READS_PER_EXEC, 
  S.DISK_READS, 
  S.BUFFER_GETS, 
  S.PARSE_CALLS, 
  S.SORTS, 
  S.EXECUTIONS, 
  S.ROWS_PROCESSED, 
  100 - ROUND(100 *  S.DISK_READS/GREATEST(S.BUFFER_GETS,1),2) HIT_RATIO, 
  S.FIRST_LOAD_TIME, 
  S.SHARABLE_MEM, 
  S.PERSISTENT_MEM, 
  S.RUNTIME_MEM, 
  S.CPU_TIME, 
  S.ELAPSED_TIME
FROM
  SYS.V_$SQL S, 
  SYS.ALL_USERS U 
WHERE
  S.PARSING_USER_ID=U.USER_ID 
AND 
  UPPER(U.USERNAME) NOT IN ('SYS','SYSTEM') 
ORDER BY 
  5 DESC

SQL query in pieces

SELECT
  *
FROM
  SYS.V_$SQLTEXT
WHERE
  SQL_ID = '68du231hjcykn'
ORDER BY 
  SQL_ID, PIECE;

SQL query concatenated

SELECT 
  SQL_ID, LISTAGG(SQL_TEXT, '') WITHIN GROUP (ORDER BY PIECE)
FROM 
  SYS.V_$SQLTEXT
WHERE
  SQL_ID = '68du231hjcykn'
GROUP BY 
  SQL_ID;

Bind variables

SELECT 
  * 
FROM
  V$SQL_BIND_CAPTURE B 
WHERE
  SQL_ID = '68du231hjcykn';

Solution: Сontent from the website listed below is being blocked by the internet explorer enhanced security configuration

Configure IE ESC (Internet Explorer’s Enhanced Security Configuration)

Disable for Administrators

(And do not forget to restart IE)

Couldn’t find entry ‘BusinessObjects.xml’

Failed to open the BIAR file. Reason: com.businessobjects.sdk.biar.om.internal.iw.ObjectManagerIWException:Input/Output exception occurred: ‘Couldn’t find entry ‘BusinessObjects.xml’.

Clipboard01

Import Wizard could not handle files larger than 500Mb. Use multiple BIARs.

Merging and filters in BO XI 3.1 SP6

Let’s define two similar queries for 2004 and 2005. The query for 2004 looks like:

The queries are merged by Service:

Now we want to filter table based on a measure from one of the queries.

Let’s define variable v_Filter as

=Abs([2004].[Revenue])<100000

With the following report filter, the table will show 3 rows and it is expected.

But we will get not quite expected results when one of “non-merged” objects is used.

With [2004].[Service], the filter will remove only numbers that does not match restriction.

With [2005].[Service], the filter will be ignored.

Why? It seems it worked more predictable in earlier versions.

Solution: Cannot launch Java Report Panel, please make sure you have installed a Java virtual machine

webi_blocked_java

Start cmd.exe as Administrator
Execute the following command which turns off blocking of outdated ActiveX controls for Internet Explorer

reg add “HKCU\Software\Microsoft\Windows\CurrentVersion\Policies\Ext” /v VersionCheckEnabled /t REG_DWORD /d 0 /f

Restart IE

PS. To enable blocking again, use command

reg add “HKCU\Software\Microsoft\Windows\CurrentVersion\Policies\Ext” /v VersionCheckEnabled /t REG_DWORD /d 1 /f

bukhantsov.org

my technical notes