https://netsuiteprofessionals.com logo
Join Slack
Powered by
Comparison question: our company just went throug...
# integrations
s
Comparison question: our company just went through a merger, and we now have two different integrations tools for NetSuite: Celigo and Jitterbit. We started using Celigo more recently, after years of always building bespoke integrations with Restlets for every use case. The other company had been using Jitterbit, primarily for Salesforce-to-Netsuite integration. We now have to make a choice: move everything to Jitterbit or Celigo. There is less to move from Celigo to Jitterbit, but is there any reason we should stick with Celigo anyway? While I like the interface, I find it’s not the fastest tool, and has some quirks, like an inability to run a script before the extract. But we used Jitterbit before, and I didn’t like the fact that all integrations had to be set up with an Administrator login the first time, and the interface felt very old. Anyone with experience using both feel there is an obvious best choice between the two?
t
Curious when you would need to run pre export scripts? What's the use case you're thinking?
s
There are quite a few use cases. Sometimes we do an aggregating of data (combining all usage for a month into a single record) before loading, sometimes we need to combing several records or sources into a single source, also before laoding
Of course, we could do this with Celigo, however we then have no “saved” copy of the aggregated data or the process that produced it. for auditing, it is good to have the data in NetSuite match the external system, and simply be a straight load of data (1:1)
Also, it simplified the load in that we can do a simple count of records and see that everything has loaded or not, rather than wonder if the aggregating process worked correctly. It’s much easier to prove a simple NS = DB to auditors
I guess I would flip the question around and ask, why not allow it? There are many instances where people want to run code before an export. I could see checking some service to ensure that it has completed processing before starting an export. Also, we recently had an extended outage in NetSuite, leading to a runaway Celigo process that produced over 20 thousand errors and ran for hours
If we could do a simple health check of NEtSuite before we started the job, it would have avoided the wasted time and so many errors
t
Yeah I'm not saying we shouldn't allow it, just asking what the use case is. It sounds like this source data is coming from a database is that right?
s
Yes, all from a database
t
sql server, mysql, snowflake?
s
Well, we use both SQL Server and Redshift
There is also 20 years worth of business logic baked into dozens and dozens of SQL stored procedures. Sometimes, we need to run them to prepare the data. Re-implementing them would not only be time-consuming but also introduce the possibility we don’t replicate the functionality perfectly
t
For database use cases, it's even easier to accomplish the specific scenario you mentioned. For the database exports (and normal apis) in Celigo, you can just group the results of the query by some returned field or fields. It's a pretty typical use case when bringing over transactions because you need a single header record, but then an array of line data. Database connectors also benefit because you control what is returned in your query so you can do some aggregation before hand, do some calcs, combine data, etc
s
Yes, but it’s not so simple as aggregating by a field. We aggregate by multiple ids, and by time as well
t
That grouping feature allows you to specify multiple fields to aggregate by
s
But even beyond that, we have a data volume issue. One of our billing metrics starts with over 100 million records, and 8 different grouping fields
We have to consolidate that down into only 20,000 records, to load into Netsuite
The stored procedures can do that in place, and in just a few minutes. Could Celigo (or any cloud-based ETL tool) really handle that data volume and speed?
We also have code that scans the contents of 100's of millions of files, counting occurrences of certain strings, and the combined size of those files is over 5 GB each month. Again, the stored procedures do this in minutes and are battle tested over two decades. We just want the ability to run a stored proc, then start an export
We have one flow, that extracts 60,000 rows from one table, and it already takes 9 minutes to run. Scaling that to 175 million, it would take over 18 days to perform the same extract, would it not?
t
The stored proc is definitely going to be the fastest method for that. Does the stored proc store the resulted aggregated data in a table that you then just need to query? After the 100million records goes down to 20,000, I assume you then need one more aggregation to make a single or few transactions in NetSuite with multiple lines, right?
s
it’s the 20 thousands records we want to import. Probably closer to 27 thousand actually.
t
And that's already stored in a table after the stored proc runs?
s
yes
it’s a separate staging table, just for loading to netsuite
t
Makes sense. We can trigger stored procs to run, so in that case, you could have 1 flow that runs and triggers it, checks if it's complete, then runs the next flow to pick up the results and subsequently imports the data
Flows can be strung together to run after completion of each other
s
Right now that’s what I have. I have two flows. The first runs the stored proc, the second runs the actual integration.
However, the second
the second flow runs regardless of the outcome of the first one. I don’t see anywhere to put a condition to not start the second one
t
Are you able to query somewhere and check the status of the stored proc?
s
the procedures just create or modify records. the SQL engine knows if it produced an error or not
however I think i could easily add a small bit of code to produce some data to check
t
Okay so when you trigger the stored proc, will IO present you an error if it failed or does it just return a success because it was triggered?
Instead of using the built in UI function to run next flow, you could add a step to actually call our API and trigger the other flow to run. With that, you could have a branch or something to do it based on if it was successful
s
Oh, interesting, is there a documentation page on that? We’ve only just begun using Celigo, so we certainly haven’t explored every feature of it
t
We have a /run endpoint on flows where you can manually trigger the flow to run https://docs.celigo.com/hc/en-us/articles/7707985934363-Flow-API-endpoints
s
thank you, i’ll explore that
t
So ideally, the stored proc returns an error on that step, then you response map in the error code and message to the initial data, then create a branch for successful or not, then call our api to run the next flow if it was. Here is an example on a different flow where that was done
s
yes, this looks like exactly what we want. and maybe even a third flow to also check if NetSuite is reachable, too, to avoid trying to send a bunch of request which will all fail
t
s
will do, thank you!
👍 1
3 Views
Open in Slack
PreviousNext
Powered by