Skip to main content
Skip table of contents

DFT Modeling for Generic ERPs

Introduction

DFT (Dimension, Fact, Time) modeling decouples the quirks of transactional sources from the analytics layer.

The aim is to keep reporting, analytics, and AI models stable as the company upgrades, replaces, or outgrows OLTP systems — for example, during ERP migrations or when consolidating multiple ERPs or payroll systems.

Within DFT, a SPOT (Single Point of Truth) defines the authoritative dataset for each critical business concept. Each SPOT is implemented as a data-warehouse table or view and uses SQL to produce clean, clearly named columns — primary keys, foreign keys, and analysis attributes — so downstream content doesn’t break when sources change.

Example — Customer SPOT: it contains all attributes for customer dimension analysis. Some fields might come from the ERP (Customer ID, Name, Address), while enrichment can come from CRM (e.g., Lead Source), spreadsheets, or other systems. The Customer SPOT can also harmonize and deduplicate customers across sources, enabling consistent reporting through consolidations and migrations.

DFT large.png

DFT Stages

Stage 1 — Source Mirroring: Creates a clean, fast, low-impact copy of raw source data, preserving lineage and keys.

Stage 2 — Data Warehouse: Builds the primary SPOTs (dimensions/facts) via SQL (or Python), applying cleansing and conformance, and defining clear columns plus surrogate/foreign keys..

Stage 3 — Analytics: Leverages analytics engines modeling (e.g., Domo, Looker, Power BI, Tableau), optimizing joins and aggregations where those tools perform best.

Stage 4 — Report, Dashboard, and KPI Templates: This is an extensive and customizable library of reports, dashboards, and KPIs that plug-and-play on Stage 3.

Stage 1 - Source Mirroring

DataSelf ETL+ is a powerful, easy-to-use tool for source mirroring.

You can design this Stage from scratch (click the following list of ETL+ 430+ No-code Data Sources), or accelerate optimized mirroring with our pre-mapped ETL+ Pre-mapped Source Systems.

Stage 2 - Data Warehouse

Stage 2 is housed in a SQL data warehouse (usually SQL tables/views). It transforms ERP-specific structures into the standardized model used by reports and dashboards (as in DataSelf Stages 3–4 templates).

Changing Stage 2 logic automatically propagates to all downstream reports and dashboards. For example, changing the Gross Profit calculation to absorb freight in Stage 2 will automatically show Gross Profit with freight absorbed in all downstream reports, dashboards, and AI models.

Source

Table Name

BI Template Code

Data Warehouse

_D_Branch

All

Data Warehouse

_D_Company

All

Data Warehouse

_D_CustAddress

AR, CA, PM, SI, SO

Data Warehouse

_D_Customer

AR, CA, CF, IP, PM, SI, SO

Data Warehouse

_D_CustomField

TBD

Data Warehouse

_D_GL_Acct

AR, GF, GT, SI, SO

Data Warehouse

_D_GL_SubAcct

AR, GF, GT, SI, SO

Data Warehouse

_D_Item

IO, IH, IP, IT, PO, SI, SO

Data Warehouse

_D_Location

IO, IH, IP, IT, PO, SI, SO

Data Warehouse

_D_Salesperson

AR, CA, PM, SI, SO

Data Warehouse

_D_ShipTo

IO, IH, IP, IT, PO, SI, SO

Data Warehouse

_D_Warehouse

IO, IH, IP, IT, PO, SI, SO

Data Warehouse

_F_AP_Aging_Today

AP

Data Warehouse

_F_AR_Aging_Today

AR

Data Warehouse

_F_Cash_Flow_Projection

CF

Data Warehouse

_F_GL_Financials

GF

Data Warehouse

_F_GL_Transaction

GT

Data Warehouse

_F_IN_Inventory_Planning

IP

Data Warehouse

_F_IN_On_Hand_Today

IO

Data Warehouse

_F_IN_On_Hand_History

IH

Data Warehouse

_F_IN_Transaction

IT

Data Warehouse

_F_MFG_BOM

MB

Data Warehouse

_F_MFG_Production_Order

MW

Data Warehouse

_F_Project_Management

PM

Data Warehouse

_F_Purchase_Order

PO

Data Warehouse

_F_Sales_Invoice

SI

Data Warehouse

_F_Sales_Order

SO

Data Warehouse

_T_DataAsOf

All

Data Warehouse

_T_Date

All

Data Warehouse

_T_Period

GF, GT, IH

Stage 2 DFT - A SQL Statement Example

Example from ETL+ transformation: mapping ERP tables to the target _D_Item DFT table.

image-20241004-033314.png

Stage 3 - Analytics

Implemented in tools such as Power BI and Tableau, the Analytics DFT turns Stage 2 data into fast, ready-to-use datasets.

Stage 3 - DFT Star Schema Example

The following exemplifies how the Sales Invoice DFT tables are linked in DataSelf’s out-of-the-box templates in a star schema arrangement. This model works in reporting tools such as Power BI, Tableau, and Excel.

image-20241004-034020.png

Stage 3 - DFT Galaxy Schema Example

The following exemplifies how DFT tables are linked in DataSelf’s out-of-the-box templates in a galaxy schema arrangement. This model works in reporting tools such as Power BI, Tableau, and Excel.

A key benefit of a galaxy schema is easy reporting across multiple fact tables in a single report. Click the image to zoom in.

image-20250702-150803.png

Stage 4 - Report, Dashboards, and KPI Template

This is an extensive and customizable library of reports, dashboards, and KPIs that plug-and-play on Stage 3.

Click the following links to learn more:

Key Words: single source of the truth, ETL templates, data warehouse templates, analytics templates, tds, tdsx, twb, twbx, pibx.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.