Physician Network Data Modeling

Hello,

I have a question about how to get started on a dimensional model that describes a physician network.

The basic idea is to have a data model that represents the relationships between a physician(s) and a
a facility(ies).

There are no measures - other than a count of physicians or sites.    The relationships are time-sensitive,
meaning a physician is associated with a facility for a certain period of time (see begin date, end date).

Here's an example of tables from the OLTP system that I want to model into a data mart.

Note : This is a simplified example but should get the basic design pattern started.


thanks
The Hurricane

tPhysician
----------
PhysicianID
PhysicianName

tFacility
----------
FacilityID
FacilityName


tMapPhysicianFacility
---------------------
FacilityID
PhysicianID
BeginDate
EndDate

It's also how you do it in a dimensional model. tMapPhysicianFacility would be considered an accumulating snapshot fact representing a business state.

ngalemmo wrote:It's also how you do it in a dimensional model.  tMapPhysicianFacility would be considered an accumulating snapshot fact representing a business state.

Thanks.   I was wondering if I had to move the date ranges to a dimension (SCD2) as there may be multiple time spans that a physician is related to a facility.

For example :  

tMapPhysicianFacility
---------------------
FacilityID
PhysicianID
BeginDate
EndDate

May have two rows for a FacilityID/PhysicianID combo representing two time periods of the pairing

FacilityId=1
PhysicianId=1
BeginDate=1/1/10
End Date = 1/31/10

FacilityId=1
PhysicianId=1
BeginDate=9/1/10
End Date = 10/31/10

Yeah, that's fine. The dates are degenerate dimensions.

ngalemmo wrote:It's also how you do it in a dimensional model.  tMapPhysicianFacility would be considered an accumulating snapshot fact representing a business state.

Thank you.   One more twist on the question.   Let's say we have another entity to account for "Contract".
This time-sensitive contract mapping is related to the physician, but not facility.


tPhysician
----------
PhysicianID
PhysicianName

tFacility
----------
FacilityID
FacilityName

tContract
----------
ContractID
ContractName

tMapPhysicianFacility
---------------------
FacilityID
PhysicianID
BeginDate
EndDate

tMapPhysicianContract
---------------------
PhysicianID
ContractID
BeginDate
EndDate


Data for tMapPhysicianFacility :

FacilityId=1
PhysicianId=1
BeginDate=1/1/10
End Date = 1/31/10

FacilityId=1
PhysicianId=1
BeginDate=9/1/10
End Date = 10/31/10

Data for tMapPhysicianContract :



ContractId=1
PhysicianId=1
BeginDate=1/1/10
End Date = 12/31/10


Would it be a decent approach to combine both maps into a single fact table?
For example :


FactNetworkMap
------------------
PhysicianID
FacilityID
PhysicianFacilityBeginDate
PhysicianFacilityEndDate
ContractID
PhysicianContractBeginDate
PhysicianContractEndDate

Obviously some of the data from tMapPhysicianContract
would repeat in the resultant fact table

PhysicianID   FacilityID   PhysicianFacilityBeginDate   PhysicianFacilityEndDate  ContractID  PhysicianContractBeginDate  PhysicianContractEndDate

1                  1              1/1/10                             1/31/10                        1                 1/1/10                              12/31/10
1                  1              9/1/10                            10/31/10                       1                1/1/10                              12/31/10

You can if the existence of a contract implies the physician is at that facility. But there would only be one set of dates, not two. The dates would represent the period the physician was at that facility with that contract. If one of those changes, there would be a new row.

ngalemmo wrote:You can if the existence of a contract implies the physician is at that facility.  But there would only be one set of dates, not two.  The dates would represent the period the physician was at that facility with that contract. If one of those changes, there would be a new row.

Interesting. So if the second set of dates (for contract) were for something else, say the physician's contract with some other company
could we include them in the fact table? Or would that be a seperate fact?

thanks
Hurricane

The dates in the fact should represent when the particular state exists. If the contract itself has dates they are attributes of the contract, not the effective period of the fact row. Mixing the two on the fact row just makes things really confusing.