llua_jira_worklog_star-dist Normal 0 / ID_BATCH Y ID_BATCH CHANNEL_ID Y CHANNEL_ID TRANSNAME Y TRANSNAME STATUS Y STATUS LINES_READ Y LINES_READ LINES_WRITTEN Y LINES_WRITTEN LINES_UPDATED Y LINES_UPDATED LINES_INPUT Y LINES_INPUT LINES_OUTPUT Y LINES_OUTPUT LINES_REJECTED Y LINES_REJECTED ERRORS Y ERRORS STARTDATE Y STARTDATE ENDDATE Y ENDDATE LOGDATE Y LOGDATE DEPDATE Y DEPDATE REPLAYDATE Y REPLAYDATE LOG_FIELD Y LOG_FIELD EXECUTING_SERVER N EXECUTING_SERVER EXECUTING_USER N EXECUTING_USER CLIENT N CLIENT ID_BATCH Y ID_BATCH SEQ_NR Y SEQ_NR LOGDATE Y LOGDATE TRANSNAME Y TRANSNAME STEPNAME Y STEPNAME STEP_COPY Y STEP_COPY LINES_READ Y LINES_READ LINES_WRITTEN Y LINES_WRITTEN LINES_UPDATED Y LINES_UPDATED LINES_INPUT Y LINES_INPUT LINES_OUTPUT Y LINES_OUTPUT LINES_REJECTED Y LINES_REJECTED ERRORS Y ERRORS INPUT_BUFFER_ROWS Y INPUT_BUFFER_ROWS OUTPUT_BUFFER_ROWS Y OUTPUT_BUFFER_ROWS ID_BATCH Y ID_BATCH CHANNEL_ID Y CHANNEL_ID LOG_DATE Y LOG_DATE LOGGING_OBJECT_TYPE Y LOGGING_OBJECT_TYPE OBJECT_NAME Y OBJECT_NAME OBJECT_COPY Y OBJECT_COPY REPOSITORY_DIRECTORY Y REPOSITORY_DIRECTORY FILENAME Y FILENAME OBJECT_ID Y OBJECT_ID OBJECT_REVISION Y OBJECT_REVISION PARENT_CHANNEL_ID Y PARENT_CHANNEL_ID ROOT_CHANNEL_ID Y ROOT_CHANNEL_ID ID_BATCH Y ID_BATCH CHANNEL_ID Y CHANNEL_ID LOG_DATE Y LOG_DATE TRANSNAME Y TRANSNAME STEPNAME Y STEPNAME STEP_COPY Y STEP_COPY LINES_READ Y LINES_READ LINES_WRITTEN Y LINES_WRITTEN LINES_UPDATED Y LINES_UPDATED LINES_INPUT Y LINES_INPUT LINES_OUTPUT Y LINES_OUTPUT LINES_REJECTED Y LINES_REJECTED ERRORS Y ERRORS LOG_FIELD N LOG_FIELD ID_BATCH Y ID_BATCH CHANNEL_ID Y CHANNEL_ID LOG_DATE Y LOG_DATE METRICS_DATE Y METRICS_DATE METRICS_CODE Y METRICS_CODE METRICS_DESCRIPTION Y METRICS_DESCRIPTION METRICS_SUBJECT Y METRICS_SUBJECT METRICS_TYPE Y METRICS_TYPE METRICS_VALUE Y METRICS_VALUE 0.0 0.0 10000 50 50 N Y 50000 Y N 1000 100 cluster N dynamic Y 1 ati-dev-ana01 ati-dev-ana01 9081 pentaho-di njain Encrypted 2be98afc86aa7f2e4bb18bd63c99dbdde Y N ATI-PRD-ANA03 ATI-PRD-ANA03 9080 pentaho-di admin Encrypted 2be98afc86aa7f2e4bb18bd63c99dbdde N N ATI-STG-ANA03 ATI-STG-ANA03 9080 pentaho-di pentaho Encrypted 2be98afc86aa7f2b4ae17ba71d69dff8b N N DI Server localhost 9080 pentaho-di joe Encrypted 2be98afc86aa7f2e4bb18bd63c99dbdde Y N DI Server 5.0 localhost 9080 pentaho-di admin Encrypted 2be98afc86aa7f2e4bb18bd63c99dbdde Y N local 127.0.0.1 9080 cluster Encrypted 2be98afc86aa7f2e4cb1aa265cd86aac8 Y N Master localhost 8081 cluster Encrypted 2be98afc86aa7f2e4cb1aa265cd86aac8 Y N remote 192.168.1.16 9080 pentaho-di/kettle admin Encrypted 2be98afc86aa7f2e4bb18bd63c99dbdde Y N Slave1 localhost 8082 cluster Encrypted 2be98afc86aa7f2e4cb1aa265cd86aac8 N N Slave2 localhost 8083 cluster Encrypted 2be98afc86aa7f2e4cb1aa265cd86aac8 N N Slave3 localhost 8084 cluster Encrypted 2be98afc86aa7f2e4cb1aa265cd86aac8 N N Slave4 localhost 8085 cluster Encrypted 2be98afc86aa7f2e4cb1aa265cd86aac8 N N Slave5 localhost 8086 cluster Encrypted 2be98afc86aa7f2e4cb1aa265cd86aac8 N N ClusterSchema 40000 2000 5000 Y N Slave3 Slave2 Slave1 Master ClusterSchema_dynamic 40000 2000 5000 Y Y Master - 2020/08/06 18:24:35.729 schmitz 2023/03/29 10:21:08.009 H4sIAAAAAAAAAAMAAAAAAAAAAAA= N Extract & Transform 16 32 131 26 .AppleSystemUIFont 13 N N 0 0 0 255 205 112 100 100 100 Y Load 16 1632 40 26 .AppleSystemUIFont 13 N N 0 0 0 255 205 112 100 100 100 Y !!! Filter for the ID of one of your custom attributes. Instead of the custom attribute "worktype" you can filter for every other custom attribute, that you defined for the worklog. Just rename it in the further data stream. Here the ATTR_WORK_LOG table is filtered for the custom attribute with ATTR_TYPE_ID = 2, because that is the ID of the custom attribute "worktype" in our enviroment. If you don't have the intention to use a custom attribute, you have to remove the corresponding Steps and adapt it's processing through the data stream. Just execute the transformation and follow the occuring errors. !!! 112 352 422 250 .AppleSystemUIFont 13 N N 0 0 0 255 205 112 100 100 100 Y Steps for custom attribute - BEGIN -------------------------------------------------------> 64 224 566 26 .AppleSystemUIFont 13 N N 0 0 0 254 255 255 100 100 100 Y Steps for custom attribute - END -------------------------------------------------------> 64 1024 554 26 .AppleSystemUIFont 13 N N 0 0 0 254 255 255 100 100 100 Y Data Warehouse [ihre_domain/container_name] POSTGRESQL Native datawarehouse [host/container_port] datawarehouse Encrypted 64617461776170dbf0c0bd19c29686bb18bd63c99dbdde FORCE_IDENTIFIERS_TO_LOWERCASE N FORCE_IDENTIFIERS_TO_UPPERCASE N IS_CLUSTERED N PORT_NUMBER [host/container_port] PRESERVE_RESERVED_WORD_CASE Y QUOTE_ALL_FIELDS N SUPPORTS_BOOLEAN_DATA_TYPE N SUPPORTS_TIMESTAMP_DATA_TYPE N USE_POOLING N Jira DB [ihre_domain/container_name] POSTGRESQL Native jira [host/container_port] jira Encrypted 2be98c5a118c69686bb18bd63c99dbdde FORCE_IDENTIFIERS_TO_LOWERCASE N FORCE_IDENTIFIERS_TO_UPPERCASE N IS_CLUSTERED N PORT_NUMBER [host/container_port] PRESERVE_RESERVED_WORD_CASE Y QUOTE_ALL_FIELDS N SUPPORTS_BOOLEAN_DATA_TYPE Y SUPPORTS_TIMESTAMP_DATA_TYPE Y USE_POOLING N Sort rows - WORK_LOG_ID Merge join id - WORK_LOG_ID Y Table input - worklog attribute value Sort rows - ID 4 Y Sort rows - id Merge join id - WORK_LOG_ID Y Select values: rename timeworked to duration and id to worklog; remove obsolet fields Sort rows - ATTR_VALUE - worktype Y Sort rows - ATTR_VALUE - worktype Merge join ATTR_VALUE - ID Y Select values: rename NAME to worktype; remove obsolet fields Sort rows issueid Y Sort rows issueid Merge join issueid - id Y Data Grid Transformation Executor Y Transformation Executor jiraisssue Y jiraisssue Sort rows id Y Sort rows id Merge join issueid - id Y Merge join issueid - id Calculator split timestamp fields Y Calculator split timestamp fields Select values: remove obsolet fields Y Select values: remove obsolet fields Main table Y Main table author dimension Y issue dimension Remove lookup fields Y Remove lookup fields log date dimension Y log date dimension log time dimension Y Table input - worklog Sort rows - id Y Table input - worklog attribute Filter rows - worktype Y Filter rows - worktype Sort rows - WORK_LOG_ID Y update date dimension updated time dimension Y log time dimension update date dimension Y author dimension worktype dimension Y Sort rows - ID 4 Merge join ATTR_VALUE - ID Y Concat fields - log date & time Concat fields - updated date & time Y Concat fields - updated date & time Text file output - CSV for PM Y Main table Concat fields - log date & time Y updated time dimension rename dimension ids Y rename dimension ids Blocking step Y Blocking step Insert / update Y Merge join ATTR_VALUE - ID Select values: rename NAME to worktype; remove obsolet fields Y worktype dimension issue dimension Y Merge join id - WORK_LOG_ID Select values: rename timeworked to duration and id to worklog; remove obsolet fields Y Blocking step BlockingStep Y 1 none Y %%java.io.tmpdir%% block 5000 Y

