Acumatica Pre-Mapped Data Warehouse Tables

Click here to learn about Tableau, Power BI, and MS Excel templates for Acumatica.

Pre-mapped Tables

Here’s the list of pre-mapped DataSelf data warehouse tables from Acumatica. This mapping of Acumatica tables into the data warehouse is easily customizable via DataSelf ETL+. The list applies to Acumatica on-premises, private, or public clouds (OData v3 and v4). Subject to change without notice.

Source

Table Name

Source

Table Name

Acumatica

Account

CF, GF, GT, IT, PO

Acumatica

AccountClass

CF, GF, GT, PO

Acumatica

Address

AR, SI, SO

Acumatica

AMBomItem

MB

Acumatica

AMBomMatl

MB

Acumatica

AMBomOper

MB

Acumatica

AMBomOvhd

MB

Acumatica

AMBomStep

MB

Acumatica

AMOrderType

MW

Acumatica

AMProdItem

MW

Acumatica

AMProdMatl

MW

Acumatica

AMProdOper

MW

Acumatica

AMProdOvhd

MW

Acumatica

AMProdStep

MW

Acumatica

AMProdTool

MW

Acumatica

AMProdTotal

MW

Acumatica

APInvoice

AP

Acumatica

APRegister

AP, CF

Acumatica

ARInvoice

AR, CF

Acumatica

ARRegister

AR, CF

Acumatica

ARTran

SI

Acumatica

BAccount

AP, AR, CA, CF, IO, IH, IP, IT, PM, PO, SI, SO

Acumatica

Branch

ALL

Acumatica

Company

ALL

Acumatica

Contact

CA, CO

Acumatica

CRActivity

CA

Acumatica

CRAddress

CA, CO

Acumatica

CRCampaign

CA, CO

Acumatica

CRContact

CA, CO

Acumatica

CROpportunity

CO

Acumatica

CROpportunityProbability

CO

Acumatica

CROpportunityRevision

CO

Acumatica

CRSMEmail

CO

Acumatica

CSAnswers

TBD

Acumatica

CSAttributeDetail

TBD

Acumatica

Customer

AR, SI, SO

Acumatica

CustomerClass

AR, SI, SO

Acumatica

CustSalespeople

AR, SI, SO

Acumatica

EPEmployee

FS

Acumatica

FinPeriod

ALL

Acumatica

FSBranchLocation

FS

Acumatica

FSEquipment

FS

Acumatica

FSManufacturer

FS

Acumatica

FSMasterContract

FS

Acumatica

FSRoute

FS

Acumatica

FSScheduleDet

FS

Acumatica

FSServiceContract

FS

Acumatica

FSServiceOrder

FS

Acumatica

FSSODet

FS

Acumatica

FSSrvOrdType

FS

Acumatica

GLHistory

GF

Acumatica

GLHistoryByPeriod

GF

Acumatica

GLTran

CF, GT

Acumatica

INItemClass

IO, IH, IP, IT, PO, SI, SO

Acumatica

INItemCostHist

IH

Acumatica

INItemCostHistByPeriod

IH

Acumatica

INItemStats

IO, IH

Acumatica

INLocation

AR, IO, IH, IP, IT, PO, SI, SO

Acumatica

INLocationStatus

IO, IH

Acumatica

INRegister

IT

Acumatica

INSite

IO, IH, IP, IT, PO, SI, SO

Acumatica

INTran

IT

Acumatica

InventoryItem

IO, IH, IP, IT, PO, SI, SO

Acumatica

InventoryItemAMExtension

MB, MW

Acumatica

Ledger

GF, GT

Acumatica

Location

IO, IH, IP, IT, PO, SI, SO

Acumatica

PMAccountGroup

PM

Acumatica

PMBudget

PM

Acumatica

PMProforma

PM

Acumatica

PMProformaLine

PM

Acumatica

PMProject

PM

Acumatica

PMRegister

PM

Acumatica

PMTask

PM

Acumatica

PMTran

PM

Acumatica

POLine

PO

Acumatica

POOrder

PO

Acumatica

Salesperson

AR, SI, SO

Acumatica

Segment

GF, GT

Acumatica

