Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Best option/practice to add a new field to a Fact table

  1. #1

    Question Best option/practice to add a new field to a Fact table

    Hi all.

    I would like like to know what is the best option/practice to add a new field to a Fact table without having to reprocess everything.

    Could someone one point me to the right direction?

    Thanks in advance.

  2. #2
    Join Date
    Aug 2011
    Posts
    360

    Default

    Hi,

    could you be more specific please?
    What do you mean "without having to reprocess everything"? You're talking about modifiying your pdi transformations or reloading all the data?

    If you add a mesure on the fact table, you'll need to:
    1. add the field on the table (that was the easy part :-))
    2. if possible create a sql script to bulk update the field for all existing rows (if you want to)
    3. add the field in you loading job.

    If you add a new dimension, this could be more tricky since:
    1. you need to add the FK on the fact table (start by adding a new field with null values). Maybe you can default to the FK id corresponding to your "UNKNOWN" or "NOT_APPLICABLE" entry in your dimension
    2. Then it depends if you can get the information of your new dimension from the existing dimensions linked to the fact table or not. If you can, you should be able to make an update script to bulk load your new dimension and then bulk update the keys on the fact table.
    If this is a totaly new dimension (for example, because there is new fields on source OLTP wich translate to a new dimension), then you dont maybe dont need to update the fact table for existing records (leave the FK NON APPLICABLE), since this information didn't exist at the time these facts where loaded.
    3. you surely need to add the loading of this dimension and modify the loading of fact table

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.