I am using Kettle to prepare data of OLAP. Data in the fact-table were queried from different OLTP tables. The SQL queries were carefully designed to produce result in unified style as following table:
Fieldname
Type
DEPARTMENTID
Integer
WAREHOUSEID
Integer
SPECIFYID
Integer
ISWORN
Integer
MOVE_OUT_AMOUNT
BigNumber
MOVE_OUT_FEE
BigNumber
MOVE_IN_AMOUNT
BigNumber
MOVE_IN_FEE
BigNumber
EXL_DEPLOY_IN_AMOUNT
BigNumber
EXL_DEPLOY_IN_FEE
BigNumber
BGT_DEPLOY_IN_AMOUNT
BigNumber
BGT_DEPLOY_IN_FEE
BigNumber
MAN_DEPLOY_IN_AMOUNT
BigNumber
MAN_DEPLOY_IN_FEE
BigNumber
RET_OF_FETCH_AMOUNT
BigNumber
RET_OF_FETCH_FEE
BigNumber
BGT_DEP_MV_OUT_AMOUNT
BigNumber
BGT_DEP_MV_OUT_FEE
BigNumber
MAN_DEP_MV_OUT_AMOUNT
BigNumber
MAN_DEP_MV_OUT_FEE
BigNumber
BGT_DEP_MV_IN_AMOUNT
BigNumber
BGT_DEP_MV_IN_FEE
BigNumber
MAN_DEP_MV_IN_AMOUNT
BigNumber
MAN_DEP_MV_IN_FEE
BigNumber
DIRECT_IN_AMOUNT
BigNumber
DIRECT_IN_FEE
BigNumber
FETCH_OUT_AMOUNT
BigNumber
FETCH_OUT_FEE
BigNumber

there are 12 SQL statements act as “table input” in the transformation, every statement fill 6 fileds and other fields will be filled Zero, for example:
select
mo.departmentid as departmentid ,
mo.warehouseid as warehouseid ,
m.specifyid ,
0 as move_out_amount,
0 as move_out_fee,
0 as move_in_amount,
0 as move_in_fee,
0 as exl_deploy_in_amount,
0 as exl_deploy_in_fee,
0 as bgt_deploy_in_amount,
0 as bgt_deploy_in_fee,
0 as man_deploy_in_amount,
0 as man_deploy_in_fee,
0 as ret_of_fetch_amount,
0 as ret_of_fetch_fee,
0 as bgt_dep_mv_out_amount,
0 as bgt_dep_mv_out_fee,
0 as man_dep_mv_out_amount,
0 as man_dep_mv_out_fee,
0 as bgt_dep_mv_in_amount,
0 as bgt_dep_mv_in_fee,
0 as man_dep_mv_in_amount,
0 as man_dep_mv_in_fee,
0 as direct_in_amount,
0 as direct_in_fee,
sum(m.amount) as fetch_out_amount,
sum(m.amount*m.price) as fetch_out_fee,
m.isworn
from
fetchpaper mo,
moffetch m
where
mo.auditingtag='2 '
and mo.avaiable=1
and m.fetchpaperid=mo.id
and to_char(mo.AUDITINGFINISHED,'yyyyMMdd')=to_char(to_timestamp('%%THEDATE%%','yyyy/mm/dd hh24:mi:ss.FF3'),'yyyyMMdd')
group by
mo.departmentid ,
mo.warehouseid,
m.specifyid,
m.isworn

please pay attention to fields followed with “amount” or “fee”, they are measurements from different OLTP tables, different SQL queries produce different Groups of these measurementsSo these 12 SQL queries produce result in a same style, but fills different “amount” and “fee” field-pair.
To append these result stream together and aggregate them by sum function with key fields : departmentid, warehouseid, specifyid, isworn. I putted an “sorted merge” symbol in the Transformation Diagram and connected those 12 “Table Input” as input stream to “sorted merge”. Fields in “sorted merge” step are “departmentid”, “warehouseid”, “specifyid”, “isworn”. Step sequence after “sorted merge” are “group by”->”get variables”->”select values”->“table out”.
The Problem is when I launched this transformation, I got only one row and only one filed filled with variable THEDATE, in fact there were 1973 rows expected.
What’s wrong with it?
Anybody help me?