Data Warehouse (part 5): Fact and Dimension Tables design practice

Transaction Fact Table & associated Dimension Tables

Design a transaction-grained fact table and the associated dimension tables to meet the following requirement:

1) The university has many different locations around campus that serve meals to students using their meal cards

2) We want to be able to analyze meal purchases by student, by date, and by location

Periodic snapshot Fact table and the associated Dimension tables

Design a periodic snapshot fact table and the associated dimension tables to meet the following requirement:

1) An important part of a faculty member’s position is publishing papers in academic journals

2) A faculty member will have, at any given point in time, some papers going through initial review; some papers going through final review; other papers that have been approved for publication but not published yet; and other papers that have been published

3) The university administration office conducts analysis at the end of each calendar month about faculty publications…specifically, how many papers each faculty member has at various stages at each…