Normalizing time windows in Data Model

Time window normalization refers to aligning two time periods for analysis purposes. It is different topic when compared to “time travel” of master data. Good example is a scenario where data for current year and previous year are compared. Here, the time spans one year i.e. 365 days, but during different periods. This concept of comparing apples to apples in relation to time is the concept of aligning / normalization.

If someone is interested in analyzing sales during “Black Friday”, it will be helpful to compare 4th week of 11th month for two or more consecutive years rather than deriving them manually

Time windows are not static and change based on the time marker in which a user seeks to analyze data. The time marker (e.g. current date, end of cycle/period etc) sets the context for which time period is calculated and different periods are aligned, which the user can change at run time. Although above example is a simple one, there are many variants to time period normalization

  • YTD, QTD, MTD  – Till Date values depend on a specific date and can change dynamically
  • YQM – Whole years, quarters and months
  • Week, DOW – Week and Day of the week

Implementing all the values in Date Dimension will ease the burden of performing time window calculations at the back end. A popular method here uses additional Index and Beginning of Period values to make computation easier. 

NOTE: In all the cases, user input is required to define the start/end period (i.e. time marker) to base the calculation logic

Whole Periods: Use the standard time contexts to calculate the respective time slices. User input is not mandatory here, but defines the granularity up to which the fact needs to be aggregated.  Time hierarchies (Year > Quarter > Month > Week) are compatible only in this scenario.

Serialized Time: Avail the index columns to calculate trends and jump across years (eg. Sep-2018 will have trailing 12 months as 212 (224-12) to 224. Helpful for time series analysis, but data needs to be pre-aggregated into whole periods before applying slices.

XTD Computation: Utilize the Beginning of Period values in conjunction with actual date values to arrive at the time span. To compute for a different period e.g. Current YTD vs Previous YTD, utilize the whole periods in addition for deriving time slices.

select SUM(Sales)from FactSales as F 
inner join DimDate d on f.SalesDateKey = d.DateKey
inner join DimDate v on f.SalesDateKey = v.DateKey
where f.SalesDateKey<= {?} and f.SalesDateKey >= v.BOY;

Weeks: Weeks are tricky due to the moving nature of weeks within a year. The number of months will be constant, but weeks, whole weeks, partial weeks, starting day etc changes every year. In this case, the week and day indexes together helps in normalizing time periods