JAMIS Prime Data Warehouse DFT+ Templates

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.

Important: DataSelf DFT foundation is ERP agnostic - click here to learn more.

Source	Table Name	BI Template Code
JAMIS Prime	Account	CF, GF, GT, IT, PO
JAMIS Prime	AccountClass	CF, GF, GT, PO
JAMIS Prime	Address	AR, SI, SO
JAMIS Prime	AMBomItem	MB
JAMIS Prime	AMBomMatl	MB
JAMIS Prime	AMBomOper	MB
JAMIS Prime	AMBomOvhd	MB
JAMIS Prime	AMBomStep	MB
JAMIS Prime	AMOrderType	MW
JAMIS Prime	AMProdItem	MW
JAMIS Prime	AMProdMatl	MW
JAMIS Prime	AMProdOper	MW
JAMIS Prime	AMProdOvhd	MW
JAMIS Prime	AMProdStep	MW
JAMIS Prime	AMProdTool	MW
JAMIS Prime	AMProdTotal	MW
JAMIS Prime	APInvoice	AP
JAMIS Prime	APRegister	AP, CF
JAMIS Prime	ARInvoice	AR, CF
JAMIS Prime	ARRegister	AR, CF
JAMIS Prime	ARTran	SI
JAMIS Prime	BAccount	AP, AR, CA, CF, IO, IH, IP, IT, PM, PO, SI, SO
JAMIS Prime	Branch	ALL
JAMIS Prime	Company	ALL
JAMIS Prime	Contact	CA, CO
JAMIS Prime	CRActivity	CA
JAMIS Prime	CRAddress	CA, CO
JAMIS Prime	CRCampaign	CA, CO
JAMIS Prime	CRContact	CA, CO
JAMIS Prime	CROpportunity	CO
JAMIS Prime	CROpportunityProbability	CO
JAMIS Prime	CROpportunityRevision	CO
JAMIS Prime	CRSMEmail	CO
JAMIS Prime	CSAnswers	TBD
JAMIS Prime	CSAttributeDetail	TBD
JAMIS Prime	Customer	AR, SI, SO
JAMIS Prime	CustomerClass	AR, SI, SO
JAMIS Prime	CustSalespeople	AR, SI, SO
JAMIS Prime	EPEmployee	FS
JAMIS Prime	FinPeriod	ALL
JAMIS Prime	GLHistory	GF
JAMIS Prime	GLHistoryByPeriod	GF
JAMIS Prime	GLTran	CF, GT
JAMIS Prime	INItemClass	IO, IH, IP, IT, PO, SI, SO
JAMIS Prime	INItemCostHist	IH
JAMIS Prime	INItemCostHistByPeriod	IH
JAMIS Prime	INItemStats	IO, IH
JAMIS Prime	INLocation	AR, IO, IH, IP, IT, PO, SI, SO
JAMIS Prime	INLocationStatus	IO, IH
JAMIS Prime	INRegister	IT
JAMIS Prime	INSite	IO, IH, IP, IT, PO, SI, SO
JAMIS Prime	INTran	IT
JAMIS Prime	InventoryItem	IO, IH, IP, IT, PO, SI, SO
JAMIS Prime	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
JAMIS Prime	Ledger	GF, GT
JAMIS Prime	Location	IO, IH, IP, IT, PO, SI, SO
JAMIS Prime	PMAccountGroup	PM
JAMIS Prime	PMBudget	PM
JAMIS Prime	PMProforma	PM
JAMIS Prime	PMProformaLine	PM
JAMIS Prime	PMProject	PM
JAMIS Prime	PMRegister	PM
JAMIS Prime	PMTask	PM
JAMIS Prime	PMTran	PM
JAMIS Prime	POLine	PO
JAMIS Prime	POOrder	PO
JAMIS Prime	Salesperson	AR, SI, SO
JAMIS Prime	Segment	GF, GT
JAMIS Prime	SegmentValue	GF. GT
JAMIS Prime	SOAddress	SI, SO
JAMIS Prime	SOLine	SO
JAMIS Prime	SOOrder	SI, SO
JAMIS Prime	SOOrderType	SO
JAMIS Prime	SOShipment	SO
JAMIS Prime	Sub	ALL
JAMIS Prime	Users	TBD
JAMIS Prime	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	JDW-FactTran_r	JDW
Data Warehouse	JDW-FacTranSum_r	JDW
Data Warehouse	JDW-JCSOBS_r	JDW
Data Warehouse	JDW-JPMBillingRule_r	JDW
Data Warehouse	JDW-JPMContract_r	JDW
Data Warehouse	JDW-JPMCosts_r	JDW
Data Warehouse	JDW-JPMInvoiceRule_r	JDW
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_JAMIS Prime	GF, GT, IT
Data Warehouse	zEntity	ALL

Notes:

Learn more about DataSelf’s DFT (Dimension, Fact, Time) at:
- DFT+ Data Model - Time Series
- DFT+ Modeling - Source Agnostic
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.