JAMIS Prime Pre-Mapped Data Warehouse Tables / GIs

Click here for learning about Tableau, Power BI, and MS Excel templates for JAMIS Prime (Acumatica).

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

Source	Table Name
Acumatica	Account
Acumatica	AccountClass
Acumatica	Address
Acumatica	AMBomItem
Acumatica	AMBomMatl
Acumatica	AMBomOper
Acumatica	AMBomOvhd
Acumatica	AMBomStep
Acumatica	AMOrderType
Acumatica	AMProdItem
Acumatica	AMProdMatl
Acumatica	AMProdOper
Acumatica	AMProdOvhd
Acumatica	AMProdStep
Acumatica	AMProdTool
Acumatica	AMProdTotal
Acumatica	APInvoice
Acumatica	APRegister
Acumatica	ARInvoice
Acumatica	ARRegister
Acumatica	ARTran
Acumatica	BAccount
Acumatica	Branch
Acumatica	Company
Acumatica	Contact
Acumatica	CRActivity
Acumatica	CRAddress
Acumatica	CRCampaign
Acumatica	CRContact
Acumatica	CROpportunity
Acumatica	CROpportunityProbability
Acumatica	CROpportunityRevision
Acumatica	CRSMEmail
Acumatica	CSAnswers
Acumatica	Customer
Acumatica	CustomerClass
Acumatica	EPEmployee
Acumatica	FinPeriod
Acumatica	GLHistory
Acumatica	GLHistoryByPeriod
Acumatica	GLTran
Acumatica	INItemClass
Acumatica	INItemCostHist
Acumatica	INItemCostHistByPeriod
Acumatica	INItemStats
Acumatica	INLocation
Acumatica	INLocationStatus
Acumatica	INRegister
Acumatica	INSite
Acumatica	INTran
Acumatica	InventoryItem
Acumatica	InventoryItemAMExtension
JAMIS Prime	JDW-EPEmployee
JAMIS Prime	JDW-JBPDetail
JAMIS Prime	JDW-JBPDetailValues
JAMIS Prime	JDW-JBPForecastBurdenAmountDetail
JAMIS Prime	JDW-JBPMaster
JAMIS Prime	JDW-JBPResourcePersonnel
JAMIS Prime	JDW-JBPType
JAMIS Prime	JDW-JCSOBS
JAMIS Prime	JDW-JCSOBSLevel
JAMIS Prime	JDW-JPMBillingRule
JAMIS Prime	JDW-JPMBillRevSummary
JAMIS Prime	JDW-JPMBurden
JAMIS Prime	JDW-JPMBurdenAmountType
JAMIS Prime	JDW-JPMBurdenPool
JAMIS Prime	JDW-JPMContract
JAMIS Prime	JDW-JPMContractClass
JAMIS Prime	JDW-JPMCostClass
JAMIS Prime	JDW-JPMCostClassType
JAMIS Prime	JDW-JPMCostElement
JAMIS Prime	JDW-JPMCosts
JAMIS Prime	JDW-JPMCostTypeCode
JAMIS Prime	JDW-JPMInvoiceHeader
JAMIS Prime	JDW-JPMInvoiceRule
JAMIS Prime	JDW-JPMJobCostBillingStatus
JAMIS Prime	JDW-JPMJobCostBurdenDetail
JAMIS Prime	JDW-JPMJobCostBurdenType
JAMIS Prime	JDW-JPMJobCostRevenueStatus
JAMIS Prime	JDW-JPMJobCostTran
JAMIS Prime	JDW-JPMLaborCategory
JAMIS Prime	JDW-JPMProjectBilling
JAMIS Prime	JDW-JPMSubContract
JAMIS Prime	JDW-JPMSubContractDetail
JAMIS Prime	JDW-JState
JAMIS Prime	JDW-JVendor
Acumatica	Ledger
Acumatica	Location
Acumatica	PMAccountGroup
Acumatica	PMBudget
Acumatica	PMProforma
Acumatica	PMProformaLine
Acumatica	PMProject
Acumatica	PMRegister
Acumatica	PMTask
Acumatica	PMTran
Acumatica	POLine
Acumatica	POOrder
Acumatica	Salesperson
Acumatica	Segment
Acumatica	SegmentValue
Acumatica	SOAddress
Acumatica	SOLine
Acumatica	SOOrder
Acumatica	SOOrderType
Acumatica	SOShipment
Acumatica	Sub
Acumatica	Users
Acumatica	Vendor
Data Warehouse ACM	4_Today
Data Warehouse ACM	5_Date
Data Warehouse ACM	6_DatePeriod
Data Warehouse ACM	GLz_02_AccountOverrides
Data Warehouse ACM	GLz_03_SubAccountSegments
Data Warehouse ACM	GLz_10_C3_Detail
Data Warehouse ACM	GLz_12_C1_MajorTotal
Data Warehouse ACM	GLz_14_C15_MinorTotal
Data Warehouse ACM	GLz_16_C2_MinorTotal
Data Warehouse ACM	GLz_50_Budget
Data Warehouse ACM	GLz_60_History_PL_YTD
Data Warehouse ACM	GLz_70_CashFlow
Data Warehouse ACM	INz_10_Inventory_Planning
Data Warehouse JAMIS	JDW-01-FactTranActRaw_v
Data Warehouse JAMIS	JDW-02-FactTranActTar_v
Data Warehouse JAMIS	JDW-03-FactTranActBill_v
Data Warehouse JAMIS	JDW-04-FactTranActRev_v
Data Warehouse JAMIS	JDW-05-FactBurdActRaw_v
Data Warehouse JAMIS	JDW-06-FactBurdActTar_v
Data Warehouse JAMIS	JDW-07-FactBurdActBill_v
Data Warehouse JAMIS	JDW-08-FactBurdActRev_v
Data Warehouse JAMIS	JDW-09-FactTranPlnRaw_v
Data Warehouse JAMIS	JDW-10-FactTranPlnTar_v
Data Warehouse JAMIS	JDW-11-FactTranPlnBill_v
Data Warehouse JAMIS	JDW-12-FactTranPlnRev_v
Data Warehouse JAMIS	JDW-13-FactBurdPlnRaw_v
Data Warehouse JAMIS	JDW-14-FactBurdPlnTar_v
Data Warehouse JAMIS	JDW-15-FactBurdPlnBill_v
Data Warehouse JAMIS	JDW-16-FactBurdPlnRev_v
Data Warehouse JAMIS	JDW-17-FactFeePlnRev_v
Data Warehouse JAMIS	JDW-18-FactFeePlnBill_v
Data Warehouse JAMIS	JDW-DimPerLastClosed_v
Data Warehouse JAMIS	JDW-FactActual_u
Data Warehouse JAMIS	JDW-FactAwardBlr_v
Data Warehouse JAMIS	JDW-FactAwardCon_v
Data Warehouse JAMIS	JDW-FactAwardJob_v
Data Warehouse JAMIS	JDW-FactTran_u
Data Warehouse JAMIS	JDW-JPMBillingRule_T
Data Warehouse JAMIS	JDW-JPMContract_T
Data Warehouse JAMIS	JDW-JPMCosts_T
Data Warehouse JAMIS	JDW-JPMInvoiceRule_T
Data Warehouse JAMIS	zDate-CompositeCode_V
Data Warehouse JAMIS	zJDW-DimPlanSrcAct_v
Data Warehouse JAMIS	zJDW-DimPlanSrcPlan_v
Data Warehouse JAMIS	zJDW-DimResourceAct_v
Data Warehouse JAMIS	zJDW-DimResourcePlan_v
Data Warehouse JAMIS	zJDW-FactAward_u
Data Warehouse JAMIS	zJDW-FactAwardBlr_v
Data Warehouse JAMIS	zJDW-FactAwardCon_v
Data Warehouse JAMIS	zJDW-FactAwardJob_v
Data Warehouse JAMIS	zJDW-FactBurdenPlanRaw_v
Data Warehouse JAMIS	zJDW-FactPlan
Data Warehouse JAMIS	zJDW-FactTran_u
Data Warehouse ACM	MFGz_BOM01_Item
Data Warehouse ACM	MFGz_BOM02_Component
Data Warehouse ACM	MFGz_BOM03_Header
Data Warehouse ACM	MFGz_BOM10_Level0
Data Warehouse ACM	MFGz_BOM11_Level1
Data Warehouse ACM	MFGz_BOM12_Level2
Data Warehouse ACM	MFGz_BOM13_Level3
Data Warehouse ACM	MFGz_BOM14_Level4
Data Warehouse ACM	MFGz_BOM15_Level5
Data Warehouse ACM	MFGz_ProdOrder01_FixedOH
Data Warehouse ACM	MFGz_ProdOrder02_Header
Data Warehouse ACM	MFGz_ProdOrder03_Labor
Data Warehouse ACM	MFGz_ProdOrder04_Mach
Data Warehouse ACM	MFGz_ProdOrder05_Matl
Data Warehouse ACM	MFGz_ProdOrder06_Tool
Data Warehouse ACM	MFGz_ProdOrder07_ToolDetail
Data Warehouse ACM	MFGz_ProdOrder08_VariableOH

Notes:

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.