656 2960 Y Calculator split timestamp fields Calculator Y 1 none Y updated_year YEAR_OF_DATE updated None -1 -1 N updated_month MONTH_OF_DATE updated None -1 -1 N updated_day DAY_OF_MONTH updated None -1 -1 N updated_hour HOUR_OF_DAY updated None -1 -1 N updated_minute MINUTE_OF_HOUR updated None -1 -1 N updated_second SECOND_OF_MINUTE updated None -1 -1 N log_year YEAR_OF_DATE startdate None -1 -1 N log_month MONTH_OF_DATE startdate None -1 -1 N log_day DAY_OF_MONTH startdate None -1 -1 N log_hour HOUR_OF_DAY startdate None -1 -1 N log_minute MINUTE_OF_HOUR startdate None -1 -1 N log_second SECOND_OF_MINUTE startdate None -1 -1 N

656 1440 Y Concat fields - log date & time ConcatFields Y 1 none - " N N

N

DOS None N Y N N txt N N N N N N Y N N 0 log_year None none -1 -1 log_month None none -1 -1 log_day None none -1 -1 log_hour None none -1 -1 log_minute None none -1 -1 log_second None none -1 -1 log_timestamp 255 N

480 1744 Y Concat fields - updated date & time ConcatFields Y 1 none - " N N

