DFT Data Model
Defines Data Model and DataSelf’s DFT Data Model.
DataSelf’s DFT (Dimension, Fact, Time) Data Model
DataSelf’s DFT data model combines the use of Fact tables and Dimensions with a comprehensive strategy for Time dimensions such as fiscal periods and company calendars.
Time in the DFT Model
The time dimension in a data model enables the analysis and reporting of data across different time periods. The treatment of time typically begins with a date, time, or date-time field and the transforms the value into a date-time buckets/slices or a code which represent the organizations view of date and time, These views include periods that do not align with standard calendar periods (day, week, month, quarter, year) such as fiscal periods. Or the division of days into workdays, weekends, or holidays.
The time dimensions bridges the gap between raw date and time fields and other business related time dimensions such as fiscal periods, reporting cycles, 4-4-5 calendars, company calendars, year to date (YTD), custom time dimensions, etc. See more examples below. A standardized set of time periods ensures that all reports follow a unified timeline, no matter the source system.
ETL+ Time Series Tables in zData Warehouse tables are pre-built tables which support the time dimension of the DFT model.
Data Models
In Business Intelligence (BI), a data model is a structured framework that defines how data is organized, stored, and related within a BI system. It acts as a blueprint for how data is collected, processed, and analyzed to support decision-making.
Data models guides the design of DataSelf’s data warehouse schemas as well as the internal transformation and data repositories of Tableau ('Tableau Data Sources') and Power BI ('semantic models').
Data Models, Data Warehousing, Semantic Models and Tableau Data Sources
Data model conceptually describes how data warehouses, Power BI’s semantic data models, and Tableau Data Sources transform and store data.
These data models:
Ensures data consistency and integrity.
Improves query and analytical performance in Tableau, Power BI, Excel and other BI tools.
Provide a single source or version of the ‘truth’ so that calculations and formulas are performed one time.
This makes reports & dashboards accurate and consistent.
Related Pages
DataSelf BI Templates for ERP & CRM (Reports, Dashboards, KPIs)
Product Catalog: Templates – strategic/conceptual overview
ETL+ Time Series Tables in zData Warehouse – tables which support the Time dimension of the DFT data model.
Data Warehouse Templates for Generic ERPs - DFT
Data Model Example – example of the advantages of a single version of the truth.
Supporting Information & Background
Common Business Date and Time Dimensions
In Business Intelligence (BI) and data warehousing, date and time play a crucial role in analytics. Fiscal periods and other business-related time dimensions help organizations analyze data based on different reporting cycles.
A time dimension typically includes these essential attributes:
Day, week, month, quarter, and year hierarchies. Both standard calendar periods and company defined periods e.g. a work week that begins on Monday.
Fiscal period indicators
Holiday flags
Seasonal markers
Calendar-specific attributes
1. Standard Calendar Periods
Year (CY - Calendar Year) → 2023, 2024
Quarter (CQ - Calendar Quarter) → Q1, Q2, Q3, Q4
Month (CM - Calendar Month) → January, February
Week (CW - Calendar Week) → Week 1, Week 2, … Week 52
Day (CD - Calendar Date) → YYYY-MM-DD (e.g., 2024-03-12)
2. Fiscal Periods (Company-Specific)
Some businesses don’t follow the standard calendar year (Jan–Dec) and instead define their own fiscal year.
Example Fiscal Year Structures:
Fiscal Year (FY) → FY 2024 (could start in July 2023 and end in June 2024)
Fiscal Quarter (FQ) → Q1 (Jul–Sep), Q2 (Oct–Dec), etc.
Fiscal Month (FM) → Different from calendar months (e.g., Aug 1–Aug 30 instead of Aug 1–Aug 31)
Fiscal Week (FW) → Week 1, Week 2 (can vary by company)
Fiscal Period → Some companies use 13 fiscal periods of 4 weeks each instead of months.
3. Retail & Business-Specific Time Dimensions
4-4-5 Calendar → Used in retail (4 weeks, 4 weeks, 5 weeks per quarter)
Rolling 12 Months → The last 12 months dynamically updating (March 2023 – Feb 2024)
Week-Based Year → Some businesses track performance using weeks instead of months
Holiday Calendar → Black Friday, Cyber Monday, Thanksgiving, etc.
Financial Closing Periods → Month-end close, quarter-end close
4. Comparative & Analytical Time Periods
Year-to-Date (YTD) → From the start of the fiscal/calendar year to today
Quarter-to-Date (QTD) → From the start of the quarter to today
Month-to-Date (MTD) → From the start of the month to today
Same Period Last Year (SPLY) → Comparing the same time last year
Trailing 12 Months (T12M) → Last 12 months rolling
5. Operational & Transactional Time Periods
Business Hours vs. Non-Business Hours
Shift-based Time (Morning, Evening, Night)
Manufacturing Production Cycles (Daily, Weekly, Monthly)
Common BI Data Modeling Approaches
Star Schema – A central fact table surrounded by related dimension tables (simplifies reporting).
Snowflake Schema – A more normalized version of Star Schema with additional sub-dimensions.
Dimensional Modeling – Focused on analytical queries with facts and dimensions.
The Time Dimension
Although one of the tenets of dimensional modeling is that all dimensions are created equal, the truth is that the time dimension is very special and must be treated differently from the other dimensions. – https://www.kimballgroup.com/1997/07/its-time-for-time/
Why Is Data Modeling Important in BI?
Ensures data consistency and integrity.
Improves query and analytical performance in Tableau, Power BI, Excel and other BI tools.
Single source of the ‘truth’ helps create accurate and consistent reports & dashboards.