SAP BODS: Row count in Source & Target Tables

[former_member465305]

Hi All,

we are using BODS 4.2 . In our environment, we have some jobs which load dimension tables and fact tables. Now, my task is to display the row/records count in both source & Target tables (in the job trace & in a table) as soon as a job gets completed and if there is any mismatch between the count of source and target table it should throw an error

Can anyone kindly help me how to achieve this.

[kalyani_kolli · Accepted solution]

Frist create one table, name it like Table_list and insert the tablenames 
which you want to get the count
Sno SRCtablename TRGtablename
1   table1	 table11
2   table2       table22
3   table3       table33


$count_X=1;
$count_Y=SQL('DATASTORE','select count(*) from Table_list');

while($count_Y>$count_X)
begin
$SRCTablename=SQL('DATASTORE','select SRCtablename from Table_list where Sno={$count_X}');
$TRGTablename=SQL('DATASTORE','select TRGtablename from Table_list where Sno={$count_X}');

$SRC_count= SQL('DATASTORE','select count(*) from {$SRCTablename}');
$TRG_count= SQL('DATASTORE','select count(*) from {$TRGTablename}');

$diff= $SRC_count-$TRG_count;


if($diff=0)
begin 
 $status='Y'; 
end 
else begin 
$status='N'; 
end

$count_X=$count_X+1;
end

Hope this help full..

[kalyani_kolli]

Hi Parag,

You can achieve by using AUDIT functionality in dataflow.

[former_member465305]

Thanks a lot for your response.

Is there any other way like using a script which I can put into my BODS job and compare the source and target count and then load this information into a table so that it can be used as a reference by some senior associates.

Can you tell me which logic should I use for multiple tables
For single source and target table I am using below logic

$source_count = sql('DATASTORE','select count(*) from Source Table');
$target_count = sql('DATASTORE','select count(*) from Target Table');
$diff_count = $source_count - $target_count;

if($diff_count = 0 )
begin $status='Y';
end
else begin
$status='N';
end

and then loading the output in a HANA table.

Can you tell me which logic should I use for multiple tables. In case if you have any reference link, document or any useful script please share with me.