Skip to main content
Skip table of contents

Acumatica Pre-Mapped Data Warehouse Tables / GIs

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

Tables

Pre-mapped ETL+ tables available for Acumatica ERP/CRM as of 08/2022. This applies to Acumatica on-premises, private cloud, or public cloud (OData v3 and v4). Subject to change without notice.

Source

Table Name

BI Template Code

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

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

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

Notes:

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

  • All Acumatica DAC tables 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.

There are some pros and cons to each of these extraction methods. Most deployments rely on one extraction method only. Example of using more than one method: clients with an on-prem Acumatica will find the SQL to SQL being the fastest extraction approach. However, there are some DAC tables not readily available in SQL, and it can be easier to just pull them via OData.

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, as well as column data type formatting and indexing.

Source: Data Warehouse: These are special tables that provide enhanced reporting value. Summary:

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

  • GLz_ tables for performing 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 Columns Example

DataSelf ETL+ templates include adjustments to table columns' data types and indexes. Click here to learn more.

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.: BAccountID (integer) has been set to an index (instead of the default AcctCD varchar index from OData).

Delta Load Example

DataSelf ETL+ templates include delta load configuration for popular large tables. This dramatically reduces the time to refresh the data warehouse. ETL+ Table Load Types.

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

Table Linking Example

The following is an example of how Acumatica _AR_Aging_Today tables are linked in DataSelf’s out of the box templates. This linking applies the same way for Tableau data sources, Power BI data sets, Excel and other reporting tools.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.