topic Re: Working w/ Bulk Data in NetSuite in Workato Pros Discussion Board

Working w/ Bulk Data in NetSuite

jessica-lie — Fri, 15 Jan 2021 11:50:33 GMT

[Nov 25, 2020] Joe Blanchett (Product Manager, Finance Systems at Seat Geek) posted:

We have a few recipes where we need to work with > 200 rows of data in NetSuite. The search records call maxes out at 200 results on the first page, so the only way I've come up with to get all results involves multiple steps that iterate through each page, and accumulating the results into a workato variable list along the way. As you can see below, this method involves 10 steps just to get all the data that's needed. Has anyone come up with a more elegant way to get high volumes of data from NetSuite?

Re: Working w/ Bulk Data in NetSuite

jessica-lie — Fri, 15 Jan 2021 11:52:02 GMT

[Nov 25, 2020] Gordon Hu from WeGalvanize replied:

Throwing my two cents here:

1. If the total number of pages is big – this recipe will run for a long time. It may also run into limit of the number of items Workato variable supported.

2. If you have a third party place to “park” your data (e.g., as simple as Workato lookup table, Box CSV, or DynamoDB, or RedShift), here is what we usually do:

Create a “master recipe” to make initial call, upload the first batch. If there is more than one page, call the “callable_1”, pass the parameter (e.g., total number of pages, next page, etc)
Callable_1: similar to master recipe, make the next page’s call and upload the next batch. If the current page is < total pages: call callable_2
Callable_2: similar to Callable_1, same logic. If the current page is < total pages: call callable_1

Then, tweak Callable_1 and Callable_2 so that it knows “when to stop”.

There are a few more small things to note:

This strategy can be used for any pagination involved api calls.
The place where you park the data may not support large number of rows (e.g., lookup table)
The place where you “park” the data may have some compliance issue (e.g., can you store customer’s data on box?)
Once the data is parked, can workato “get it back” ? Does workato have ability to digest the large number of rows?

Re: Working w/ Bulk Data in NetSuite

jessica-lie — Fri, 15 Jan 2021 11:53:05 GMT

[Nov 25, 2020] Joe Blanchett (Product Manager, Finance Systems at Seat Geek) replied:

Thanks for the feedback Gordon!

I totally agree with your points below. I haven’t ran into the variable list limit yet - and to be honest I’m not sure what that is. To avoid a 90 min timeout on the Workato side, I’ve typically used (1) recipe to just get all of the data and (2) a recipe and that processes each individual record. We’re currently processing up to 8K records daily. As we scale up past ~20K records we will use a Postgres DB to “park” the data like you suggested.

Re: Working w/ Bulk Data in NetSuite

jessica-lie — Fri, 15 Jan 2021 11:55:48 GMT

[Nov 26, 2020] Jason Jho (CTO at Anvyl) replied:

If the data processing logic doesn't have to live in the body of your parent recipe, you could potentially leverage Bulk triggers instead. For instance, if you have a New/Updated Records in Bulk, a job will be created for each batch of 200 records. This way you don't have to maintain lists and pagination logic.

Hope this helps!

Re: Working w/ Bulk Data in NetSuite

jessica-lie — Fri, 15 Jan 2021 11:56:58 GMT

[Nov 26, 2020] Brian Flood (Vice President, IT, Business Systems, & Data at Fastly) replied:

We've had to do essentially the same thing for results from BigQuery and Okta (https://app.workato.com/recipes/1148428?st=3318f9- this one gets ugly because of the way they give you the next page).

I feel like this is an area where I disagree philosophically with Workato about how this should be handled. I can't think of a use case where a user would only want a single page of results, which leads me to think the pagination should just be built into the connector, rather than making the users figure out how to page through the results from the various APIs. In some cases, you wouldn't even know there were more pages since, like with Okta, the next page might be passed as a parameter in the response header which the OOB connector does not give you access to and there's no indicator to tell you that your results are partial. It might just seem odd you have exactly 1000 records.

Is there a use case for not wanting every page of results (outside of testing)?

Re: Working w/ Bulk Data in NetSuite

jessica-lie — Fri, 15 Jan 2021 12:00:00 GMT

[Nov 27, 2020] Mike Flynn (Principal Software Engineer at Rapid7) replied:

Joe - I agree with Gordon, we follow a similar strategy for getting large searches back from NetSuite (like generating all 1099s at the end of the year). We created some utility recipes to help with getting all the results back from a saved search. There are 3 recipes - the main recipe that initiates the search, the pub/sub recipe that gets all the results from all the pages, and the recipe that processes the results. Here's basically how it works:

1. Main recipe calls the saved search to get the Search ID and the number of pages.2. Main recipe then does a loop for the number of pages in the search (does not get the page from NetSuite yet) 2a. Posts to a Pub/Sub topic with the search ID and page number 2b. Adds an entry into a lookup table with the search ID, page number, and status of "PENDING"3. Uses recipe ops to start the recipe that will monitor and process all the results from the search.

4. Main recipe job ends.

1. Pub/Sub recipe is listening for Pub/Sub posts for searches, when a new post is made to the topic it does the following2. Executes the search and gets all the results for the page3. Stores the results in a database or a lookup table4. Updates the entry in the lookup table to have a status of "COMPLETED"5. Pub/Sub recipe job ends.

1. Processing Recipe is started by the main recipe, and checks every 5 minutes if all the pages of the search have been found (does a search of the lookup table to see if all pages have status of "COMPLETED"). When all pages are "COMPLETED", then do the following2. Do whatever action is needed to process the rows (perform calculations, kick off new searches, update NetSuite or other system, etc)3. Delete all the rows from the lookup table for this search

