Time window normalization refers to aligning two time periods for analysis purposes. It is a different topic when compared to the ‘time travel’ of master data. A good example is a scenario where data for the current year and the 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 the 4th week of the 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 the time period is calculated, and different periods are aligned, which the user can change at run time. Although the 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 an 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 to trail 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 help in normalizing time periods.