SegmentValue

GF. GT

Acumatica

SOAddress

SI, SO

Acumatica

SOLine

SO

Acumatica

SOOrder

SI, SO

Acumatica

SOOrderType

SO

Acumatica

SOShipment

SO

Acumatica

Sub

ALL

Acumatica

Users

TBD

Acumatica

Vendor

AP, CF, IP, IT, MB, PO

Data Warehouse

_D_Attribute (Custom Fields)

TBD

Data Warehouse

_D_Branch

ALL

Data Warehouse

_D_Company

ALL

Data Warehouse

_D_Customer

AR, CA, CF, IP, PM, SI, SO

Data Warehouse

_D_CustAddress

AR, CA, PM, SI, SO

Data Warehouse

_D_GL_Acct

AR, GF, GT, SI, SO

Data Warehouse

_D_GL_SubAcct

AR, GF, GT, SI, SO

Data Warehouse

_D_Item

IO, IH, IP, IT, PO, SI, SO

Data Warehouse

_D_Location

IO, IH, IP, IT, PO, SI, SO

Data Warehouse

_D_Salesperson

AR, CA, PM, SI, SO

Data Warehouse

_D_Warehouse

IO, IH, IP, IT, PO, SI, SO

Data Warehouse

_F_AP_Aging_Today

AP

Data Warehouse

_F_AR_Aging_Today

AR

Data Warehouse

_F_Cash_Flow_Projection

CF

Data Warehouse

_F_GL_Transaction

GT

Data Warehouse

_F_IN_On_Hand_Today

IO

Data Warehouse

_F_IN_Transaction

IT

Data Warehouse

_F_Project_Management

PM

Data Warehouse

_F_Purchase_Order

PO

Data Warehouse

_F_Sales_Invoice

SI

Data Warehouse

_F_Sales_Order

SO

Data Warehouse

4_Today

ALL

Data Warehouse

5_Date

ALL

Data Warehouse

6_DatePeriod

GF, GT

Data Warehouse

GLz_02_AccountOverrides

GF, GT

Data Warehouse

GLz_03_SubAccountSegments

GF, GT

Data Warehouse

GLz_10_C3_Detail

GF, GT

Data Warehouse

GLz_12_C1_MajorTotal

GF

Data Warehouse

GLz_14_C15_MinorTotal

GF

Data Warehouse

GLz_16_C2_MinorTotal

GF

Data Warehouse

GLz_50_Budget

GF, GT

Data Warehouse

GLz_60_History_PL_YTD

GF

Data Warehouse

GLz_70_CashFlow

CF

Data Warehouse

INz_10_Inventory_Planning

IP

Data Warehouse

MFGz_BOM01_Item

MB

Data Warehouse

MFGz_BOM02_Component

MB

Data Warehouse

MFGz_BOM03_Header

MB

Data Warehouse

MFGz_BOM10_Level0

MB

Data Warehouse

MFGz_BOM11_Level1

MB

Data Warehouse

MFGz_BOM12_Level2

MB

Data Warehouse

MFGz_BOM13_Level3

MB

Data Warehouse

MFGz_BOM14_Level4

MB

Data Warehouse

MFGz_BOM15_Level5

MB

Data Warehouse

MFGz_ProdOrder01_FixedOH

MW

Data Warehouse

MFGz_ProdOrder02_Header

MW

Data Warehouse

MFGz_ProdOrder03_Labor

MW

Data Warehouse

MFGz_ProdOrder04_Mach

MW

Data Warehouse

MFGz_ProdOrder05_Matl

MW

Data Warehouse

MFGz_ProdOrder06_Tool

MW

Data Warehouse

MFGz_ProdOrder07_ToolDetail

MW

Data Warehouse

MFGz_ProdOrder08_VariableOH

MW

Data Warehouse

zLists_Acumatica

GF, GT, IT

Data Warehouse

zEntity

ALL

Legend and Notes

