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

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:

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