About Transaction Entities

Table Types

Each type of table follows a naming pattern for all functional areas.

<Transaction Type> are transaction type stripes. In the NetSuite universal module definition (UMD), the transaction table is striped by transaction types using the TYPE column. NetSuite Analytics Warehouse creates tables by stripes. Transaction type can be SalesOrd, CustInvc, and so on, but <Transaction Type> contains an expanded and readable format such as _SALES_ORDER_ or _CUSTOMER_INVOICE_ respectively.
<NS Table Name> are NetSuite tables which store dimensional data in the source.

Table 2-1 NetSuite Analytics Warehouse Table Types

Table Type	Naming Pattern	Source
Fact — Transaction Header	DW_NS_<Transaction Type>_F	Derived from NetSuite source tables transaction
Fact — Transaction Lines	DW_NS_<Transaction Type>_LINES_F	Derived from NetSuite source tables transaction, transactionline, and transactionaccountingline
Fact — Snapshot	DW_NS_<Transaction Type>_SNAPSHOT_F	Derived from transaction, systemnote, transactionhistory, and other transactions which act as snapshot events for the transaction
Dimensions	DW_NS_<NS Table Name>_D	Usually derived directly from source table
General Helper Table	DW_NS_<NS Table Name>_G	Usually derived directly from source table

Fact Denormalization

Fact — Transaction Lines are usually denormalized and contain additional attributes and dimension keys from the Fact — Transaction Header table to aid ease of reporting. For example, the Sales Order Lines fact contains status and trandate, which are denormalized from the Sales Order Header fact.

Fact — Transaction Header also contains dimensions keys or attributes from the Fact — Transaction Lines mainline in the NetSuite source tables that are non-transactional in nature.

Dimensions

The dimensions keys used in a fact usually follow the rule where they are named similar to the dimension table with which they are supposed to join to. For example, DW_NS_SALES_ORDER_F.CUSTOMER joins to DW_NS_CUSTOMER_D.ID.

Dimensions can be common across functional areas and transaction types. For example, customer and item are some of the common dimensions in NetSuite Analytics Warehouse.

Dimension History

The dimensions used in NetSuite Analytics Warehouse are type 1. Any changes in the source are reflected in NetSuite Analytics Warehouse either incrementally (if they are high volume dimensions) or in full update mode.

Base and Accounting Book Amounts for Transaction Amounts

Usually, transaction currency-based amount columns extracted from a source are converted to a subsidiary or base currency in NetSuite Analytics Warehouse fact lines tables. For example, foreignamount has a corresponding base_amount, estgrossprofit has a corresponding base_estgrossprofit, and so on.

Similarly, in line facts, the accounting book currency-based amounts are also converted into base and transaction currency when applicable. For example, accountingbook_credit and accountingbook_debit have base_creditamount and base_debitamount (in base or subsidiary currency) along with credit and debit (in transaction currency). The lines facts also consist of accountingbook_currency along with the base_currency key as a role-playing dimension in the lines fact tables. This helps in identifying the currency used for these measure columns.

Header facts contain conversion for the base or subsidiary currency amounts only for corresponding transaction currency amounts, such as foreigntotal, which will have a corresponding base_total along with the base_currency key. Header facts contain the base_currency key as a role-playing dimension. Header facts don’t contain any accounting book amounts.

Flags Attributes

Both facts and dimension tables in NetSuite Analytics Warehouse consist of flag attributes which are useful for reporting purposes. The typical values in these columns is either T or F, indicating true or false. Alternately, Y or N indicates yes or no.

Snapshot Facts

NetSuite Analytics Warehouse contains snapshot facts for many transaction types. These tables track daily status and amount changes for a transaction type. The granularity of the snapshots tables is a combination of transaction identifier and date. Snapshots usually contain one row for each day from the transaction start date to the transaction close date. If the transaction is open on the ETL run date, then snapshots are built until one day before the ETL run date.

Delete Transactions

NetSuite Analytics Warehouse supports deletion of transactions. When any transaction is deleted in the source system, the same is deleted in NetSuite Analytics Warehouse after an incremental refresh.

Warehouse Table Grain Identification

The granularity of each table (fact, dimension, etc.) can be determined by checking the source_record_id column. When filtered by a single value, the table should have only one record. As a convention, source record ID is placed immediately after the columns that constitute the grain of the table. For example, for DW_NS_SALES_ORDER_LINES_F, the granularity of the table consists of transaction, id, and accountingbook, hence the source_record_id is placed as the fourth column after the above three grain columns.

Source record source_record_id contains the concatenated string-converted value of the grain columns. Hence for the above example, DW_NS_SALES_ORDER_LINES_F.source_record_id = transaction~id~accountingbook (or 32451~2~1).

Extraction Dates and Load Volume Control

Both Fact — Transaction Header and Fact — Transaction Lines tend to be high-volume tables and may impact data extract performance. To control the extract data volume, users can set an initial extract date as part of pipeline parameters while performing the setup. This controls the extraction of data in the load. Incremental loads are controlled using the last modified date attribute in transaction and transactionlines tables. Many dimensions also extract data incrementally using the last modified date attribute, while some dimensions always extract in full extracts. The ones which load in full extracts are usually smaller dimensions,

Reporting Parameters

NetSuite Analytics Warehouse has one reporting parameter that needs to be set: subsidiary. The subsidiary parameter is used for consolidating transaction values from other subsidiaries' currencies to this reporting subsidiary currency.