Data Warehouse Design for People not Products

Question

I understand the star schema of facts and dimensions is recommended for products, orders and that style of data.

Is there a recommended way to model facts about people from monthly reports e.g tracking progress over time where the person may change state (progressing, Absence, Completed, NewPerson) between months and also that person would be associated with a number of mentors which could also change between reports.

• Would I have a dimension table for each state and each mentor role with the date the fact was true recorded.
• Do I need 2 fact tables? 1 for the person and another for the mentors?
• How does other data fit in like expected end date

Answer 1

Based on the example that we discussed in the comments section, you could have the following data marts.

Fact Table:

A fact table to record the activity of people moving(I just picked a name) Fact_People_Move

Please consider how you would like to record the 'business activity' and then decide up on whether it is a Transactional Fact or a Snapshot Fact.

More on different Fact tables from Kimball: http://www.kimballgroup.com/2008/11/fact-tables/

Dimension tables:

A table to hold employee details: Dim_Employee This table could role play as Employee and Mentors, as Mentors could be Employees themselves.

A table to hold dates: Dim_Date

A table to hold Addresses (only if you would like to record addresses): Dim_Address

A table to hold the divisions/sections of the organisation: Dim_Division

There could be so many other dims and may be other facts(if you want to record more business activities).