Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2007
    Posts
    348

    renormalizing denormalized data

    Good afternoon (but just barely for me),
    I'm encountering a problem that I have previously, data that has been warehoused and thus denormalized but needs to be normailized for a query I want to run. This seems pretty common in healthcare warehouses, I don't know if it exists as prominently in other types of warehousing systems. Below is an example:

    Data as it may have existed previously
    PatientID..Diagnosis....Treatment
    1............Headache....Sent Home
    2............Broken Arm..Cast
    1............Migraine......Medicated
    1............Unconscious.Revived

    Data as it is warehouse
    PID.Diag1.Diag2.Diag3.Treat1.Treat2.Treat3
    1....Hach..Migr..Unc...Home...Med....Rev
    2....BArm.................Cast...................

    If I want to retrieve patients with diagnoses or treatments or a value list of either from the warehouse I typically do it with UNION statements, something like:
    SELECT Diag1 FROM warehouseTable
    UNION
    SELECT Diag2 FROM warehouseTable
    UNION
    SELECT Diag3 FROM warehouseTable

    Today I've had some free time so I've been looking at ways of renormalizingthe data from the warehouse or generating a value list that would be more efficient, intelligent and reusable. In my head it would go something like

    columnNameRoot AS Diag
    FOR Num=1; Num<=3; Num++
    SELECT columnNameRootNum FROM warehouseTable
    LOOP

    Now I realize I'm mixing all manner of code that doesn't belong together there but it should make roughly logical sense. The idea is that I could just change the variable name.

    This seems like something that a lot of people have probably come up against and come up with interesting and cunning solutions to. I was hoping to find some kind of standard solution that was better than my UNION solution.

    I didn't place this under a specific database topic because it seems like it could happen in nearly any database system (I've seen it in at least 3 different ones)

    Thanks for taking the time to look at this

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Run your query against the DW staging tables. The normalized data should already exist there.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2007
    Posts
    348
    to my knowledge, I've not had access to the staging areas of any datawarehouse I have queries against. I would think that would be very atypical, giving warehouse users staging access.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Search Jeff Smith's blog for some tips on normalising denormalised data. Also your RDBMS is significant - I think UNPIVOT is an option in SQL Server.

  5. #5
    Join Date
    Feb 2007
    Posts
    348
    Thanks for the refernce Pootle I will take a look to see what I can find.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    They're calling that a data warehouse? I think I just vomited a bit in my mouth.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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