Irregular Period Aggregations

Typically, facts are aggregated over regular predefined intervals, such as weeks and accounting periods. This discussion is about aggregations of facts over irregular intervals, defined between explicit dates, such as those defined between physical inventory dates in retail. Stores take inventory at different dates, and department inside stores follow different schedules and frequencies, forming irregular inventory periods specific to each department store. Calculation and trend reporting of shrink requires aggregating sales, receipts and respective adjustments over three consecutive inventory periods. The diagram above shows how the base inventory period aggregations are implemented and delivered through a view to front-end tools such as Microstrategy. The arrows indicate dependencies. Here are the components

• Inventory Dates: This is a simple list of store/department and dates that designate the boundaries of inventory period intervals for each store/department. The day prior to the store open date is by convention the first date.
• Inventory Periods: This is a table that is derived from the Inventory Dates table, showing for each store/department every inventory interval begin and end date. We adopt the convention that begin dates are exclusive (Date + 1 day) while ending dates are inclusive. The definition of the inventory periods is best captured as a view which is materialized to a table. Logic is included to eliminate duplicate dates in the inventory dates.
• Inventory Period Array: The rows of this table are keyed on store/department and inventory date. Each store department is included with an adequate history of inventory dates. The rows contain an additional three date columns (can be any fixed number) containing consecutive inventory dates ending at the key date, thus defining the last three consecutive inventory intervals. This definition is also captured in a view. It is materialized, replicated and indexed for performance, as it is dynamically accessed at query time.
• Inventory Period Fact: This is a group of views and corresponding materialized tables following the usual history/current (YY/WW) range partitioning scheme used by all facts and MQTs. It is derived by aggregation of underlying facts of the same family (sales, adjustments, receipts etc.) over the inventory periods defined above. The data is organized by year into the history tables by the inventory period ending date. One inventory period fact is stored per row.
• Inventory Period Fact Array: This is the access view used for reporting, based on the inventory period array table and the inventory period facts. The view pivots the last three periods dynamically at query time, and present the three sets of facts to the application. The definition of this view is included here (DSS_INV_PER_FACT.)
• Mainteance of these facts is discussed  here.