Dimension Roles

View previous topic View next topic Go down

Dimension Roles

Post  veskojl on Wed Feb 08, 2012 8:07 am

I'm modeling health care data warehouse, which has patient and physician dimension. They share pretty much the same information: names,gender, dob, address, some identification number etc. And the question is whether to load them into different tables or combine into only one?
Sure, the attributes could change in future, but now they are the same?

Thanks

veskojl

Posts : 11
Join date : 2011-07-21

View user profile

Back to top Go down

Re: Dimension Roles

Post  ngalemmo on Wed Feb 08, 2012 10:41 am

In a general sense, yes they are the same, but there are significant privacy and legal issues based on the role. Information about a physician in the role of a physician is not as protected as the information about the physician as a patient. Same goes with employees as employees and employees as patients. Managing security becomes much more complicated if you attempt to treat all within the same dimension.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Dimension Roles

Post  veskojl on Wed Feb 08, 2012 11:41 am

Putting security aside I didn't come out with any reasonable argument that they can not be combined, even if the attributes differ a little bit.
I've already modeled them as separate tables, but felt tempted to create one table and add views on top of it.

So thanks for pointing that out.

veskojl

Posts : 11
Join date : 2011-07-21

View user profile

Back to top Go down

Re: Dimension Roles

Post  Jeff Smith on Wed Feb 08, 2012 12:33 pm

I wouldn't combine them into 1 dimension.

Combining them into 1 dimension doesn't really save any space. Dr. Johnny Fever the Physician and Dr. Johnny Fever the pateint will be 2 rows because Physician and patient have slightly different attributes.

On a fact table, you would have to have a dimension key for Physician and one for patient, so no savings there.

There's going to be a lot more patients than physicians and physicians will be queried much more frequently. Physicians will account for what 10% of the rows. You could create views and add indexes to improve performance, but they'll be a point where the work to make the views perform as fast as seperate tables is more than the work to make them seperate dimensions.


Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Dimension Roles

Post  BoxesAndLines on Wed Feb 08, 2012 2:18 pm

What Jeff said. They are different things. People often want to do the same thing for code tables. Bad idea. Additionally, Physician and Patient will be related to different facts. You've abstracted the model with no added value.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Dimension Roles

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