Netsuite HAS your data

I've been working with a client over the last several weeks trying to get their data OUT of NetSuite (NS) and into an Azure data warehouse.  All I can say is What a chore!

Regrettably this client has extended their customer file in NS beyond the bounds of what the NS ODBC connector is able to handle.  That means more than 1000 columns!!

According to NS this is a limitation of Oracle, their database platform.  All we get are errors in ODBC.

Regardless that puts a serious crimp on what we can do.  Here's why.

Even though it's only the customer file that is extended so widely the effect of this is the employee and vendor files is also blocked.  These are the critical master files that feed the dimension data in the warehouse.  I can only speculate that their underlying views in ODBC somehow extend the problem from one table to all the other master file tables.  I have no idea why this is - it simply makes no sense.

Fortunately there is a Plan B for this.  In NS you can create Saved Searches (SS) and those can be downloaded via email on a schedule.  We were lucky enough at this point to be able to grab just the needed data from the customer, employee and vendor files in SS downloads and that is working for now.

What we've found, though, is that while we can get the transactional data from ODBC it does not include all the attributes available.  So we have to go back to SS to grab the missing data elements to fill out the Fact tables.

There is one other odd fact I can't get over.  I can find a Project entity in SS that I can download, but I can find no similar item in ODBC.  So even if ODBC worked for the files it does not it is still missing the project file.  Go figure!

There are serious limitations for SS that make this entire project a real kludge.  Any SS download is limited to either 2MB of data or 100,000 records, whichever comes first.  This ruled out grabbing transactional data from SS, as the transactions far overrun both those limitations.  In addition we hit the limit on the SS transaction supplements at the 100k record mark and thus had to break those into bite-size chunks and work from a "Last 90-day" window in our warehouse updates.

If you are planning any data extract projects with NS keep these limitations in mind.