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';

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:

pic 1

The queries are merged by Service:

pic 5

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.

 

pic 6

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

pic 7

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

pic 8

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

XI 3.1 Issue with Excel Data Provider

Problem

A Webi document has a query based on Excel (Excel data source). The document can be refreshed in Web Intelligence Rich Client, but it fails in Infoview with WIS 30270 error. The following errors appear in the log:

ExtensionFactoryImpl.cpp:201:void __thiscall WICDZExt::ExtensionFactoryImpl::createRemoteExtension(const 
class WICDZExt::ExtensionDescriptor &,struct WICDZExt::IExtension **): TraceLog message 24650
2014/10/17 09:02:31.474|>>|E| |13916|15036| |||||||||||||||ExtensionFactoryImpl::createRemoteExtension 
has failed : ExtensionFactoryImpl.createExtension has failed : java.lang.Exception: extension creation failed : null object
ExtensionFactoryImpl.cpp:202:void __thiscall WICDZExt::ExtensionFactoryImpl::createRemoteExtension(const 
class WICDZExt::ExtensionDescriptor &,struct WICDZExt::IExtension **): TraceLog message 24651
2014/10/17 09:02:31.474|>>|E| |13916|15036| |||||||||||||||java.lang.Exception: extension creation failed : null object
      at com.businessobjects.cdz_ext.ExtensionFactoryImpl.createExtension(Unknown Source)
      at com.businessobjects.cdz_ext.server.ExtensionFactoryServant.createExtension(Unknown Source)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:592)
      at com.businessobjects.framework.servers.platform.adapters.ebus.orb.CommonTransportInterceptor.invoke(CommonTransportInterceptor.java:93)
      at com.businessobjects.framework.servers.common.proxy.cglib.MethodInterceptorChain.intercept(MethodInterceptorChain.java:136)
      at com.crystaldecisions.enterprise.ocaframework.idl.OCA.OCAcdz.WICDZExtensions.ExtensionFactoryPOA$$EnhancerByCGLIB$$9792817d.createExtension(<generated>)
      at com.crystaldecisions.enterprise.ocaframework.idl.OCA.OCAcdz.WICDZExtensions.ExtensionFactoryPOA._OB_op_createExtension(Unknown Source)
      at com.crystaldecisions.enterprise.ocaframework.idl.OCA.OCAcdz.WICDZExtensions.ExtensionFactoryPOA._invoke(Unknown Source)
      at com.crystaldecisions.thirdparty.com.ooc.OBPortableServer.ServantDispatcher.dispatch(ServantDispatcher.java:234)
      at com.crystaldecisions.thirdparty.com.ooc.OBPortableServer.POA_impl._OB_dispatch(POA_impl.java:1917)
      at com.crystaldecisions.thirdparty.com.ooc.OB.DispatchRequest_impl.invoke(DispatchRequest_impl.java:75)
      at com.businessobjects.framework.servers.platform.adapters.ebus.orb.ThreadPoolDispatchStrategy$Dispatcher.run(ThreadPoolDispatchStrategy.java:124)
      at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:417)
      at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:269)
      at java.util.concurrent.FutureTask.run(FutureTask.java:123)
      at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:651)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:676)
      at java.lang.Thread.run(Thread.java:595)
kc3dsxls.cpp:276:long __thiscall tbXlsDS::xtDSExcel::CreateXDS(void): TraceLog message 24652

Solution
AddNode.bat -name LOCALHOST -update -cms LOCALHOST:6400 -user Administrator -password “” -siaport 6410

Maconomy version 

SELECT 
  MAINVERSIONNUMBER, 
  PATCHNUMBER 
FROM 
(
  SELECT 
    MAINVERSIONNUMBER, 
    PATCHNUMBER, 
    ROW_NUMBER() OVER (ORDER BY MAINVERSIONNUMBER DESC, PATCHNUMBER DESC) RN 
  FROM 
    SYSTEMMAINTENANCELOG
)A
WHERE RN = 1