Physician Network Data Modeling

View previous topic View next topic Go down

Physician Network Data Modeling

Post  hurrican3dev on Fri Nov 21, 2014 12:57 pm

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

hurrican3dev

Posts : 17
Join date : 2012-08-26

View user profile

Back to top Go down

Re: Physician Network Data Modeling

Post  ngalemmo on Fri Nov 21, 2014 1:49 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Physician Network Data Modeling

Post  hurrican3dev on Fri Nov 21, 2014 1:53 pm

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

hurrican3dev

Posts : 17
Join date : 2012-08-26

View user profile

Back to top Go down

Re: Physician Network Data Modeling

Post  ngalemmo on Fri Nov 21, 2014 2:12 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Physician Network Data Modeling

Post  hurrican3dev on Fri Nov 21, 2014 2:25 pm

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

hurrican3dev

Posts : 17
Join date : 2012-08-26

View user profile

Back to top Go down

Re: Physician Network Data Modeling

Post  ngalemmo on Fri Nov 21, 2014 6:13 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Physician Network Data Modeling

Post  hurrican3dev on Sun Nov 23, 2014 5:32 pm

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


hurrican3dev

Posts : 17
Join date : 2012-08-26

View user profile

Back to top Go down

Re: Physician Network Data Modeling

Post  ngalemmo on Sun Nov 23, 2014 11:22 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Physician Network Data Modeling

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum