DWH Loading dimension keys to fact table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: DWH Loading dimension keys to fact table

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447

    DWH Loading dimension keys to fact table

    Hi

    I have a qustion regarding DWH design and ETL processes.

    Letīs say I have a fact table call sales, a time dimension which is a calendar year with granularity up to hours with surrogate key from 1 to 8760 (8760 hours per year).

    In a ETL process how would you load this surrogate key in the fact table? In a procedural approach, to load 10 million rows 10 million queries would needed to run against the dimension table. Is this the proper way to do it?

  2. #2
    Join Date
    Mar 2002
    Posts
    534
    For time dimensions I always used a date column as FK/PK. I know that Kimball recommands to use a surrogate key for time dimensions but I think that it is much easier for the development and administration to use the date datatype for it.

    A workaround for your problem may be the use of table functions.
    http://download-west.oracle.com/docs...qlpl.htm#18796

    HTH
    Mike

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hi

    If you use date column as PK/FK what value do you store? To what granularity? Days, hours?

    If your granularity is days then we would have 365 calendar days such as 200506140000, 200506150000 etc.

    If in your sales fact table your sale date is 200506141300 how do you use that as FK since itīs not a PK? Or you have an extra attribute which truncate the timestamp to day granularity so you have a FK which corresponds to a PK?

    Cheers

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: DWH Loading dimension keys to fact table

    Originally posted by pando
    In a ETL process how would you load this surrogate key in the fact table? In a procedural approach, to load 10 million rows 10 million queries would needed to run against the dimension table. Is this the proper way to do it?
    Cant u use sysdate for this purpose?.. ( btw, how would you tie this fact table to dimension to get the surrogate key? )..

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I use surrogate keys as dimension PK and fact table FK thatīs why I say to load the fact table I must get the keys from the dimension.

    If I werent using a surrogate keys then I would use timestamp with granulary up to hour as dimension PK, in the fact table whenever I load a record I would load the timestamp and to_char(timestamp, 'YYYYMMDD24HH') and use the to_char part as FK

    The second approach is probably better (my conclusion) since I dont need to query the dimension during ETL process

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I was wondering who has experience with Snowflake model, i.e normalized dimensions?

Posting Permissions

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



Click Here to Expand Forum to Full Width