Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Posts
    4

    Historical data in datawarehouse

    Hi,

    I designed a datawarehouse that contains only current patient data (i.e. if some data is modified for a patient, the "new" data overwrites the "old" data) in the datawarehouse. This works fine, but the customer wants to have any historical data in addition to the current data in the warehouse.

    Do I need to build a separate warehouse that will contain historical data? What are the design considerations in such a case?

    I would be glad if someone can you point me some articles or books that can help me.

    Thx.

    Yanick

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    add a datetime column to the primary key, and populate the new rows with the timestamp of any change
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2004
    Posts
    4
    Hi,

    Thanks for the answer.

    Do you mean having more that one row in the fact table for a particular patient?

    Do you apply the same principle to weight tables? (i.e. : allergies, because a patient can have multiple allergies. If the patient's allergies change over time).

    How do you build cubes on such a datawarehouse? (since there are more than one row per patient in the fact table)

    Thx.

    Yanick

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Yanick Mongeau
    Do you mean having more that one row in the fact table for a particular patient?
    of course, each change in patient data is a new fact

    Quote Originally Posted by Yanick Mongeau
    Do you apply the same principle to weight tables? (i.e. : allergies, because a patient can have multiple allergies. If the patient's allergies change over time).
    yes

    Quote Originally Posted by Yanick Mongeau
    How do you build cubes on such a datawarehouse? (since there are more than one row per patient in the fact table)
    um...

    that all depends on which cubes you want, doesn't it?

    if you want one row per patient, then obviously you have to summarize something
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Is it me, or is a time dimension missing from this model?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •