Post reply

Populating Fact Table with keys from Bridge Table?

  • December 15, 2006 at 4:03 pm

    #115606

    Hi, I am wondering if someone can give me a hand on how to populate a fact table with the surrogate keys of a bridge table. The CLAIM Stage table has 9 fields for possible DRG Codes. Many of these can be blank.

    CLAIM STAGE TABLE

    CLAIMNO, DRG1,DRG2,DRG3,DRG4,DRG5,DRG6,DRG7,DRG8,DRG9

    FACTCLAIM TABLE

    CLAIMNO, DRGGROUPKEY (FK)

    DRG GROUP BRIDGE TABLE

    DRGGROUPKEY,DRGKEY

    DRG DIMENSION TABLE

    DRGKEY,DRG,DESCR

    I think I would want to populate the DRGGroup table with all the unique DRG group's found in the CLAIM STAGE TABLE but exclude the blank values. Then I would have to do a lookup on the DRGGROUP TABLE to update the FACTCLAIMNO table's DRGGROUPKEY (FK) field as the records are being inserted into the table. I have a rough idea of how to do this, but was wondering if any TSQL Guru's could offer their advice on this situation both excluding the blank values for the DRG GROUP TABLE and on the best way to do the lookup when populating the FACTCLAIM table. I also have SSIS if that helps at all. Any help is very much appreciated. Thanks!

  • December 15, 2006 at 5:15 pm

    #678490

    I think I'd go the user defined function route:

    First, your group bridge table is probably normalized, so I'd write a function that concatenates your DRGkey into a sorted, comma delimited string (say varchar(1000).

    Then you can put these into a temp table for ease of use

    groupkey  | grouplist

    123          "1,5,8,7"

    157          "2,5,8,10"

    Then I'd create a function for use on the stage table that looks up, sorts, and comma delimits the drg. Thus

    row:

    claimno, drg1, 2, 3, ...

    123, 'abc', 'def', '', 'xcw', 'blm'

    using udf function becomes:

    claimno, drg1, 2, 3, ... MyudfStringit(drg1,drg2,drg3...)

    123, 'abc', 'def', '', 'xcw', 'blm',... '1,2,7,20'

    Then you can join using that udf to the temp table to get the group key to assign.

    (and most of that can be done efficiently with embedded subselects if the tables aren't too big).

    If you don't have a bridge group table then make it (/update it) in a first pass using the udf on the claim table.

    (Disclaimer: Now this advice is based on the limited understanding I was able to get from your limited description of the situation.)

Viewing 2 posts - 1 through 1 (of 1 total)

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