This strategy allows us to split the search into multiple threads, so that the pub/sub recipe is fetching 5 pages of results at a time. When using this for generating 1099s, the pub/sub recipe is fetching all vendors, all vendor bills, and all vendor payments (so executing thousands of searches and multiple pages of results for each search). There is one main recipe - the slack command that starts the 1099 process. There is a processing recipe for vendors, vendor bills, vendor payments, and the relationships between payments and bills (since it is a many-to-many relationship). The processing recipe will initiate new pub/sub searches (first get all the vendors, then for each vendor get all their bills, then for each bill get the payments made for that bill, and finally do the calculation for what is 1099able). Like Gordon mentioned, it is handy for the processing recipe to know when to start and when to kick off the next level of processing.

Here are some screenshots of examples:

1. main recipe - for each vendor start the search for vendor payments

2. pub-sub example recipe - executes search for vendor payments, updates MySQL (where we are temporarily storing data), start process for more searches

3. processing recipe - this is the very final processing recipe that generates the final report. There are other processing recipes for handling results for vendors/payments/bills

Re: Working w/ Bulk Data in NetSuite

jessica-lie — Fri, 15 Jan 2021 12:01:11 GMT

[Nov 27, 2020] Ryan Koh (Customer Success at Workato) replied:

Lots of good discussion points here. I would love to find out what are some of the business scenarios where you require the full data set from your systems as opposed to more real time data? And how often do you run these types of workflows?

And when you have the full data set, what do you do with that data? How do you detect what is new and what has changed or is that even necessary?

Re: Working w/ Bulk Data in NetSuite

jessica-lie — Fri, 15 Jan 2021 12:02:06 GMT

[Nov 27, 2020] Joe Blanchett (Product Manager, Finance Systems at Seat Geek) replied:

Thanks everybody for the great info. We will definitely be implementing some of these strategies at SeatGeek.

@Mike - way to utilize the entire Workato Suite! I like the idea of leveraging lookup tables and pub/sub. Have not had the opportunity to use Recipe Ops yet.

@Brian - I totally agree. It would be great if pagination was a built-in option in the connector itself.

@Ryan - we have at least 2 use cases that come to mind (1) payment processor settlements and (2) customer syncs from NetSuite to Adaptive.

For 1, we drop in all transactions that our payment processor settles to us (we process payments on behalf of our enterprise clients) in a custom NetSuite table, calculate our fees, then create bill/bill payment transactions to payout the remaining funds to our clients. We get up to 8K transactions per batch and need to ensure a batch is fully processed and never in a partially processed state for a few reasons, one of which is that our cash release process is based on comparing incoming and outgoing cash on the batch level. For 2, we have > 200 customers and sync the customer table nightly to Adaptive Insights.

Re: Working w/ Bulk Data in NetSuite

jessica-lie — Fri, 15 Jan 2021 12:02:50 GMT

[Nov 30, 2020] Brian Flood (Vice President, IT, Business Systems, & Data at Fastly) replied:

There are several use cases we have where a large bulk upload makes more sense than real-time. One is daily aggregated customer usage statistics. Once a day we update every single customer account in Salesforce with aggregated usage data from our data warehouse. The underlying tables contain billions of events, and updating Saelsforce in real time would quickly overwhelm their APIs and hit our call limits, we also don't really care about intraday usage so more than once a day is not useful. There are also use cases where either the source or destination does not support real-time and only supports something like a daily FTP feed. We have a couple of those with our Equity management tools (Shareworks) and our LMS (Bridge). We also use our data warehouse in BigQuery to calculate all types of things we want to push to various systems, as well as use it as lightweight master data management. BigQuery just does not lend itself to real time processing so some sort of batch update is always needed whenever it is involved, which is often for us.

Re: Working w/ Bulk Data in NetSuite

jessica-lie — Fri, 15 Jan 2021 12:03:57 GMT

[Nov 30, 2020] Gordon Hu from WeGalvanize replied:

I found a “work around” to overcome the real time API limit.

Original: we need multiple workato real time recipient will pull every 5 minutes.

Work around - to slow down calls

1. Define a master schedule recipe that calls every 5, 15, 60, day, week callable recipes.

2. Make callable recipes according to these schedules.

3. Then make another callable recipe to extract data between now and now-<period>

We use this approach for Okta events to monitor login failure, new hire activations, user offboardings etc. We’ve also used this to pull data for aggrgated statistics (eg Gong, Salesforce).

Pros: one recipe design that can be reused in many systems.

Cons: now I need to make sure the time master recipes are correct, and attach the callable recipes to the right schedule. However once attached, there is pretty much no debugging. Only need to tweak the last callable recipe.

Re: Working w/ Bulk Data in NetSuite

jessica-lie — Fri, 15 Jan 2021 12:05:43 GMT

[Dec 2, 2020] Amy Jorde (Senior Integration Engineer at Malwarebytes) replied:

If you have an OPA, you can also connect to the NetSuite JDBC driver to pull higher volumes of data. That said, this might not be completely practical for a regular integration use case without parking the data in a data warehouse, as it does pull the object ids rather than field values for the related objects (like subsidiary, location, custom list fields), so some work would be required to piece the details back together.

We send the raw NetSuite data into Snowflake, and then I piece it back together using SQL for use cases with this type of requirements (like sending transaction data to Anaplan, for example).