Sage X3 Pre-Mapped Data Warehouse Tables
Click here to learn about Tableau, Power BI, and MS Excel templates for Sage 100.
Pre-mapped Tables
Here’s the list of pre-mapped DataSelf data warehouse tables from Sage X3. This mapping of X3 tables into the data warehouse is easily customizable via DataSelf ETL+. Subject to change without notice.
Source | Table |
|---|---|
Sage X3 | ATEXTRA |
Sage X3 | BALANA |
Sage X3 | BOMD |
Sage X3 | BPADDRESS |
Sage X3 | BPARTNER |
Sage X3 | BPCCATEG |
Sage X3 | BPCUSTOMER |
Sage X3 | BPSUPPLIER |
Sage X3 | CACCE |
Sage X3 | COMPANY |
Sage X3 | FACGROUP |
Sage X3 | FACILITY |
Sage X3 | GACCCLS |
Sage X3 | GACCCODE |
Sage X3 | GACCDUDATE |
Sage X3 | GACCENTRY |
Sage X3 | GACCENTRYA |
Sage X3 | GACCENTRYD |
Sage X3 | GACCOUNT |
Sage X3 | GCOA |
Sage X3 | GJOURNAL |
Sage X3 | GTYPACCENT |
Sage X3 | ITMCATEG |
Sage X3 | ITMFACILIT |
Sage X3 | ITMMASTER |
Sage X3 | ITMMVT |
Sage X3 | ORDERS |
Sage X3 | PERIOD |
Sage X3 | PINVOICE |
Sage X3 | PORDER |
Sage X3 | PORDERP |
Sage X3 | PORDERQ |
Sage X3 | SALESREP |
Sage X3 | SINVOICE |
Sage X3 | SINVOICED |
Sage X3 | SINVOICEV |
Sage X3 | SORDER |
Sage X3 | SORDERP |
Sage X3 | SORDERQ |
Sage X3 | STOCK |
Sage X3 | STOJOU |
Sage X3 | TABBOMALT |
Sage X3 | TABSOHTYP |
Data Warehouse | 4_Today |
Data Warehouse | 5_Date |
Data Warehouse | 6_DatePeriod |
Data Warehouse | BPCUSTOMER_T |
Data Warehouse | BPSUPPLIER_T |
Data Warehouse | GLz_02_AccountOverrides |
Data Warehouse | GLz_10_C3_Detail |
Data Warehouse | GLz_10_C3_Detail-CYE |
Data Warehouse | GLz_12_C1_MajorTotal |
Data Warehouse | GLz_14_C15_MinorTotal |
Data Warehouse | GLz_16_C2_MinorTotal |
Data Warehouse | GLz_20_C_Labels_Unioned |
Data Warehouse | GLz_30_BUD_Unpivoted |
Data Warehouse | GLz_34_GACCENTRYs |
Data Warehouse | GLz_38_BALANA_Unpivoted |
Data Warehouse | INH_01_TranBase |
Data Warehouse | INH_03_TransMonthly |
Data Warehouse | INH_10_EndBalBase |
Data Warehouse | INH_25_AllItemsTransEndOH |
Data Warehouse | INH_26_AllItemsTransOH |
Data Warehouse | INH_30_AllItemsMonths |
Data Warehouse | INH_35_ItemsMonthsHistoryDates |
Data Warehouse | ITMMASTER_T |
Data Warehouse | MFGz_BOM01_Item |
Data Warehouse | MFGz_BOM02_Component |
Data Warehouse | MFGz_BOM03_Header |
Data Warehouse | MFGz_BOM10_Level0 |
Data Warehouse | MFGz_BOM11_Level1 |
Data Warehouse | MFGz_BOM12_Level2 |
Data Warehouse | MFGz_BOM13_Level3 |
Data Warehouse | MFGz_BOM14_Level4 |
Data Warehouse | MFGz_BOM15_Level5 |
Data Warehouse | zDateUntreated |
Data Warehouse | zEntity |
Legend and Notes
Source:
Source: Sage X3: Sage X3 tables that have been pre-mapped in ETL+. They are part of DataSelf Step 1 - mirroring Sage X3 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 might have their data types adjusted for reporting purposes.
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 special tables provide enhanced reporting value. Summary:
4_ 5_ and 6_ tables for advanced period analytics.
GLz_ tables are used to report 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 Name: The name of the table in the data warehouse. When the table comes from X3, it usually matches the same name as in X3 (e.g., STOJOU from X# is named STOJOU in the data warehouse). If the table comes from another source or is a reimport (transformed data), then it’s labeled in a way that describes or hints at its function (e.g., INz_10_Inventory_Planning contains inventory data for inventory planning).
Notes:
Some tables above will only be deployed using customized SOWs or via services.
All Acumatica DAC tables and their columns can be extracted into the data warehouse using OData.
All Acumatica tables can be extracted when you have direct access to Acumatica’s database.
We only recommend extracting tables that are important for your reporting purposes.
Table Columns
DataSelf ETL+ pre-mapped tables include popular columns for reporting purposes and not necessarily all columns. The idea for not bringing unnecessary tables and columns is to keep your BI solution fast, lean, and easy to maintain. For instance, bringing unnecessary tables and columns can cause disruptions to your BI if a future X3 upgrade changes or removes those data sets.
The following is an example of an X3 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.: ROWID is set as the primary key for this table.
Delta Load
DataSelf ETL+ templates include delta load configuration for popular large tables. After the initial load that can take a while, this configuration dramatically reduces the time to refresh the data warehouse on an ongoing basis. ETL+ Table Load Types.
The following is an example of SINVOICED using the Upsert feature: modified records are updated, and new records are added.
Data Models
Data Models gather and transform raw data into fast and ready-to-be-reported information. They work as the single version of the truth defined in the DataSelf Server-side components (ETL+, data warehouse, Power BI models, and/or Tableau data sources). All reports, dashboards, and KPIs derive information from their data models. Changing the data model will automatically reflect the new rule across all reports and dashboards. For instance, a simple change to the Gross Profit data model calculation to absorb freight, and next time, reports and dashboards will automatically show GP with freight absorbed.
The following exemplifies how Sage X3 _AR_Aging_Today tables are linked in DataSelf’s out-of-the-box templates. This linking applies to Tableau data sources, Power BI data sets, Excel, and other reporting tools.
The following is part of the Inventory On Hand History data model using a T-SQL expression in ETL+. It gathers and transforms data from multiple inventory tables to guarantee that every item and every warehouse will have a record for every reporting period.