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

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.