My first Publication Agile-Data-Warehouse-Design-eBook

216 Chapter 7 Time of day attributes that vary based on actual dates can be handled by a seasonal or HV CLOCK dimension When and Where If work shift start times, or any other CLOCK attributes, change on a specific date rather than “on Saturdays”, infrequent change can be handled by defining CLOCK as an HV dimension with a Type 2 SCD TIME KEY. If date-specific change is occurring on a more regular basis it may be seasonal; e.g., summer descriptions and winter descriptions. Check that values don’t cycle back before you treat them as normal HV changes that would grow the dimension year on year. You may just need a few seasonal versions of a minute as well as day versions. Day Clock Consequences HV clock dimensions which contain special versions of a minute, like DAY CLOCK, keep the dimensional model simple and easy to query, but fact loading ETL processes must be designed to assign the correct TIME KEY value based on time of day and: Clock dimensions Day type, which can be looked up from the CALENDAR dimension. that contain special versions of minutes require more complex TIME KEY Location type, which can come from an explicit where dimension such as STORE, or the implicit where details embedded within dimensions such as CUSTOMER, EMPLOYEE, or SERVICE ETL lookups The current version of the minute, where CLOCK.CURRENT=‘Y’ unless the ETL processes are loading late-arriving facts and older versions of the time de- scriptions would be valid. Time Keys TIME KEYS are normal surrogate keys that are not based on time sequence. This TIME KEY in Figure 7-7 is a normal surrogate key with no implicit time meaning. Unlike DATE KEY it is not derived from time and is not in time sequence (though the first 1440 are). By keeping time keys “meaningless” you can start with a simple clock dimension and expand it (by creating new rows) to cope with attribute variations as they arise. For example: allows them to cope with change and variation when it arrives Time of day attributes that vary by location. For example, certain branch types may have longer operating hours than others or different TV channels may have different advertising slot names and lengths. Time of day descriptions may simply change. The standard attributes of time such as hour and minute cannot change (unless everyone gets new decimal watches) and are defined as fixed value (FV) attributes. But an organization may decide to change the start time of its peak service. You can define the Peak/Off Peak attribute as HV to preserve the peak/off peak status of historical descriptions. The TIME KEY can act like any other HV surrogate key and allow an ETL process to create new versions of the minutes that are moving from peak to off peak and vice versa.

My first Publication Agile-Data-Warehouse-Design-eBook | Page 236