N

DOS None N Y N N txt N N N N N N Y N N 0 updated_year None none -1 -1 updated_month None none -1 -1 updated_day None none -1 -1 updated_hour None none -1 -1 updated_minute None none -1 -1 updated_second None none -1 -1 updated_timestamp 255 N

480 1840 Y Data Grid DataGrid Y 1 none

128 1120 Y Filter rows - worktype FilterRows Y 1 none Sort rows - WORK_LOG_ID N ATTR_TYPE_ID = constant Integer 2 -1 0 N ####0;-####0

304 288 Y Insert / update InsertUpdate Y 1 none Data Warehouse 100 N llua_jira_worklog fact_worklog worklog worklog = timeworked duration Y author_id author_id Y worktype_id worktype_id Y issue_id issue_id Y log_date_id log_date_id Y log_time_id log_time_id Y update_date_id update_date_id Y update_time_id update_time_id Y worklog worklog N

816 2960 Y Main table Dummy N 1 none

656 1744 Y Merge join ATTR_VALUE - ID MergeJoin N 1 none LEFT OUTER Sort rows - ATTR_VALUE - worktype Sort rows - ID 4 ATTR_VALUE ID

656 848 Y Merge join id - WORK_LOG_ID MergeJoin Y 1 none LEFT OUTER Sort rows - id Sort rows - WORK_LOG_ID id WORK_LOG_ID

