Hi Experts,

I am having a requirement to do dimensional datamodel for a datawarehouse for the below scenario. Please provide me your suggestions. How many dimensions and facts which i should consider for this? Given below is my requirements.

1.       Percentage and Numbers of care plans due for review, indate or expired
   

1.       Analysis by home, keyworker, client
2.        Drill down to home by client
3.       Drill down to keyworker by client
4.       Drill down to client

Source OLTP Tables
Patient : (PatientInfo)
=========
clientName
clientAdress
Age

Staff: (StaffInfo/Keyworker)
============
StaffName
StaffDept
DOB

Careplan:(This table will holds Patient Careplans)(1:M)
=========
Expected Outcome (Expected Result of this actionplan)
Current Situation (Current condition of patient)
Action (what is the action taken for the result)
Start Date (patient start)
review Date (for every care plan there will be review date and on that date this record will expire and create a new record with the current situation)
EndDate (End date of patient)
Keyworker(Staff which is in primary charge for this patient- A staff can be keyworker for multiple patients)

thanks

MUS