Data Warehouse DFT Templates for Generic ERPs

DFT Tables Views

The SQL Data Warehouse houses the DFT layer (SQL tables /views). D stands for Dimensions, F for Facts, and T for Time.

The DFT layer is the main part of the DataSelf tier 2 templates. It bridges that transforms an ERP-specific database structure into the standardized structure needed for the reports and dashboards (available in the DataSelf tier 3 and 4 templates).

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

SQL Statement Example

The following from the ETL+ engine’s data transformation process is an example showing how tables from an ERP were mapped into their target D table. In this example, the InventoryItem and INItemClass tables are mapped into the _D_Item table.

Data Model Example

Usually found within PowerBI, Tableau, Excel, or other reporting tools, 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 example, after a simple change to the Gross Profit data model calculation to absorb freight, reports and dashboards will automatically show Gross Profit with freight absorbed.

The following exemplifies how the Sale Invoice DFT tables are linked in DataSelf’s out-of-the-box templates. This linking applies to any reporting tool, such as Power BI models, Tableau data sources, and Excel.

Depending on specific needs, this modeling is maintained in the reporting tool of choice, sometimes directly in the data warehouse and sometimes in both places.

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