Category Archives: Maconomy

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.

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