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 | |
|---|---|---|
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 |
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