Post reply

Datawarehouse Denormalization

  • November 6, 2015 at 3:34 am

    #322132

    Hi,

    I Have a table which I've de-normalized for my DimDestinationLocations. see Below

    How ever due to the amount of potential repeating rows I'm tempted to

    load out the distinct address's into another table and have a look up.

    For every booking there would be a Collection and Destination address, this table is going to get very large.

    Is there any room to do this in Kimball methodology or is it just bad practise.

  • November 6, 2015 at 3:36 am

    #1838175

    Couldn't insert the image for some reason

    See Below

  • November 6, 2015 at 3:42 am

    #1838176

    SimonH (11/6/2015)

    Hi,

    I Have a table which I've de-normalized for my DimDestinationLocations. see Below

    How ever due to the amount of potential repeating rows I'm tempted to

    load out the distinct address's into another table and have a look up.

    For every booking there would be a Collection and Destination address, this table is going to get very large.

    Is there any room to do this in Kimball methodology or is it just bad practise.

    Could you have a dim.Location table and then, in fact.Booking, add CollectionKey and DestinationKey, both FKs to dim.Location?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • November 6, 2015 at 3:55 am

    #1838179

    Thanks Phil.

    That's the plan,

    However

    If a company has a warehouse say in Southampton, and they were sending

    packages from Manchester say every day/every hour.

    The Manchester and Southampton addresses are going to be repeated 1000's of times in the Dimlocations table, each with a unique Natural Key (Booking Key)

    And a surrogate key

    to the Bookings Fact table.

    My Question is could I have just the distinct addresses in DimLoocations,

    and have a bridging table (containing the keys) to the Fact table?

    Or Should I just leave repeating addresses in Dimension table?

    Thanks,

  • November 6, 2015 at 5:16 am

    #1838188

    SimonH (11/6/2015)

    Thanks Phil.

    That's the plan,

    However

    If a company has a warehouse say in Southampton, and they were sending

    packages from Manchester say every day/every hour.

    The Manchester and Southampton addresses are going to be repeated 1000's of times in the Dimlocations table, each with a unique Natural Key (Booking Key)

    And a surrogate key

    to the Bookings Fact table.

    My Question is could I have just the distinct addresses in DimLoocations,

    and have a bridging table (containing the keys) to the Fact table?

    Or Should I just leave repeating addresses in Dimension table?

    Thanks,

    Assuming that the repetition of addresses is merely a shortcoming of the front-end system and not something that you will ever want to analyse in the DW, I would get rid of the dupes on their way in to the DW.

    As I suggested, if the booking fact table contains FKs as necessary to the de-duped addresses dimension, you have just made things a whole lot simpler without losing anything useful.

    Of course, your ETL process will have to implement a lookup on the locations dim (and have a way of creating new ones before the booking fact population).

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • November 6, 2015 at 9:06 am

    #1838261

    SimonH (11/6/2015)

    Thanks Phil.

    That's the plan,

    However

    If a company has a warehouse say in Southampton, and they were sending

    packages from Manchester say every day/every hour.

    The Manchester and Southampton addresses are going to be repeated 1000's of times in the Dimlocations table, each with a unique Natural Key (Booking Key)

    And a surrogate key

    to the Bookings Fact table.

    My Question is could I have just the distinct addresses in DimLoocations,

    and have a bridging table (containing the keys) to the Fact table?

    Or Should I just leave repeating addresses in Dimension table?

    Thanks,

    That really depends on your requirements and how that data is stored/represented on the front end. For example if you normalize the address information and link it with a fact table if that address changes it will change all the historical data, that may or may not be desired in a datawarehouse....

  • November 6, 2015 at 9:41 am

    #1838270

    That really depends on your requirements and how that data is stored/represented on the front end. For example if you normalize the address information and link it with a fact table if that address changes it will change all the historical data, that may or may not be desired in a datawarehouse

    The Address Is linked to a booking so it's added everytime a booking is made.

    It just so happens some client make multiple bookings to the same destination. So that wouldn't be an issue.

    Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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