Time Dimension Entity Mapping

The mapping of the time dimension between the ODM and DWM requires reconciling the notion of periods (blocks of contiguous time units bounded by a beginning and ending point in time) with points in time. This reconciliation requirement is introduced by the Inventory Data Warehouse model which extracts both periodic and point-in-time data to populate the Inventory Fact entity type. Fortunately, the ARTS Operational Data Model contains some assumptions that help align periods and points in time.

The most granular point in time, for data warehouse purposes, is the business day;

Reporting periods may represent any number of one or more contiguous business days. For the data warehouse, the underlying operational entities that use ReportingPeriodID as their period identifier are assumed to be at the business day level. This is to align transactional sources with summary entity sources; and

This works because the ARTS Operational Data Model maintains an equivalence relationship between a BusinessDay entity type and a ReportingPeriod entity type. For purposes of supporting the data warehouse, this relationship is mandatory. Without it, the time periods and point in time attributes that place facts in time will not be aligned.

Figure 25: Entity Diagram Showing ODM To DWM Time Dimension Mapping

defines

is the start of

is part of

is credited w ith

is equivalent to

defines business day for Calendar is equivalent to BusinessDayDate which aligns ARTS ODM entities with a consistent notion of business day

for the data warehouse Time dimension.

There are some important observations to be made about this diagram

❶ The equivalence relationship defined here (which is optional) is a requirement for aligning period and point-in-time source data for the Inventory Fact entity.

❷ The power and flexibility of the ARTS ODM allows a retailer to create many calendars and time reporting hierarchies. Like the merchandise and business unit hierarchies these have to have

constraints imposed as part of mapping to a dimensional representation. This means placing reasonable constraints on the number of levels (depth) of the hierarchy and choosing which calendars you want to include in the Time Dimension entity type.

❸ BusinessDay and the lowest level of ReportingPeriod are equivalent. Note that both are foreign keys of the Time Dimension entity type. ReportingPeriodID is used as the primary key because it is the predominant attribute referenced in the various operational entities used as the source of counts, balances, cost and retail values used in the Inventory Fact entity type.

❹ The Time Dimension entity type is the "product" of all of this mapping logic. Like its peer dimensional entity types, it provides a flattened hierarchy to allow ReportingPeriod - BusinessDay bounded

quantities, counts and values to be aggregated in different higher-level time periods.

The actual mapping logic for the time dimension is based on a generation of calendar dimensions (discussed a little later, see Figure 26).

Mapping ARTS Operational Data Model time entities, attributes and relationships will follow a different pattern than the one used for location and item classification hierarchies. A major reason for this different pattern is that retail business time periods are driven by reporting requirements. The other dimensions, store location and an item are driven by geography and item physical and selling properties.

Instead of mapping time periods from the ARTS Operational Data Model, a time line will be defined divided into business day references. A business day is a calendar day (a 24 hour period of time starting at 12:00 midnight (00:00:00 in 24 hour format)) and running to 11:59:59 PM (23:59:59 in 24 hour format). For the initial release of the ARTS Sample Data Warehouse model, business day is the lowest level of granularity used for reporting.

The “mapping” process presented here is a derivation process. In effect, the approach is to generate the retail calendars as opposed to refactoring the ARTS Operational Data Model structures. The reason for this approach is that by definition, reporting periods and calendars are driven by the way a retailer wants to aggregate data over time. It is their reporting requirements and preferences that define the calendar as opposed to geography or item characteristics. Retailers that cover large geographical areas will contend with time zones. However these represent local time interval displacements from a base set of retailer calendars. The incorporation of business unit into the time dimension view of the ARTS Operational Data Model allows a retailer to establish named reporting periods for business units over a common set of retail calendars. It also enables the assigning of local, time zone-dependent names to reporting periods.

Figure 26: Notational Calendar Generation for ARTS Data Warehouse Time Dimension

Start

Define the EPOCH which is simply the underlying time

index for ALL calendars

A

Identify and name a calendar

Define start and end dates for the

calendar L =0

Identify and name a time interval T within Level L

Always start with Level 0 which is the CALENDAR.

Append a named time

interval to the current level?

T=0

Add a sub-time interval level?

T=T+1

L=L+1 Yes

Define start and end dates or days

of the week for time interval T within level N

Append a new calendar?

Yes

Yes

No

A No

Exit No

Add time intervals in the same level

Add levels of time intervals

Name Level L