Incremental update of a dimension

• Bruce Gilpin

  Mr or Mrs. 500

  Points: 558

  More actions

  June 29, 2004 at 8:42 am

  #152206

  I have an account table that looks like this (the ddl isn't exactly right but you get the idea

     Acct_id  identity not null PK,

     Acct_no varchar(10) not null,

     Acct_name varchar(50) not null,

     Effective_from datetime not null,

     Effective_to datetime not null

  This table links to the sales table with a view that consists Acct_no column where effective_to is set to high values (2999-12-31).  When the Acct_name column changes, a new row is added and the effective_to date of the old row is set to the system date.

  My question is, since the value of the Acct_no column doesn't change, is the new account name picked up in an incremental update of the dimension?

• Jamie Thomson

  SSChampion

  Points: 11805

  More actions

  June 29, 2004 at 10:40 am

  #512493

  It will be picked up. However, if in your AS dimension you have:

  Member Key Column = Acct_id

  Member Name Column = Acct_name

  then you will get an error when you try the incremental update.

   

  Regards

  Jamie Thomson

  Jamie Thomson
  http://sqlblog.com/blogs/jamie_thomson

• Bruce Gilpin

  Mr or Mrs. 500

  Points: 558

  More actions

  June 29, 2004 at 12:51 pm

  #512517

    Thanks, thats the answer I was hoping for.