When Working with NetSuite you have various internal Search and Reports options. They are often sufficient for the Analytics requirements and hide quite well the complexity of the database behind the scenes. Sometimes we might want more. One option is to use ETL, see here for example. Other might prefer connecting to NetSuite data directly. It might be because we want to combine NetSuite live data with additional resources or because we just want more analytical capabilities.
Oracle Analytics Cloud (OAC) has an NetSuite connection since OAC 5.5. This YouTube video shows the simple part of connection creation, yet this is only part of the picture.
I would like to thank Tomer Yudelevitz, who patiently helped me discovering most of the described bellow.
To connect to NetSuite from OAC you need SuiteAnalytics Connect enabled on NetSuite. This is a paid option. It is not extremely expensive, but not free.
NetSuite Steps:
1. Under Setup -> Enable Features, turn on SuiteAnalytics Connect.
You will see a screen similar to the following, it provides important information for later connectivity requirements, with few problems.
a. The better data source, since 2019 is NetSuite2.com (NetSuite.com is older version)
b. Role ID 3 is Administrator. We can't use it with Netsuite2.
From NetSuite documentation we find that the following list of Roles not allowed with Netsuite2.com:
* Administrator
* Full Access (Deprecated)
* Roles requiring Two-Factor Authentication (2FA)
* Roles accessing the Connect Service with IP restrictions
* SuiteAnalytics Connect - Read All Permission
3. To set up SuiteAnalytics Connect permissions to a
role:
a. Navigate to Setup > Users/Roles
> User Management > Manage Roles.
b. Click Customize next to the name of
the role for which you would like to add the SuiteAnalytics Connect permission.
c. Click the Setup tab under the
Permissions tab.
d. Add the SuiteAnalytics Connect
permission- full
e. Click Save.
Use this role to set up the connection to the new data
source to see if it works.
4. You might want just to create a new Role in NetSuite for SuiteAnalytics Connect. If you do it, please consider it to be a name without spaces, due to a little annoying bug in OAC at the moment (OAC 5.9).
OAC Steps:
In OAC create a new Connection and select NetSuite.
Now we can create a new data source:
When you do it, you can see your NetSuite Role name and select views:
As I mentioned, while I'm writing this blog there is bug in OAC (OAC 5.9) with spaces in NetSuite role name (it should be fixed soon). As a result we can preview the data source data, but can't continue after you add the data source. Workarounds are using NetSuite role name without spaces or generating select statements instead of selecting views.
Extra Tips:
Unfortunately, I don't know of any simple way to convert existing NetSuite content to SQL. As a result, some digging is required.
They are many views in the system. Most are easy to understand, such as Account or Transaction, other are less, such as CUSTOMLIST292. We might also be interested in discovering the joins and keys of the views, as well of getting the most from existing NetSuite content such as searches.
They are few tools that can assist us:
1. The NetSuite data dictionary including connections between tables, (the links bellow are with transaction example):
The Analytics Tables/Views: https://www.netsuite.com/help/helpcenter/en_US/srbrowser/Browser2020_2/analytics/record/transaction.html
The General schema Browser: https://www.netsuite.com/help/helpcenter/en_US/srbrowser/Browser2021_1/odbc/record/transaction.html.
2. There are various tips how to extract code from NetSuite searches like this one: https://chrome.google.com/webstore/detail/netsuite-search-export/gglbgdfbkaelbjpjkiepdmfaihdokglp?hl=en-US, this chrome add-on lets you export a saved search as Suitescript code. From this script it's easy to see the physical column names and formulas. This add-on of field explorer might help as well https://chrome.google.com/webstore/detail/netsuite-field-explorer/cekalaapeajnlhphgdpmngmollojdfnd. See more about them here: https://www.abaci-us.com/chrome-extensions-netsuite/
3. There is a view named Customlists that has all the readable names of the custom tables (so we can understand what CUSTOMLIST292 is):
If you have more tips and ideas, especially about converting NetSuite content to SQL, please update here.
P.S.
Apparently there is formal NetSuite documentation about SuiteAnalytics Connect here.
No comments:
Post a Comment