Static Data Model
The Static Data Model can make working with the Netsuite SuiteAnalytics Connect new data source more informative. Regardless if you are just starting to use SuiteAnalytics for the first time or you are tasked with transitioning an existing project to the analytics data source. It is beneficial by allowing you to see all the tables and fields regardless if you have the necessary permissions.
SuiteAnalytics Connect data source change
If you use Netsuite SuiteAnalytics Connect to pull data from Netsuite to a data warehouse there has been a change in what data source you should be using. The Netsuite.com data source is being deprecated and will not be updated with new functionality. As of November 8, 2021 new users only have access to the Netsuite2.com data source. The preferred data source is the Netsuite2.com data source but it has a different schema and uses role-based permissions. More information regarding the deprecation of the NetSuite.com data source can be found here: SuiteAnswers ID 101873
Different schema and permissions
To start using the new data source you will have to figure out how to handle permissions and map out the new schema. You can use the Data Warehouse Integrator (DWI) role to access all tables but it requires token-based authentication. This limits how you can connect since the method needs to support the token-based authentication. Currently, the Netsuite provided ODBC drivers do not support using a token. An alternate method is to use the role-based permissions and build out access to the tables that are needed. However, trying to determine what tables are needed is not easy. Even with the knowledge of what it took to get a certain set of fields may have changed in the new data source. For example it may take two tables to get what you need for transactions in the Netsuite.com data source but takes at least three in Netsuite2.com to get the same information.
One method that can help is using what is called the Static Data Model to view all the tables in the new data source regardless of permissions. When making the transition to using the new data source it is helpful to see all the tables even if you do not have access to them. This facilitates the process of knowing what tables you need to request access to. If the Static Data Model is enabled you can see the structure and the name of all available record types and fields. If you do not have permission to the table it will return an empty table with no data. This is not a way to circumvent security and permissions.
Missing Tables from the ER Diagram
I setup two connections - one without the attribute and one with the attribute. I mainly use the connection without the attribute until I find I am missing a table and then I can confirm by going to the connection that has the attribute enabled. A recent example was I did not have access to the AccountingBook table. By going to the StaticSchema enabled connection I could confirm which tables I was missing. If you are using a tool like DBeaver and try to view the ER Diagram it will not show tables you do not have access to without using the StaticSchema attribute. For example even though the TransactionAccountingLine table has accountingBook as a composite primary key it will not display the accountingBook table in the ER diagram if you do not have permissions to the table.
ODBC for Windows
There are many ways to connect to SuiteAnalytics Connect including ODBC (DSN and DSN-less), JDBC, and ADO.NET. For any of the methods that use a connection string it is as simple as adding the attribute to the connection string. If you are using Windows and want to add the attribute to ODBC drivers using a DSN connection you must use the Windows Registry Editor.
First, you need to create your ODBC connection. For my example I have created a 64 bit connection named Netsuite2_StaticSchema. I use the ODBC Data Source (64 bit) application in Windows to do this.
Then we need to go to the Windows registry and edit the connection.
Open the Windows Registry Editor and go to the following folders:
32-bit drivers: Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC\ODBC.INI.
64-bit drivers: Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI.
Find the ODBC connection that we created and then go to CustomProperties and add the attribute StaticSchema = 1;
One thing to be aware of is after adding this attribute if you make any change to the ODBC connection such as renaming it - you will lose the attribute and need to add it back.
ODBC for Linux
For Linux you need to edit the odbc[64].ini or odbc.ini files to add the StaticSchema attribute to the CustomProperties.
CustomProperties=AccountID={accountID};RoleID={RoleID};Uppercase=1;StaticSchema=1;OAuth2Token={connection string value}
JDBC
For the JDBC connection you need to add the attribute to the connection string. It needs to be added to the CustomProperties in the order listed
CustomProperties=(AccountID={accountID};RoleID={RoleID};Uppercase=1;StaticSchema=1;OAuth2Token={connection string value})
For instance for my JDBC connection in DBeaver it looks like this:
ADO.NET
Add the StaticSchema attribute to the list of CustomProperties in the connection string
CustomProperties='AccountID={accountID};RoleID={RoleID};StaticSchema=1;OAuth2Token={connection string value}'
Learn More
More detail on the Static Data Model can be found on Oracle's site: Static Data Model
The Static Data Model has been invaluable as I map out transitioning from the legacy data source to the new analytics one. Being able to see all the tables and how they relate regardless of current permissions has been invaluable.