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

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.

image-20240414-160238.png

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.

Â