4

I am fully aware of what is a fact, attribute and dimension. However, I'm quite confused to which traits I should choose for dimensions vs attributes of that dimension. For instance, I'm building a hospital data warehouse and gender could be a dimension. However, it can also be an attribute of the "patient" dimension.

I am aware that ONE advantage of choosing gender as a dimension is that I can save on space (i.e. there could be millions of patients, and thus millions of attributes). But, what if gender changes? How can I track that? Same with other attributes: diagnoses can be a dimension AND it could be an attrbiute. If I choose it as a dimension, how can I track a patient's flow/change of diagnoses??

Thank you so much

LearnByReading
  • 711
  • 10
  • 26

2 Answers2

1

To make gender a freestanding "dimension" is, more likely than not, an attempt to normalize your dimensional model. Unless you have attributes associated with gender then how is it a dimension? Is it possible to have a conformed gender dimension?

As for diagnosis are you modeling the process or the values? Very different requirements and issues to deal with. I would imagine a series of dimensions and facts to accurately keep track of the diagnosis process.

Disk space... considering our modern computing environment, space is your last optimization/design concern. If it truly is, you should know this for certain.

what if gender changes? How can I track that?

SCD Type 2 Dimension

how can I track a patient's flow/change of diagnoses?

Transactional or Accumulating Snapshot Fact table(s)

Dave
  • 2,399
  • 1
  • 12
  • 20
0

The basic question I ask to myself or the user when I gather the requirements in order to create a model and solve your doubt is "How much information do we want to keep about this now and if it is possible to foresee, in the future?"

If it is incidental information and you will not need to store much about it apart from the name, maybe you can keep it as an attribute of the corresponding entity. If you need to store specifically related information that, otherwise you would need to denormalize it heavily to be stored, then maybe better create a separated dimensional table with all the related information and then keep just the foreign key.

Anyway, as you ask for the case of a SCD, in either case you can historicize your table no matter if you are storing the information as an attribute or as a FK of a proper dimensional table. So for me the necessity of storing such changes wouldn't play any role in the decision making of having it as an attribute or a dimension.

LironCareto
  • 138
  • 7