Sage 500 Pre-mapped Data Warehouse Tables
Click here for learning about Tableau, Power BI, and MS Excel templates for Sage 500.
Tables
Pre-mapped ETL+ tables available for Sage 500 as of 12/2022. Subject to change without notice.
Source | Table Name |
|---|---|
Sage 500 | tapVendClass |
Sage 500 | tapVendor |
Sage 500 | tapVendPmt |
Sage 500 | tapVendPurchHist |
Sage 500 | tapVoucher |
Sage 500 | tapVoucherDetl |
Sage 500 | tarCommClass |
Sage 500 | tarCommPlan |
Sage 500 | tarCommPlanSplit |
Sage 500 | tarCustAddr |
Sage 500 | tarCustClass |
Sage 500 | tarCustomer |
Sage 500 | tarInvoice |
Sage 500 | tarInvoiceDetl |
Sage 500 | tarInvoiceLineDist |
Sage 500 | tarNationalAcct |
Sage 500 | tarNationalAcctLevel |
Sage 500 | tarNationalAcctMember |
Sage 500 | tarSalesperson |
Sage 500 | tarSalesTeam |
Sage 500 | tarSalesTerritory |
Sage 500 | tciAddress |
Sage 500 | tciCompany |
Sage 500 | tciPaymentTerms |
Sage 500 | tciTranType |
Sage 500 | tciUnitMeasure |
Sage 500 | tglAccount |
Sage 500 | tglAcctGroup |
Sage 500 | tglAcctHist |
Sage 500 | tglAcctRef |
Sage 500 | tglAcctSegment |
Sage 500 | tglAcctType |
Sage 500 | tglBudget |
Sage 500 | tglBudgetType |
Sage 500 | tglFiscalPeriod |
Sage 500 | tglFiscalYear |
Sage 500 | tglJournal |
Sage 500 | tglNaturalAcct |
Sage 500 | tglSegment |
Sage 500 | tglSegmentCode |
Sage 500 | tglTransaction |
Sage 500 | timCostTier |
Sage 500 | timInventory |
Sage 500 | timInvtCalPerHist |
Sage 500 | timInvtFiscPerHist |
Sage 500 | timInvtPeriod |
Sage 500 | timInvtTran |
Sage 500 | timItem |
Sage 500 | timItemClass |
Sage 500 | timItemDescription |
Sage 500 | timItemUnitOfMeas |
Sage 500 | timProdCategory |
Sage 500 | timProdCatItem |
Sage 500 | timPurchProdLine |
Sage 500 | timWarehouse |
Sage 500 | timWhseBin |
Sage 500 | timWhseBinInvt |
Sage 500 | timWhseGroup |
Sage 500 | timWhsePurchProdLn |
Sage 500 | tpoPOLine |
Sage 500 | tpoPOLineDist |
Sage 500 | tpoPurchOrder |
Sage 500 | tsoPickList |
Sage 500 | tsoSalesOrder |
Sage 500 | tsoSalesOrdShip |
Sage 500 | tsoShipLine |
Sage 500 | tsoShipLineDist |
Sage 500 | tsoShipment |
Sage 500 | tsoShipmentLog |
Sage 500 | tsoSOLine |
Sage 500 | tsoSOLineDist |
Data Warehouse | _AP_Aging_Today |
Data Warehouse | _AR_Aging_Today |
Data Warehouse | _GL_Account |
Data Warehouse | _GL_Transaction |
Data Warehouse | _Sales_Invoice |
Data Warehouse | _Sales_Order |
Data Warehouse | 4_Today |
Data Warehouse | 5_Date |
Data Warehouse | 6_Period |
Data Warehouse | zDateUntreated |
Data Warehouse | zEntity |
Data Warehouse | zLog_ETL |
Data Warehouse | zLog_TableStats |
Notes:
Some of the above tables will only be deployed using customized SOWs or via services.
We only recommend extracting tables that are important for your reporting purposes.
Sage 500
The out-of-the-box ETL+ and data warehouse mappings have been designed to let users easily extract the data from Sage 500.
Source: Sage 500: Sage 500 tables that have been pre-mapped in ETL+. They are part of DataSelf Step 1 - mirroring Sage 500 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+.
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:
_TableName links tables together for a specific business area (ex.: AR Aging analysis).
4_ 5_ and 6_ tables for advanced period analytics.
zTableName tables have auxiliary data for time series and log analysis.
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 Sage 500 table and its column names, indexes, and formatting in ETL+.
Delta Load Example
DataSelf ETL+ templates provide 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 tglTransaction using the Replace feature. This example only updates the last 60-days worth of transactions.
Using table linking in the ETL SQL Statement, one can link detail tables that don’t have date fields with their parent tables and then set up the delta refreshes.
