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	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	CSAttributeDetail	TBD
Acumatica	Customer	AR, SI, SO
Acumatica	CustomerClass	AR, SI, SO
Acumatica	CustSalespeople	AR, SI, SO
Acumatica	EPEmployee	FS
Acumatica	FinPeriod	ALL
Acumatica	FSAppointment	FS
Acumatica	FSBillHistory	FS
Acumatica	FSBranchLocation	FS
Acumatica	FSContractSchedule	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	GF, 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.

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