Skip to Content

SAP BODS: Row count in Source & Target Tables

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

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

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 18, 2017 at 07:50 AM

    Hi Parag,

    You can achieve by using AUDIT functionality in dataflow.

    Add comment
    10|10000 characters needed characters exceeded

    • 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.