656 288 Y Merge join issueid - id MergeJoin N 1 none LEFT OUTER Sort rows issueid Sort rows id issueid id

656 1328 Y Remove lookup fields SelectValues Y 1 none N assignee summary label project issuetype epic sprint issueid creator reporter

656 2224 Y Select values: remove obsolet fields SelectValues Y 1 none N startdate updated id

656 1536 Y Select values: rename NAME to worktype; remove obsolet fields SelectValues Y 1 none NAME worktype Y ATTR_VALUE ID

656 944 Y Select values: rename timeworked to duration and id to worklog; remove obsolet fields SelectValues Y 1 none timeworked duration id worklog Y ATTR_TYPE_ID WORK_LOG_ID ATTR_VALUE ATTR_VALUE Integer -2 -2 ####0;-####0 false false

656 624 Y Sort rows - ATTR_VALUE - worktype SortRows N 1 none %%java.io.tmpdir%% out 1000000 N N ATTR_VALUE Y N N 0 N

656 720 Y Sort rows - ID 4 SortRows N 1 none %%java.io.tmpdir%% out 1000000 N N ID Y N N 0 N

320 848 Y Sort rows - WORK_LOG_ID SortRows Y 1 none %%java.io.tmpdir%% out 1000000 N N WORK_LOG_ID Y N N 0 N

464 288 Y Sort rows - id SortRows Y 1 none %%java.io.tmpdir%% out 1000000 N N id Y N N 0 N

656 112 Y Sort rows id SortRows Y 1 none %%java.io.tmpdir%% out 1000000 N N id Y N N 0 N

320 1328 Y Sort rows issueid SortRows Y 1 none %%java.io.tmpdir%% out 1000000 N N issueid Y N N 0 N

656 1168 Y Table input - worklog TableInput Y 1 none Jira DB SELECT id , issueid , author , updated , startdate , timeworked FROM public.worklog 0 N N N N Integer normal id 18 0 Table input - changeitem id ####0;-####0 . , none N Y 0 N N N en_US Europe/Berlin N Integer normal issueid 18 0 Table input - changeitem issueid ####0;-####0 . , none N Y 0 N N N en_US Europe/Berlin N String normal author 255 -1 Table input - changeitem author . , none N Y 0 N N N en_US Europe/Berlin N Timestamp normal updated 6 -1 Table input - changeitem updated . , none N Y 0 N N N en_US Europe/Berlin N Timestamp normal startdate 6 -1 Table input - changeitem startdate . , none N Y 0 N N N en_US Europe/Berlin N Integer normal timeworked 18 0 Table input - changeitem timeworked ####0;-####0 . , none N Y 0 N N N en_US Europe/Berlin N

