Sage 100 Premapped Data Warehouse Tables
Click here for learning about Tableau, Power BI, and MS Excel templates for Sage 100.
Tables
Premapped 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 

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 outofthebox 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 premapped in ETL+. They are part of DataSelf Step 1  mirroring Sage 100 raw data in the data warehouse. This premapping 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 preconfigured 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 30days 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 outofthebox templates. This linking applies the same way for Tableau data sources, Power BI data sets, Excel, and other reporting tools.