Source:

  • Acumatica: Acumatica tables that have been pre-mapped in ETL+. They are part of DataSelf Step 1 - mirroring Acumatica raw data in the data warehouse. This pre-mapping includes:

    • Popular tables and columns for BI and reporting. We only map popular tables and columns to keep the extraction efficient and less taxing on resources. It’s easy to add more tables and columns via ETL+.

    • Many columns have their data types adjusted for reporting purposes. For instance, OData extractions set string columns to varchar(max) which can’t be used for SQL table linking - these columns are changed to varchar(50) or others (users can easily change the data types via ETL+).

    • Large tables come pre-configured with delta refresh set up. This includes delta Load Type settings, column data type formatting, and indexing.

  • Data Warehouse: These special tables provide enhanced reporting value. Summary:

    • 4_ 5_ and 6_ tables for advanced period analytics.

    • GLz_ tables are used to report GL Trial Balances, P&L, B/S, and Cash Flow.

    • INz_ table(s) for inventory planning (combining Inventory On Hand Today, Open POs/WOs, Open SOs, and Sales projection).

    • MFGz tables for manufacturing BOM and Production Order reporting.

Table Name: The name of the table in the data warehouse. When the table comes from Acumatica, it usually matches the same name as in Acumatica (e.g., Account from Acumatica is named Account in the data warehouse). If the table comes from another source or is a reimport (transformed data), then it’s labeled in a way that describes or hints at its function (e.g., INz_10_Inventory_Planning contains inventory data for inventory planning).

BI Template Code: This code identifies to which reporting template areas (Excel, Power BI, Tableau) each table belongs. For instance, the Salesperson table is used in the AR, SI, and SO template areas.

Notes:

  • Some of the above tables will only be deployed using customized SOWs or via services.

  • All Acumatica DAC tables and their columns can be extracted into the data warehouse using OData.

  • All Acumatica tables can be extracted when you have direct access to Acumatica’s database.

  • We only recommend extracting tables that are important for your reporting purposes.

Acumatica MS SQL, OData v3, OData v4

The out-of-the-box ETL+ and data warehouse mappings have been designed to let users easily extract the data from Acumatica’s MS SQL database when available and/or via OData v3 and/or OData v4.

Each of these extraction methods has pros and cons. Most deployments rely on one extraction method only. An example of using multiple methods is clients with an on-prem Acumatica who will find the SQL to SQL extraction approach the fastest. However, some DAC tables are not readily available in SQL, and it can be easier just to pull them via OData.

Table Columns

DataSelf ETL+ pre-mapped tables include popular columns for reporting purposes and not necessarily all columns. The idea for not bringing unnecessary tables and columns is to keep your BI solution fast, lean, and easy to maintain. For instance, bringing unnecessary tables and columns can cause disruptions to your BI if a future Acumatica upgrade changes or removes those data sets.

The following is an example of an Acumatica table and its column names, indexes, and formatting in ETL+. Column green icons indicate the data type and/or index definition have been set or changed in ETL+. Ex.: AcctCD has been set to varchar(5) (instead of the default varchar(max) from OData).

Delta Load

DataSelf ETL+ templates include delta load configuration for popular large tables. After the initial load that can take a while, this configuration dramatically reduces the time to refresh the data warehouse on an ongoing basis. ETL+ Table Load Types.

The following is an example for ARTran using the Upsert feature.

image-20240413-150511.png

Data Models

Data Models gather and transform raw data into fast and ready-to-be-reported information. They work as the single version of the truth defined in the DataSelf Server-side components (ETL+, data warehouse, Power BI models, and/or Tableau data sources). All reports, dashboards, and KPIs derive information from their data models. Changing the data model will automatically reflect the new rule across all reports and dashboards. For instance, a simple change to the Gross Profit data model calculation to absorb freight, and next time, reports and dashboards will automatically show GP with freight absorbed.

The following exemplifies how Acumatica _AR_Aging_Today tables are linked in DataSelf’s out-of-the-box templates. This linking applies to Tableau data sources, Power BI data sets, Excel, and other reporting tools.

The following is part of the Cash Flow Projection data model using a T-SQL expression in ETL+. It gathers and transforms cash on hand today, plus expected inbound cash (such as from AR and SO), minus expected outbound cash (such as from AP, PO, and payroll) forecasted into the future.

Key Words: Generic Inquiries, Generic Inquiry, GIs