128 112 Y Table input - worklog attribute TableInput N 1 none Jira DB SELECT "ATTR_TYPE_ID" , "ATTR_VALUE" , "WORK_LOG_ID" FROM public.AO_7B9828_ATTR_WORK_LOG 0 N N N N Integer normal ATTR_TYPE_ID 9 0 Table input - status ATTR_TYPE_ID ####0;-####0 . , none N Y 0 N N N en_US Europe/Berlin N String normal ATTR_VALUE 100 -1 Table input - status ATTR_VALUE . , none N Y 0 N N N en_US Europe/Berlin N Integer normal WORK_LOG_ID 15 0 Table input - status WORK_LOG_ID ####0;-####0 . , none N Y 0 N N N en_US Europe/Berlin N

128 288 Y Table input - worklog attribute value TableInput Y 1 none Jira DB SELECT "ID" , "NAME" FROM public.AO_7B9828_ATTR_VALUE 0 N N N N Integer normal ID 9 0 Table input - worklog attribute value ID ####0;-####0 . , none N Y 0 N N N en_US Europe/Berlin N String normal NAME 255 -1 Table input - worklog attribute value NAME . , none N Y 0 N N N en_US Europe/Berlin N

128 848 Y Text file output - CSV for PM TextFileOutput Y 1 none ; " N N

Y

UNIX None UTF-8 N Y ${DI_HOME}/csv/jira_worklog_star N Y csv N N N N N Y -yyyy-MM-dd-HH-mm-ss Y N N

272 1840 Y Transformation Executor TransExecutor Y 1 none rep_name llua_jira_issue_dimension ${Internal.Entry.Current.Directory} 0 Y Transformation Executor ExecutionTime ExecutionResult ExecutionNrErrors ExecutionLinesRead ExecutionLinesWritten ExecutionLinesInput ExecutionLinesOutput ExecutionLinesRejected ExecutionLinesUpdated ExecutionLinesDeleted ExecutionFilesRetrieved ExecutionExitStatus ExecutionLogText ExecutionLogChannelId jiraisssue id Integer 18 0 assignee String 255 -1 creator String 255 -1 reporter String 255 -1 summary String 255 -1 label String -1 -1 project String 255 -1 issuetype String 60 -1 epic String 255 -1 sprint String 255 -1 Transformation Executor FileName

128 1216 Y author dimension CombinationLookup Y 1 none llua_jira_worklog dim_author Data Warehouse 100 9999 Y N N hashcode author author id tablemax N

656 1904 Y issue dimension DimensionLookup Y 1 none llua_jira_worklog dim_issue Data Warehouse 100 Y issueid issue date_from date_to summary summary Update assignee assignee Insert project project Update issuetype issuetype Update label labels Update epic epic Update sprint sprint Insert creator creator Update reporter reporter Insert id tablemax N version 1900 2199 5000 N N none N

656 2112 Y jiraisssue Dummy Y 1 none

128 1328 Y log date dimension CombinationLookup Y 1 none llua_jira_worklog dim_date Data Warehouse 100 9999 Y N N hashcode log_year year log_month month log_day day id autoinc Y

656 2336 Y log time dimension CombinationLookup Y 1 none llua_jira_worklog dim_time Data Warehouse 100 9999 Y N N hashcode log_hour hour log_minute minute log_second second id autoinc Y

656 2464 Y rename dimension ids SelectValues Y 1 none duration id author_id id_1 worktype_id id_2 issue_id id_3 log_date_id id_4 log_time_id id_5 update_date_id id_6 update_time_id worklog N

656 2848 Y update date dimension CombinationLookup Y 1 none llua_jira_worklog dim_date Data Warehouse 100 9999 Y N N hashcode updated_year year updated_month month updated_day day id autoinc Y

656 2592 Y updated time dimension CombinationLookup Y 1 none llua_jira_worklog dim_time Data Warehouse 100 9999 Y N N hashcode updated_hour hour updated_minute minute updated_second second id autoinc Y

656 2720 Y worktype dimension CombinationLookup Y 1 none llua_jira_worklog dim_worktype Data Warehouse 100 9999 Y N N hashcode worktype worktype id tablemax N

656 2000 Y N