Skip to Content
0

SAP BODS: Row count in Source & Target Tables

Sep 16, 2017 at 12:12 PM

580

avatar image
Former Member

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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
kalyani kolli Sep 18, 2017 at 11:23 AM
0
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..

Share
10 |10000 characters needed characters left characters exceeded
kalyani kolli Sep 18, 2017 at 07:50 AM
0

Hi Parag,

You can achieve by using AUDIT functionality in dataflow.

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

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.



0