Transitioning to a new data source
One of the data sources that I use in my data models is from Netsuite ERP. To access the data I am using SQL Server Integration Services with an ODBC driver going through the SuiteAnalytics Connect service. Netsuite has deprecated the current data source that I am using - Netsuite.com. I am in the process of transitioning everything to the newer analytics data source named NetSuite2.com. Some tables have been pretty straight forward but with different column names. One of the instances where it became more complex is with the General Ledger tables which require joining 3 or more tables whereas in the older data source it may have only required 2 tables. The table that I thought would have been straight forward but ended up requiring some investigation was the Accounting Period table.
With the new analytics data source I am not able to use the Data Warehouse Integrator role. My access to everything through SuiteAnalytics Connect has to be configured by the Netsuite Administrator. With the old data source I was given the SuiteAnalytics - Read All permissions which gave me full access through SuiteAnalytics Connect. Since everything is now based on permissions there is always a chance that I am not able to see a table or data because of permissions. That is what I thought was happening when trying to pull data from the Accounting Period (accountingPeriod) table.
To decipher the analytics data source I use a combination of resources including the Records Catalog in Netsuite and using DBeaver to see the ER Diagrams and properties like foreign keys. When I looked at the data in accountingPeriod it was missing recent accounting periods as well as future ones. DBeaver has a data tab that similar to doing a Select TOP 1000 rows in SQL Server Management Studio. My first theory was it must be because of permissions - for instance I was only seeing periods that are closed. When I run into a problem like this I try to do everything I can do to narrow down the issue. I knew in the past we had used two different fiscal calendars and had transitioned to only using one. I noticed all the data was for the old fiscal calendar that ran from April-March.
DBeaver has a button that will calculate total row count. When I ran that it estimated 315 rows whereas I was only returning 171. This just got interesting. Running a Count(*) also returned the 315 number (I ran a trace and DBeaver just sends a COUNT(*) when you click the calculate total row count). The Records Catalog in Netsuite for accountingPeriod has a note under the parent column that states "Not Multiple Calendars".
Records Catalog in Netsuite for Accounting Period:
The next thing I did was join the accountingPeriod table to the transaction table on postingPeriod. I was able to see new transactions with the missing periods. Next I tried just pulling the column id from the accountingPeriod table. That returned 315 ids. Including all columns except for parent returns the full table of 315 rows. Once you add back the parent column that number goes back to 171. This is really odd behavior in my opinion. The parent column is what is used to assign the correct quarter and year for a given period.
Based on working with the original data source I figured accountingPeriod would require joining to multiple tables to get the correct parent for the period. I was not expecting to only see a partial set of rows returned if I included the parent column from the accountingPeriod table. If the column only works for customers with a single calendar it should not be included in the analytics data source once multiple calendars have been used.
Many Joins to rule them all
To get the data I needed I ended up joining the accountingPeriod table with the accountingPeriodFiscalCalendars and the FiscalCalendar tables.
My passion lies with working with the Tabular Data Model. In order to get the data in one place I have to build out the processes that pull that data. When working with the new Netsuite data source there is a part of me that really likes figuring out the schema as if it is a puzzle. However, the more time I spend building out SSIS packages the less time I get to work with Power BI and the underlying tabular data model. I am hoping in the future to spend more time in the land of DAX.