Managing some particular dimensions in a data warehouse based on medical records

  • Hi,

    I need to design a data warehouse solution to manage medical records about the hospital patients.

    For a such dwh, the medical records could represent the facts, the patients a dimension as also diagnosis data and procedure data. In particular, the diagnosis and procedure dimensions could be handled as a multi-valued dimension, but I hope to manage them in a more pragmatic/simple manner, if possible.

    Now, any suggests to me in order to manage the diagnosis and procedure dimensions in the "right" way, please?

    Moreover, does it exists any good specific book about designing a dwh for a healthcare context?

    Many thanks

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • In particular, the diagnosis and procedure dimensions could be handled as a multi-valued dimension

    How do you mean? Could you give an example of "a diagnosis being handled as a multi-valued dimension"?  Are you talking about groups of symptoms or something that relate to the diagnosis?

  • A data warehouse stores current and historical data from one or more systems in a predefined and fixed schema, which allows business analysts and data scientists to easily analyze the data.

    That begin said, You may be building data warehouse tables/views/Stored procedures/functions on the data warehouse database to include SQL server analyses, SSIS, and SQL server reporting.

    The data warehouse database would need to be refreshed often depending on your needs.    A proper data warehouse design would be good to provide robust reporting. (first/second/third/.. normal forms)  Consider High availability groups - SQL server Enterprise / replication processes / etc.

    Here are some examples to get you started:

    + Multi-valued dimensions occur when a fact table row may need to refer to more than one row in a dimension table.

    +Patients can have multiple diagnoses.

    I would definitely start with a good prototyping tool / database design document.  Requirement analysis is a must.

    This could be handled with proper entity design diagram / database design tools.  (ERWIN)

    I hope this helps.



  • There really isn't an "easier" way to deal with have a many-to-many relationship because of the nature of the data, and you have to model it accordingly.

    I'd recommend that you start with the following as guidelines, and then refine it to get whatever measures/reports/analytics you need to get out of it:

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply