JAMIS Prime Pre-Mapped Data Warehouse Tables / GIs
Click here to learn more about Tableau, Power BI, and MS Excel templates for JAMIS Prime.
Tables
The following pre-mapped ETL+ tables (or SQL Views) are available for JAMIS Prime v7.0 or newer as of 07/2023. This applies to JAMIS Prime on-premises, private cloud, or public cloud (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 | CSAttributeDetail | TBD |
Acumatica | Customer | AR, SI, SO |
Acumatica | CustomerClass | AR, SI, SO |
Acumatica | CustSalespeople | AR, SI, SO |
Acumatica | EPEmployee | FS |
Acumatica | FinPeriod | ALL |
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 |
JAMIS Prime | EPEmployee | JDW |
JAMIS Prime | JBPDetail | JDW |
JAMIS Prime | JBPDetailValues | JDW |
JAMIS Prime | JBPForecastBurdenAmountDetail | JDW |
JAMIS Prime | JBPMaster | JDW |
JAMIS Prime | JBPResourcePersonnel | JDW |
JAMIS Prime | JBPType | JDW |
JAMIS Prime | JCSOBS | JDW |
JAMIS Prime | JCSOBSLevel | JDW |
JAMIS Prime | JPMBillingRule | JDW |
JAMIS Prime | JPMBillRevSummary | JDW |
JAMIS Prime | JPMBurden | JDW |
JAMIS Prime | JPMBurdenAmountType | JDW |
JAMIS Prime | JPMBurdenPool | JDW |
JAMIS Prime | JPMContract | JDW |
JAMIS Prime | JPMContractClass | JDW |
JAMIS Prime | JPMCostClass | JDW |
JAMIS Prime | JPMCostClassType | JDW |
JAMIS Prime | JPMCostElement | JDW |
JAMIS Prime | JPMCosts | JDW |
JAMIS Prime | JPMCostTypeCode | JDW |
JAMIS Prime | JPMInvoiceHeader | JDW |
JAMIS Prime | JPMInvoiceRule | JDW |
JAMIS Prime | JPMJobCostBillingStatus | JDW |
JAMIS Prime | JPMJobCostBurdenDetail | JDW |
JAMIS Prime | JPMJobCostBurdenType | JDW |
JAMIS Prime | JPMJobCostRevenueStatus | JDW |
JAMIS Prime | JPMJobCostTran | JDW |
JAMIS Prime | JPMLaborCategory | JDW |
JAMIS Prime | JPMProjectBilling | JDW |
JAMIS Prime | JPMSubContract | JDW |
JAMIS Prime | JPMSubContractDetail | JDW |
JAMIS Prime | JState | JDW |
JAMIS Prime | JVendor | JDW |
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 ACM | 4_Today | ALL |
Data Warehouse ACM | 5_Date | ALL |
Data Warehouse ACM | 6_DatePeriod | GF, GT |
Data Warehouse ACM | GLz_02_AccountOverrides | GF, GT |
Data Warehouse ACM | GLz_03_SubAccountSegments | GF, GT |
Data Warehouse ACM | GLz_10_C3_Detail | GF, GT |
Data Warehouse ACM | GLz_12_C1_MajorTotal | GF |
Data Warehouse ACM | GLz_14_C15_MinorTotal | GF |
Data Warehouse ACM | GLz_16_C2_MinorTotal | GF |
Data Warehouse ACM | GLz_50_Budget | GF, GT |
Data Warehouse ACM | GLz_60_History_PL_YTD | GF |
Data Warehouse ACM | GLz_70_CashFlow | CF |
Data Warehouse ACM | INz_10_Inventory_Planning | IP |
Data Warehouse JAMIS | JDW-FactTran_r | JDW |
Data Warehouse JAMIS | JDW-FacTranSum_r | JDW |
Data Warehouse JAMIS | JDW-JCSOBS_r | JDW |
Data Warehouse JAMIS | JDW-JPMBillingRule_r | JDW |
Data Warehouse JAMIS | JDW-JPMContract_r | JDW |
Data Warehouse JAMIS | JDW-JPMCosts_r | JDW |
Data Warehouse JAMIS | JDW-JPMInvoiceRule_r | JDW |
Data Warehouse ACM | MFGz_BOM01_Item | MB |
Data Warehouse ACM | MFGz_BOM02_Component | MB |
Data Warehouse ACM | MFGz_BOM03_Header | MB |
Data Warehouse ACM | MFGz_BOM10_Level0 | MB |
Data Warehouse ACM | MFGz_BOM11_Level1 | MB |
Data Warehouse ACM | MFGz_BOM12_Level2 | MB |
Data Warehouse ACM | MFGz_BOM13_Level3 | MB |
Data Warehouse ACM | MFGz_BOM14_Level4 | MB |
Data Warehouse ACM | MFGz_BOM15_Level5 | MB |
Data Warehouse ACM | MFGz_ProdOrder01_FixedOH | MW |
Data Warehouse ACM | MFGz_ProdOrder02_Header | MW |
Data Warehouse ACM | MFGz_ProdOrder03_Labor | MW |
Data Warehouse ACM | MFGz_ProdOrder04_Mach | MW |
Data Warehouse ACM | MFGz_ProdOrder05_Matl | MW |
Data Warehouse ACM | MFGz_ProdOrder06_Tool | MW |
Data Warehouse ACM | MFGz_ProdOrder07_ToolDetail | MW |
Data Warehouse ACM | MFGz_ProdOrder08_VariableOH | MW |
Data Warehouse | zLists_Acumatica | GF, GT, IT |
Data Warehouse | zEntity | ALL |
Notes:
Learn more about DataSelf’s DFT (Dimension, Fact, Time) at:
Some of the above tables and views will only be deployed using customized SOWs or via services.
All DAC tables can be extracted into the data warehouse using OData.
All JAMIS Prime SQL tables can be extracted when you have direct access to JAMIS Prime’s database.
We only recommend extracting tables and columns that are important for your reporting purposes.
JAMIS Prime 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 JAMIS Prime’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 JAMIS Prime will find the SQL to SQL be 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: JAMIS Prime: JAMIS Prime tables that have been pre-mapped in ETL+. They are part of DataSelf Step 1 - mirroring JAMIS Prime 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 a JAMIS Prime 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 of ARTran using the Upsert feature.
Table Linking Example
The following is an example of how JAMIS Prime _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.
