Skip to Content

SAP DS implementation logic for input files delay

Hi ,

We are going to start one project . It it related to Data Warehousing project for Hotels.

We need to collect the all hotels data and build Data warehousing on that. Here problem is we may not get the all hotels at the same time. I need to maintain the log n need to inform to the BI people about this. I need to run only flows that are having data availability. Once I get the for the remaining one I need to execute the flows that data come after first execution.

These things should be automated I mean scheduled.

Please give me Idea , how we are going to manage these things using SAP Data Services.

Thanks & Regards,

Ramana.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Aug 07, 2017 at 03:57 PM

    Venkata Ramana Paidi,

    I have an idea for this, Please let me know if you have not implemented this yet.

    Thanks,

    Ravi kiran

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Venkata Ramana Paidi,

      Create a dataflow for each input file and place each dataflow in a conditional object.

      Take a script before all these Conditional objects in which you write if file_exists function for each file. suppose if you will get 7 files daily

      then write if file_exists function 7 times and create a global variable for each file like $G_HOTEL1, $G_HOTEL2..... and initialize all these values to 'N'. and write script like below

      if (file_exists('c:/HOTEL1.txt') = 1)

      begin

      $G_HOTEL1='Y';

      end

      if (file_exists('c:/HOTEL2.txt') = 1)

      begin

      $G_HOTEL2='Y';

      end

      so if the files exists then the variable will be set to 'Y' , else 'N'.

      Suppose you got only 3 files HOTEL1,HOTEL3,HOTEL7 at the time of your job execution then $G_HOTEL1='Y' , $G_HOTEL3='Y' and $G_HOTEL7='Y' will be set.

      Now in each conditional object give condition like $G_HOTEL1='Y' and for 2nd file conditional give $G_HOTEL2='Y'...........

      and in each conditional object in 1st branch take the dataflow which loads from file to target and in the 2nd branch take a script and you can print('File is not loaded for this flow');

      so now if you execute the job only HOTEL1,HOTEL3,HOTEL7 dataflows will be executed and other 4 conditional will execute the script in the 2nd branch.

      At the end of the job take a script and archive these files.

      Now suppose if you get HOTEL2, HOTEL5 files after 1 hour, if you execute the job again, then $HOTEL2='Y' and $HOTEL5='Y' will be set as previous files are already moved to archive. so only these 2 flows will be executed.

      we can also make this job untill all the files are placed, like HOTEL1,HOTEL3,HOTEL7 will get executed and HOTEL2,HOTEL4,HOTEL5,HOTEL6 will keep waiting for file.

      So modify the above logic accordingly as per your requirement. let me know if it doesn't help your requirement.

      Thanks,

      Ravi kiran

  • Aug 08, 2017 at 08:55 AM

    Hi Ravi Kiran,

    I have tested from my side still it is not working.

    In the initialization script I have some SQL syntax error and it is not captured by catch statement.

    In the catch script , I have used the below syntax but it is catching.

    SQL('DS_TEST','UPDATE SYS_AUDIT_JOB SET JOB_END_DATETIME = getdate(),DURATION= JOB_END_DATETIME - JOB_START_DATETIME, STATUS = \'FAILED\' WHERE AUDIT_ID = {$G_AuditID} AND JOB_NAME = {$G_JobName}' );


    $G_PROCESS_NAME = job_name();
    $G_AuditID = SQL('DS_TEST','SELECT MAX(AUDIT_ID) from SYS_AUDIT_JOB WHERE JOB_NAME = {$G_JobName} AND PROCESS_NAME = {$G_PROCESS_NAME}');

    SQL('DS_TEST','UPDATE SYS_AUDIT_JOB SET JOB_END_DATETIME = getdate(),DURATION= datediff(JOB_END_DATETIME - JOB_START_DATETIME), STATUS = \'FAILED\' WHERE AUDIT_ID = {$G_AuditID} AND JOB_NAME = {$G_JobName} AND PROCESS_NAME = {$G_PROCESS_NAME}');

    Because of try/catch doesn't capture the this type syntax errors so my job is showing as still running status only.

    Finally my output becomes likes this.

    Could you please share your ideas on this.

    Thanks & Regards,

    Ramana.

    Add comment
    10|10000 characters needed characters exceeded

  • Aug 08, 2017 at 08:59 AM

    Hi Ravi Kiran,

    Sorry for my wrong response.

    We were discussing about the control table yesterday so I responded to that about the issue.

    Could you please share your idea on this query also.

    Thanks & Regards,

    Ramana.

    Add comment
    10|10000 characters needed characters exceeded

  • Aug 23, 2017 at 05:37 PM

    Hi Venkata Ramana Paidi,

    Create a dataflow for each input file and place each dataflow in a conditional object.

    Take a script before all these Conditional objects in which you write if file_exists function for each file. suppose if you will get 7 files daily

    then write if file_exists function 7 times and create a global variable for each file like $G_HOTEL1, $G_HOTEL2..... and initialize all these values to 'N'. and write script like below

    if (file_exists('c:/HOTEL1.txt') = 1)

    begin

    $G_HOTEL1='Y';

    end

    if (file_exists('c:/HOTEL2.txt') = 1)

    begin

    $G_HOTEL2='Y';

    end

    so if the files exists then the variable will be set to 'Y' , else 'N'.

    Suppose you got only 3 files HOTEL1,HOTEL3,HOTEL7 at the time of your job execution then $G_HOTEL1='Y' , $G_HOTEL3='Y' and $G_HOTEL7='Y' will be set.

    Now in each conditional object give condition like $G_HOTEL1='Y' and for 2nd file conditional give $G_HOTEL2='Y'...........

    and in each conditional object in 1st branch take the dataflow which loads from file to target and in the 2nd branch take a script and you can print('File is not loaded for this flow');

    so now if you execute the job only HOTEL1,HOTEL3,HOTEL7 dataflows will be executed and other 4 conditional will execute the script in the 2nd branch.

    At the end of the job take a script and archive these files.

    Now suppose if you get HOTEL2, HOTEL5 files after 1 hour, if you execute the job again, then $HOTEL2='Y' and $HOTEL5='Y' will be set as previous files are already moved to archive. so only these 2 flows will be executed.

    we can also make this job untill all the files are placed, like HOTEL1,HOTEL3,HOTEL7 will get executed and HOTEL2,HOTEL4,HOTEL5,HOTEL6 will keep waiting for file.

    So modify the above logic accordingly as per your requirement. let me know if it doesn't help your requirement.

    Thanks,

    Ravi kiran

    Add comment
    10|10000 characters needed characters exceeded