Integration

Get Involved. Join the Conversation.

Topic

    Sanin Thulapparumban
    Suggestion Required On - Pull BULK Data in Segments/ChunksAnswered
    Topic posted September 17, 2019 by Sanin ThulapparumbanBlue Ribbon: 750+ Points, tagged API, Integration 
    132 Views, 16 Comments
    Title:
    Suggestion Required On - Pull BULK Data in Segments/Chunks
    Summary:
    Suggestion Required On - Pull BULK Data in Segments/Chunks
    Content:

    Dear All,

    Requirement:

    We have a requirement where we have to develop Parts/ Item Outbound Integration from Oracle to SAP. This integration is for a Service Center where all types of car get serviced and painted. They have around 1 Million Parts/ Items . The Requirement is to push only net change records (i.e. only those items which get newly created/ updated) to SAP. For few items the the integration works fine. but sometime when they add new model or a new car parts details the net change record count may go above 100,000. While testing this scenario, OIC Integration flow is getting rejected because of exceeding the report data(.csv) maximum size of  >10MB.

    Suggestion Required On:

    How to pull the BULK Data from Report Data in Segments/Chunks/Batches and send to DBCS using UCM [Universal Content Management] Webservice.

    Appreciate your quick response.

     

    Best Comment

    Kanchankumar Khedkar

    I believe you are using BIP report WS. Please check if this service supports attachments (I think, it does). You can invoke BIP service to send you report data as an attachment. Download this attachment to OIC in stage action and then us Stage - Chunked Read to process each chunk as per your requirement. 

    Comment

     

    • Kanchankumar Khedkar

      I believe you are using BIP report WS. Please check if this service supports attachments (I think, it does). You can invoke BIP service to send you report data as an attachment. Download this attachment to OIC in stage action and then us Stage - Chunked Read to process each chunk as per your requirement. 

      • Sanin Thulapparumban

        We tried following steps:

        a) Invoke ERP Integration ESS JOB  Service to initiate respective ESS job using Operation: exportBulkData.

        b)Upon job completion, ERP Cloud will notify through asynchronous callback.

        c) Callback will provide the status and information to download extracted data file from the ERP Cloud.

        Here facing while passing parameters to ERP integration ESS JOB WebService. Please find the below screenshot in attachment.

         

        • Kanchankumar Khedkar
          Hello Sanin, From this comment, it looks like you are trying to invoke custom was job to export data and now you are facing issue to invoke this custom ESS job via exportBulkData operation of ERPIntegrationService? Is this understanding correct?
          • Sanin Thulapparumban

            Hi Kanchan,

            That correct but last night we were able to fix it. We are currently working on next steps [downloading file from UCM] and will update you in case we face any issue.

            • Sanin Thulapparumban

              Hi Kanchankumar,

              We were able to do a call back and get the document id. But when we try to look for files in UCM we only file 1 file but BIP report as created multiple files (using bursting). Kindly see the screenshot. Could you please help? I am not sure why we are getting only 1 file name.

               

               

    • Monish Munot

      Sanin - Did you try to read the file directly or first you downloaded the file and then read it using stage read? Because if you try to read it directly it fails for sure but not otherwise and we are having the similar use case working fine for us.

      • Sanin Thulapparumban

        Thank you for reply Monish.

        We are still looking into. Is it possible for you to provide us the steps and web service using which we can achieve this requirement?

    • Monish Munot

      I see you have already got the correct answer, not sure if you still have a question. Still let me put my thoughts based on my understanding and I assume this as batch transfer for bulk data.

      1. Create BIP report to extract updated items/data to an SFTP server periodically.
      2. Create integration in OIC which will look for a file extract on sftp server as per given schedule.
      3. Using FTP adapter download the file to stage directory(internal server/machine space).
      4. Using stage chunk read feature transfer data to SAP.

      If you are want to extract data using BIP service, you can make use of BIP Web Service - https://host:port/xmlpserver/services/ExternalReportWSSService?WSDL.  You may refer to call BIP Service and payload creation - https://cloudcustomerconnect.oracle.com/posts/4a69fb2ba6

      Though I would not recommend WS call for this such a high volume, as file option is useful from maintenance and debugging perspective. And BIP service extract report data in base 64 format which we need to download to stage and read back.

      Hope this helps.

    • Arvind Mathivanan

      Hi All,

      Not sure if my response could help this topic, but i would like to understand - what if this use case was designed with a real-time business event integration? Would OIC support 100,000 event messages/day without issues? I want to understand OIC's volume considerations.

      • Kanchankumar Khedkar

        Hello Arvind, 

        I am not sure use case you are working on, but it would be good idea to implement the message aggregation, re-sequencing and throttling with heavy load. In real-time integration, it is important to aggregate messages e.g. if you are implementing a MDM Item, whenever an item is updated you receive an event, in that case if user clicks multiple saves within a short span of time , say 4 saves in a minute then you will end up processing 4 messages in parallel in case your orchestration takes say 2 mins to complete for each call. in that case, it might be end up in scenario where you latest update reaches first and then it old update to end system causing data discrepancy. This can be solved using message re-sequencing i.e. only when first message is processed second message is processed using an identifier. Since, this result in multiple calls to end system, even end system will be loaded and there can be performance issues on end system / target systems, so we should throttle messages. In case of MDM kind of implementation, best solution is to aggregate messages say all updates on a particular item in last 2 mins will be clubbed together and then a single update with latest data is sent to target system. Some of Oracle SaaS events / outbound already takes care of it e.g. purchase order where message aggregation happens on a Source side itself. However, when it is no done in Source, either target or MW layer doing it helps. I agree, this might make integrations near real time, but it definitely does not load target systems. 

        Regards,

        Kanchan

         

        • Arvind Mathivanan

          Hi Kanchan,

          Thanks for your insight, I too agree on throttling. How do you do this message re-sequencing and aggregation if source ERP is not doing it? Are you staging all the updates in DB and running a logic in integration layer to send the latest? I think PDH Cloud doesn't do this at source for Item Updates. Correct me if wrong.

          • Kanchankumar Khedkar
            Yes, PDH does not have message aggregation for item events. As of now this message resequencing feature is not available OOTB in OIC as we had for mediator. Yes, We did stage data and use pl/sql based aggregation and resequencing. If you remember the link I sent for parking lot pattern in OIC, we can enhance it for message aggregation and resequencing.
      • Monish Munot

        100,000 event messages/day for real time using business events approach, I do not think would be a good one to implement. What is the use case you are thinking about?

        • Arvind Mathivanan

          Hi Monish,

          We implemented our business events use case in SOA Cloud. Initially we tried OIC as well. We had to publish item creates/updates to multiple systems real-time. They need to know the items immediately to place the orders in next few mins. Hence we did not went for batching. Though our volume was not in 100000s, but it can vary between 500 to 5000 creates/updates on a day. So far our design works well.