Dimensional modelling few questions

I am getting familiar with Dimensional model, so started looking at health claims process. 开发者_JAVA百科I am trying to acheive the following:

1) ability to report claims by patient by speciality and service provider (monthly, quarterly and yearly)

2) claims by referring provider by service provider

3) claims by monthly payments received for (1) and (2)

4) claims by month of services for (1) and (2)

Here is the dimsion model:

FactClaims

Charge Amount

Payment Amount

Service Date Key (FK)

Payment Date Key (FK)

Patient Key (FK)

Service Provider Key (FK)

Facility Key (FK)

Referred Provider Key (FK)

Dimension Tables:

DimServiceProvider ServiceProviderID (SK)

Service Provider Name

Speciality

DimPatient PatientID (SK)

Name

Address

DimDate

DimFacility FacilityID (SK, PK)

FacilityName

FacilityRegion

FacilityState

Questions: 1) Should i separate fact tables for Charges and Payments?

2) Not sure whether I am thinking correct for Referred Provider Key (which also points to DimServiceProvider)

3) Any rule of thumb to combine some of the dimension tables or separate them? what are the rules to combine Dimension tables or keep them separate?

---

Whether separate payments and charges depends on what kind of reports you are going to run. Also, did you consider payments/charges to the insurance, to secondary insurance, if applicable, and to the patient/person responsible for the patient?

If you keep Referred Provider Key, you should provide a special value for self-reffed patients.

There are no dimensions in your model that might be considered for consolidation.