Sage 100 Pre-mapped Data Warehouse Tables

Owned by Cortlandt _
Oct 06, 2023
3 min read

Click here for learning about Tableau, Power BI, and MS Excel templates for Sage 100.

Tables

Pre-mapped ETL+ tables available for Sage 100 (Providex and MS SQL Server) as of 02/2022. Subject to change without notice.

Source

Table Name

Advanced

Pro & Ent

Source

Table Name

Advanced

Pro & Ent

Sage 100

AP_InvoiceHistoryHeader

 

Sage 100

AP_OpenInvoice

 

Sage 100

AP_Vendor

 

Sage 100

AR_Customer

Sage 100

AR_InvoiceHistoryDetail

Sage 100

AR_InvoiceHistoryHeader

Sage 100

AR_OpenInvoice

Sage 100

AR_Salesperson

Sage 100

BM_BillDetail

 

Sage 100

BM_BillHeader

 

Sage 100

BM_ProductionHistoryDetail

 

Sage 100

BM_ProductionHistoryHeader

 

Sage 100

BM_ProductionHistoryTierDist

 

Sage 100

CI_Item

Sage 100

GL_Account

Sage 100

GL_AccountCategory

Sage 100

GL_AccountGroup

Sage 100

GL_AccountStructure

Sage 100

GL_AccountType

Sage 100

GL_DetailPosting

Sage 100

GL_FiscalYearDetail

Sage 100

GL_MainAccount

Sage 100

GL_PeriodBudgetDetail

Sage 100

GL_PeriodPostingHistory

Sage 100

GL_SubAccount

Sage 100

IM_ItemTransactionHistory

 

Sage 100

IM_ItemWarehouse

Sage 100

IM_ItemWhseHistoryByPeriod

 

Sage 100

IM_PeriodPostingHistory

 

Sage 100

PO_PurchaseOrderDetail

Sage 100

PO_PurchaseOrderHeader

Sage 100

SO_SalesOrderHeader

Sage 100

SO_SalesOrderHistoryDetail

Sage 100

SO_SalesOrderHistoryHeader

Sage 100

SO_ShipToAddress

Data Warehouse

4_Today

Data Warehouse

5_Date

Data Warehouse

6_DatePeriod

Data Warehouse

GLz_04_Dimensions_NoOverride

Data Warehouse

GLz_05_Dimensions

Data Warehouse

GLz_10_C3_Detail

Data Warehouse

GLz_12_C1_MajorTotal

Data Warehouse

GLz_14_C15_MinorTotal

Data Warehouse

GLz_16_C2_MinorTotal

Data Warehouse

GLz_50_Balances_Budget

Data Warehouse

GLz_60_PeriodBudgetDetail_WithDates

Data Warehouse

IMz_01_PeriodPostingHistory_ItemWhseAllPeriods

 

Data Warehouse

SY0_CompanyParameters

Data Warehouse

zSage 100 Tips & Documentation

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 100 (Providex and MS SQL Server)

The out-of-the-box ETL+ and data warehouse mappings have been designed to let users easily extract the data from Sage 100.

Source: Sage 100: Sage 100 tables that have been pre-mapped in ETL+. They are part of DataSelf Step 1 - mirroring Sage 100 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:

  • 4_ 5_ and 6_ tables for advanced period analytics.

  • GLz_ tables for performing GL Trial Balances, P&L, B/S and Cash Flow.

  • IMz_ table(s) for inventory planning (combining Inventory On Hand Today, Open POs/WOs, Open SOs, and Sales projection).

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 100 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.: InvoiceNo and InvoiceType are set as the primary key of this table.

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 AR_InvoiceHistoryHeader using the Replace feature. This example only updates the last 30-days worth of transactions.

Using table linking in the ETL SQL Statement, one can link detail tables that don’t have date fields (such as AR_InvoiceHistoryDetail) with their parent tables and then set up the delta refreshes.

Table Linking Example

The following is an example of how Sage 100 _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.

Â